Lesson 20: CDM_SOURCE, METADATA, COHORT & Summary of the entire OMOP CDM 5.4
The table CDM_SOURCE describes the data source, METADATA stores additional information, COHORT manages the research group. Summary of all 37 OMOP CDM 5.4 tables and the next roadmap.
Introduction
Final post of the series! We will learn about the Metadata group (CDM_SOURCE, METADATA) and COHORT — the research group management table. Then summarize all 37+ OMOP CDM 5.4 tables and further learning roadmap.
1. CDM_SOURCE — Data source information
1.1. Table structure
Column
Type
Required
Description
cdm_source_name
VARCHAR(255)
✅
Data source name
cdm_source_abbreviation
VARCHAR(25)
✅
Abbreviated name
cdm_holder
VARCHAR(255)
Ownership organization
source_description
CLOB
Detailed description
source_documentation_reference
VARCHAR(255)
Document URL
cdm_etl_reference
VARCHAR(255)
URL ETL documentation
source_release_date
DATE
Data release date
cdm_release_date
DATE
CDM conversion date
cdm_version
VARCHAR(10)
CDM version (v5.4)
cdm_version_concept_id
INTEGER
FK → CONCEPT
vocabulary_version
VARCHAR(20)
Vocabulary version
1.2. Vietnam data example
INSERT INTO cdm_source (
cdm_source_name,
cdm_source_abbreviation,
cdm_holder,
source_description,
cdm_etl_reference,
source_release_date,
cdm_release_date,
cdm_version,
cdm_version_concept_id,
vocabulary_version
) VALUES (
'Bệnh viện Bạch Mai - Hệ thống HIS',
'BACHMAI_HIS',
'Bệnh viện Bạch Mai',
'Dữ liệu EMR từ hệ thống HIS Bệnh viện Bạch Mai, '
|| 'bao gồm khám ngoại trú và nội trú từ 2020-2024. '
|| 'Chuyển đổi theo OMOP CDM 5.4 phục vụ nghiên cứu '
|| 'dịch tễ học lâm sàng.',
'https://github.com/bachmai-etl/omop-cdm',
'2024-06-30', -- Ngày xuất dữ liệu nguồn
'2024-09-15', -- Ngày hoàn tất ETL
'v5.4',
756265, -- CDM v5.4 concept_id
'v5.0 30-AUG-24' -- Vocabulary version từ Athena
);
1.3. Why is CDM_SOURCE important?
Traceability: know where the data comes from, ETL when
Network studies: compare results between sites
Reproducibility: reproduce research results
Compliance: compliance audit
2. METADATA — Additional information
2.1. Table structure
Column
Type
Required
Description
metadata_id
INTEGER
✅ PK
Unique ID
metadata_concept_id
INTEGER
✅
Type metadata (FK → CONCEPT)
metadata_type_concept_id
INTEGER
✅
Type metadata
name
VARCHAR(250)
✅
Key name
value_as_string
VARCHAR(250)
Text value
value_as_concept_id
INTEGER
Concept value
value_as_number
FLOAT
Numeric value
metadata_date
DATE
Record date
metadata_datetime
DATETIME
Datetime recorded
2.2. Usage example
-- Ghi nhận thông tin ETL
INSERT INTO metadata VALUES (1, 0, 0, 'ETL_TOOL', 'WhiteRabbit + RabbitInAHat', NULL, NULL, '2024-09-15', NULL);
INSERT INTO metadata VALUES (2, 0, 0, 'ETL_VERSION', '1.2.0', NULL, NULL, '2024-09-15', NULL);
INSERT INTO metadata VALUES (3, 0, 0, 'SOURCE_PATIENT_COUNT', NULL, NULL, 125000, '2024-09-15', NULL);
INSERT INTO metadata VALUES (4, 0, 0, 'CDM_PATIENT_COUNT', NULL, NULL, 118500, '2024-09-15', NULL);
INSERT INTO metadata VALUES (5, 0, 0, 'MAPPING_COVERAGE_PCT', NULL, NULL, 94.8, '2024-09-15', NULL);
INSERT INTO metadata VALUES (6, 0, 0, 'COUNTRY', 'Vietnam', NULL, NULL, '2024-09-15', NULL);
METADATA is a flexible key-value table — used to store any information that doesn't fit into CDM_SOURCE.
3. COHORT — Research team
3.1. Table structure
Column
Type
Required
Description
cohort_definition_id
INTEGER
✅
FK → COHORT_DEFINITION
subject_id
INTEGER
✅
Entity ID (usually = person_id)
cohort_start_date
DATE
✅
Cohort entry date
cohort_end_date
DATE
✅
Cohort release date
3.2. COHORT_DEFINITION (recall from Lesson 16)
Column
Type
Description
cohort_definition_id
INTEGER PK
Definition ID
cohort_definition_name
VARCHAR(255)
Cohort name
cohort_definition_description
CLOB
Description
definition_type_concept_id
INTEGER
Type
cohort_definition_syntax
CLOB
Logic to create cohort
subject_concept_id
INTEGER
Object
cohort_initiation_date
DATE
Created Date
3.3. How to use: Create a type 2 diabetes cohort
-- Bước 1: Định nghĩa cohort
INSERT INTO cohort_definition (
cohort_definition_id,
cohort_definition_name,
cohort_definition_description,
definition_type_concept_id,
cohort_definition_syntax,
subject_concept_id,
cohort_initiation_date
) VALUES (
101,
'Tiểu đường Type 2 mới phát hiện 2023',
'BN có chẩn đoán T2DM lần đầu trong 2023, '
|| 'có ít nhất 365 ngày observation trước đó, '
|| 'không có T1DM.',
0,
'{
"PrimaryCriteria": {
"CriteriaList": [{
"ConditionOccurrence": {
"CodesetId": 201826
}
}],
"ObservationWindow": {"PriorDays": 365}
},
"ExclusionCriteria": [{
"ConditionOccurrence": {
"CodesetId": 201254
}
}]
}',
0,
'2024-09-15'
);
-- Bước 2: Populate cohort
INSERT INTO cohort (
cohort_definition_id,
subject_id,
cohort_start_date,
cohort_end_date
)
SELECT
101 AS cohort_definition_id,
co.person_id AS subject_id,
MIN(co.condition_start_date) AS cohort_start_date,
COALESCE(
(SELECT MAX(op.observation_period_end_date)
FROM observation_period op
WHERE op.person_id = co.person_id),
MIN(co.condition_start_date)
) AS cohort_end_date
FROM condition_occurrence co
JOIN concept_ancestor ca
ON co.condition_concept_id = ca.descendant_concept_id
WHERE ca.ancestor_concept_id = 201826 -- Type 2 DM
AND co.condition_start_date BETWEEN '2023-01-01' AND '2023-12-31'
-- Phải có 365 ngày observation trước
AND EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = co.person_id
AND op.observation_period_start_date
<= co.condition_start_date - INTERVAL '365 days'
)
-- Loại trừ T1DM
AND NOT EXISTS (
SELECT 1 FROM condition_occurrence co2
JOIN concept_ancestor ca2
ON co2.condition_concept_id = ca2.descendant_concept_id
WHERE ca2.ancestor_concept_id = 201254 -- Type 1 DM
AND co2.person_id = co.person_id
AND co2.condition_start_date <= co.condition_start_date
)
GROUP BY co.person_id;
3.4. Analysis on cohort
-- Tổng quan cohort T2DM 2023
SELECT
cd.cohort_definition_name,
COUNT(DISTINCT c.subject_id) AS patient_count,
AVG(p.year_of_birth) AS avg_birth_year,
ROUND(
SUM(CASE WHEN p.gender_concept_id = 8507 THEN 1 ELSE 0 END)
* 100.0 / COUNT(*), 1
) AS male_pct
FROM cohort c
JOIN cohort_definition cd
ON c.cohort_definition_id = cd.cohort_definition_id
JOIN person p ON c.subject_id = p.person_id
WHERE c.cohort_definition_id = 101
GROUP BY cd.cohort_definition_name;
4.3. CDM 5.4 — Important changes (compared to 5.3)
Change
Details
EPISODE / EPISODE_EVENT
New table for oncology
measurement_event_id
Polymorphic FK in MEASUREMENT
observation_event_id
Polymorphic FK in OBSERVATION
procedure_end_date/datetime
Add an end date for Procedure
unit_source_concept_id
Add to MEASUREMENT
production_id
Add DEVICE_EXPOSURE (UDI)
5. Data Quality Check (DQD)
5.1. OHDSI Data Quality Dashboard
┌──────────────────────────────────────────┐
│ Data Quality Dashboard (DQD) │
│ │
│ Kiểm tra 3500+ rules: │
│ │
│ 1. Completeness — Đầy đủ │
│ Bao nhiêu % records có concept != 0? │
│ │
│ 2. Conformance — Tuân thủ │
│ Giá trị có hợp lệ? (date, range) │
│ │
│ 3. Plausibility — Hợp lý │
│ Trẻ 5 tuổi có chẩn đoán Alzheimer? │
│ │
│ Output: Bảng báo cáo PASS/FAIL │
│ cho từng rule │
└──────────────────────────────────────────┘
5.2. Quick check using SQL
-- Mapping completeness: % records có concept_id != 0
SELECT
'condition_occurrence' AS table_name,
COUNT(*) AS total,
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 mapped_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 'procedure_occurrence', COUNT(*),
SUM(CASE WHEN procedure_concept_id = 0 THEN 1 ELSE 0 END),
ROUND(SUM(CASE WHEN procedure_concept_id != 0 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
FROM procedure_occurrence
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;
-- Kiểm tra orphan records
-- (records không có observation_period tương ứng)
SELECT 'condition_occurrence' AS src, COUNT(*) AS orphan_count
FROM condition_occurrence co
WHERE NOT EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = co.person_id
AND co.condition_start_date BETWEEN
op.observation_period_start_date
AND op.observation_period_end_date
)
UNION ALL
SELECT 'drug_exposure', COUNT(*)
FROM drug_exposure de
WHERE NOT EXISTS (
SELECT 1 FROM observation_period op
WHERE op.person_id = de.person_id
AND de.drug_exposure_start_date BETWEEN
op.observation_period_start_date
AND op.observation_period_end_date
);
6. OHDSI tools ecosystem
Tools
Role
WhiteRabbit
Scan source data
RabbitInAHat
Design ETL mapping
Usagi
Source code map → Standard Concept
Athena
Download/search Vocabulary
ATLAS
Create cohort, analyze, characterize
WebAPI
Backend API for ATLAS
Achilles
Database profiling & DQD
HADES
R packages for research (PLE, PLP)
DataQualityDashboard
Check data quality
7. Next route
Bạn đã hoàn thành ✅
──────────────────────────────────
OMOP CDM 5.4 — 37+ bảng, 7 nhóm
ETL concepts, Vocabulary system
VN-specific mapping patterns
Bước tiếp theo 📘
──────────────────────────────────
1. Thực hành ETL
→ Dùng WhiteRabbit + RabbitInAHat
→ Chuyển 1 bộ dữ liệu nhỏ sang OMOP
2. ATLAS & Cohort Building
→ Cài ATLAS + WebAPI
→ Tạo cohort definitions UI
3. Achilles + DQD
→ Chạy database profiling
→ Kiểm tra chất lượng dữ liệu
4. Nghiên cứu với HADES
→ Population Level Estimation
→ Patient Level Prediction
→ Characterization
5. Tham gia cộng đồng OHDSI
→ forums.ohdsi.org
→ OHDSI Symposium hàng năm
→ Study-a-thon
Summary
CDM_SOURCE: metadata about data sources, CDM & Vocabulary versions
METADATA: key-value table stores additional information (ETL tool, coverage...)
COHORT + COHORT_DEFINITION: research team management, foundation for ATLAS
OMOP CDM 5.4 includes 37+ tables in 7 groups — all revolving around PERSON
New CDM 5.4: EPISODE/EPISODE_EVENT, polymorphic FK, procedure_end_date
Congratulations on completing the OMOP CDM 5.4 for Beginners series! From here you have a solid foundation to embark on ETL of Vietnamese medical data according to international standards.
