Mastering Source to Concept Map OMOP: Build Robust ETL

You inherit a source system, pull distinct codes, and quickly realize the easy part was getting the extract. The hard part is figuring out what those codes mean, which ones already align to OMOP, which ones belong nowhere, and which ones need a clinician to break the tie. That's where most OMOP projects stop feeling theoretical.

The happy path is well known. If your source uses supported vocabularies with clean pre-built mappings, the ETL is mostly plumbing. Real projects usually aren't that clean. They include legacy lab catalogs, local diagnosis picklists, retired billing terms, proprietary medication dictionaries, and values that made sense to one department years ago but don't survive contact with a research-grade data model.

A solid Source to Concept Map in OMOP is what turns that mess into something durable. Not just valid enough to load, but traceable enough to defend later when a study result looks odd and someone asks, “Which source values fed this concept, and why?”

Why Your OMOP ETL Lives or Dies by the Source to Concept Map

Most new teams treat mapping as a vocabulary lookup problem. It isn't. It's a lineage problem.

The SOURCE_TO_CONCEPT_MAP table is a mandatory part of the OHDSI OMOP Common Data Model, and the official OMOP CDM ETL tutorial places generation of that map at step 6 of vocabulary processing, after loading source codes and reviewing suggested mappings with medical experts, as described in The Book of OHDSI on standardized vocabularies. That sequence matters. You don't guess your way into a final mapping table. You prepare source codes, review them, then persist the decisions in a place the ETL can use repeatedly.

What this table actually protects

When teams skip the map and write direct transformations into ETL SQL, they create a brittle system. A diagnosis code gets replaced inline. A local medication term gets normalized in a CASE statement. A lab code gets translated through a spreadsheet no one versions properly. The load may succeed, but the evidence trail disappears.

That's exactly what the Source to Concept Map prevents. It stores the relationship between raw source values and the OMOP concept IDs used downstream. You preserve the original fact and the standardized interpretation side by side.

Practical rule: If you can't explain a standard concept by pointing to the exact source value and mapping decision that produced it, the ETL isn't ready for validation.

The cost of skipping this shows up later:

• During issue triage: Analysts find an unexpected cohort inclusion and engineering has to reverse-engineer old transformation logic.
• During study replication: A second team can't tell whether a result changed because the source changed, the vocabulary changed, or the ETL changed.
• During governance review: Nobody can show which local terms were intentionally mapped versus implicitly defaulted.

Why this is bigger than one table

OMOP standardized vocabularies are mandatory reference tables for any OMOP CDM instance, and the mapping layer is what bridges source values to source concepts and then to standard concepts for analysis, as defined in the OHDSI vocabulary guidance. That bridge is what gives your dataset analytical integrity.

A useful mental model is simple:

ETL layer	What it answers
Source extract	What did the source system store?
Source to concept map	How did we interpret that source value?
Standardized OMOP tables	How do we analyze it consistently?

If you build this middle layer carefully, the rest of the ETL gets calmer. If you rush it, every downstream artifact inherits the ambiguity.

Choosing Your Mapping Strategy for OMOP

Most documentation assumes your source vocabularies already exist in ATHENA with obvious mappings. That's not where teams struggle. The hard part is legacy and local code systems that don't have official pre-mappings.

That gap is real. Existing content on this topic tends to focus on pre-built ATHENA mappings but doesn't adequately address legacy or non-standard vocabularies that lack official mappings, leaving engineers without a clear workflow for fuzzy matching or custom map creation, as discussed in this analysis of OMOP concept mapping gaps.

Three strategies and their trade-offs

Teams usually fall into one of three patterns.

Strategy	What works	What breaks
Fully manual	Strong control for ambiguous terms and clinical nuance	Slow, expensive, hard to repeat
Fully automated	Fast for obvious matches and high-volume normalization	Produces silent errors when labels are vague or overloaded
Hybrid	Scales routine work while preserving expert review where needed	Requires workflow discipline and clear review queues

The wrong strategy depends on your source data.

If you have a small, clinically sensitive local vocabulary with messy synonyms, manual review may be the right starting point. If you have a giant list of near-standard codes, automated candidate generation makes sense. Most organizations need both.

What fully manual gets right

Manual mapping is still necessary for:

• Legacy codes with missing definitions: The source label alone may not tell you whether a concept is a finding, procedure, or billing artifact.
• Department-specific abbreviations: “PT eval” might mean a therapy evaluation in one system and prothrombin time in another.
• Clinically loaded synonyms: Similar phrases can point to very different standard concepts.

The mistake is using manual review for everything. Teams burn weeks reviewing exact or near-exact matches that software could have queued accurately for approval.

A clinician's time belongs on ambiguity, not on confirming obvious one-to-one mappings all day.

