HL7 Patient Administration - ADT Lifecycle

Unify HL7 v2 ADT admit/update/discharge messages from six EHRs across v2.3 through v2.6 into queryable patient tables.

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 table - MSH header + full JSON (one row per message)
-- ============================================================================

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

-- ============================================================================
-- STEP 3: Materialized table - PID/PV1/EVN fields projected as columns
-- ============================================================================

CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.hl7_demos.adt_materialized
USING HL7
LOCATION '{{data_path}}/*.hl7'
OPTIONS (
    hl7_config = '{
        "materialized_paths": [
            "pid_3", "pid_5", "pid_7", "pid_8", "pid_11",
            "pv1_2", "pv1_3", "pv1_7",
            "evn_1", "evn_2"
        ]
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- ============================================================================
-- STEP 4: Explore - MSH header, patient demographics, visit class
-- ============================================================================

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

SELECT pid_5 AS patient_name, pid_7 AS dob, pid_8 AS gender,
       pv1_2 AS patient_class, pv1_3 AS assigned_location
FROM {{zone_name}}.hl7_demos.adt_materialized
ORDER BY pid_5;

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

DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.adt_messages WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.adt_materialized WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.hl7_demos;
DROP ZONE IF EXISTS {{zone_name}};

Description

## When to Use Use this demo when you need to read HL7 v2 ADT (Admit-Discharge-Transfer) feeds from multiple EHRs into SQL without writing a custom parser. It covers the full patient lifecycle - A01 admission, A08 demographics update, A03 discharge - across HL7 versions 2.3, 2.3.1, 2.5, 2.5.1, and 2.6 from EPIC, Folio3/MCM, Ritten, AWS, MegaReg, and Contoso/Azure. It is the right starting point whenever you are ingesting an ADT feed and need to verify that MSH headers, PID/PV1 fields, and Z-segments all land correctly in one unified table. ## What You Will Learn 1. How `CREATE EXTERNAL TABLE ... USING HL7` loads `.hl7` message files into SQL with zero custom parsing. 2. How MSH header fields (`msh_3`, `msh_9`, `msh_12`, ...) are always exposed as first-class columns regardless of HL7 version. 3. How the `materialized_paths` option in `hl7_config` projects PID, PV1, and EVN fields as typed columns alongside the default `df_message_json` payload. 4. How to reach segments that are not materialized (NK1, AL1, DG1, GT1, IN1, and Z-segments like ZMP) through `df_message_json`. 5. How file-level traceability works via the `file_metadata` option and the injected `df_file_name` column. 6. How HL7 escape sequences (`\T\`, `\S\`, `\E\`) are decoded automatically by the parser. ## Prerequisites - DeltaForge GUI running locally with permission to `CREATE ZONE`, `CREATE SCHEMA`, and `CREATE EXTERNAL TABLE`. - Eight sample ADT `.hl7` files downloaded into `{{data_path}}` (the demo loader handles this automatically). - Familiarity with basic HL7 v2 structure (MSH, PID, PV1 segments) is helpful but not required.

Pitfalls

msh_9 groups differently across HL7 versions - v2.3 emits 'ADT^A01' (type^trigger) while v2.5+ emits 'ADT^A01^ADT_A01' (type^trigger^structure). Filter with LIKE 'ADT^A01%' instead of exact equality or you will miss half your messages.
Only the first occurrence of a repeating segment is projected into materialized_paths columns (default repeating_segment_mode = First). For segments that repeat per message (NK1, AL1, DG1, IN1, multi-OBX), you MUST read df_message_json - querying obx_5 will only show the first observation.
Z-segments (custom fields like ZMP) are never materialized as columns regardless of what you list in materialized_paths. They are preserved inside df_message_json and must be extracted with JSON functions.
Fields that do not exist in a given HL7 version or sender profile come back NULL rather than raising an error. A row from AWS v2.3 may have NULL pv1_7 while the EPIC v2.5.1 row has it populated - always plan for cross-system sparsity.
Encoding characters in MSH-1 (field separator) and MSH-2 (component/repetition/escape/subcomponent separators) are honored per-message. Do not assume '|^~\&' - DeltaForge reads them from the MSH header so messages with non-default delimiters still parse.
DROP EXTERNAL TABLE without WITH FILES keeps the underlying `.hl7` files on disk. Use DROP EXTERNAL TABLE ... WITH FILES (as the cleanup script does) when you want the ingestion artifacts gone.

HL7 Patient Administration - ADT Lifecycle

Syntax

Description

Pitfalls

See Also