Staging and auditability¶
An architecture where every OMOP CDM row can be traced back to a line of source JSON. This is what makes a data quality question answerable.
The layered pattern¶
Four layers, each with a different job.
Raw Immutable NDJSON exactly as received, plus manifests
↓
Staged Parsed into queryable structures, still source-shaped
↓
Mapped Concepts resolved, domains determined, still traceable
↓
OMOP CDM The Common Data Model instance
Raw¶
The NDJSON files as delivered, unmodified, with the export manifest and a record of the request that produced them.
Write once, never modify. Everything downstream can be rebuilt from this, and if it cannot, you have a dependency you did not know about.
Storage cost is the objection and it is almost always the wrong trade. Re-exporting means another request cycle, another wait, and possibly another round of approvals, and it does not reproduce the earlier snapshot anyway.
Staged¶
Parsed into something queryable, preserving source structure. A table per resource type with columns for the elements you use, plus the resource id and a pointer back to the source file and line.
The purpose of this layer is that source-shaped questions become SQL questions. "How many Conditions have a null onset" is a query here and an exercise in JSON parsing without it.
Preserve the resource id. It is your join key back to raw and the basis of every trace.
Mapped¶
Source codes resolved to concepts, domains determined, destinations assigned. Still row-per-source-fact rather than table-per-domain.
This layer is where the interesting questions live. Which codes failed to map, how many rows each affects, which domains they would have gone to, what the mapping was before the vocabulary upgrade. Keeping mapping as a visible intermediate rather than a step inside the load makes all of that queryable.
OMOP CDM¶
The instance. Populated from the mapped layer, ideally by logic simple enough that its correctness is easy to verify.
Traceability¶
The goal is that for any OMOP CDM row, someone can ask "where did this come from" and get an answer in one query.
The minimal mechanism: carry a source lineage identifier through every layer, and keep a mapping table from OMOP CDM surrogate key to source lineage identifier.
That mapping table is not part of the OMOP CDM specification and lives alongside it. Its access controls may differ from the OMOP CDM's, since lineage can be re-identifying in ways the OMOP CDM instance is not.
What this buys you:
- A researcher asks why a person has a diagnosis that seems wrong. You show them the source resource in one query.
- A data quality check fails. You find the affected rows, trace them to source, and determine whether the fault is in the source, the mapping, or the load.
- A vocabulary upgrade changes a mapping. You identify every affected row without reprocessing.
- An auditor asks how you know a value is correct. You show them.
What its absence costs you: every one of those becomes a reprocessing exercise, and reprocessing may not reproduce the original result if anything upstream has changed.
Idempotency and reload¶
Design for the pipeline to be run repeatedly on the same input and produce the same output.
This sounds obvious and is frequently violated by surrogate key assignment. If person_id is assigned by an autoincrementing sequence, a reload produces different person_id values for the same people, and any external artifact referencing them, such as a saved cohort, is silently invalidated.
The fix: derive surrogate keys deterministically from source identifiers, or maintain a persistent key mapping table that survives reloads. The second is more common and needs its own backup strategy, because losing it means losing the ability to append.
Incremental loading¶
Full reload is simplest and works until the data set is large enough that it does not.
Incremental loading needs three things from the source, and the third is the one that usually fails.
A reliable change indicator. _since on the export, or meta.lastUpdated on resources.
A way to detect deletions. If a resource was deleted at source, a _since export will not mention it, and your instance will retain it forever. Some servers support a deleted-resource mechanism and many do not.
A way to detect amendments. A corrected laboratory result should replace the preliminary one rather than joining it.
If the source cannot support all three, incremental loading produces an instance that drifts from source in ways that accumulate silently. A workable compromise is incremental loading with periodic full reconciliation, comparing counts and spot-checking, at a cadence your governance can defend.
Reasonable technology choices¶
The pattern above is technology-agnostic. Common realizations:
- Object storage for raw, columnar tables for staged and mapped, a relational warehouse for the OMOP CDM
- A transformation framework that makes lineage and testing first-class rather than bolt-on
- Version control for everything, including mapping files and vocabulary version pins
Two things matter more than the specific stack. The raw layer must be immutable and retained. The mapping decisions must be data rather than code, so they can be reviewed by someone who does not read your transformation language.
What to document as you build¶
Written while building, not after. Six months later, nobody remembers why.
- The coding-selection policy when a CodeableConcept has several codings
- The status-filtering policy per resource type, with counts
- The visit construction rule with numeric parameters
- The observation period derivation rule
- Every default and imputation, with the rows affected
- The vocabulary version and the date it was loaded
- Every local mapping, with reasoning and confidence
- Known gaps: resource types not exported, elements not populated, tables left empty
The ETL Decision Log tool gives you a structured place to capture these as you go and exports them in a form you can paste into your documentation.