Where automation helps and where it hurts

Automated mapping is useful when the source vocabulary has enough text quality to support candidate generation. Exact string match, normalized token match, hierarchy-aware search, and semantic similarity can all produce good first-pass candidates.

Automation fails when engineers confuse candidate generation with final mapping. A fuzzy match engine can suggest. It can't own the decision when the source term is underdefined.

Use automation for these jobs:

• Deduplicating variants: Normalize spelling, punctuation, and casing before search.
• Generating ranked candidates: Return likely OMOP concepts for review.
• Routing obvious mappings: Auto-approve only under strict rules you can defend.

Don't auto-approve because a score looks high. Approve because the source term, domain, and intended target use agree.

The hybrid workflow that usually survives production

The durable pattern is straightforward:

1. Extract distinct source values with context.
2. Search for candidate OMOP concepts programmatically.
3. Auto-resolve only narrow, high-confidence classes.
4. Queue the rest for human review.
5. Persist every approved decision into the map.
6. Re-run unresolved values on each vocabulary refresh.

That workflow fits much better than one-off spreadsheet cleanups. It also makes handoff easier between engineering and clinical reviewers.

For teams modernizing ETL pipelines, mapping in ETL workflows is the right frame. Mapping isn't a side task. It's a managed sub-pipeline with candidate generation, adjudication, audit history, and reprocessing logic.

Finding OMOP Concepts Fast with ATHENA and OMOPHub

You have a few thousand distinct source values from a legacy system, half of them poorly labeled, and reviewers are waiting on candidate concepts. Search speed becomes a delivery issue at that point, not a convenience issue.

Concept lookup slows down for a predictable reason. Teams start mapping before they decide how they will access OMOP vocabularies day after day, refresh after refresh.

The traditional ATHENA path is still valid. Download the vocabulary files, load them into a local database, index the core tables, learn the schema well enough to query relationships correctly, and repeat the refresh process whenever vocabularies change. I use that approach in controlled environments, especially when policy requires local hosting or when a team needs custom extensions next to the standard vocabulary. The trade-off is obvious. Your ETL team now owns another service.

What self-hosting ATHENA really asks of the team

Self-hosting is usually less about concept quality and more about operational overhead. Someone has to provision storage, load updates, watch query performance, and make sure lookup behavior stays consistent between development and production. That work is reasonable if you need tight control. It is wasted effort if the core issue is finding and reviewing candidates quickly.

A cleaner option for many teams is an API-first vocabulary workflow. Instead of maintaining a local vocabulary stack first and mapping second, the team can search, inspect, resolve, and record decisions immediately. That matters a lot on messy projects where source codes are non-standard, local, deprecated, or mixed with free text.

For a more detailed comparison, the guide on ATHENA and OMOP vocabulary access patterns covers the implementation choices in more depth.

OMOPHub vs. Self-hosted ATHENA

Capability	Self-hosted ATHENA	OMOPHub
Setup time	Local install, database load, indexing, and query setup	API-first setup
Vocabulary updates	Team-managed reload process	Managed updates
Search options	Team builds query layer	Built-in search modes
REST API and SDKs	Team builds or adds them	Included
FHIR Terminology Service	Separate setup if needed	Built-in
FHIR concept resolver	Custom implementation	Built-in
Infrastructure cost	Ongoing database and compute cost	Usage-based platform option
Maintenance burden	Team-owned	Vendor-managed

That table is not an argument against self-hosting. It is a reminder to choose it on purpose. If security, air-gapped deployment, or custom vocabulary logic drives the decision, fine. If the team just copied the usual OHDSI setup without thinking through maintenance, that choice often shows up later as delayed mapping reviews and stale vocabulary refreshes.

What an API-first workflow changes in practice

An API-first setup shortens the path from source term to reviewable candidate set. That is the primary benefit.

OMOPHub exposes ATHENA-backed OMOP vocabularies through REST and FHIR APIs. It supports full-text, faceted, fuzzy, autocomplete, and semantic search, and it can resolve a system URI plus code into an OMOP concept with domain and target table metadata. For teams dealing with inherited source systems, that helps in two places. It speeds up the first pass, and it gives reviewers more context before they approve a mapping.

That second point matters more than teams expect. A fast lookup tool is useful only if it returns enough detail to reject bad candidates early. Legacy source terms are often ambiguous. “Cold therapy,” “MS,” or a local lab mnemonic can point to several valid-looking concepts. Good search reduces hunting time. It does not remove the need to inspect vocabulary, domain, and intended ETL use together.

A practical way to use OMOPHub is to treat it as the search and resolution layer while your own staging tables remain the system of record for approved mappings. That keeps the pipeline stable. If you later switch tools, you are replacing candidate generation, not rebuilding every decision your reviewers already made.

