OMOP CDM Vocabulary Tables: A Complete Guide for 2026

You're probably dealing with this right now. One source sends diagnoses as ICD-10-CM, another uses SNOMED CT, the lab feed arrives in LOINC, pharmacy data comes through RxNorm or NDC, and your local source systems still carry years of homegrown codes no one fully trusts anymore.

That's where most OMOP work either becomes disciplined or drifts into expensive ambiguity. The OMOP CDM vocabulary tables are the layer that decides whether your ETL produces a stable analytical model or a pile of records that only look standardized from a distance. When teams struggle with OMOP, the problem usually isn't the clinical event tables first. It's the vocabulary logic underneath them.

The Foundation of Standardized Health Data

Your ETL is loaded, the row counts look right, and the first cohort query still returns nonsense because the same clinical event arrived under three coding systems and two local code sets. That failure usually starts in vocabulary handling, not in the event tables.

The OMOP CDM vocabulary tables provide the reference layer that keeps those source variations from turning into analytic drift. In practice, that means the vocabulary lives separately from observational data and should be treated as versioned reference content, not something an implementation team edits in place through ad hoc SQL.

Why the read-only design matters

A common early instinct with OMOP is to "just fix" a concept in CONCEPT or patch a row in CONCEPT_RELATIONSHIP. I have seen teams do that under delivery pressure. It creates short-term relief and long-term confusion.

Once local edits enter the vocabulary tables, every downstream process becomes harder to trust. SQL-based ETL logic starts depending on site-specific exceptions. API consumers inherit behavior that no longer matches the released vocabulary. Analysts cannot tell whether a concept set reflects OHDSI-standard semantics or a local patch someone added six months ago to get one feed through validation.

The better pattern is simple. Keep OMOP vocabulary tables read-only. Put local remediation in explicit mapping layers, source-to-standard translation tables, ETL rules, or service logic sitting above the vocabulary. That preserves reproducibility and makes version upgrades manageable.

This matters just as much if you query vocabularies directly in SQL as it does if you access them through an API such as OMOPHub. The access method changes. The governance rule does not.

The VOCABULARY table anchors that reference system by identifying the terminologies and releases loaded into your environment. It is part of what turns ICD, SNOMED CT, LOINC, RxNorm, and local source codes into a consistent semantic substrate for ETL, cohort definitions, feature engineering, and AI workflows.

If you are still aligning vocabulary work with the broader CDM structure, the OHDSI data model overview gives the architectural context behind these implementation choices.

Core Vocabulary Concepts Explained

If you don't distinguish source concepts from standard concepts, you'll build mappings that look correct in SQL and still break downstream analytics.

The OMOP vocabulary system is populated and maintained through the ATHENA standardized vocabulary service, which serves as the central repository for terminologies such as SNOMED CT, ICD-10-CM/PCS, LOINC, and RxNorm. That centralization is what allows data from different sources to be harmonized into a single queryable format, as outlined in this ATHENA vocabulary overview.

Standard concept versus source concept

A source concept is the code you received from the original system. It reflects how that source captured the information. You preserve it for lineage, auditability, and ETL traceability.

A standard concept is the canonical OMOP representation used for analytics. This is the concept that should drive cohort logic, phenotype rules, feature generation, and most downstream analysis.

Here's the operational difference:

• Source concept belongs to ingestion and provenance.
• Standard concept belongs to normalization and analysis.
• Both matter because you need to know what arrived and what it became.

Teams that skip source retention usually regret it when they hit an edge case, especially with older local code systems or context-dependent mappings.

Domain and vocabulary aren't the same thing

A vocabulary is the terminology system itself, such as SNOMED CT or LOINC. A domain is the clinical category where a concept belongs inside OMOP, such as Condition, Drug, or Measurement.

That distinction matters because a concept can originate in one vocabulary and still map into a domain-specific target table in the CDM. Analysts often confuse “what coding system did this come from?” with “where does this concept belong analytically?” OMOP keeps those separate on purpose.

Don't choose a target table from the source feed name. Choose it from the mapped domain and the CDM rules.

The mental model that actually works

