FHIR to OMOP CDM ETL: A Practical Implementation Guide
Dr. Rachel Green
The hardest part of a FHIR to OMOP CDM ETL is rarely the SQL. It's the moment the first real FHIR Bundle lands, a product team wants it in OMOP by Monday, and the vocabulary questions start piling up faster than the data itself. A FHIR Condition needs to land in CONDITION_OCCURRENCE, an Observation needs to become MEASUREMENT, and the team still has to prove that the transformation is traceable, de-identified, and stable enough for research use.
That's why the work has matured beyond ad hoc scripts. The HL7 FHIR-to-OMOP Cookbook now treats the transformation as a repeatable implementation pattern, starting with the study question, moving through FHIR element alignment, then analyzing CodeableConcept gaps, mapping resources to the OMOP CDM, and finally populating atomic OMOP records with provenance preserved where possible through fields such as meas_event_id, observation_event_id, and fact_relationship (HL7 FHIR-to-OMOP Cookbook). That matters because OMOP uses integer-based identifiers to connect tables and does not store patient-identifiable fields such as medical record numbers, names, or addresses, so identity and privacy can't be assumed during transformation. They have to be engineered.
A 2025 study on bidirectional FHIR and OMOP transformations reported that the transformations from HL7 FHIR to OMOP CDM were largely accurate and usable, with most mandatory and optional OMOP fields populated correctly (PMC study). That's a practical signal for engineering teams, not a theoretical one. The job now is to turn that promise into a governed pipeline that holds up under real source variation, version churn, and audit scrutiny, not just a demo dataset. For a broader data-model refresher, the OMOP table structure overview at OMOP data model is a useful companion.
Why FHIR to OMOP CDM ETL Matters in 2026
A production request usually starts with a familiar mismatch. The source EMR team can expose FHIR Condition and Observation resources, the analytics group wants OMOP for cohort analysis, and the ETL owner has to explain why the map is harder than it looks. The work is not about moving fields. It is about turning clinical events into an OMOP structure that supports downstream research, joins cleanly, and keeps identity out of the analytical layer.
The practical shift is already visible in the tooling and guidance. The HL7 cookbook treats FHIR to OMOP as a stepwise process, starting with the study question, aligning FHIR elements to that question, analyzing CodeableConcept content against the OMOP ontology for vocabulary gaps, mapping relevant FHIR resources or profile elements to OMOP CDM, and then populating OMOP records at the atomic level while preserving provenance where possible through fields such as meas_event_id, observation_event_id, and fact_relationship (HL7 FHIR-to-OMOP Cookbook).
What changed in practice
The OMOP CDM is not a patient chart mirror. It uses integer-based identifiers and standardized concepts, not direct carryover of source identifiers or free-form source structures. If the pipeline treats FHIR as a straight export, joins break, provenance gets murky, and privacy issues surface late. If the pipeline treats transformation as a governed process, each OMOP row can still be traced back to a source event and a mapping decision.
For teams that need to connect source workflows to enterprise analytics, even an integration point like Integrate with Epic Systems API has to land in this model cleanly, because the downstream OMOP load still depends on consistent resource shapes, code handling, and provenance rules.
Practical rule: if the ETL cannot explain identity, vocabulary choice, and provenance for a row, it is not ready for production analytics.
OMOP also has its own table logic and domain structure, so the transformation has to respect the target model instead of forcing source semantics into the wrong place. A useful reference for that structure is the OMOP data model overview, especially when the team is deciding where source resources belong and where they do not.
The positive signal is not abstract. A 2025 bidirectional transformation study reported that FHIR-to-OMOP outputs were largely accurate and usable when the mapping was well defined (PMC study). That qualifier matters. The production work is to make the mapping explicit, govern vocabulary resolution, and keep the ETL defensible when source variation, version churn, and audit review all hit at once.
The Cookbook Workflow Translated Into Engineering Steps
The HL7 workflow becomes manageable once each stage produces a concrete artifact that someone can review, version, and test. Teams that try to “just map the fields” usually skip the evidence trail and spend the next sprint untangling vocabulary mismatches. Teams that ship reliably tend to make each step auditable.
Step 1, define the question as a contract
Start by turning the observational question into a target-schema contract. If the research team wants condition incidence and lab trends, capture that in a YAML or JSON contract that names the fields, tables, and allowed source profiles. This gives the ETL owner a reference point when source FHIR resources contain optional elements, local extensions, or repeated codings.
Step 2, align resource elements to the information need
Use the relevant FHIR profiles, often US Core in mixed enterprise settings, to decide which source elements matter. That alignment is where many projects drift, because teams copy everything from the Bundle instead of limiting the load to analytically meaningful content. The output should be a mapping sheet that says which FHIR element feeds which OMOP field, and which fields are intentionally ignored.
Step 3, identify vocabulary gaps before code is written
The cookbook's CodeableConcept analysis is the most underrated part of the process. It forces the team to examine the source corpus and find what can't yet be represented cleanly in OMOP. That gap report is what you bring to a data steward or terminology owner when local codes, outdated code systems, or unusual display-only values show up.
Step 4, map resource types to CDM tables with rules
Domain logic matters. A FHIR resource does not automatically imply a single OMOP table, so the mapping table should include the destination table, the standard concept target, the domain rationale, and the fallback rule when a code is non-standard.
Step 5, load atomic records with provenance intact
The final load should write one clinical fact at a time and preserve traceability. meas_event_id, observation_event_id, and fact_relationship are the fields that keep source events connected after the transformation. Without them, debugging becomes guesswork.
If your team needs a practical integration point for clinical source systems, a reference like Integrate with Epic Systems API can be helpful when you're wiring the source extraction side, but the ETL contract still has to stand on its own.
The cleanest handoff I've seen is the one where the mapping sheet, vocabulary coverage report, and load contract all point to the same row-level rule.
Resolving Source Codes Into Standard OMOP Concepts
Vocabulary handling is where a FHIR-to-OMOP ETL stays reliable or starts drifting. The FHIR-to-OMOP IG is explicit here, a source code should exist in the OHDSI Standardized Vocabularies before you treat it as mappable, and a concept is only directly usable in OMOP analytics when it carries the standard-concept “S” flag. If OHDSI reference content is not available, the IG points you to authoritative crosswalks such as CMS or NLM mapping tables as the fallback path (FHIR-to-OMOP IG code mappings).
What the resolver has to do
The hard part shows up in source feeds. One system sends coded text with a clean system URI, another sends a local code with a display string, and a third sends a code that looks standard but does not resolve to a standard OMOP target. The resolver has to handle all three without pushing vocabulary logic back into ad hoc SQL.
OMOPHub's FHIR integration docs describe a composite FHIR Resolver that accepts a FHIR Coding or CodeableConcept and returns a standard OMOP concept, a mapping type, and a CDM target table in one call. The resolver chains system-URI vocabulary resolution, source-concept lookup, Maps to traversal for non-standard concepts, semantic fallback on display text when codes are missing, and CDM table assignment from the concept domain. The batch workflow also deduplicates inputs and resolves unique codings in chunks of 100 (OMOPHub FHIR integration docs).
Here's the shape I would wire into an ETL job:
curl -X POST "https://api.omophub.com/v1/fhir/resolve" \
-H "Authorization: Bearer oh_your_api_key" \
-H "Content-Type: application/json" \
-d '{"system": "http://snomed.info/sct", "code": "44054006", "resource_type": "Condition"}'
A typical response shape should give the ETL enough to decide the destination and write the row:
{
"concept_id": 123456,
"concept_name": "Type 2 diabetes mellitus",
"domain_id": "Condition",
"mapping_type": "standard",
"cdm_table": "condition_occurrence"
}
The value is not lookup speed by itself. It is turning vocabulary resolution into one tested contract. If a batch contains repeated codings, let the resolver deduplicate them before the ETL fan-out. That keeps the vocabulary layer deterministic and makes the load step easier to reason about.
For a vocabulary-table refresher, the OMOP vocabulary overview at OMOP CDM vocabulary tables is a useful reference when you are explaining why concept standards and domains matter to downstream analysts.
Sample Transformations From FHIR Bundles to CDM Tables
The fastest way to make the pipeline real is to show what lands where. The exact SQL will vary by warehouse, but the pattern stays consistent, FHIR source, vocabulary resolution, domain-driven destination, then provenance. I prefer keeping the mapping logic readable enough that a second engineer can review it without reverse-engineering the entire job.
PERSON and CONDITION_OCCURRENCE
A FHIR Patient usually feeds PERSON with demographic fields, and a FHIR Condition becomes CONDITION_OCCURRENCE once you have a resolved concept ID and an onset date. The important part is not the syntax, it's the dependency chain. The row should not be inserted until the code is resolved and the source encounter context is available.
, PERSON
insert into person (
person_id,
gender_concept_id,
year_of_birth,
month_of_birth,
day_of_birth
)
select
p.patient_id,
coalesce(g.concept_id, 0),
extract(year from p.birth_date),
extract(month from p.birth_date),
extract(day from p.birth_date)
from fhir_patient p
left join resolved_gender_map g
on p.gender_code = g.source_code;
, CONDITION_OCCURRENCE
insert into condition_occurrence (
person_id,
condition_concept_id,
condition_start_date,
condition_type_concept_id,
visit_occurrence_id
)
select
c.patient_id,
r.concept_id,
c.onset_date_time::date,
0,
e.visit_occurrence_id
from fhir_condition c
join resolved_concept_cache r
on c.code = r.source_code
left join encounter_bridge e
on c.encounter_id = e.source_encounter_id;
MEASUREMENT and DRUG_EXPOSURE
For Observation with valueQuantity, the critical step is to keep the resolved standard concept and the numeric value together. That's where MEASUREMENT gets its analytical value. For MedicationRequest, a dosage instruction often informs DRUG_EXPOSURE, but the mapping still needs a clean vocabularized drug concept and a visit link when available.
, MEASUREMENT
insert into measurement (
person_id,
measurement_concept_id,
measurement_date,
value_as_number,
visit_occurrence_id,
observation_event_id
)
select
o.patient_id,
r.concept_id,
o.effective_datetime::date,
o.value_quantity,
e.visit_occurrence_id,
o.source_observation_id
from fhir_observation o
join resolved_concept_cache r
on o.code = r.source_code
left join encounter_bridge e
on o.encounter_id = e.source_encounter_id;
, DRUG_EXPOSURE
insert into drug_exposure (
person_id,
drug_concept_id,
drug_exposure_start_date,
visit_occurrence_id
)
select
m.patient_id,
r.concept_id,
m.authored_on::date,
e.visit_occurrence_id
from fhir_medication_request m
join resolved_concept_cache r
on m.medication_code = r.source_code
left join encounter_bridge e
on m.encounter_id = e.source_encounter_id;
If you want to avoid re-calling the vocabulary service in production, develop against OMOPHub and cache the resolution results locally. The Python SDK at OMOPHub Python SDK and the R SDK at OMOPHub R SDK are the cleanest ways to prototype that pattern before you lock the job into your warehouse scheduler.
Validation, QA, and Coverage Audits
A pipeline can be fully loaded and still be wrong. That's why QA needs three layers, and they need to run independently of each other. Schema checks catch structural mistakes, vocabulary checks catch mapping drift, and data checks catch issues that only show up once the records are inside OMOP.
The three checks that actually matter
First, verify mandatory and optional CDM fields, plus foreign-key integrity. If a row can't link to the right person, visit, or source event, it may exist in the table but it won't be usable. Second, audit vocabulary coverage by source vocabulary, and flag any concept that remains non-standard after Maps to traversal. Third, compare row counts and data distributions between the FHIR Bundle and the CDM tables, because a quiet drop in transformed observations is usually a mapping bug, not a source-system miracle.
Practical rule: if the vocabulary layer changes, rerun the mapping audit before you trust the analytics layer.
ACHILLES fits in here because it gives you a quick heuristic read on whether the transformed OMOP data still looks coherent. It's also the cleanest way to spot a silent failure mode like a vocabulary update that reroutes an ICD-10 code to a different standard concept. Temporal comparison across runs makes that drift visible even when the ETL itself doesn't fail.
I also keep a small audit table for every job run, with source system, vocabulary version, mapping type, and resolution timestamp for each concept written. That table becomes your first stop when the analyst asks why a cohort definition changed after a vocabulary refresh.
The audit cadence I've seen hold up is simple. Run ACHILLES on every ETL execution, do vocabulary coverage audits weekly, and perform full source-to-target reconciliation monthly. If the team skips one of those, the gap usually shows up later as a research discrepancy that's much harder to explain.
For a practical data-quality checklist, the guide at data quality checking is worth keeping close while you build the runbook.
Deployment Patterns and Performance Considerations
The first deployment choice is simple: run the vocabulary stack yourself, or call it as a service. A self-hosted ATHENA setup still fits some environments, but the operational cost is real. You own the database, the search layer, the FHIR endpoint, and every vocabulary refresh. OMOPHub takes the service route, with REST and FHIR access to vocabulary lookups, automatic sync with ATHENA releases, built-in search, and a lighter path when the team does not want to run local terminology infrastructure.
Self-hosting versus API-driven access
A self-hosted setup usually means a 1 to 2 day setup, manual re-download and reload cycles roughly every 6 months, and ongoing infrastructure costs around $150 to $400 per month for database and compute, plus the engineering time needed to build the REST, search, and FHIR layer yourself (OMOPHub one-pager). The service-based option removes most of that work, with a fast onboarding flow, built-in terminology search, and a FHIR Concept Resolver exposed at POST /v1/fhir/resolve.
| Capability | Self-hosted ATHENA | OMOPHub |
|---|---|---|
| Setup time | 1 to 2 days | 5 minutes, get an API key |
| Vocabulary updates | Manual re-download and reload | Automatic, synced with ATHENA |
| REST, search, and FHIR layer | Build your own | Included |
| FHIR Concept Resolver | Not standard | Built-in |
| Infrastructure cost | $150 to $400 per month | Free tier, paid tiers for volume |
| Maintenance burden | Ongoing | Zero |
The pattern that tends to work in production is hybrid. Develop and test against the API, cache concept_id to domain_id to CDM-table mappings in a local lookup table, and pin the vocabulary version per ETL run so results stay reproducible. That keeps production traffic from depending on a network call for every row while still letting the team use centralized vocabulary resolution during development.
Throughput planning matters too. The batch resolver deduplicates and processes unique codings in chunks of 100, which fits moderately sized payloads (OMOPHub FHIR integration docs). For very large backfills, move resolution into an async job stage and cache aggressively, because a single FHIR Bundle can carry many repeated codings even when the clinical variety is low. The goal is to keep vocabulary lookup predictable, not chatty.
Compliance, Governance, and Operational Tips
A real FHIR to OMOP pipeline fails in small, avoidable ways. A source system sends more data than the team expected, a mapping changes without review, or an analyst cannot explain why a concept landed in a specific OMOP domain. The fix is not more ceremony. It is tighter scope, clearer lineage, and a governance model that treats terminology resolution as production code.
The compliance posture is manageable if you keep the vocabulary layer separate from patient data. OMOPHub's workflow is a vocabulary lookup service, so it receives terminology codes and concept IDs, not patient identifiers, clinical records, or free-text notes. That does not remove your responsibility. Run de-identification before FHIR Bundles enter the ETL, and treat the terminology layer as part of the governed pipeline, not as a loose helper endpoint.
The controls that prevent avoidable incidents
Version pinning comes first. If a vocabulary refresh changes a concept path, that change needs to be visible, reviewed, and tied to a release. Key rotation comes next. Bearer API keys should be revocable and scoped, and FHIR-aware clients can also use OAuth2 client_credentials when the stack calls for it. Audit trails matter because lineage is what lets you answer what changed, when it changed, and why.
Operational tip: log the source system, vocabulary version, mapping type, and resolution timestamp for every concept row. That one habit saves hours during incident review.
A small runbook covers most of the operational risk:
- Pin the vocabulary release: tie each ETL run to a specific vocabulary sync so mappings do not drift without notice.
- Cache validated resolutions: store stable concept mappings locally after validation, then reuse them in production.
- Rotate credentials deliberately: revoke and reissue Bearer keys on a documented schedule.
- Keep the source clean: de-identify before transformation, not after.
- Monitor drift, not just failures: a successful load can still be semantically wrong.
Production governance also needs an ownership model. Someone should approve vocabulary updates, someone should review mapping exceptions, and someone should own the rollback path when a source code starts resolving differently than expected. That is especially important in hybrid deployments, where the API may be used for development and validation while the production pipeline relies on cached lookups and pinned releases. The engineering trade-off is simple, more control means more process, but it also means fewer surprises when analysts query the CDM.
If you are building this now, keep the first version boring. Make source-code resolution deterministic, pin the vocabulary version, keep the audit trail rich, and test the output against the OMOP tables that matter most to your analysts. Start with the workflow you trust, validate the mappings against real FHIR codings, and then freeze the local cache pattern into your deployment.
