Building an OMOP ETL

OMOP ETL: an incremental approach

When discovering OMOP, it’s tempting to picture the ETL as a multi-year effort aiming to standardise the entire local dictionary before any data can be used. That view is legitimate — it reflects what several pioneering teams have actually gone through — but it isn’t the only one.

A more incremental approach lets you deliver a first useful OMOP ETL in a few weeks, then expand coverage progressively over successive projects. It fits inside a data-warehouse team’s normal workflow, without upending habits. It rests on two principles:

1. Project by project. For a given study you need a few dozen variables. You only map those; the others wait for another project that will reuse what has already been mapped.
2. Semantic mapping first, the rest follows mechanically. Once the SOURCE_TO_CONCEPT_MAP table is produced, the ETL becomes mostly routing: you look at each standard concept’s domain_id and it directly tells you which OMOP table the data lands in.

A direct consequence of this approach: the OMOP database you produce won’t necessarily contain every table in the model. You populate the tables that match the concepts actually mapped for the project — typically PERSON, VISIT_OCCURRENCE, plus a few event tables (CONDITION_OCCURRENCE, MEASUREMENT, DRUG_EXPOSURE…). The others (DEVICE_EXPOSURE, SPECIMEN, NOTE…) stay empty until a project needs them. That reduced scope is enough for the current project: the analyses will only rely on the tables actually populated, and that’s what matters to deliver.

This article follows that incremental path. We first present the semantic part (the most demanding, involving domain experts), then routing by domain_id, and finally walk through an immersive MIMIC-IV example: you execute the SQL yourself in your browser, step by step, to build a mini OMOP database.

Semantic mapping, the heart of the work

Semantic mapping consists of linking each local code (a MIMIC itemid, an internal lab identifier, a facility-specific label) to a standard OMOP concept: a LOINC identifier for a lab measurement, a SNOMED CT for a diagnosis or observation, an RxNorm for a drug. The output is a CSV file: the famous SOURCE_TO_CONCEPT_MAP.

This is the most time-consuming part of the whole ETL. It relies on domain experts: biologists for lab parameters, pharmacists for drugs, clinicians (intensivists, internists, emergency physicians…) for diagnoses, observations and procedures. The quality of the mapping determines the quality of everything that follows — this is where you take the necessary time.

Linkr’s mapping module

To support this work, Linkr offers a dedicated module — Concept Mapping. Creating a mapping project takes a few clicks, by importing a source file (CSV, Parquet…) listing the concepts to map. During that import, Linkr offers a parameter step where you pick the columns matching the local code, the label, the category, the frequency and the JSON metadata:

Once the project is created, the mapping editor opens in two panels: source concepts on the left, candidate standards on the right:

Agentic-AI-assisted mapping

Hand-mapping remains slow. To speed up the first pass, Linkr integrates with Claude Code via a dedicated concept-mapping skill. An agent processes source concepts in batches, proposes the best standard candidates for each, and produces a combined score:

Four method families contribute to the score:

• Syntactic — string similarity between the local label and standard labels (and their synonyms).
• Semantic — distance between biomedical embeddings (BioLORD-2023-M), computed offline over the entire OMOP vocabulary.
• Statistical (coming) — comparison of value distributions for numeric concepts.
• Agentic — an LLM agent reviews the best candidates, applies clinical reasoning (consistent units? right granularity? correct domain?), and proposes an equivalence (exactMatch, closeMatch, broadMatch, narrowMatch) with a written justification.

Each suggestion still has to be validated or corrected explicitly in the editor — the AI prepares the ground, the final call always rests with the reviewer. The Suggestions page details the full mechanism.

Metadata and traceability

A concept mapping is more than a correspondence table. It is also a traceability dossier: who mapped what, when, with what reasoning, with what confidence, and what comments were exchanged during review. These metadata matter for the team that will later analyse the data. Linkr stores them with each mapping and can surface them on the source-concept side — distributions, statistics, properties, temporal charts:

The expected JSON structure is documented on the Mapping editor page. It accepts statistics (min/median/max/mean…), distributions (bar, pie or line charts), properties (free key/value pairs) and metadata (scalar metadata). All of it helps the reviewer validate a mapping with full context.

Routing by domain_id

Once SOURCE_TO_CONCEPT_MAP is produced, the rest of the ETL becomes mostly mechanical. For each source row, you ask:

1. What OMOP standard concept is associated (via the SOURCE_TO_CONCEPT_MAP join)?
2. What is its domain_id?

And the domain_id directly determines the target OMOP table:

Anything that doesn’t fit any of these tables lands by convention in OBSERVATION — OMOP’s general-purpose landing table for such cases. The implementation logic becomes the same for every event table: extract source rows, join on SOURCE_TO_CONCEPT_MAP, filter by the target’s domain_id, insert into the target table with standard _concept_ids, custom _source_concept_ids and _source_values for traceability.

Building a MIMIC-IV → OMOP ETL, step by step

Time to practice. We’ll build a mini OMOP database from MIMIC-IV together, executing the SQL in your browser (via DuckDB-Wasm). The source data is the public MIMIC-IV demo — 100 patients, their stays, measurements and prescriptions.

