xDev Asia

Record deaths (DEATH), long-term disease processes (EPISODE — new CDM 5.4) such as cancer treatment, and associate events in episodes (EPISODE_EVENT).

Introduction

The last three tables in the Clinical Data group: DEATH records death events, EPISODE (new in CDM 5.4) records long-term clinical/treatment progression, and EPISODE_EVENT links events within an episode. EPISODE is the most notable addition to CDM 5.4, especially important for cancer research.

1. DEATH — Death record

1.1. Table structure

Column	Type	Required	Description
`person_id`	INTEGER	✅ PK/FK	Patient (1 record/patient)
`death_date`	DATE	✅	Date of death
`death_datetime`	DATETIME		Date and time of death
`death_type_concept_id`	INTEGER	✅	Data source
`cause_concept_id`	INTEGER		Cause of death (SNOMED)
`cause_source_value`	VARCHAR(50)		Original ICD
`cause_source_concept_id`	INTEGER		Original concept

1.2. Important characteristics

1 unique record per person — if there are multiple sources, choose the most trustworthy
person_id is both PK and FK → does not have its own death_id
cause_concept_id: use SNOMED for the main cause

1.3. death_type_concept_id

Concept ID	Source	Description
32817	EHR	Recorded from HIS
32810	Claim	Social insurance data
32885	Death certificate	Death certificate
32886	National Death Index	National Register

1.4. For example

-- BN tử vong do nhồi máu cơ tim cấp
INSERT INTO death (
    person_id, death_date,
    death_type_concept_id,
    cause_concept_id,
    cause_source_value,
    cause_source_concept_id
) VALUES (
    100001, '2024-06-20',
    32885,                        -- Death certificate
    4329847,                      -- SNOMED: AMI
    'I21.9',                      -- ICD-10
    45572161                      -- ICD10CM concept
);

1.5. SQL analysis

-- Top 10 nguyên nhân tử vong
SELECT
    c.concept_name AS cause_of_death,
    COUNT(*) AS death_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct
FROM death d
JOIN concept c ON d.cause_concept_id = c.concept_id
WHERE d.cause_concept_id != 0
GROUP BY c.concept_name
ORDER BY death_count DESC
LIMIT 10;

-- Tỉ lệ tử vong sau nhập viện ICU
SELECT
    ROUND(
        COUNT(DISTINCT d.person_id) * 100.0 /
        NULLIF(COUNT(DISTINCT v.person_id), 0), 1
    ) AS mortality_rate_pct
FROM visit_occurrence v
LEFT JOIN death d ON v.person_id = d.person_id
    AND d.death_date BETWEEN v.visit_start_date
    AND v.visit_start_date + INTERVAL '30 days'
WHERE v.visit_concept_id = 32037;  -- ICU visit

2. EPISODE — Pathological process (new CDM 5.4)

2.1. Why do we need EPISODE?

Before CDM 5.4, there was no way to represent the "cancer treatment process" — events (diagnosis, chemotherapy, radiation therapy, surgery) were scattered across multiple tables. EPISODE brings them together into a complete "story".

  Trước CDM 5.4:

  CONDITION: Ung thư phổi ─────────── (rời rạc)
  PROCEDURE: Sinh thiết phổi ────────── (rời rạc)
  DRUG:      Cisplatin cycle 1 ──────── (rời rạc)
  DRUG:      Cisplatin cycle 2 ──────── (rời rạc)
  PROCEDURE: Phẫu thuật cắt thùy phổi ─ (rời rạc)

  Sau CDM 5.4:

  EPISODE: "Điều trị ung thư phổi giai đoạn 3"
       │
       ├── EPISODE_EVENT → CONDITION (chẩn đoán)
       ├── EPISODE_EVENT → PROCEDURE (sinh thiết)
       ├── EPISODE_EVENT → DRUG (hóa trị cycle 1)
       ├── EPISODE_EVENT → DRUG (hóa trị cycle 2)
       └── EPISODE_EVENT → PROCEDURE (phẫu thuật)

2.2. EPISODE table structure

Column	Type	Required	Description
`episode_id`	BIGINT	✅ PK	Unique ID
`person_id`	INTEGER	✅ FK	Patients
`episode_concept_id`	INTEGER	✅	Episode type
`episode_start_date`	DATE	✅	Start date
`episode_start_datetime`	DATETIME
`episode_end_date`	DATE		End date
`episode_end_datetime`	DATETIME
`episode_parent_id`	BIGINT		Episode parent (hierarchy)
`episode_number`	INTEGER		Serial number
`episode_object_concept_id`	INTEGER	✅	Episode object
`episode_type_concept_id`	INTEGER	✅	Data source
`episode_source_value`	VARCHAR(50)		Original code
`episode_source_concept_id`	INTEGER

2.3. episode_concept_id — Episode type

Concept ID	Episode Type	Example
32528	Disease first occurrence	Lung cancer for the first time
32529	Disease recurrence	Cancer recurrence
32531	Treatment regimen	Cisplatin-Etoposide regimen
32532	Treatment cycle	Cycle 1, Cycle 2...

