SNOMED to OMOP Mapping: A Practical ETL Guide

You can have a clean source extract, a tidy staging table, and a mapping script that looks correct, then still discover weeks later that cohort counts are off and nobody can explain why. That's the usual shape of SNOMED to OMOP mapping pain. The trouble isn't just vocabulary size, it's that SNOMED sits at the center of OMOP condition and procedure normalization, and the mapping path can change depending on version, hierarchy, and whether a source code lands cleanly on one standard concept or fans out into several.

A lot of teams treat this as a lookup problem. It isn't. It's a controlled translation problem where the source code format, the vocabulary release, the relationship table, and the target OMOP domain all have to agree at the same time, or the ETL drifts.

For broader platform hygiene, the best data engineering teams usually apply the same discipline they use for schema management, lineage, and rollback planning. TekRecruiter best data practices is a useful reminder that vocabulary work deserves the same operational rigor as the rest of the pipeline.

Why SNOMED to OMOP Mapping Breaks More Pipelines Than You Expect

A Friday load can look clean in staging and still fail in production review. The source files land, the mapping job finishes, the dashboard renders, then a researcher spots a cohort gap that no one can explain from the logs alone. That kind of failure is common in SNOMED to OMOP mapping, because the mistake usually sits in the mapping rule itself, not in the final table.

The failure is usually structural, not cosmetic

OMOP does not treat source-to-standard mapping as a loose suggestion. The CDM uses CONCEPT_RELATIONSHIP, especially the Maps to relationship, to place source concepts into standard clinical tables such as condition_concept_id, observation_concept_id, measurement_concept_id, drug_concept_id, procedure_concept_id, and device_concept_id OMOP vocabulary documentation. The mapped concept changes where the record lands, so a bad translation affects the clinical table, not just the label.

SNOMED CT sits at the center of OMOP's condition and procedure normalization. All of Us guidance maps ICD-9 and ICD-10 conditions to SNOMED, and procedures from ICD-9, ICD-10, and CPT also map to SNOMED All of Us OMOP basics. That works well when the source code matches the vocabulary path. It becomes messy when the source format is inconsistent, the target domain is ambiguous, or the same source concept can land in more than one standard concept.

Practical rule: if a SNOMED code does not resolve cleanly in CONCEPT and CONCEPT_RELATIONSHIP, do not patch it in downstream analytics. Fix the mapping logic in the ETL layer, where the traceability still exists.

Version drift and inactive concepts do the quiet damage

A code can be valid in one ATHENA release and inactive in another. If your ETL pins no vocabulary version, records can load cleanly one day and stop mapping the next, without any obvious change in the source feed. Version control belongs in the mapping design, not in the postmortem.

Hierarchy handling creates a second trap. The OMOP guidance for SNOMED-centered work recommends walking Is a descendants carefully, capping hierarchy depth, excluding inactive concepts, and de-duplicating concepts that appear through multiple paths SNOMED to OMOP concept workflow. Those checks are necessary because SNOMED traversal can fan out fast, and a careless descendant query can pull in concepts that look related but do not belong in the cohort definition.

The safest pipelines treat vocabulary work like any other production dependency. The same discipline used for schema management, lineage, and rollback planning applies here too. TekRecruiter best data practices is a useful reminder that vocabulary changes need the same operational control as the rest of the ETL.

Three Mapping Strategies and When to Use Each

The fastest way to make SNOMED to OMOP mapping brittle is to force every source concept through the same logic. Some inputs are already in standard-like form. Others need a crosswalk. Others need relationship traversal because the cohort definition depends on descendants, not just the parent concept.

Direct standardization

Use this when the source code already matches the distributed vocabulary format. OMOP guidance says you can match CONCEPT.concept_code to the source code and CONCEPT.vocabulary_id to the source vocabulary, as long as the formatting lines up OMOP data model conventions. That is the cleanest path, and it is usually the least error-prone.

A direct join looks like this in practice:

SELECT s.source_id,
       c.concept_id,
       c.concept_name
FROM staging_snomed s
JOIN concept c
  ON c.vocabulary_id = s.source_vocabulary
 AND c.concept_code = s.source_code;

If your source encoding uses alternate formatting, fix it before the join. OMOP notes that source codes sometimes need transformation first, such as removing decimal points from ICD-9-style codes before lookup. The same discipline applies to SNOMED feeds that arrive with spaces, punctuation, or other local formatting quirks. If the string does not match what the vocabulary store expects, the join will fail even when the concept is otherwise valid.