Before diving into the SQL, here are the five steps of the approach:

Step A — Extract source concepts

The very first step of any OMOP ETL is not an INSERT: it’s the inventory of what needs to be mapped. For our scope (demographics, visit types, transfers, diagnoses, measurements and drugs), we extract distinct source concepts from MIMIC tables along with their frequencies (rows count, distinct patients) and some metadata (units, categories…) that will help the reviewer later. The extraction logic fits in one SQL query per category:

Here’s a representative excerpt of the extraction SQL for labevents lab parameters. The query computes in one pass the descriptive statistics, the histogram, the sex breakdown, the typical measurement frequency and the temporal distribution — all assembled into a single JSON object that follows the format Linkr expects (the other categories — chartevents, diagnoses_icd, transfers, admissions, prescriptions — follow a very similar pattern, adjusted depending on whether the concept is numeric or categorical):

-- Lab parameters (labevents) with a rich info_json
WITH lab_stats AS (
  SELECT
    le.itemid,
    COUNT(*)                                       AS record_count,
    COUNT(DISTINCT le.subject_id)                  AS patient_count,
    MIN(le.valuenum)                               AS v_min,
    MAX(le.valuenum)                               AS v_max,
    AVG(le.valuenum)                               AS v_mean,
    STDDEV(le.valuenum)                            AS v_sd,
    QUANTILE_CONT(le.valuenum, 0.05)               AS p5,
    QUANTILE_CONT(le.valuenum, 0.25)               AS p25,
    QUANTILE_CONT(le.valuenum, 0.50)               AS p50,
    QUANTILE_CONT(le.valuenum, 0.75)               AS p75,
    QUANTILE_CONT(le.valuenum, 0.95)               AS p95
  FROM labevents le
  WHERE le.itemid IN (50971, 50983, 50912, 50882, 51265, 51222, 51301, 50931)
  GROUP BY le.itemid
)
SELECT
  'MIMIC_LABITEMS'                                 AS vocabulary,
  'Lab test'                                       AS category,
  CAST(le.itemid AS VARCHAR)                       AS concept_code,
  ANY_VALUE(dli.label)                             AS concept_name,
  ANY_VALUE(s.record_count)                        AS record_count,
  ANY_VALUE(s.patient_count)                       AS patient_count,
  -- Compose the JSON metadata expected by Linkr's mapping editor
  json_object(
    'full_name',  'Lab test / ' || ANY_VALUE(dli.label),
    'data_source', 'MIMIC-IV',
    'data_types',  'numeric',
    'unit',        ANY_VALUE(le.valueuom),
    'numeric_data', json_object(
      'min',    ANY_VALUE(s.v_min),
      'p5',     ANY_VALUE(s.p5),
      'p25',    ANY_VALUE(s.p25),
      'median', ANY_VALUE(s.p50),
      'mean',   ANY_VALUE(s.v_mean),
      'p75',    ANY_VALUE(s.p75),
      'p95',    ANY_VALUE(s.p95),
      'max',    ANY_VALUE(s.v_max),
      'sd',     ANY_VALUE(s.v_sd)
    )
    -- + histogram, categorical_data (sex), temporal_distribution
    -- (assembled via similar sub-queries)
  )                                                AS info_json
FROM labevents le
JOIN d_labitems dli ON dli.itemid = le.itemid
JOIN lab_stats s    ON s.itemid   = le.itemid
WHERE le.itemid IN (50971, 50983, 50912, 50882, 51265, 51222, 51301, 50931)
GROUP BY le.itemid;

The resulting info_json column follows the structure documented on the Mapping editor page, which Linkr reads to render the source-concept detail card. For a vital sign like heart rate, the result looks like:

{
  "full_name": "Vital sign / Routine Vital Signs / Heart Rate",
  "data_source": "MIMIC-IV",
  "data_types": "numeric",
  "unit": "bpm",
  "missing_rate": 0.0,
  "numeric_data": {
    "min": 0.0, "p5": 62.0, "p25": 78.0, "median": 90.0, "mean": 91.12,
    "p75": 104.0, "p95": 122.0, "max": 200.0, "sd": 18.69
  },
  "histogram": [
    { "x": 10.0, "count": 3 },    { "x": 30.0, "count": 12 },
    { "x": 50.0, "count": 454 },  { "x": 70.0, "count": 3313 },
    { "x": 90.0, "count": 5555 }, { "x": 110.0, "count": 3685 },
    { "x": 130.0, "count": 764 }, { "x": 150.0, "count": 117 }
  ],
  "categorical_data": [
    { "category": "Female", "count": 6284, "percentage": 45.2 },
    { "category": "Male",   "count": 7629, "percentage": 54.8 }
  ],
  "temporal_distribution": {
    "start_date": "2110-04-11", "end_date": "2201-12-13",
    "by_year": [ { "year": 2110, "percentage": 6.5 }, … ]
  },
  "measurement_frequency": "every 1 hours"
}

The final result is a CSV with one row per source concept — for our scope, 46 concepts in total (2 sexes + 7 admission types + 8 care units + 10 diagnoses + 8 vitals + 8 lab parameters + 3 drugs).

