Healthcare Data & ETL Platform
Swing Therapeutics · Lead Software Engineer / Primary Contributor
Swing Therapeutics | Data Engineering / Cloud Infrastructure
Built and operated a Python batch ETL platform that transformed versioned JSON exports into relational staging schemas and analytical MySQL warehouse datasets across AWS Lambda and ECS Fargate.
Overview
A two-stage Python batch ETL platform that loads versioned JSON exports from object storage into relational staging schemas, then transforms the data into an analytical MySQL warehouse. The same core logic could run through local tooling, AWS Lambda, or an ECS Fargate container.
My Contribution
- Operated and extended a two-stage ETL pipeline that converted nested JSON exports into relational staging schemas and warehouse-ready datasets
- Implemented idempotent loading through upserts, duplicate guards, parent-child integrity checks, and transactional commit/rollback
- Hardened ingestion for malformed JSON and evolving source schemas
- Packaged the pipeline for both AWS Lambda and ECS Fargate using Serverless Framework, Docker, ECR, and S3
- Extended warehouse transforms, backups, indexes, staging retention, and support for an additional data track
Technical Architecture
- Versioned JSON exports in object storage (S3)
- Stage-one inspection and relational staging load
- Ordered stage-two SQL transforms and upserts
- Analytical MySQL warehouse with views and integrity filters
- Runtime options: local CLI, AWS Lambda, or ECS Fargate
Engineering Challenges
- Flattening nested JSON into relational schemas under schema drift
- Idempotent warehouse loading with duplicate and orphan prevention
- Long-running jobs that exceed Lambda-friendly execution patterns
- Malformed source data and operational backups / staging retention
- Deploying the same pipeline across Lambda and container runtimes
Reliability, Privacy, and Security Considerations
Batch healthcare data pipelines require careful access control, durable logging, and conservative public documentation. This portfolio omits real schemas, object-storage paths, account identifiers, clinical field names, and throughput metrics.
Engineering Outcomes
- Improved repeatability and resilience of batch warehouse loads
- Supported idempotent transformation of evolving JSON data
- Enabled the same pipeline to run in serverless and container environments
- Improved maintainability of staging and warehouse transformations
Technologies
- Python, Docker, boto3, PyMySQL, mysql-connector-python
- MySQL, Amazon RDS, Amazon S3, Amazon ECR
- AWS Lambda, ECS Fargate, Serverless Framework, CloudWatch Logs