Crosswalk-based mapping

Use this when the source code is non-standard and you need Maps to to reach a standard concept. In OMOP, the crosswalk lives in CONCEPT_RELATIONSHIP, and the target concept should be validated back in CONCEPT before load. That is the normal path for ETL work, not the exception. A more detailed walkthrough of that pattern is in this OMOP concept mapping guide, which is useful when you want to compare local SQL against API-driven resolution.

A simple pattern is:

SELECT s.source_id,
       cr.concept_id_2 AS standard_concept_id
FROM staging_codes s
JOIN concept src
  ON src.vocabulary_id = s.source_vocabulary
 AND src.concept_code = s.source_code
JOIN concept_relationship cr
  ON cr.concept_id_1 = src.concept_id
 AND cr.relationship_id = 'Maps to'
JOIN concept dst
  ON dst.concept_id = cr.concept_id_2
 AND dst.standard_concept = 'S';

When the code travels through the OMOPHub API instead of your local database, the same idea becomes a single resolve call. That is useful when you want to inspect mappings before you hard-code them into ETL, or when the vocabulary tables are not loaded locally yet.

Rule-based hierarchy traversal

Use this when the goal is phenotype expansion, not one-off code conversion. The hard part is not finding descendants. It is keeping the traversal bounded so the query stays explainable and cheap to run.

A depth-capped recursive pattern is safer than a broad descendant pull:

WITH RECURSIVE snomed_tree AS (
    SELECT c.concept_id,
           c.concept_name,
           0 AS depth
    FROM concept c
    WHERE c.concept_id = :root_concept_id

    UNION ALL

    SELECT child.concept_id,
           child.concept_name,
           parent.depth + 1
    FROM snomed_tree parent
    JOIN concept_relationship cr
      ON cr.concept_id_1 = parent.concept_id
     AND cr.relationship_id = 'Is a'
    JOIN concept child
      ON child.concept_id = cr.concept_id_2
    WHERE parent.depth < :max_depth
)
SELECT concept_id, concept_name, depth
FROM snomed_tree;

That pattern gives you a controlled expansion point. You can inspect the frontier at each depth, compare the output against the source intent, and decide whether the concept set belongs in a cohort definition or should stay as a direct mapping only. It also keeps the SQL shape stable when vocabularies shift, which makes version checks easier to automate later.

Building Your ETL Pipeline with SQL and API Calls

A working pipeline starts with source identification, not with transformation logic. The source code has to be normalized into the form your vocabulary lookup expects, and OMOP conventions make clear that formatting can matter before the first join runs. If that step is skipped, the downstream failure looks like a mapping problem even when the issue is a string-format mismatch.

Step 1, identify the source concept

Match the raw code against CONCEPT using vocabulary_id and concept_code. If the feed uses a formatted variant, normalize it first. A small preprocessing table helps here because it preserves the original source string while still giving you a reliable join key.

Step 2, traverse Maps to

Once the source concept is identified, follow CONCEPT_RELATIONSHIP where relationship_id = 'Maps to'. Then validate the destination concept in CONCEPT and confirm that it is standard. That validation matters because source-to-standard links can represent a direct equivalent or a broader semantic category, and the wrong assumption creates bad loads later. For a deeper look at OMOP concept mapping patterns, see a deeper look at OMOP concept mapping patterns.

Step 3, load the destination table

Insert the result into the target CDM table with both source and standard identifiers preserved. The exact table depends on the clinical meaning of the source, but the discipline stays the same. Keep the source concept traceable and keep the standard concept available for downstream analytics.

A compact pattern:

INSERT INTO condition_occurrence (
  person_id,
  condition_concept_id,
  condition_source_concept_id,
  condition_source_value
)
SELECT s.person_id,
       dst.concept_id,
       src.concept_id,
       s.source_code
FROM staging_snomed s
JOIN concept src
  ON src.vocabulary_id = s.source_vocabulary
 AND src.concept_code = s.source_code
JOIN concept_relationship cr
  ON cr.concept_id_1 = src.concept_id
 AND cr.relationship_id = 'Maps to'
JOIN concept dst
  ON dst.concept_id = cr.concept_id_2;

Step 4, use the API when local vocabulary plumbing is not ready