To go further, open the Linkr demo and:

1. Create a new mapping project (see the Mapping projects docs).
2. Import the source-concepts.csv file above, mapping the columns according to the import settings shown earlier.
3. Map concept by concept in the editor — by hand, or with AI assistance via the Suggestions page.

For the next steps, we skip this and fetch the already-produced mapping table directly.

Step B — Retrieve the mapping table

We assume the mapping is done: here’s the final file, ready to be loaded into the OMOP database.

46 rows, 9 columns total (full reference on the source_to_concept_map table page). Three representative rows, in the schema’s column order:

Note the source_concept_ids in the custom OMOP range (≥ 2 billion) and the target_concept_ids pointing at standard concepts.

Step C — Load the reference vocabularies into the database

Before writing the business INSERTs, the target OMOP database has to hold the dictionary of standard concepts — typically the tables CONCEPT, CONCEPT_RELATIONSHIP, CONCEPT_SYNONYM, CONCEPT_ANCESTOR, VOCABULARY, DOMAIN. We fetch them from ATHENA (free account required), ticking the vocabularies useful to our scope (SNOMED CT, LOINC, RxNorm, ICD10CM, UCUM…). The download returns a ZIP of CSV files to load via a COPY or an INSERT INTO ... SELECT FROM read_csv(...).

Once those vocabularies are in place, we also load our source_to_concept_map.csv into the table of the same name, and we add the custom concepts (the source_concept_ids ≥ 2 billion from our STCM) into the CONCEPT table — that’s what allows our _source_concept_id columns on event rows to point to valid entries in the database.

Step D — Populate the OMOP tables

Now the ETL proper. The MIMIC raw data and the mapping table are already loaded into a DuckDB database running in your browser. The OMOP target tables (PERSON, VISIT_OCCURRENCE, VISIT_DETAIL, MEASUREMENT, CONDITION_OCCURRENCE, OBSERVATION, DRUG_EXPOSURE) are created empty with the v5.4 schema.

Three tables are populated first — each with its own INSERT:

• PERSON from patients (gender, year of birth derived from anchor_year - anchor_age).
• VISIT_OCCURRENCE from admissions (one stay = one hadm_id).
• VISIT_DETAIL from transfers (one care-unit transfer = one transfer_id).

On MIMIC, source vocabularies are partitioned by domain (MIMIC_CHARTEVENTS → Measurement, MIMIC_DRUG → Drug, MIMIC_ICD10 → Condition…) — you could in theory write one INSERT per vocabulary, relying on that partition.

However, to stay general and match the approach described earlier, we prepare a materialised CTE mapped_events that unifies the event sources (diagnoses, vital signs, labs) and resolves each row’s target domain_id via CONCEPT. We then filter this CTE by target_domain_id to dispatch into the right OMOP table:

• target_domain_id = 'Measurement' → MEASUREMENT
• target_domain_id = 'Condition' → CONDITION_OCCURRENCE
• Everything else → OBSERVATION (the default fallback table)

PROCEDURE_OCCURRENCE, DEVICE_EXPOSURE, SPECIMEN, NOTE… follow exactly the same logic — just add a WHERE target_domain_id = '…' branch; we leave them out here because no concept in our scope maps to them.

DRUG_EXPOSURE is handled separately: its sources and specifics (dose, route, unit) don’t fit the same generic router.

Each OMOP table has its own section below, with a ready-to-run INSERT INTO query followed by a SELECT that previews the result. To run a query, click Run (or use the ⌘ / Ctrl + Enter shortcut). The Reset query button above each editor restores the original version in one click. Run the queries in order: mapped_events must exist before the routing INSERTs, and PERSON / VISIT_OCCURRENCE must exist before DRUG_EXPOSURE.

The pattern is always the same: JOIN on source_to_concept_map, INSERT into the target table, COALESCE(..., 0) for unmapped codes so as not to drop rows, and the _source_value and _source_concept_id columns preserve traceability back to the original data.

Step E — Validate the resulting database’s quality

After the INSERTs are done, the OMOP database exists. But it can contain silent inconsistencies: nonsensical dates, non-standard concepts where standards are expected, null required fields. Before any research use, you have to measure quality.

Two OHDSI tools are designed for this:

• ACHILLES — an R package that produces a synthetic characterisation report: number of patients, age distributions, condition prevalence, drug frequencies, lab-measurement distributions. Excellent for taking stock of the database — and quickly spotting obvious anomalies.
• Data Quality Dashboard (DQD) — around 4,000 automated checks across three axes: schema conformance, completeness of required fields, plausibility of values. The interactive HTML report lets you prioritise fixes.

In practice DQD almost always flags hundreds of anomalies on a freshly built database. Most are minor (non-standard concepts, missing dates on optional events…), a handful point at real ETL bugs. It’s an iterative cycle: run, fix, re-run, until the quality level is good enough for release.

What’s next?

Once your first OMOP database is in place, the next project will reuse existing mappings and only add a few new concepts. Project after project, coverage of the local dictionary grows steadily — without ever having to align everything at once.