How ohdsi/mimic works
Project type and purpose
• This is a data-engineering/ETL project that implements a complete pipeline to convert the MIMIC-IV clinical dataset into the OMOP Common Data Model (CDM) format.
• It pulls source data (MIMIC-IV tables and waveform CSVs), applies a multi-stage ETL (snapshot → clean → map → join → load), runs tests/metrics, then unloads the final CDM & vocabulary tables for use in OHDSI ATLAS or downstream analysis.
High-level components
A. Data sources
1. MIMIC-IV source tables (BigQuery or Postgres/PhysioNet)
2. Waveform CSVs on Google Cloud Storage
3. Standard OMOP vocabulary tables (loaded from Athena)
4. Custom mapping CSVs (in custom_mapping_csv, crosswalk_csv)
B. Orchestration & Config
• conf/*.etlconf & conf/workflow_*.conf drive workflows
• Python orchestrators:
– scripts/run_workflow.py (runs sets of SQL tasks)
– scripts/bq_run_script.py (executes individual SQL to BigQuery)
– scripts/wf_read.py (waveform ingestion)
C. Storage & staging datasets (BigQuery)
• ETL dataset: contains staging tables (src_*), lookup/clean/concept/mapped tables (lk_*), and CDM target tables (cdm_*)
• Vocabulary dataset: holds standard + custom vocabularies
• Final ATLAS dataset: cleansed OMOP CDM tables with technical prefixes removed
• Metrics & QA datasets: hold test results and metrics reports
D. ETL logic modules
1. ddl – DDL scripts to create empty CDM and vocabulary tables
2. staging – snapshot of source & vocabulary tables into src_* and voc_*
3. etl – core ETL:
a. clean (lk_*_clean)
b. concept mapping (lk_*_concept)
c. join & map (lk_*_mapped)
d. distribute into cdm_* tables
4. unload – unload scripts that copy cdm_* and vocabulary tables into the ATLAS dataset (removing unit_id, load_row_id, etc.)
E. Testing & reporting
– ut (unit tests on CDM tables)
– metrics_gen (generates row counts, mapping rates, custom reports)
– qa (SQL-based quality‐assurance checks)
Relationships and data flow
1. Source data → staging workflow
– Snapshot source and vocabulary → src_*/voc_* tables
2. Clean & map → ETL workflow
– src_* → lk_*_clean → lk_*_concept → lk_*_mapped → cdm_*
3. Vocabulary refresh → vocabulary dataset
– custom_mapping_csv & standard CSVs → load_to_bq_vocab.py → vocabulary tables
4. Waveform ingest → wf_read.py → wf_header/wf_details → used by ETL measurement & condition_occurrence
5. Testing & metrics
– After ETL, scripts/run_workflow.py with workflow_ut.conf & workflow_metrics.conf → ut_/me_ tables
6. Unload
– cdm_* & vocabulary tables → unload scripts → Atlas dataset
7. Delivery (optional)
– export_from_bq.py & load_to_bq.py → CSV export/import for external systems
Architectural patterns & design principles
• Layered/pipelined ETL: clearly separated stages (snapshot, clean, map, load, unload)
• Config-driven orchestration: workflows defined via conf files; Python wrappers handle execution order and error handling
• Modular SQL code: each CDM table ETL in its own SQL file (etl/etl/*.sql)
• Immutable staging: source snapshots never overwritten; intermediate tables have prefixes to track lineage
• Test & metrics integration: built directly into pipeline to enforce data quality