MCH·OMOPLive
Under construction This sheet is being revised. Content may change or be incomplete.

Cheat sheet · Sheet 01

OMOP Vocabulary & SQL

Quick reference for the OMOP Standardized Vocabularies and the SQL patterns you write against them most often. Written against OMOP CDM v5.4.

← All cheat sheets

01Concept fields

Field Is Example
Concept The unit of meaning. Every clinical fact is stored as one. Type 2 diabetes mellitus; metformin
concept_id OHDSI integer key. Stable across releases. What you analyze. 201826 = Type 2 diabetes mellitus
concept_code Code as written in the source vocabulary. Not unique on its own, so always pair it with vocabulary_id. SNOMED 44054006; ICD10CM E11.9
concept_name Human-readable label. "Type 2 diabetes mellitus"
vocabulary_id Source vocabulary. SNOMED, RxNorm, LOINC, ICD10CM
domain_id Kind of fact, which determines which CDM table stores the record. Condition → CONDITION_OCCURRENCE
concept_class_id Finer grouping within a vocabulary. Clinical Finding; Ingredient
standard_concept 'S' / 'C' / NULL. See next panel. n/a
invalid_reason 'D' deleted · 'U' updated · NULL valid. Filter IS NULL for current concepts

02Standard vs classification vs source

standard_concept Meaning Example
'S' Standard SNOMED 44054006, Type 2 diabetes mellitus (concept_id 201826)
'C' Classification (grouper) ATC A10B, Blood glucose lowering drugs
NULL Non-standard (source code) ICD10CM E11.9, Type 2 diabetes mellitus without complications

03Relationships and mappings

concept_relationship = directed pairs: concept_id_1, concept_id_2, relationship_id. Has its own invalid_reason, so filter it.

relationship_id Direction Use for
Maps to Non-standard → standard (standard → itself) ETL; source list → standard concepts
Mapped from Reverse of Maps to Which source codes roll up
Maps to value Source code → value concept Measurements, observations
Is a Child → parent Hierarchy
Subsumes Parent → child Hierarchy
Concept replaced by / replaces Deprecation across releases Rescuing invalid_reason = 'U'

04Vocabulary tables

Table Holds
concept id, code, name, vocabulary, domain, class, standard flag, validity dates
concept_relationship Directed concept pairs + relationship_id
concept_ancestor Ancestor/descendant pairs + separation levels
concept_synonym Alternate names
vocabulary vocabulary_version, to cite in every protocol
relationship Types, with is_hierarchical, defines_ancestry
concept_class · domain Class and domain lists
source_to_concept_map Legacy/custom mapping; prefer concept_relationship
drug_strength Ingredient amounts and units

05SQL for lookups

Search by name:

SELECT concept_id, concept_name, vocabulary_id, domain_id, concept_class_id
FROM   concept
WHERE  LOWER(concept_name) LIKE '%hypertension%'
  AND  standard_concept = 'S'
  AND  invalid_reason IS NULL;

Look up a source code:

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

Source code → standard concept:

SELECT src.concept_code  AS source_code,
       src.vocabulary_id AS source_vocabulary,
       tgt.concept_id    AS standard_concept_id,
       tgt.concept_name  AS standard_concept_name,
       tgt.domain_id
FROM   concept              src
JOIN   concept_relationship cr  ON cr.concept_id_1 = src.concept_id
                               AND cr.relationship_id = 'Maps to'
                               AND cr.invalid_reason IS NULL
JOIN   concept              tgt ON tgt.concept_id = cr.concept_id_2
WHERE  src.concept_code  = 'E11.9'
  AND  src.vocabulary_id = 'ICD10CM';

06SQL for expansion and upkeep

Concept → all standard descendants:

SELECT d.concept_id, d.concept_name, d.vocabulary_id,
       ca.min_levels_of_separation
FROM   concept_ancestor ca
JOIN   concept d ON d.concept_id = ca.descendant_concept_id
WHERE  ca.ancestor_concept_id = 201826       -- Type 2 diabetes mellitus
  AND  d.standard_concept = 'S'
  AND  d.invalid_reason IS NULL;

Which source codes roll up to a standard concept:

SELECT src.vocabulary_id, src.concept_code, src.concept_name
FROM   concept_relationship cr
JOIN   concept src ON src.concept_id = cr.concept_id_1
WHERE  cr.concept_id_2 = 320128              -- Essential hypertension
  AND  cr.relationship_id = 'Maps to'
  AND  cr.invalid_reason IS NULL
ORDER  BY src.vocabulary_id, src.concept_code;

Rescue a deprecated concept:

SELECT cr.concept_id_2 AS replacement_concept_id, c.concept_name
FROM   concept_relationship cr
JOIN   concept c ON c.concept_id = cr.concept_id_2
WHERE  cr.concept_id_1 = <deprecated_concept_id>
  AND  cr.relationship_id = 'Concept replaced by';

Record the vocabulary version:

SELECT vocabulary_id, vocabulary_name, vocabulary_version
FROM   vocabulary
ORDER  BY vocabulary_id;

07Quick recall

See… Think…
concept_id OHDSI key. Stable. Analyzable.
concept_code Source code. Meaningless without its vocabulary.
standard_concept = 'S' Analyzable.
standard_concept = 'C' Grouper only. Expand with it; never expect it in the data.
standard_concept IS NULL Source code. Needs mapping.
Maps to Bridge from source to standard.
concept_ancestor The tree, already flattened.
invalid_reason Does this concept still exist in this release?

08References

Corrections or suggestions welcome: danielle@boycedatascience.com