Orchestrating Healthcare Data: The PECOS AWS Glue and Step Functions Pipeline

PECOS Data Extraction Pipeline - DevOps Documentation

The PECOS Data Extraction Pipeline is an enterprise-grade ETL workflow that extracts and transforms healthcare provider data from CMS datasets. The system utilizes AWS Step Functions to orchestrate four parallel PySpark jobs running on AWS Glue. Each parallel branch is configured with a 3-retry policy and 2x exponential backoff to ensure pipeline resilience.

Why This Matters

Ideal ETL models often assume linear, error-free data flows, but technical reality involves handling inconsistent healthcare records across multiple datasets. The PECOS pipeline addresses this by implementing a parallel state machine that isolates failures to specific datasets without halting the entire workflow. This serverless architecture optimizes costs by using Glue G.1X workers and Snappy compression, reducing storage overhead while providing the 16GB RAM necessary for complex window-based windowing operations. By automating the deployment via a bootstrap script, engineers can maintain consistent environments across local, Docker, and AWS production stages.

Key Insights

• AWS Step Functions orchestrates four parallel ETL tasks with 3 retries and exponential backoff to handle transient cloud failures.
• AWS Glue G.1X workers provide 1 DPU and 16GB RAM, sufficient for windowing functions and deduplication tasks.
• Data quality is maintained by zero-padding NPIs to 10 digits and deriving credentials from enrollment dates.
• The pipeline outputs Snappy-compressed Parquet files partitioned by state to optimize downstream query performance.
• A bootstrap script automates IAM role creation and S3 artifact syncing to ensure the principle of least privilege.

Working Examples

AWS CLI command to create a serverless Glue ETL job for the Clinicians dataset.

aws glue create-job --name "PECOS-Clinicians-ETL" --role "arn:aws:iam::ACCOUNT_ID:role/PECOSGlueRole" --command "{\"Name\": \"glueetl\",\"ScriptLocation\": \"s3://$BUCKET/spark_jobs/glue_clinicians.py\",\"PythonVersion\": \"3\"}" --default-arguments "{\"--config\": \"s3://$BUCKET/config/pipeline_config.yaml\",\"--additional-python-modules\": \"pyyaml\",\"--enable-continuous-cloudwatch-log\": \"true\"}" --glue-version "4.0" --number-of-workers 2 --worker-type "G.1X"

Environment-aware pipeline configuration supporting both local and AWS paths.

pipeline:\n  name: "PECOS-Extraction-Pipeline"\n  environment: "local"\n  paths:\n    base_input_dir: "s3://bucket/data/input"\n    base_output_dir: "s3://bucket/data/output"\n  datasets:\n    clinicians:\n      input_file: "clinicians.csv"\n      partition_by: ["state"]\n      primary_key: "npi"

Practical Applications

• Use case: CMS PECOS healthcare data ingestion using AWS Glue for high-throughput analytics. Pitfall: Granting wildcard S3 permissions instead of scoping to the specific pipeline bucket.
• Use case: Local PySpark development using Docker to simulate EMR-like environments for cost-free testing. Pitfall: Using Python 3.12 locally when Glue 4.0 requires Python 3.11.
• Use case: Automated notifications for ETL failure using Amazon SNS to alert engineering teams. Pitfall: Failing to confirm SNS email subscriptions resulting in missed critical alerts.

References:

• https://github.com/durrello/PECOS-Data-Extraction-Pipeline
• https://dev.to/durrello/pecos-data-extraction-