Think of OMOP vocabulary as a controlled translation system:

• VOCABULARY tells you which terminology family a term belongs to.
• CONCEPT gives each term a stable identifier and metadata.
• Domain metadata tells you where that term belongs in the model.
• Relationships tell you how source and standard representations connect.

If you hold those four ideas firmly, the rest of the OMOP CDM vocabulary tables stop feeling abstract and start behaving like an implementation system rather than a terminology encyclopedia.

Anatomy of the Key Vocabulary Tables

A typical OMOP failure starts in a familiar place. An ETL job resolves source codes, inserts rows, and passes row-count checks. Two weeks later, analysts find diagnoses in the wrong domain, retired concepts still in use, or drug records that cannot support dose logic. The root cause is usually the same. Someone treated the vocabulary layer like a static lookup instead of an operational part of the pipeline.

These are the tables that determine whether your mappings stay correct under real data, vocabulary refreshes, and downstream reuse through SQL, services, or an API.

The tables you'll touch most often

Table	Primary purpose	What you usually use it for
CONCEPT	Master record for individual concepts	Lookup, filtering, domain checks, standard/source status
VOCABULARY	Registry of terminologies	Vocabulary metadata and release awareness
DOMAIN	Clinical categorization	Determining the correct analytical context
CONCEPT_RELATIONSHIP	Directed links between concepts	Maps to, hierarchy edges, non-hierarchical links
CONCEPT_ANCESTOR	Precomputed ancestry paths	Descendant expansion and concept set construction
CONCEPT_CLASS	Concept type metadata	Narrowing by class such as clinical finding or ingredient
DRUG_STRENGTH	Drug-specific strength detail	Medication normalization and dose logic

CONCEPT is where implementation starts

CONCEPT is the table I check first, whether I am writing raw SQL, building ETL logic, or calling a vocabulary API such as OMOPHub behind an application layer. It gives every term a stable concept_id and carries the metadata that drives practical decisions: code, vocabulary, domain, class, validity dates, standard status, and invalidation status.

Three columns deserve repeated scrutiny:

• concept_id is the key you should persist after mapping. Joining on source code later is brittle.
• standard_concept tells you whether the concept is intended for standard analytic use or retained for classification and mapping support.
• invalid_reason tells you whether the concept has been retired or replaced and needs special handling.

Ignore any of those fields and drift shows up fast. The pipeline may still run. The semantics will not hold.

VOCABULARY, DOMAIN, and CONCEPT_CLASS prevent quiet mistakes

VOCABULARY answers a simple question with operational consequences: which terminology system owns this concept. That matters when your logic spans SNOMED CT, ICD-10-CM, LOINC, RxNorm, and local vocabularies, each with different update cycles and mapping behavior.

DOMAIN is the field that keeps ETL honest. It tells you which analytical part of the model a concept belongs to. Teams get into trouble when they route records based on source file names or feed labels instead of mapped domain.

CONCEPT_CLASS adds the precision that broad domain filters cannot provide. In drug work, for example, ingredient, clinical drug, branded drug, and dose form are not interchangeable. In condition work, a hierarchy built from broad findings can behave very differently from one built from billing-oriented source concepts.

A good pattern is simple. Filter by domain first. Refine by concept class when the use case depends on representation level.

CONCEPT_RELATIONSHIP is where mapping logic lives

CONCEPT_RELATIONSHIP carries the edges that make normalization work. For ETL, the relationship used most often is Maps to, because it connects a source-oriented concept to its standard target concept. For maintenance, the same table also helps you inspect replacement paths and other directed links that are easy to misuse if you do not check direction and validity.

That last point matters. Relationship direction is not optional metadata. A query that joins on the wrong side can return technically valid rows and still produce the wrong target concept.

This is also where SQL and API-based access start to converge. In SQL, you write the joins yourself and control every predicate. In OMOPHub or another service layer, the API can hide that complexity and return mapping results directly. The trade-off is visibility. APIs speed up application development, but you still need to know the underlying relationship model well enough to debug unexpected results.

CONCEPT_ANCESTOR supports hierarchy work at production speed

