Lesson 14: CONCEPT & VOCABULARY — Standard dictionary foundation

Introduction

The Standardized Vocabularies system is the "brain" of OMOP CDM — all clinical data is linked here. This article focuses on two central tables: CONCEPT (containing ~10 million medical concepts) and VOCABULARY (managing 70+ dictionary sources). Understanding these two tables is the key to mastering OMOP.

---

1. CONCEPT table

1.1. Structure

Column	Type	Description	Example
concept_id	INTEGER	PK — Globally unique ID	201826
concept_name	VARCHAR(255)	Concept name	"Type 2 diabetes mellitus"
domain_id	VARCHAR(20)	Domain (which table)	"Condition"
vocabulary_id	VARCHAR(20)	FK → VOCABULARY	"SNOMED"
concept_class_id	VARCHAR(20)	Classification in vocab	"Clinical Finding"
standard_concept	VARCHAR(1)	S=Standard, C=Classification, NULL	"S"
concept_code	VARCHAR(50)	Original code in vocabulary	"44054006"
valid_start_date	DATE	Effective start date	2002-01-31
valid_end_date	DATE	Expiry date	2099-12-31
invalid_reason	VARCHAR(1)	NULL=valid, U=updated, D=deleted	NULL

1.2. standard_concept — Three types of Concepts

  ┌─────────────────────────────────────────────────────┐
  │ standard_concept = 'S' (Standard)                    │
  │ → Concept "chính thống" dùng trong *_concept_id     │
  │ → VD: SNOMED 201826 "Type 2 diabetes mellitus"      │
  │ → Dùng cho condition_concept_id                      │
  └──────────────────────┬──────────────────────────────┘
                         │ Maps to (nguồn → đích)
  ┌──────────────────────┴──────────────────────────────┐
  │ standard_concept = NULL (Non-standard / Source)      │
  │ → Concept nguồn từ ICD, CPT4, ATC...               │
  │ → VD: ICD10CM 45591837 "E11 - Type 2 DM"           │
  │ → Dùng cho *_source_concept_id                       │
  └─────────────────────────────────────────────────────┘

  ┌─────────────────────────────────────────────────────┐
  │ standard_concept = 'C' (Classification)              │
  │ → Concept dùng cho phân cấp / ancestor              │
  │ → VD: MedDRA PT "Diabetes mellitus"                 │
  └─────────────────────────────────────────────────────┘

1.3. domain_id — Identifies the target table

domain_id	CDM Table	Example
Conditions	CONDITION_OCCURRENCE	Diseases, symptoms
Drugs	DRUG_EXPOSURE	Medicine
Procedure	PROCEDURE_OCCURRENCE	Tips
Measurement	MEASUREMENT	Testing
Observation	OBSERVATION	Acknowledgment
Device	DEVICE_EXPOSURE	Equipment
Spec Anatomic Site	SPECIMEN	Sampling location
Visit	VISIT_OCCURRENCE	Type visit
Type Concept	*_type_concept_id	Data source
Gender	PERSON	Gender
Race	PERSON	Race
Unit	MEASUREMENT.unit	Unit
Route	DRUG_EXPOSURE.route	Route of administration

---

2. VOCABULARY table

2.1. Structure

Column	Type	Description
vocabulary_id	VARCHAR(20)	PK — ID vocabulary
vocabulary_name	VARCHAR(255)	Full name
vocabulary_reference	VARCHAR(255)	Reference URL
vocabulary_version	VARCHAR(255)	Version
vocabulary_concept_id	INTEGER	FK → Representative Concept

2.2. Popular Vocabulary

vocabulary_id	Name	Main domain	Role
SNOMED	SNOMED-CT	Condition, Procedure, Observation	Standard for clinical use
RxNorm	RxNorm	Drugs	Standard for medicine
LOINC	LOINC	Measurement	Standard for testing
ICD10CM	ICD-10-CM	Conditions	Source concept diagnosis
ICD10PCS	ICD-10-PCS	Procedure	Source concept tips
CPT4	CPT-4	Procedure, Measurement	Source billing code
ATC	ATC	Drugs	Classification of drugs
UCUM	UCUM	Unit	Unit of measurement
Gender	OMOP Gender	Gender	Gender
Race	Race	Race	Race
CVX	Vaccines	Drugs	Vaccine codes

---

3. Look up Concept on Athena

3.1. Lookup step

  1. Vào athena.ohdsi.org
  2. Gõ từ khóa: "Type 2 diabetes"
  3. Filter:
     - Standard Concept: Standard ✓
     - Domain: Condition ✓
     - Vocabulary: SNOMED ✓
  4. Kết quả:
     concept_id:     201826
     concept_name:   Type 2 diabetes mellitus
     vocabulary_id:  SNOMED
     concept_code:   44054006
     standard_concept: S
     domain_id: Condition

