Data Engineer II (Hybrid)

American Medical Association; Chicago (IL); Hybrid; USD 85,000 - 113,000; Full time

Job summary

American Medical Association in Chicago, IL is hiring a Data Engineer II (Hybrid) to build data pipelines, automate file-based workflows, and support reporting needs for AMA Insurance.

The role focuses on data ingestion, transformation, and validation of structured and semi-structured data, using Snowflake, SQL Server, and scripting in PowerShell and Python. The position requires 3 days in the office weekly.

Qualifications

• Strong experience with SQL Server for complex queries and data updates.
• Hands-on experience loading, staging, and extracting data with Snowflake.
• Proficient in PowerShell scripting for automation and system integrations.
• Proficient in Python scripting for automation and data handling.
• Experience with FTP servers and external system integrations.
• Solid understanding of data quality, validation, and transformation.

Responsibilities

• Data Preparation and Management: process, clean, and transform Excel/CSV files for claims and administration data; identify duplicates and quality issues; load and transform data in Snowflake; run manual and scheduled Snowflake jobs; generate outbound CSV outputs.
• PowerShell Automation: develop and maintain PowerShell scripts; automate file transfers, FTP interactions, and notifications; build automation to improve efficiency.
• SQL Server Development and Python Scripting: write and optimize SQL queries; update backend tables; prepare datasets for analytics; develop Python scripts for file-based automation and end-to-end workflow automation.
• Other responsibilities as assigned.

Skills

SQL Server

Snowflake

PowerShell

Python

FTP

Data quality

Interpersonal skills

Project multitasking

Tools

Snowflake

Job description

Data Engineer II (Hybrid)

Chicago, IL

AMA Insurance (AMAI) offers life, health and disability insurance at affordable and exclusive rates to help doctors achieve a healthy and secure financial future. AMAI is part of the American Medical Association (AMA), a nonprofit, and the nation's largest professional Association of physicians. We are a unifying voice and powerful ally for America's physicians, the patients they care for, and the promise of a healthier nation.To be part of the AMA is to be part of our Mission to promote the art and science of medicine and the betterment of public health.

As an employer, we are dedicated to many efforts, including employee learning and development, social responsibility, diversity and inclusion and wellness.
Our well-defined culture has strongly impacted the prosperity of our organization.Our foundational values of Respect, Integrity, Innovation, Impact, Collaboration, Agility and Trust are at the core of our efforts and continue to shape the success of the AMA.

We have an opportunity at our corporate offices in Chicago for a Data Engineer II (Hybrid) on our AMA Insurance team. This is a hybrid position reporting into our Chicago, IL office, requiring 3 days a week in the office.

This role is responsible for data ingestion,transformation, automation, and validating file based integrations. This rolefocuses on building and maintaining automation scripts for file extractions,managing structured and semi structured data files, and supporting reportingneeds across the agency.This role requires working knowledge and experience in their job discipline with broadened capabilities. Performs a range of assignments related to the job discipline and uses prescribed guidelines or policies inanalyzing situations. This role receives a moderate level of guidance and direction and continues to build knowledge of the AMA Mission and processes.

RESPONSIBILITIES

• Data Preparation and Management
  • Process and clean Excel, CSV, and other flat files for various claims and administration related data with high volumes
  • Identify and resolve duplicates, inconsistencies, and data quality issues within these files
  • Transform files to required schemes and formats for downstream consumption
  • Load, stage, update, extract data in Snowflake
  • Perform manual and scheduled Snowflake runs
  • Prepare and transform rate data across multiple processing stages and generate outbound data files and upload CSV outputs to EXL
• PowerShell Automation
  • Develop and maintain PowerShell scripts from scratch and enhance existing scripts as needed
  • Automate file transfers, processing FTP interactions, directory management, and email notifications
  • Build automation solutions to improve operational efficiency
• SQL Server Development and Python Scripting
  • Write and optimize SQL queries, including complex joins and transformations
  • Update backend tables and support data corrections
  • Prepare reports and datasets for analytics and operational needs
  • Support database troubleshooting and data validation activities
  • Develop Python scripts for file-based automation, including file movement, copying, editing, and integration tasks
  • Support end to end automation of data and file workflow
• May include other responsibilities as assigned

s

REQUIREMENTS

1. Strong experience with SQL Server (complex queries, reporting, data updates)
2. Hands on experience with Snowflake data loading, staging, and extraction
3. Proficient in PowerShell scripting for automation and system integrations
4. Proficient in Python scripting for automation and system integrations
5. Proficient in Python scripting for file handling and automation
6. Experience working with FTP servers and external system integrations
7. Solid understanding of data quality, validation, and transformation
8. Demonstrated ability to multi-task and effectively manage multiple, concurrent projects with minimal supervision
9. Polished interpersonal skills, including proven liaison and relationship building skills
10. Written and verbal communications skills, including ability to collaborate with technical and nontechnical colleagues

The American Medical Association is located at 330 N. Wabash Avenue, Chicago, IL 60611 and is convenient to all public transportation in Chicago.

This role is an exempt position, and the salary range for this position is $85,396 $112,925. This is the lowest to highest salary we believe we would pay for this role at the time of this posting. An employee’s pay within the salary range will be determined by a variety of factors including but not limited to business consideration and geographical location, as well as candidate qualifications, such as skills, education, and experience. Employees are also eligible to participate in an incentive plan. To learn more about the American Medical Association’s benefits offerings,

We are an equal opportunity employer, committed to diversity in our workforce. All qualified applicants will receive consideration for employment. As an EOE/AA employer, the American Medical Association will not discriminate in its employment practices due to an applicant’s race, color, religion, sex, age, national origin, sexual orientation, gender identity and veteran or disability status.

THE AMA IS COMMITTED TO IMPROVING THE HEALTH OF THE NATION