Automatic ETL Pipeline Generation for Mapping Heterogeneous Clinical Data into the OMOP Common Data Model

Area of Science:

• Biomedical Informatics
• Data Science
• Clinical Research Informatics

Background:

• Standardizing heterogeneous clinical data is crucial for large-scale research.
• The OMOP Common Data Model (CDM) facilitates data standardization.
• Current ETL processes for OMOP CDM are often manual and complex.

Purpose of the Study:

• To develop an automated, mapping-driven ETL pipeline for converting CSV clinical datasets into OMOP CDM v5.4.
• To improve the efficiency, reproducibility, and maintainability of ETL processes for OMOP CDM.

Main Methods:

• Developed a Python-based ETL pipeline utilizing metadata processing and dbt-generated SQL models.
• Implemented a layered architecture (raw, staging, intermediate, OMOP) in PostgreSQL.
• Incorporated Rabbit-in-a-Hat-derived mappings and robust CSV handling.
• Automated the generation of transformation logic, requiring only manual mapping definitions.

Main Results:

• The pipeline successfully converts CSV data to OMOP CDM v5.4.
• Automated generation of downstream models enhances efficiency and reusability.
• The system improves reproducibility, transparency, and maintainability of ETL processes.

Conclusions:

• The automated ETL pipeline significantly simplifies and accelerates the conversion of clinical data to OMOP CDM.
• This approach enables efficient reuse of data transformation logic across various research settings.
• Manual mapping definition is the primary manual step, streamlining the overall ETL workflow.