CONCEPT_ANCESTOR exists so you do not have to recurse through terminology trees every time you build a concept set or roll data up to a parent level. It stores precomputed ancestor-descendant paths, which makes descendant expansion far cheaper and more predictable for repeated use.

Use it for hierarchy expansion, cohort definitions, and reusable analytic groupings. Do not use it as a substitute for source-to-standard mapping. That confusion causes concept sets to look correct while ETL outputs stay wrong.

The distinction is practical. CONCEPT_RELATIONSHIP connects concepts across mapping and other directed relationships. CONCEPT_ANCESTOR expands concepts within hierarchical structure.

DRUG_STRENGTH matters as soon as pharmacy data gets serious

DRUG_STRENGTH is easy to ignore until the first request for dose normalization, ingredient-level exposure checks, or route-sensitive medication logic. Then it becomes clear that drug concepts alone are not enough.

This table carries strength details such as amount, numerator, denominator, and units. Those fields support the kinds of transformations that pharmacy pipelines often need but many first-pass OMOP implementations skip. If your workload includes medication quality checks, ingredient rollups, dose comparisons, or AI use cases that depend on normalized therapy signals, DRUG_STRENGTH belongs in the core design, not in a later cleanup phase.

That is the practical anatomy of the vocabulary layer. Each table has a narrow job. The hard part is knowing which one to use for lookup, which one to use for mapping, which one to use for hierarchy, and where an API can save time without hiding logic you still need to understand.

Querying Concepts and Mappings with SQL

SQL is still the most transparent way to understand what the OMOP CDM vocabulary tables are doing. Even if you later move to an API, you should know the join patterns by heart. That's how you debug broken mappings, invalid concepts, and domain mismatches.

Find a concept directly

Start simple. If you know the incoming code and vocabulary, look it up in CONCEPT before doing anything else.

SELECT
  c.concept_id,
  c.concept_name,
  c.concept_code,
  c.vocabulary_id,
  c.domain_id,
  c.concept_class_id,
  c.standard_concept,
  c.invalid_reason
FROM concept c
WHERE c.vocabulary_id = 'ICD10CM'
  AND c.concept_code = 'E11.9';

This query tells you whether the source code exists, whether it's standard, which domain it belongs to, and whether it has been invalidated. In practice, that saves time because many ETL issues start with assumptions about code availability or status.

Map a source concept to its standard equivalent

The usual ETL pattern is CONCEPT to CONCEPT_RELATIONSHIP back to CONCEPT.

SELECT
  src.concept_id AS source_concept_id,
  src.concept_code AS source_code,
  src.concept_name AS source_name,
  std.concept_id AS standard_concept_id,
  std.concept_code AS standard_code,
  std.concept_name AS standard_name,
  std.domain_id AS standard_domain
FROM concept src
JOIN concept_relationship cr
  ON cr.concept_id_1 = src.concept_id
 AND cr.relationship_id = 'Maps to'
JOIN concept std
  ON std.concept_id = cr.concept_id_2
WHERE src.vocabulary_id = 'ICD10CM'
  AND src.concept_code = 'E11.9'
  AND src.invalid_reason IS NULL
  AND std.invalid_reason IS NULL;

This is the canonical source-to-standard pattern. Don't skip the validity checks. A stale source concept can still look joinable.

A quick visual break helps if you want to see the workflow in context.

Pull only standard concepts for a domain

For analytics support, you often want a domain-scoped standard concept inventory.

SELECT
  c.concept_id,
  c.concept_name,
  c.concept_code,
  c.vocabulary_id
FROM concept c
WHERE c.domain_id = 'Condition'
  AND c.standard_concept = 'S'
  AND c.invalid_reason IS NULL
ORDER BY c.vocabulary_id, c.concept_name;

That's useful for concept set authoring, QA validation, and checking whether your ETL is landing on concepts analysts can use.

Join vocabulary logic to clinical data

The point of the vocabulary layer is to support event tables. A basic pattern for condition analytics looks like this:

SELECT
  co.person_id,
  co.condition_start_date,
  co.condition_concept_id,
  c.concept_name AS standard_condition_name