A REST resolver is useful when you are prototyping or validating mappings outside the warehouse. OMOPHub exposes vocabulary lookup and mapping through REST and FHIR interfaces, which lets you inspect concept resolution before you commit a rule to ETL. That is especially practical when the local vocabulary tables are not loaded yet, or when you want to compare API output against your warehouse results before shipping the pipeline.

Step 5, batch only after single-code behavior is stable

For programmatic runs, keep your batch size small enough to debug failures cleanly. The OMOPHub pattern supports batch-style mapping workflows, and the safer habit is to test a few codes first, confirm the target concept, then scale out to the full feed. That incremental approach keeps one bad source code from contaminating an entire load.

Common Mapping Pitfalls That Corrupt Your Data

SNOMED to OMOP pipelines often fail. The tables still load, the row counts still look reasonable, and the bad mapping only shows up later as duplicate concepts, missing cohorts, or odd coverage gaps. The safe response is detection logic in staging, not blind trust in the ETL.

One source code can map to more than one standard concept

A single source code can resolve to more than one standard concept, which means the ETL has to make a choice instead of assuming the vocabulary will make it for you. OHDSI's discussion of one-to-many mapping makes that risk clear, especially when a source term can match multiple valid targets OHDSI abstract on one-to-many mapping. If your rule does not say which target wins, downstream joins can drift in ways that look legitimate at first glance.

Detection pattern:

SELECT src.concept_code,
       COUNT(*) AS mapped_targets
FROM concept_relationship cr
JOIN concept src
  ON src.concept_id = cr.concept_id_1
WHERE cr.relationship_id = 'Maps to'
GROUP BY src.concept_code
HAVING COUNT(*) > 1;

Once that query surfaces a code, the remediation is usually a source-side rule, not a warehouse-side patch. I prefer a small exception table that records the approved target for each ambiguous source code, then join to that table during load so the behavior stays explicit.

Inactive concepts vanish without warning

Vocabulary refreshes can retire a code that used to resolve cleanly. The failure mode is subtle, because historic records still exist, but a later load or rerun may stop mapping them the same way. Track the vocabulary release used for each ETL run and flag any source concepts that no longer resolve in the current release.

A targeted audit helps here. A simple report that compares yesterday's unresolved codes to today's unresolved codes will show whether the change came from source data or from a vocabulary update. If the breakage lines up with a release change, fix the mapping reference first before touching the clinical source.

Hierarchy traversal can inflate cohorts

Walking Is a descendants is useful, but it can also create false growth if you treat every path as equally valid. A better remediation pattern is to hash the resolved concept set after traversal, then compare it to the prior load and look for concept growth concentrated in one branch. That exposes bad traversal logic faster than eyeballing the tree.

If the branch explosion comes from alternate paths to the same concept, deduplicate after resolution and log the duplicates you removed. If the issue comes from a broad ancestor term, tighten the source rule instead of trying to clean it later in cohort logic. For a practical review of data-quality checks that catch this kind of drift early, see data quality checking in OMOPHub.

Formatting mismatches break lookups before they start

OMOP's conventions are strict about how source values line up with vocabulary entries. If the source format does not match the stored code format, the lookup fails before mapping even begins OMOP data model conventions. That sounds small, but it is enough to break an entire feed when one system stores punctuation and another strips it out.

The fix is to normalize the source code before lookup, then validate the transformed value against CONCEPT. That should be a repeatable transform in staging, not a manual cleanup step in production.

Detection first, remediation second: run orphan checks, duplicate mapping checks, and unmapped-code reports before loading downstream tables. Fixing the warehouse is slower than catching the problem in staging.

Validation Checks and Version Audit Strategies

A mapping pipeline is only trustworthy when it can show what it loaded and when. In practice, that means checking the row itself, then checking the vocabulary state behind the row. Row-level validation tells you whether the ETL populated the right target columns, while version checks tell you whether yesterday's mapping still means the same thing after a vocabulary refresh. Without both, auditability turns into guesswork.

Validate the row before you trust the table

Every mapped row should carry both the source concept and the standard concept. If a source record reaches a target table without a valid standard concept, the load should fail or the row should be quarantined. That is the minimum bar for OMOP ETL quality.

The check should happen close to staging, where you can inspect the source code, the resolved concept, and the target domain together. I prefer a straight SQL gate here, with one query that flags unmapped codes, invalid concept IDs, and one-to-many results that were not approved by the mapping rule. That keeps the failure visible before it spreads into fact tables and cohort logic.

