A reproducible and scalable pipeline for processing administrative health claims data
Kaur, Mahima; Nalluri, Shreya; C. Kitch, James; M. Tapera, Tinashe; Gilmour, Jonathan; Audirac, Michelle; Braun, Danielle
- Publisher
- Zenodo
- Language
- en
Abstract
Administrative data from the Centers for Medicare and Medicaid Services (CMS) [1] are a cornerstone of epidemiological and health outcomes research in the United States. These datasets support extensive analyses of healthcare utilization, policy impacts, and disease burden [2], covering hundreds of millions of beneficiaries. However, the complexity, privacy protections, and inconsistent coding across years in these datasets hinder reproducibility and require significant preprocessing efforts. Furthermore, the under-utilization of scalable pipelines leads to repeated data cleaning across different research groups, resulting in duplicated efforts and inefficiencies. To address these challenges, we developed a modular, open-source, serverless, and containerized data pipeline that streamlines the CMS Medicare data lifecycle from raw file ingestion to analysis-ready output. The pipeline currently focuses on two key CMS Medicare data sources: the Master Beneficiary Summary File (MBSF), which provides enrollment and demographic information, and the Medicare Provider Analysis and Review (MedPAR) file, which contains inpatient and skilled nursing facility (SNF) admissions. The pipeline begins by processing raw Medicare data, parsing fixed-width .dat files with .fts metadata into columnar Parquet format [3]. In the next step, harmonization is driven by a declarative YAML configuration that resolves year-to-year structural inconsistencies through field-level transformation rules. This enables consistent handling of discrepancies in variable naming (e.g., bene_zip vs. zip_cd), data encodings (e.g. string vs. numeric date formats) and storage structure(e.g., whether monthly indicators are split across columns or packed into strings). The harmonized datasets are then normalized into three core datasets - beneficiaries, enrollment, and admissions. These datasets are validated to remove records with missing or conflicting identifiers and resolve demographic inconsistencies such as multiple birth dates or contradictory race codes for the same beneficiary. The pipeline generates materialized views, which are precomputed, query-optimized data products that integrate enrollment, admissions, and disease-specific admissions information. These reusable, analysis-ready datasets enable rapid cohort construction, longitudinal follow-up, and outcomebased analytics. Each step is implemented in a modular, version-controlled script (e.g., via Git), with workflow orchestration handled by Snakemake [4] (for reproducibility and scalability), and Docker [5] (to ensure portability across computing environments). Furthermore, we leverage DuckDB [6] as the central execution engine in all pipeline steps, enabling efficient in-process SQL queries over Parquet files with support for year-wise partitioning and batch processing, avoiding the need for a centralized database infrastructure. In conclusion, by enabling direct querying of materialized datasets without repeatedly accessing raw files, the pipeline supports efficient, large-scale, and scalable analyses while automating key preprocessing steps and reducing redundant data engineering. The pipeline further promotes transparency, traceability, and reproducibility, aligning with the FAIR (Findable, Accessible, Interoperable, Reusable) [7] data principles, and lowers the barrier to working with complex administrative health data.
Full text
A reproducible and scalable pipeline for processing administrative health claims data Department of Biostatistics, Harvard T.H. Chan School of Public Health, Boston, MA Department of Data Science, Dana-Farber Cancer Institute, Boston, MA US-RSE’ 2025 Research Software Engineering Conference Mahima Kaur, Shreya Nalluri, James C. Kitch, Tinashe M. Tapera, Jonathan Gilmour, Michelle Audirac, Danielle Braun
2 Motivation Briefly: 70+ projects targeting climate adaptation solutions, 200+ publications, 8,000+ dataset downloads, 30,000+ software downloads 20+ Principal investigators 20+ Post-Postdoctoral fellows 20+ PhD & Master’s students 10+ Research scientists & collaborators 5+ Visiting students 5+ Staff members Team overview •Maintain and integrate large climate and health data inventories. • Build end-to-end, open-source pipelines for scalable, reproducible analytics. • Deliver containerized workflows for CMS and other large datasets. • Share FAIR, analysis-ready datasets via Harvard Dataverse and open code on GitHub. •Develop tools for machine learning and causal inference research. • Drive impactful, policy-relevant science in climate and health.
•Access: purchased annually and delivered in large batches •Scale: covers >60M beneficiaries in total •Breadth: contains enrollment, demographics, utilization, prescriptions and admissions data. • Research impact: • Used extensively in epidemiology, health economics, and outcomes research. • Supports policy evaluation (payment reforms, coverage expansions). https://data.cms.gov/infographic/medicare-beneficiaries-at-a-glance 3 Background Example dataset: CMS Medicare data
Inconsistent data quality across batches Privacy and confidentiality limitations Schema drift & structural inconsistencies Lack of interoperability with FAIR standards Scale & performance bottlenecks Delayed access to analysisready data 4 Challenges for the research community using CMS data Challenges
5 Solution Security restrictions prevent sharing of preprocessed datasets → hinders reuse and collaboration Gap: Our solution: Online Analytical Processing (OLAP) server-less health data pipeline; a modular, self-service and containerized data pipeline → producing analysis-ready datasets without redundant engineering
5 Solution Security restrictions prevent sharing of preprocessed datasets → hinders reuse and collaboration Gap: Our solution: Online Analytical Processing (OLAP) server-less health data pipeline; a modular, self-service and containerized data pipeline → producing analysis-ready datasets without redundant engineering
6 Generalizable data pipeline
7 Modular data pipeline • Independent, loosely-coupled stages; easier debugging & faster iteration • Parallelizable modules; more efficient, costeffective execution • Built-in validation & provenance; traceable, reliable outputs • Data products are reusable & self-contained from the start • Consumers can interact at different levels of granularity Building blocks
8 Parquet warehouse + DuckDB engine Why Parquet? • Columnar format with high compression • Embedded schema • Scales to very large historical datasets • Supported across Python, R, Spark, SQL engines Why DuckDB? • Parallel execution, blazing speed • Columnar + vectorized query execution reduces CPU cycles • Optimized for OLAP queries (aggregations, joins, scans) • No external server setup needed • Handles larger-than-memory datasets directly from Parquet • Runs anywhere (HPC, cloud, local machines) https://blog.dataengineerthings.org/i-spent-8-hours-learning-parquet-heres-what-i-discovered-97add13fb28f
14 Open science & FAIR data infrastructure Execution & orchestration Data processing & storage Reproducibility & portability Open-Source Tech Stack
15 Key takeaways Serverless, containerized pipeline designed to standardize and deliver AI/ ML-ready datasets at unprecedented speed. •Accelerates research across key domains, including environmental health, hospitalizations, causal inference, and spatial epidemiology. •Streamlines data workflows for large-scale epidemiology teams, enabling seamless collaboration across research labs. •Modular architecture eliminates duplication, reduces computational waste, and ensures optimal resource utilization. •Fully aligned with FAIR principles and Open Science best practices, supporting transparency, reproducibility, and data reusability. AI/ML-Ready Data Pipeline for Scalable Scientific Research
16 Thanks for listening. This work used the computing resources of Harvard University Research Computing and Data Services (HURC) and Harvard FAS Research Computing (FASRC). Funding Sources: •Air Pollution, Heat, Cold, and Health: Disparities in the Rural South R01MD016054 supplement R01MD016054 S2 •National Cohort Studies of Alzheimer's Disease, Related Dementias and Air Pollution R01AG066793, supplement #1 R01AG066793 S1, supplement #2 R01AG066793 S2 •Characterizing the link between multiple environmental exposures and Parkinson's disease exacerbation R01ES034373 •The confluence of extreme heat/cold on the health and longevity of an Aging Population with Alzheimer’s (and related Dementia) R01AG074372 National Studies on Air Pollution and Health (NSAPH): https://hsph.harvard.edu/research/air-pollution-health/ NSAPH Harvard Dataverse: https://dataverse.harvard.edu/dataverse/nsaph NSAPH GitHub: https://github.com/NSAPH-Data-Processing