A short feature summary is enough here because these capabilities map directly to day-to-day ETL work:

Platform detail	What it means for ETL teams
Large OMOP concept coverage	Better candidate recall across standard and inherited source vocabularies
Broad vocabulary support	More useful when the source system mixes standard, local, and legacy code sets
FHIR operations	Easier integration if upstream systems already expose terminology in FHIR form
Python, R, and MCP tooling	Faster integration into scripts, review workflows, and analyst tooling

Sometimes the fastest way to align a team is to show the search and resolution flow before writing ETL code.

Fast concept search saves reviewer time. It does not approve mappings for you.

Implementing the Source to Concept Map in Your ETL

The ETL usually fails in a boring place. A local diagnosis code gets mapped during testing, the reviewer remembers what it meant, and six months later nobody can explain why that code lands in a standard Condition concept instead of Observation. The load still runs. The analytics are wrong.

A source to concept map prevents that failure only if the ETL treats mapping as a controlled artifact, not a side effect of lookup code. Candidate search can be automated. Approval, versioning, and replay need to be deterministic. That matters even more when the source system mixes standard codes, retired local values, and text labels that changed over time.

Start with a repeatable extraction set

Build a distinct source vocabulary worklist before any concept resolution. Pull the exact raw value, the source description, where it came from, and enough clinical or operational context for a reviewer to make a defensible choice. Teams that extract only the code create avoidable review backlog, especially with legacy systems where the same code can mean different things by table or column.

A practical staging table usually includes:

• The raw source value: Preserve the original code or string exactly as received.
• Display text from source: Reviewers need the label and any local wording.
• Origin context: Diagnosis field, lab catalog, medication list, flowsheet item, charge code, or other source location.
• Candidate status: New, auto-matched, needs review, approved, rejected.
• Approved target fields: The concept IDs and mapping metadata the ETL will use at load time.

If the source is messy, add one more field for reviewer notes. That small decision saves time later when someone asks why a retired billing code was mapped to a broader standard concept instead of marked unmapped.

Test resolution before wiring it into the ETL

For standard code paths, validate the lookup with a plain request first. It is the fastest way to confirm that your assumptions about vocabulary system, code format, and target domain are correct before you hide the behavior inside batch logic.

The following example resolves a SNOMED code to its OMOP standard concept and target table:

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"}'

Use that kind of call as a smoke test, not as your mapping workflow. It works well for confirming standard terminology already present in the source. It does not solve the harder part, which is handling local codes, near-duplicates, and values that need a reviewer to choose between several plausible OMOP concepts.

Separate candidate generation from approved mappings

Keep search and approval in different parts of the pipeline. Let code propose candidates in bulk. Let reviewers approve the final target that gets persisted. When teams combine those steps, they usually end up re-running search against a changing vocabulary and unintentionally changing historical decisions.

Here is a Python sketch that keeps the lookup simple and explicit:

import os
import requests

API_KEY = os.environ["OMOPHUB_API_KEY"]

payload = {
    "system": "http://snomed.info/sct",
    "code": "44054006",
    "resource_type": "Condition"
}

resp = requests.post(
    "https://api.omophub.com/v1/fhir/resolve",
    headers={
        "Authorization": f"Bearer {API_KEY}",
        "Content-Type": "application/json"
    },
    json=payload,
    timeout=30
)

resp.raise_for_status()
result = resp.json()

print(result)

If your team prefers SDKs over raw HTTP, the maintained repos are the Python SDK, R SDK, and MCP server. Model-facing examples are collected in the LLM reference text.

In practice, I recommend writing the search results to a staging table with a batch ID, candidate rank, and the raw response payload. Then write approved mappings to a separate table that the ETL reads from directly. That split costs a little more up front, but it prevents a common failure mode where a better search result appears later and changes production output without review.

What to persist in your map

Store enough information to replay the mapping decision and explain it later. A two-column lookup breaks down fast once you have reviewer workflows, vocabulary refreshes, and exceptions from legacy systems.

At minimum, keep:

1. Source value and source concept context
2. Chosen OMOP concept ID
3. Mapping relationship used
4. Reviewer or approval process marker
5. Vocabulary version or release marker
6. Timestamps for creation and update

Also persist the mapping status for unresolved values. Unmapped is a valid state. Forcing a weak match just to improve coverage creates more downstream pain than carrying a controlled backlog of unresolved source terms.

Store the decision and the reason for it. Next year's ETL maintainer will need both.

Ensuring Quality and Maintaining Your OMOP Concept Map

A map that loads cleanly can still be analytically unsafe. Quality work starts after the first pass of approvals.