Audit cohort size deltas, not just row counts

A clean row count can still hide a bad mapping if the wrong concepts were selected. Compare the source cohort size to the mapped cohort size and investigate any large drop in coverage, especially after vocabulary updates. Coverage drift often shows up first as a small change in mapped counts, then as a researcher complaint later.

The useful check is not just total volume, but where the loss occurs. A branch that goes quiet after a release often points to hierarchy traversal problems, while a sudden spike in duplicates usually means the same source concept now resolves through more than one path. That is the kind of pattern you want to catch before anyone builds a cohort definition on top of it.

Track vocabulary release state with the ETL run

If a load used one ATHENA release and the next load uses another, document it. Then compare release changes before promoting the pipeline. Version drift is where stable mappings become unstable, especially when a concept is retired, remapped, or moved under a different ancestor. OMOPHub's version tracking and FHIR $diff release comparison are practical ways to spot vocabulary drift before it reaches production dashboards.

For more detail on quality gates, the data quality checking guide is a solid companion when you are wiring these checks into CI/CD.

Use a simple audit checklist

• Orphan Concept Check: confirm every mapped concept still exists in the target vocabulary.
• Mapping Coverage Report: measure how much source data resolved.
• Version Consistency Audit: tie each ETL run to a specific vocabulary release.
• Duplicate Mapping Flag: surface one-to-many pairs that need human review.

A final pass should compare the current load against the previous approved load, not just against the source file. That is where version drift, hierarchy traversal mistakes, and accidental remapping show up together.

Choosing Between Self-Hosted ATHENA and API-Based Tooling

A SNOMED to OMOP mapping pipeline gets painful fast when the vocabulary layer is hard to inspect. Self-hosting ATHENA gives you local control, but it also means someone owns the download, the reload, the refresh cadence, and the query layer that sits on top. API-based tooling removes that maintenance work, as long as your security model and integration rules allow external calls.

Capability	Self-Hosted ATHENA	OMOPHub
Setup time	1–2 days	5 minutes with an API key
Vocabulary updates	Manual re-download and reload on release cycles	Automatic sync with ATHENA
Search	You build it	Built in, including semantic search
REST API, Python SDK, R SDK, MCP server	You build it	Included
FHIR Terminology Service	You build it or deploy Snowstorm	Built in
Infrastructure burden	You operate it	Minimal local overhead

Self-hosting still makes sense for air-gapped environments, proprietary extensions, and regulatory setups that forbid external terminology calls. It also fits teams that need to keep every lookup inside their own network boundary. The trade-off is operational overhead, because the vocabulary store, indexing, and query performance all become part of your ETL surface area.

API-based tooling fits the day-to-day work better when analysts need concept lookup, code resolution, or quick hierarchy exploration without standing up a separate PostgreSQL stack. The practical gain is speed, but the key benefit is consistency. Everyone sees the same vocabulary state through the same interface, which reduces arguments about whether a mapping issue came from local setup or from the source terminology itself.

The hybrid pattern is usually the most useful one. Develop against an API, validate mappings quickly, and cache results locally for production runs where repeatability matters. If you are deciding where that boundary should sit, the OMOPHub ATHENA API overview is a useful reference for the integration side of the choice.

Your Mapping Implementation Checklist

Start with the vocabulary release, normalize the source formats, and decide whether code resolution happens locally or through an API. Then verify the source code path in CONCEPT, confirm the Maps to relationship, and flag any code that maps to multiple targets for manual review. After load, compare source and mapped cohort counts, review unmapped codes, and attach the vocabulary release to the ETL audit trail.

For ad hoc work, the OMOPHub concept lookup tool is useful when you need to inspect a single code before you touch the pipeline. If you automate more of the workflow, the Python SDK, the R SDK, and the MCP server can sit in the same developer toolchain, so analysts and engineers are looking at the same vocabulary truth.

The safest mapping pipelines are boring ones. They resolve codes the same way every time, log every release, and fail loudly when the vocabulary changes underneath them.

If you are cleaning up a fragile SNOMED to OMOP pipeline, start with a vocabulary layer that lets you inspect mappings, compare releases, and resolve codes without guessing. OMOPHub gives you a practical way to do that while keeping your ETL logic grounded in the same OMOP vocabulary structure your warehouse depends on.