2.4. For example: Lung cancer treatment

-- Episode cha: Bệnh ung thư phổi
INSERT INTO episode (
    episode_id, person_id,
    episode_concept_id,
    episode_start_date, episode_end_date,
    episode_parent_id,
    episode_object_concept_id,
    episode_type_concept_id
) VALUES (
    200001, 100001,
    32528,                            -- Disease first occurrence
    '2024-01-15', NULL,               -- Chưa kết thúc
    NULL,                             -- Không có cha
    4311499,                          -- SNOMED: Lung cancer
    32817                             -- EHR
);

-- Episode con: Phác đồ hóa trị
INSERT INTO episode (
    episode_id, person_id,
    episode_concept_id,
    episode_start_date, episode_end_date,
    episode_parent_id,
    episode_number,
    episode_object_concept_id,
    episode_type_concept_id
) VALUES (
    200002, 100001,
    32531,                            -- Treatment regimen
    '2024-02-01', '2024-06-30',
    200001,                           -- Thuộc episode ung thư phổi
    1,
    35804410,                         -- Cisplatin regimen
    32817
);

-- Episode con: Cycle 1
INSERT INTO episode (
    episode_id, person_id,
    episode_concept_id,
    episode_start_date, episode_end_date,
    episode_parent_id,
    episode_number,
    episode_object_concept_id,
    episode_type_concept_id
) VALUES (
    200003, 100001,
    32532,                            -- Treatment cycle
    '2024-02-01', '2024-02-21',
    200002,                           -- Thuộc phác đồ
    1,                                -- Cycle 1
    35804410,
    32817
);

3. EPISODE_EVENT — Event binding

3.1. Table structure

Column	Type	Required	Description
`episode_id`	BIGINT	✅ FK	Episode
`event_id`	BIGINT	✅	Event ID
`episode_event_field_concept_id`	INTEGER	✅	Table containing event

3.2. episode_event_field_concept_id

Concept ID	Event Table
1147127	condition_occurrence.condition_occurrence_id
1147094	drug_exposure.drug_exposure_id
1147082	procedure_occurrence.procedure_occurrence_id
1147138	measurement.measurement_id
1147165	device_exposure.device_exposure_id

3.3. For example: Attach events to Cycle 1

-- Chẩn đoán ung thư → Episode chẩn đoán
INSERT INTO episode_event VALUES (
    200001,                           -- Episode: lung cancer
    70010,                            -- condition_occurrence_id
    1147127                           -- condition_occurrence table
);

-- Hóa trị Cisplatin → Episode Cycle 1
INSERT INTO episode_event VALUES (
    200003,                           -- Episode: Cycle 1
    80010,                            -- drug_exposure_id (Cisplatin)
    1147094                           -- drug_exposure table
);

-- XN máu trước hóa trị → Episode Cycle 1
INSERT INTO episode_event VALUES (
    200003,
    110020,                           -- measurement_id (CBC)
    1147138                           -- measurement table
);

4. Application of EPISODE in research

-- Tìm BN ung thư phổi có >= 4 cycle hóa trị
SELECT
    e_disease.person_id,
    c_disease.concept_name AS cancer_type,
    COUNT(e_cycle.episode_id) AS total_cycles
FROM episode e_disease
JOIN concept c_disease
    ON e_disease.episode_object_concept_id = c_disease.concept_id
JOIN episode e_regimen
    ON e_disease.episode_id = e_regimen.episode_parent_id
    AND e_regimen.episode_concept_id = 32531  -- Treatment regimen
JOIN episode e_cycle
    ON e_regimen.episode_id = e_cycle.episode_parent_id
    AND e_cycle.episode_concept_id = 32532    -- Treatment cycle
WHERE e_disease.episode_concept_id = 32528    -- First occurrence
  AND c_disease.concept_id = 4311499          -- Lung cancer
GROUP BY e_disease.person_id, c_disease.concept_name
HAVING COUNT(e_cycle.episode_id) >= 4;

-- Timeline điều trị 1 BN
SELECT
    e.episode_number,
    ec.concept_name AS episode_type,
    e.episode_start_date,
    e.episode_end_date,
    oc.concept_name AS episode_object
FROM episode e
JOIN concept ec ON e.episode_concept_id = ec.concept_id
JOIN concept oc ON e.episode_object_concept_id = oc.concept_id
WHERE e.person_id = 100001
ORDER BY e.episode_start_date, e.episode_number;

Summary

DEATH: 1 record/person, cause of death using SNOMED
EPISODE (new CDM 5.4): pathology/treatment process, parent-child hierarchy support
EPISODE_EVENT: link events from multiple tables to the episode
EPISODE is designed primarily for oncology but is applicable to all chronic diseases
Structure: Disease → Treatment Regimen → Treatment Cycle → Events

Next article: Start Part 5 — Standardized Vocabularies, a standardized dictionary system.

Lesson 13: DEATH, EPISODE & EPISODE_EVENT