HL7 Clinical Workflows - Documents, Scheduling & Edge Cases

Parse MDM clinical documents, SIU appointment bookings, and a parser-torture ADT with all six HL7 escape sequences in a single unified table.

Category: hl7

Syntax

-- ============================================================================
-- STEP 1: Zone & Schema
-- ============================================================================

CREATE ZONE IF NOT EXISTS {{zone_name}}
    TYPE EXTERNAL
    COMMENT 'External tables - demo datasets and file-backed data';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.hl7_demos
    COMMENT 'HL7 v2 message-backed external tables';

-- ============================================================================
-- STEP 2: Compact view - MSH header + full JSON for all 4 messages
-- ============================================================================

CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.hl7_demos.clinical_messages
USING HL7
LOCATION '{{data_path}}/*.hl7'
OPTIONS (
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- ============================================================================
-- STEP 3: Materialized view - PID, TXA, SCH, OBX projected as columns
-- ============================================================================

CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.hl7_demos.clinical_materialized
USING HL7
LOCATION '{{data_path}}/*.hl7'
OPTIONS (
    hl7_config = '{
        "materialized_paths": [
            "pid_3", "pid_5", "pid_7", "pid_8",
            "pv1_2", "pv1_3",
            "txa_2", "txa_12", "txa_14",
            "sch_1", "sch_7", "sch_10", "sch_25",
            "obx_2", "obx_3", "obx_5"
        ]
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- ============================================================================
-- STEP 4: Explore clinical documents, appointments, and edge cases
-- ============================================================================

SELECT df_file_name, msh_3 AS sending_app, msh_9 AS message_type, msh_12 AS hl7_version
FROM {{zone_name}}.hl7_demos.clinical_messages
ORDER BY df_file_name;

SELECT pid_5 AS patient_name, txa_2 AS document_type, txa_14 AS document_status,
       obx_3 AS first_section_id, obx_5 AS first_section_text
FROM {{zone_name}}.hl7_demos.clinical_materialized
WHERE msh_9 LIKE 'MDM%';

SELECT pid_5 AS patient_name, sch_1 AS appointment_id, sch_7 AS reason,
       sch_10 AS duration, sch_25 AS status
FROM {{zone_name}}.hl7_demos.clinical_materialized
WHERE msh_9 LIKE 'SIU%'
ORDER BY df_file_name;

SELECT pid_5 AS patient_name_decoded, obx_3 AS first_obs_id, obx_5 AS first_obs_value
FROM {{zone_name}}.hl7_demos.clinical_materialized
WHERE df_file_name LIKE '%edge%';

-- ============================================================================
-- STEP 5: Cleanup
-- ============================================================================

DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.clinical_messages WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.clinical_materialized WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.hl7_demos;
DROP ZONE IF EXISTS {{zone_name}};

Description

## When to Use Reach for this demo after the ADT and lab basics when you need to prove the parser handles real-world clinical workflows beyond the core ADT/ORM/ORU types. It exercises MDM^T02 medical document management (a full 7-section History & Physical narrative), SIU^S12 scheduling (two appointment bookings from different systems on different HL7 versions), and an edge-case ADT^A01 that stress-tests every HL7 escape sequence, empty fields, and multi-line text. Use it when validating a new HL7 integration, debugging character-encoding bugs, or demonstrating that DeltaForge's HL7 reader is production-grade. ## What You Will Learn 1. How to materialize TXA (document metadata), SCH (appointment), and OBX (observation) fields side-by-side in one external table. 2. How the scheduling resource model (RGS, AIS, AIG, AIL, AIP) is preserved inside `df_message_json` for downstream extraction. 3. How DeltaForge decodes the six HL7 escape sequences automatically: `\F\` -> `|`, `\T\` -> `&`, `\S\` -> `^`, `\R\` -> `~`, `\E\` -> `\`, and `\X0D\` -> carriage return. 4. Why SCH field positions drift between versions - v2.5.1 puts 'SCHEDULED' at SCH-18, v2.3 puts 'Scheduled' at SCH-25 - and how to cope using `df_message_json` for reliable lookups. 5. How empty OBX-5 values, numeric OBX with reference ranges, and Z-like custom codes survive parsing without raising errors. 6. How to unify heterogeneous message types (MDM + SIU + ADT) in a single materialized table and filter by `msh_9 LIKE 'MDM%'`. ## Prerequisites - Completed `demo_hl7_patient_admin` and `demo_hl7_lab_orders_results` (or equivalent familiarity with MSH + materialized_paths). - DeltaForge GUI with `CREATE ZONE`, `CREATE SCHEMA`, and `CREATE EXTERNAL TABLE` privileges. - Four sample `.hl7` files (MDM, two SIU, edge-case ADT) downloaded into `{{data_path}}`. - Comfort with reading raw HL7 pipe-delimited payloads when diagnosing escape decoding.

Pitfalls

SCH field positions are not standardized across HL7 versions - the v2.5.1 SIU here puts the filler status at SCH-18 while the v2.3 SIU puts it at SCH-25. Materializing sch_25 yields NULL for the v2.5.1 message. When you need filler status reliably, extract it from df_message_json rather than a single materialized column.
The MDM^T02 H&P contains seven OBX sections (Chief Complaint, HPI, PMH, Meds, Exam, Assessment, Plan), but materialized_paths only surfaces the first one. Always use df_message_json for multi-section clinical narratives or you will silently drop six of seven sections.
Escape sequences are decoded before materialization, so the pid_5 column for the edge-case row contains 'LAST&NAME^FIRST^NAME^MIDDLE~NAME' even though the raw file uses '\T\', '\S\', '\R\'. This is correct behavior - do not try to re-escape when joining against other systems that expect raw HL7.
The edge-case ADT deliberately includes empty OBX-5 values (OBX-3=EMPTY). These parse as empty strings, not NULL. Filter with `obx_5 IS NOT NULL AND obx_5 <> ''` when hunting for populated observations.
The MSH-9 third component (structure code) is present in v2.5+ but absent in v2.3, so 'SIU^S12^SIU_S12' and 'SIU^S12' are two distinct group keys. Always filter with `msh_9 LIKE 'SIU%'`, never `msh_9 = 'SIU^S12'`, when you want all SIU messages regardless of version.
SIU scheduling messages carry the interesting payload in RGS/AIS/AIG/AIL/AIP resource segments, none of which are materialized here. If you need the assigned provider, room, or service, reach into df_message_json - the flat SCH columns alone do not tell the full appointment story.
The demo uses a single shared hl7_demos schema in the external zone. Running this demo's cleanup will also tear down any objects the patient-admin or lab-orders demos left in the same schema - tear down the other demos first or accept the shared lifecycle.

HL7 Clinical Workflows - Documents, Scheduling & Edge Cases

Syntax

Description

Pitfalls

See Also