OMOP vocabularies

What you already know

In the Understanding health data warehouses section, the article Speaking the same language: medical terminologies introduced medical terminologies in general: ICD-10, SNOMED CT, LOINC, ATC, RxNorm, and their respective uses.

If these names don’t ring a bell, read that article first: we assume here that you know what a standardized terminology is and why one is needed.

The previous article, The OMOP CDM model, also detailed the source/standard duality in the event tables (condition_concept_id vs condition_source_value vs condition_source_concept_id). This article exposes the machinery that makes that duality possible.

The problem OMOP solves

Every country, hospital, and software vendor uses its own codes. OMOP therefore needs to do two things:

1. Gather all these vocabularies into a single dictionary that can be queried in SQL.
2. Map local codes to standard concepts so international comparison becomes possible.

The result: a system where every medical code exists in a unified form, identified by a concept_id, and where links between codes (equivalences, hierarchies, mappings) are themselves queryable data.

Three levels to keep apart

This is probably the least obvious point in the whole OMOP system, and the one worth nailing down before going further. When you look at a clinical event row — say a diagnosis in CONDITION_OCCURRENCE — you don’t see two representations in parallel (source and standard), as you might initially think, but three distinct levels.

The distinction that surprises most people sits between cases 1 and 2 (below): being part of the OHDSI Vocabularies doesn’t mean being standard. ICD-10 is integrated, every code has an official concept_id, but those concepts are non-standard — SNOMED CT was chosen as the standard vocabulary for diagnoses. ICD-10 therefore plays the role of source concept, not standard concept.

Three typical cases make the relationship between these levels concrete. We present them from least to most demanding for the ETL.

Case 1 — The source code is already a standard concept

This is the easiest situation: the source already uses the vocabulary that OHDSI designated as the standard for the domain (LOINC for labs, RxNorm for drugs, SNOMED CT for diagnoses when the EHR supports it). The ETL looks the concept up in CONCEPT and places it in both _source_concept_id and _concept_id — both fields carry the same value, without any real mapping work.

Case 2 — The source code is in an OHDSI vocabulary but non-standard

This is the most common case in European databases: the source uses a vocabulary integrated into the OHDSI Vocabularies — ICD-10, ICD-10-CM, CCAM, ATC, NABM… — that isn’t standard for the domain. The ETL finds the official concept_id for the code, then follows the mapping already pre-built in CONCEPT_RELATIONSHIP to reach the standard concept.

Case 3 — The source code is not in the OHDSI Vocabularies

This is the most demanding case: the source code has no equivalent in the vocabularies the OHDSI community maintains (in-house institution thesaurus, proprietary vendor code, short monitor label…). The ETL then has two options.

Option A — Create a custom concept (“2-billionaires”). When the code deserves to be properly tracked, it gets a concept_id above 2,000,000,000. OMOP reserves this range for local concepts to avoid clashes with official identifiers — hence the nickname “2B+” or “2-billionaires”. The concept is added to a vocabulary_id specific to the institution, and the ETL has to draw the Maps to link to a standard concept itself.

Option B — Leave the source concept at 0. When the source code isn’t worth tracking (ephemeral label, unreliable data, no business need), the ETL doesn’t create a custom concept. The _source_value is kept as is, but _source_concept_id is set to 0 (“No matching concept”). The standard concept can still be filled in if the ETL manages to interpret the label.

Who decides what’s standard?

This “standard / non-standard” designation is not an intrinsic property of a vocabulary: it’s a decision made by OHDSI, vocabulary by vocabulary, domain by domain. ICD-10 and SNOMED CT are both mature, widely used diagnosis vocabularies, but OHDSI chose SNOMED CT as the standard. ICD-10 therefore stays in the OHDSI Vocabularies, fully integrated, but confined to the role of source vocabulary.

One concept representing the meaning of each clinical event is designated the Standard. (…) Only these Standard Concepts are used to record data in the CDM fields ending in _CONCEPT_ID.

— Book of OHDSI, chapter 5

The Book of OHDSI spells out the criteria: the choice is made “for each domain separately at the vocabulary level”, based on concept quality, available hierarchy, and the declared purpose of the vocabulary. The OHDSI Vocabulary Working Group maintains these decisions, integrates new vocabularies, and publishes updates through ATHENA.

Standards and sources: who plays which role

Not all terminologies play the same role. For each domain, OMOP designates one standard vocabulary and accepts the others as source vocabularies to be mapped onto the standard.

The three tables at the heart of the vocabularies

OMOP fits its entire vocabulary system into three tables, each complementing the others:

• CONCEPT — the dictionary: a unique identifier for every code in every vocabulary.
• CONCEPT_RELATIONSHIP — the links between concepts: equivalences, mappings, semantic relationships.
• CONCEPT_ANCESTOR — the pre-computed hierarchies, so a single query can reach a concept and all its descendants.

We cover them in that order.

CONCEPT — the universal dictionary

CONCEPT is the central table of the system. Every medical code in every integrated vocabulary appears there as a unique row, with a stable internal identifier: the concept_id.

All vocabularies coexist within the same table: a query like WHERE concept_name LIKE '%diabetes%' can return SNOMED CT results, ICD-10 codes, British Read codes, or concepts from other national vocabularies.

CONCEPT_RELATIONSHIP — the links between concepts

Once the dictionary is in place, concepts need to be linked together. That’s the role of CONCEPT_RELATIONSHIP. Each row defines a directed relationship:

concept_id_1 → [relationship_id] → concept_id_2

