Model the end-to-end lab workflow with HL7 v2 ORM orders and ORU results, including abnormal flags, value types, and the 14-analyte comprehensive metabolic panel.
-- ============================================================================
-- 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: Orders-only table via orm*.hl7 glob
-- ============================================================================
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.hl7_demos.lab_orders
USING HL7
LOCATION '{{data_path}}/orm*.hl7'
OPTIONS (
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- ============================================================================
-- STEP 3: Full corpus with materialized OBR/OBX fields
-- ============================================================================
CREATE EXTERNAL TABLE IF NOT EXISTS {{zone_name}}.hl7_demos.lab_results
USING HL7
LOCATION '{{data_path}}/*.hl7'
OPTIONS (
hl7_config = '{
"materialized_paths": [
"pid_3", "pid_5",
"obr_4",
"obx_2", "obx_3", "obx_5", "obx_6", "obx_7", "obx_8"
]
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- ============================================================================
-- STEP 4: Explore orders, results, abnormal flags, and value types
-- ============================================================================
SELECT df_file_name, msh_3 AS sending_app, msh_9 AS message_type, msh_12 AS hl7_version
FROM {{zone_name}}.hl7_demos.lab_orders
ORDER BY df_file_name;
SELECT pid_5 AS patient, obr_4 AS test_ordered, obx_3 AS first_obs_id,
obx_5 AS first_obs_value, obx_6 AS units, obx_7 AS reference_range,
obx_8 AS abnormal_flag
FROM {{zone_name}}.hl7_demos.lab_results
WHERE msh_9 LIKE 'ORU%'
ORDER BY df_file_name;
SELECT pid_5 AS patient, obx_3 AS test, obx_5 AS value, obx_8 AS flag
FROM {{zone_name}}.hl7_demos.lab_results
WHERE obx_8 IS NOT NULL AND obx_8 <> '' AND obx_8 <> 'N';
-- ============================================================================
-- STEP 5: Cleanup
-- ============================================================================
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.lab_orders WITH FILES;
DROP EXTERNAL TABLE IF EXISTS {{zone_name}}.hl7_demos.lab_results WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.hl7_demos;
DROP ZONE IF EXISTS {{zone_name}};## When to Use Use this demo whenever you need to unify laboratory order and result feeds from heterogeneous LIS and EHR systems - EPIC, Ritten, InterfaceWare, GHH LAB - into SQL-queryable tables. It covers three ORM^O01 order messages (CBC+BMP+urine culture, single glucose, X-ray ankle) and five ORU^R01 result messages (14-analyte CMP, simple positive result, radiology narrative, 9-vaccine immunization history, high-flag glucose) across HL7 v2.3 through v2.5.1. Reach for it when modeling abnormal-flag alerting, value-type routing (NM/ST/TX/SN/HD), or multi-OBX result sets. ## What You Will Learn 1. How to split a single HL7 directory into two tables using glob patterns (`orm*.hl7` for orders, `*.hl7` for the full corpus). 2. How to materialize OBR (order) and OBX (observation) fields - test ID, value, units, reference range, abnormal flag - as first-class columns. 3. How OBX-2 value types (NM numeric, ST string, TX text, SN structured numeric, HD hierarchic designator) influence downstream handling. 4. How to detect abnormal results via OBX-8 flags (H=High, L=Low, N=Normal, empty=unflagged). 5. How `\X0D\` carriage returns embedded in radiology narratives decode automatically into line breaks. 6. Why materialized columns only capture the first OBX per message and when you must fall back to `df_message_json` (for the 14-analyte CMP or the 9-vaccine history). ## Prerequisites - DeltaForge GUI running locally with `CREATE ZONE`, `CREATE SCHEMA`, and `CREATE EXTERNAL TABLE` privileges. - Eight sample ORM/ORU `.hl7` files downloaded into `{{data_path}}` (the demo loader fetches them). - Basic understanding of the HL7 v2 lab pipeline (OBR = order, OBX = observation, ORC = common order, NTE = note).