The most important automated control is straightforward. OMOP data quality audits require verification that every source concept ID in the SOURCE_TO_CONCEPT_MAP has a valid standard concept mapping linked through CONCEPT_RELATIONSHIP, and the OHDSI Data Quality Dashboard includes the standardConceptRecordCompleteness check to validate that requirement for analytic readiness, as documented in the DQD check reference.

The checks that catch real problems

This isn't just a box to tick. When that check fails, it usually means one of a few things:

• A vocabulary issue exists: The local source concept doesn't connect to a standard concept as expected.
• Your local mapping is incomplete: A row exists in the map, but the path to a standard concept isn't valid.
• The wrong relationship got used: The mapping was stored in a way your ETL or audit process can't interpret correctly.

In practice, your QA cycle should include both automated and human checks.

QA area	What to review
Coverage	Which source values remain unmapped
Ambiguity	Which values have multiple plausible targets
Validity	Whether the chosen concept resolves to a standard target
Drift	Whether vocabulary updates changed mapping behavior

Versioning is where mature teams separate themselves

A Source to Concept Map isn't static. Source systems change. ATHENA releases change. Local definitions get clarified. Concepts get updated.

That means your maintenance process needs to answer a few operational questions:

• What happens when a previously approved source term gets a better target in a later review?
• How do you reprocess only impacted rows instead of rebuilding everything blindly?
• Can you reproduce a study using the same mapping state that existed when the study ran?

The cleanest pattern is to version the map like code. Freeze approved mapping snapshots for each ETL release. Re-run unresolved and recently changed values against current vocabularies. Require targeted review for anything that changed materially.

If analysts can't tie a study extract to a specific mapping snapshot, reproducibility is already compromised.

A short maintenance routine that works

A lightweight but effective cadence looks like this:

• After each ETL cycle: Report newly seen source values.
• After each vocabulary refresh: Re-evaluate open and impacted mappings.
• Before each research release: Validate the current map against DQD and review exceptions.
• After each issue report: Trace back from standard concept to source value and update the map, not just the downstream SQL.

That discipline prevents the common failure mode where teams patch analytic tables directly and leave the underlying mapping debt untouched.

Productionizing Your Map and Expert Tips

A map that works in a workshop can still fail in production.

The usual failure pattern is familiar. The team gets initial mappings approved, the first ETL run succeeds, and everyone moves on. Three months later, runtime is creeping up, analysts are comparing results from different extracts, and nobody can explain why the same source code landed on two different concepts across releases. That is a production problem, not a vocabulary problem.

At this stage, the Source to Concept Map needs to behave like a governed data asset. The practical concerns are performance, auditability, and controlled use of outside services.

Performance usually comes down to avoiding repeated work. Real-time lookups help during triage, review, and small incremental loads. They are a poor choice for high-volume reruns of stable source values. Cache approved mappings in staging or in a dedicated mapping table, keyed by the exact source code, source field, and any context your approval process requires. That keeps ETL runs predictable and prevents external dependency issues from blocking routine loads.

Auditability is less technical and more about discipline. If a concept assignment changes, the change should pass through the same review path as ETL logic. Temporary spreadsheets, sidecar CSVs, and analyst-owned overrides create the kind of drift that only shows up during study review or validation. By then, cleanup is expensive.

External lookup services can still fit cleanly into a regulated environment. In many OMOP mapping workflows, the request payload contains codes, source terms, field names, and concept identifiers rather than patient-level records. Security review is usually easier when teams keep that boundary clear and document it early.

Tips that prevent future pain

• Cache approved mappings locally: Use external services to find candidates and speed review. Run production ETL against approved local decisions.
• Pass enough context for ambiguous terms: A source code by itself is often not enough. Source column, domain hint, unit, or local description can prevent a bad match.
• Split candidate generation from approval: Automated suggestion helps with scale. Human approval protects data quality, especially with local codes and legacy terms.
• Treat mapping changes like code changes: Review them, version them, and release them on purpose.
• Test failure paths: Decide what ETL should do when a lookup service is slow, unavailable, or returns a different candidate set than expected.
• Keep unresolved terms visible: A small daily queue is manageable. A quarter's worth of unknown values is how teams end up hard-coding exceptions under deadline pressure.
• Read the product guidance before building wrappers: As noted earlier, the product docs and lookup interfaces answer many implementation questions faster than trial and error.

The teams that keep a source to concept map healthy are usually the ones working with messy source systems, not pristine ones. They expect local codes, retired vocabularies, and odd edge cases. They build for replay, review, and change from the start.

If your team is tired of wrestling with local vocabulary infrastructure before you can even start mapping, OMOPHub is a practical way to search ATHENA vocabularies, resolve FHIR codes to OMOP concepts, and integrate concept mapping into ETL workflows with REST, FHIR, Python, R, and MCP tooling.