3.2. SQL lookup

-- Tìm Standard Concept cho "tiểu đường type 2"
SELECT concept_id, concept_name, vocabulary_id,
       domain_id, standard_concept, concept_code
FROM concept
WHERE LOWER(concept_name) LIKE '%type 2 diabetes%'
  AND standard_concept = 'S'
  AND domain_id = 'Condition'
ORDER BY concept_name;

-- Tìm Source Concept (ICD-10) mapping đến Standard
SELECT
    c_src.concept_id AS source_concept_id,
    c_src.concept_code AS icd10_code,
    c_src.concept_name AS icd10_name,
    c_std.concept_id AS standard_concept_id,
    c_std.concept_name AS standard_name,
    c_std.vocabulary_id AS standard_vocab
FROM concept c_src
JOIN concept_relationship cr
    ON c_src.concept_id = cr.concept_id_1
    AND cr.relationship_id = 'Maps to'
JOIN concept c_std
    ON cr.concept_id_2 = c_std.concept_id
    AND c_std.standard_concept = 'S'
WHERE c_src.concept_code = 'E11'
  AND c_src.vocabulary_id = 'ICD10CM';

---

4. concept_class_id — Classification in Vocabulary

4.1. SNOMED

concept_class_id	Meaning	Example
Clinical Findings	Diseases, symptoms	Type 2 DM
Procedure	Tips	Appendectomy
Body Structure	Body structure	Liver
Substance	Quality	Glucose
Observable Entities	Measured Quantity	Blood pressure
Qualifier Value	Additional Value	Severe

4.2. RxNorm

concept_class_id	Level	Example
Ingredient (IN)	Active ingredients	Metformin
Clinical Drug Form (CDF)	HC + dosage form	Metformin Oral Tablet
Clinical Drug (CD)	HC + dose + form	Metformin 500mg Tab
Branded Drug (BD)	Trade name	Glucophage 500mg Tab
Clinical Drug Comp (CDC)	HC + dose	Metformin 500mg
Brand Name (BN)	Trade name	Glucophage
Dose Form (DF)	Dosage form	Oral Tablet

---

5. DOMAIN table

Column	Type	Description
domain_id	VARCHAR(20)	PK
domain_name	VARCHAR(255)	Domain name
domain_concept_id	INTEGER	FK → concept

---

6. CONCEPT_CLASS table

Column	Type	Description
concept_class_id	VARCHAR(20)	PK
concept_class_name	VARCHAR(255)	Class name
concept_class_concept_id	INTEGER	FK → concept

---

7. concept_id = 0 — Special meaning

Concepts	Meaning	When to use
concept_id = 0	"No matching concept"	Unable to map
concept_name	"No matching concept"
domain_id	NULL
vocabulary_id	"None"

-- Kiểm tra % records không map được
SELECT
    'condition_occurrence' AS table_name,
    COUNT(*) AS total_records,
    SUM(CASE WHEN condition_concept_id = 0 THEN 1 ELSE 0 END) AS unmapped,
    ROUND(SUM(CASE WHEN condition_concept_id = 0 THEN 1 ELSE 0 END) * 100.0
          / COUNT(*), 1) AS unmapped_pct
FROM condition_occurrence
UNION ALL
SELECT 'drug_exposure', COUNT(*),
    SUM(CASE WHEN drug_concept_id = 0 THEN 1 ELSE 0 END),
    ROUND(SUM(CASE WHEN drug_concept_id = 0 THEN 1 ELSE 0 END) * 100.0
          / COUNT(*), 1)
FROM drug_exposure
UNION ALL
SELECT 'measurement', COUNT(*),
    SUM(CASE WHEN measurement_concept_id = 0 THEN 1 ELSE 0 END),
    ROUND(SUM(CASE WHEN measurement_concept_id = 0 THEN 1 ELSE 0 END) * 100.0
          / COUNT(*), 1)
FROM measurement;

---

Summary

1. CONCEPT: ~10 million records, each medical concept has a unique concept_id
2. standard_concept: S = Standard (mainly used), C = Classification, NULL = Source
3. domain_id determines which CDM table the data is stored in
4. VOCABULARY: 70+ dictionary sources (SNOMED, RxNorm, LOINC, ICD-10...)
5. concept_id = 0: "cannot map" — used when ETL cannot find the Standard Concept
6. Look up: athena.ohdsi.org or query the CONCEPT table directly

Next article: CONCEPT_RELATIONSHIP & CONCEPT_ANCESTOR — relationship and hierarchy between Concepts.

---

References

• OMOP CDM 5.4 — CONCEPT
• OMOP CDM 5.4 — VOCABULARY
• Athena
• Book of OHDSI — Chapter 5: Standardized Vocabularies