FROM condition_occurrence co
JOIN concept c
  ON c.concept_id = co.condition_concept_id
WHERE c.domain_id = 'Condition'
  AND c.standard_concept = 'S';

Tips that save time

• Validate first: Check the source concept before mapping it.
• Filter invalid concepts: Retired concepts create subtle errors.
• Trust domains: If the mapped domain is wrong for the target event table, stop and inspect.
• Keep source and standard IDs: You'll need both for audit and remediation.

SQL is where you prove your mapping logic. If you can't explain it in joins, you probably shouldn't automate it yet.

Traversing Hierarchies to Build Concept Sets

You start with one diagnosis code, run the cohort, and the counts are obviously wrong. The usual cause is not the SQL syntax. It is the concept set logic. In OMOP, a phenotype rarely maps to one concept_id. It usually starts with a clinical idea, then expands through the vocabulary hierarchy, then removes concepts that do not belong in the analysis.

Confusion often arises about whether CONCEPT_ANCESTOR already stores hierarchical paths or whether you still need recursive SQL. For standard OMOP vocabulary work, CONCEPT_ANCESTOR is the precomputed path table. In practice, that means you should reach for it first when you need all descendants of a standard concept. Recursive traversal is usually reserved for debugging terminology structure or inspecting relationship patterns that are not represented as ancestor-descendant pairs.

Use CONCEPT_ANCESTOR for hierarchical expansion

CONCEPT_ANCESTOR gives you a stable expansion path for phenotype authoring, cohort definitions, and ETL validation. It also keeps hierarchy logic readable in SQL, which matters when someone has to review the result six months later.

SELECT
  ca.ancestor_concept_id,
  a.concept_name AS ancestor_name,
  ca.descendant_concept_id,
  d.concept_name AS descendant_name,
  ca.min_levels_of_separation,
  ca.max_levels_of_separation
FROM concept_ancestor ca
JOIN concept a
  ON a.concept_id = ca.ancestor_concept_id
JOIN concept d
  ON d.concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 201826;

The trade-off is breadth. A broad ancestor can expand into far more descendants than the clinical question needs, so the raw result is usually a draft, not a final concept set.

When CONCEPT_RELATIONSHIP still matters

CONCEPT_ANCESTOR handles subsumption. It does not replace relationship-level work. You still need CONCEPT_RELATIONSHIP for source-to-standard mapping, replacement handling, and terminology-specific links that are not hierarchical.

The order of operations matters:

1. Map non-standard source codes to standard concepts
2. Expand descendants from the standard starting point
3. Apply exclusions and analyst-reviewed carve-outs

That sequence keeps the logic testable. Teams get into trouble when they mix source-code translation and descendant expansion in one query, especially if the output is later reused in ETL and analytics.

If you want the API equivalent of this workflow, the OMOP concept hierarchy API guide shows the same expansion pattern without requiring local traversal code.

Performance patterns that hold up in production

Hierarchy expansion is cheap for a single ad hoc lookup and expensive when it gets embedded everywhere. I treat concept-set expansion as a reusable asset, not a query fragment copied into five downstream jobs.

A few patterns work well:

• Materialize approved expansions: Save descendant sets in a temp table, staging table, or governed concept set table if more than one process uses them.
• Filter after expansion, but before downstream joins: Apply standard_concept, invalid_reason, domain, and vocabulary rules before joining to large event tables.
• Version the result, not just the root concept: Vocabulary refreshes can change descendant membership even when your starting ancestor stays the same.

A practical descendant query

SELECT DISTINCT
  d.concept_id,
  d.concept_name,
  d.domain_id,
  d.vocabulary_id
FROM concept_ancestor ca
JOIN concept d
  ON d.concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 201826
  AND d.standard_concept = 'S'
  AND d.invalid_reason IS NULL;

This query is a good baseline for concept set authoring. For production use, I usually persist the output with the vocabulary version and the root ancestor that generated it. That gives ETL jobs, BI queries, and API consumers one reviewed list to use instead of each component expanding the hierarchy on its own.

Modern Programmatic Access with the OMOPHub API