A few of the most important relationships:

Standard Concepts are mapped to themselves, non-standard concepts to Standard Concepts. Most non-standard and all Standard Concepts have this relationship to a Standard Concept.

— Book of OHDSI, section 5.3.1

Concretely, if you have an ICD-10 source code E11.9 (type 2 diabetes mellitus without complications) in condition_source_value, its condition_source_concept_id will point to the OMOP concept 45561952 that represents that code. That concept is non-standard, but it has a Maps to relationship pointing to the standard SNOMED CT concept 201826 — which is what you’ll find in condition_concept_id.

CONCEPT_ANCESTOR — pre-computed hierarchies

Medical vocabularies are hierarchical: Type 2 diabetes is a child of Diabetes, which is a child of Endocrine disease. When you want all patients with any kind of diabetes, you don’t want to list every subtype manually.

CONCEPT_ANCESTOR solves this by storing ahead of time every ancestor/descendant pair, at every depth in the hierarchy.

This enables a query such as:

SELECT *
FROM condition_occurrence c
WHERE c.condition_concept_id IN (
  SELECT descendant_concept_id
  FROM concept_ancestor
  WHERE ancestor_concept_id = 201820  -- Diabetes mellitus
)

Without CONCEPT_ANCESTOR, you’d have to do a recursive join on CONCEPT_RELATIONSHIP — much slower and more fragile.

DRUG_STRENGTH — the actual dose of drugs

As recalled in the previous article, DRUG_EXPOSURE doesn’t store the dose actually received by the patient: the quantity column gives only the number of units dispensed (tablets, ampoules, milliliters…). To compute the actual dose, you have to join DRUG_EXPOSURE with DRUG_STRENGTH, which holds the ingredient strength for each standard product.

DRUG_STRENGTH distinguishes two families of dosage forms via different columns:

• Solid or unit-dose forms (tablet, capsule, whole ampoule): the amount per unit lives in amount_value and amount_unit_concept_id.
• Liquid or concentration-based forms (solution, infusion, syrup, patch): the strength is expressed as a numerator_value / denominator_value ratio with their respective units.

The full dose-calculation formulas (by dosage form, with or without concentration) live in the official OMOP dose-calculation documentation. For hands-on practice, the advanced SQL tutorial has dedicated exercises (total dose of a tablet, infusion rate of an IV drug).

Other vocabulary tables

CONCEPT, CONCEPT_RELATIONSHIP, CONCEPT_ANCESTOR and DRUG_STRENGTH carry the bulk of the system. Six auxiliary tables round it out. You rarely touch them in analytical queries, but it’s worth knowing what they’re for.

Concept_id = 0 — OMOP’s “NULL”

A convention already met in the previous article: when a source code has no available mapping, its _concept_id is set to 0.

CONCEPT.concept_id = 0 actually exists in the dictionary; it carries the name “No matching concept”. It’s a design choice to avoid classic SQL NULLs and provide an explicit signal.

In the Standardized Vocabularies, there is no distinction made why a piece of information is not available; it might be because of an active withdrawal of information by the patient, a missing value, a value that is not defined or standardized in some way, or the absence of a mapping record in CONCEPT_RELATIONSHIP. Any such concept is not mapped, which corresponds to a default mapping to the Standard Concept with the concept ID = 0.

— Book of OHDSI, section 5.6.10 “Flavors of NULL”

In practice:

• Regularly count concept_id = 0 rows in each table — it’s the simplest mapping quality indicator.
• A source code can be present (source_value non-empty) and its source_concept_id valid, while the standard _concept_id is 0 if the source concept doesn’t have a mapping yet.

ATHENA — exploring the vocabularies without SQL

ATHENA is the centralized dictionary of OMOP vocabularies, accessed through a graphical interface. It exposes the same information you’d find in the CONCEPT, CONCEPT_RELATIONSHIP, and CONCEPT_ANCESTOR tables of an OMOP instance, but without having to write a SQL query every time you look up a concept: search by label, filters by domain, vocabulary, and standard status, and navigation through relationships and hierarchies in a few clicks.

With a (free) ATHENA account, you can download a vocabulary bundle ready to import into your own OMOP database — a required step for CONCEPT, CONCEPT_RELATIONSHIP and CONCEPT_ANCESTOR to be populated in your instance.

ATHENA is covered in more depth in the OHDSI tools ecosystem article.

In practice: concept sets

For clinical analyses, you rarely work with a single concept at a time. You build concept sets: collections of standard concepts that together represent the same clinical notion. For example, a “heart rate” concept set will gather all the LOINC codes under which this measurement can be recorded (variations by measurement context, method, patient position…), and a “serum creatinine” concept set will likewise gather the different LOINC codes used for the blood creatinine test.

A typical concept set combines several mechanisms:

1. A few root concepts selected explicitly (for example the LOINC concept Heart rate).
2. Inclusion of descendants via CONCEPT_ANCESTOR to capture all subtypes — especially useful for conditions and drugs, which have the most complete hierarchies.
3. Selective exclusion of descendants you don’t want (for example, excluding a variant measured in a specific context).

This is exactly what ATLAS lets you build without coding, or what you write directly in SQL for the exercises that follow. The official ATLAS documentation on concept sets walks through the workflow, and the INDICATE Data Dictionary provides 300+ ready-made clinical concept sets.

Going further

• Our interactive explorer also covers the vocabulary tables with their columns and keys.
• Chapter 5 of the Book of OHDSI is the complete official reference.
• ATHENA to explore concepts and their relationships hands-on.