A common failure mode shows up after the SQL is already written. The ETL works in development, analysts can query concept and concept_relationship, and then a product team asks for live code resolution, autocomplete, FHIR terminology validation, or concept expansion inside an application. At that point, vocabulary access stops being a database problem and becomes an operational service problem.

That is the use case API-first tooling addresses. The OMOP vocabulary API overview is a good reference if you are evaluating where an API fits alongside direct SQL rather than replacing it.

What an API changes in practice

With OMOPHub, teams can call a REST and FHIR terminology service against the ATHENA vocabulary set instead of standing up their own vocabulary delivery layer. In practical terms, that removes a familiar stack of work: downloading releases, loading them into a local store, building search endpoints, maintaining traversal logic, and exposing those functions safely to other systems.

That changes how you implement several common tasks:

• search by meaning, synonym, or partial text instead of exact source code
• resolve a FHIR system URI and code to an OMOP concept in application code
• translate between source and target vocabularies through an API call
• expand hierarchies without writing recursive SQL or shipping local closure tables into every service
• expose terminology operations to downstream applications that expect FHIR semantics

For teams building clinician-facing tools, ETL support utilities, or AI workflows, this matters because the vocabulary logic can live behind a service boundary. SQL is still the right tool for bulk review, audit trails, and set-based validation inside the warehouse. An API is often the better tool when the caller needs low-latency lookups, controlled access, and a stable contract.

Side-by-side trade-offs

Capability	Self-hosted ATHENA	OMOPHub
Setup time	1–2 days	5 minutes (get an API key)
Vocabulary updates	Manual re-download & re-load every ~6 months	Automatic, synced with ATHENA
Full-text / semantic / autocomplete search	Build your own	Built-in
REST API, Python SDK, R SDK, MCP server	Build your own	Included
FHIR Terminology Service	Build your own / deploy Snowstorm	Built-in
FHIR Concept Resolver (Coding → OMOP + CDM table)	Not a standard OHDSI tool	Built-in (POST /v1/fhir/resolve)
Infrastructure cost	$150–400/month (DB + compute)	Free tier; paid tiers for volume
Maintenance burden	Ongoing	Zero

The trade-off is straightforward. Self-hosting gives maximum control and is still the right answer for air-gapped deployments, tightly governed internal networks, and implementations with proprietary vocabulary content that cannot leave the environment. API access reduces platform work, but it also introduces normal service concerns such as authentication, rate limits, and dependency management. For many teams, that is still a good exchange because vocabulary hosting is rarely where they want to spend engineering time.

A practical API example

The basic resolver flow is straightforward:

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

This pattern is useful when the source payload arrives as FHIR Coding or CodeableConcept data and the application needs an OMOP concept before the record reaches the warehouse. It also helps in ETL support tooling, where analysts want to test a source code, confirm the target standard concept, and inspect the likely CDM destination without opening a SQL client.

If you want to inspect concepts interactively before wiring code, the Concept Lookup tool is a practical place to sanity-check names, codes, and relationships.

Useful implementation tips

• Keep SQL for warehouse-grade validation: Use database queries for reconciliation, backfills, and provenance checks across batches or vocabulary versions.
• Use the API at system boundaries: Application services, ingestion gateways, and UI workflows benefit from a consistent resolver instead of embedding vocabulary tables locally.
• Cache stable lookups: Repeated translations for common codes should be cached in the application or staging layer to reduce latency and external calls.
• Persist both request and result context: For ETL support tools, store the incoming coding system, code, returned concept, and vocabulary release context so mapping decisions can be reviewed later.
• Use SDKs where they reduce boilerplate: The R package, MCP server, and OMOPHub GitHub resources help standardize client behavior without rebuilding auth, retries, and request models from scratch.
• Treat API and SQL as complementary, not competing: I use SQL to verify and bulk-audit mappings, and I use an API to serve the same vocabulary operations safely to applications and agents.

The advantage of an API is removing vocabulary operations from the list of infrastructure your team has to own.

ETL Patterns and Vocabulary Versioning

A mapping that looks correct on day one can still break your pipeline six months later.

The failure mode is usually operational, not conceptual. A source system adds new local codes, the vocabulary refresh changes a relationship, or a previously valid standard concept becomes invalid. Then an analyst asks why records loaded in March classify differently from records loaded in July, and the answer is buried across release folders, ETL logs, and a few manual SQL patches. Stable OMOP ETL depends on reproducibility. You need to know exactly which vocabulary state produced each result, whether you are resolving codes with SQL inside the warehouse or calling an external service from an ingestion app.

Versioning patterns that hold up

Treat each vocabulary refresh like an application release. Promote it through dev, test, and production. Record the release identifier with every ETL run. Keep source codes, source concepts, and chosen standard concepts available for audit where your model and governance process allow it. That one discipline saves a large amount of rework when you need to explain a remap or rerun a historical load.

Three patterns consistently pay off:

• Stamp every ETL batch with vocabulary context: Persist the vocabulary release, load date, and mapping logic version so results are reproducible.
• Persist unresolved and ambiguous mappings explicitly: A null standard concept and a reason code are more useful than dropping a source value without explanation.
• Retest concept sets after every vocabulary update: Event-level ETL may stay unchanged while downstream phenotype definitions, cohort logic, and feature engineering outputs shift.

SQL is usually the right tool for bulk regression checks. Compare old and new mappings by source code, count changed standard concepts, and isolate concepts that moved from valid to invalid. API-driven workflows help at a different layer. They let upstream services resolve codes consistently before data even lands in staging, which reduces the number of one-off translation rules hidden inside ETL jobs.

Handling local or proprietary vocabularies

Custom vocabularies expose weak process quickly. The modeling work matters, but packaging and release discipline matter just as much.

When adding custom vocabularies to an OMOP CDM instance, transformation pipelines require three tab-delimited CSV files with case-sensitive suffixes: VOCABULARY.csv, CONCEPT.csv, and CONCEPT_RELATIONSHIP.csv. Those file names are how the pipeline identifies and processes the vocabulary data, as specified in the Microsoft OMOP transformation guidance.

In practice, I treat local vocabulary content like code. It gets version control, review, release notes, and rollback rules. If your team edits custom concepts directly in the database, you lose provenance almost immediately and future refreshes become harder than they should be.

What works and what usually fails

What works:

• Create a governed local vocabulary namespace for custom content instead of inserting local codes into external vocabularies.
• Centralize mapping logic so every ETL path, service, and validation script resolves the same source code the same way.
• Require review before promotion so technical checks and clinical review happen before mappings affect downstream analytics or AI features.

What usually fails:

• spreadsheet-based mappings with no version history
• direct edits in vocabulary tables
• inconsistent naming and concept class conventions for custom concepts
• changing source-to-standard mappings without recording who approved the change and why

For teams automating this work, the practical pattern is straightforward. Use SQL to diff releases, audit invalid concepts, and backfill historical changes at scale. Use an API layer for application-facing resolution, ETL support tooling, and any workflow where you do not want each service reimplementing vocabulary logic on its own.

Choosing Your OMOP Vocabulary Strategy

The right strategy depends on where your complexity sits.

If you need full local control, operate in an air-gapped environment, or manage proprietary terminology assets that can't leave your boundary, self-hosting is still the cleanest answer. You own the database, the release process, the audit posture, and the operational burden that comes with them.

If your main challenge is shipping ETL support tools, FHIR integrations, research workflows, or AI-assisted terminology features quickly, an API model is usually the more efficient path. You remove vocabulary hosting from your backlog and focus on the code that differentiates your work.

A hybrid pattern often works best. Develop against an API for speed, cache or persist approved mappings locally for production stability, and reserve direct SQL access for audit, validation, and exceptional troubleshooting. That gives teams fast iteration without surrendering control where it matters.

---

OMOP vocabulary work gets easier when the access layer stops being the bottleneck. OMOPHub gives teams programmatic access to OHDSI ATHENA vocabularies through REST, FHIR, SDKs, and MCP tooling, so you can search, map, resolve, and traverse concepts without standing up local vocabulary infrastructure first. If you want to move from schema theory to working integrations, it's a practical place to start.