X12 Supply Chain Basics: ISA/GS/ST Envelope + Materialized Fields

Parses 14 X12 EDI transactions (850/810/855/856/857/861/997/824) into two external tables, a compact view exposing the ISA/GS/ST envelope and df_transaction_json, and a materialized view surfacing BEG/BIG/BSN/N1/CTT business fields as first-class columns.

Syntax

-- Zone & schema
CREATE ZONE IF NOT EXISTS external
    TYPE EXTERNAL
    COMMENT 'External tables, demo datasets and file-backed data';

CREATE SCHEMA IF NOT EXISTS external.edi_demos
    COMMENT 'EDI transaction-backed external tables';

-- Compact view, envelope + full transaction JSON
CREATE EXTERNAL TABLE IF NOT EXISTS external.edi_demos.supply_chain_messages
USING EDI
LOCATION '/path/to/edi/*.edi'
OPTIONS (
    edi_config = '{"ediFormat": "x12"}',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- Materialized view, business fields surfaced as columns
CREATE EXTERNAL TABLE IF NOT EXISTS external.edi_demos.supply_chain_materialized
USING EDI
LOCATION '/path/to/edi/*.edi'
OPTIONS (
    edi_config = '{
        "ediFormat": "x12",
        "materialized_paths": [
            "beg_1", "beg_3", "beg_5",
            "big_1", "big_2",
            "bsn_2", "bsn_3",
            "n1_1", "n1_2",
            "ctt_1"
        ]
    }',
    file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);

-- Query 1, envelope header overview
SELECT
    df_file_name,
    isa_6 AS sender_id,
    isa_8 AS receiver_id,
    st_1  AS txn_type,
    gs_8  AS x12_version
FROM external.edi_demos.supply_chain_messages
ORDER BY df_file_name;

-- Query 2, purchase order details (BEG populated only on 850s)
SELECT
    df_file_name,
    beg_3 AS po_number,
    beg_5 AS po_date,
    n1_2  AS party_name,
    ctt_1 AS line_items
FROM external.edi_demos.supply_chain_materialized
WHERE beg_3 IS NOT NULL
ORDER BY df_file_name;

-- Query 3, invoice details (BIG populated only on 810s)
SELECT
    df_file_name,
    big_1 AS invoice_date,
    big_2 AS invoice_number,
    n1_2  AS party_name,
    ctt_1 AS line_items
FROM external.edi_demos.supply_chain_materialized
WHERE big_1 IS NOT NULL
ORDER BY df_file_name;

-- Query 4, transaction type mix
SELECT st_1 AS txn_type, COUNT(*) AS txn_count
FROM external.edi_demos.supply_chain_messages
GROUP BY st_1
ORDER BY txn_count DESC, st_1;

Description

## When to Use Start here if you have never parsed X12 with DeltaForge. This demo shows the two tables every X12 workload needs: a compact envelope-only table for routing and triage, and a materialized table that promotes the most-queried business elements to real columns. Both read the same *.edi glob, only the OPTIONS payload differs. Use this pattern whenever you need to unify heterogeneous X12 feeds (mixed document types, mixed versions, mixed trading partners) behind a single external table. ## What You Will Learn 1. How CREATE EXTERNAL TABLE ... USING EDI parses X12 interchanges without a per-document schema 2. How the ISA envelope (ISA_1..ISA_16), GS functional group (GS_1..GS_8), and ST transaction header (ST_1, ST_2) are always surfaced as columns 3. How materialized_paths promotes specific segment_element paths (e.g. beg_3, n1_2, ctt_1) to first-class columns 4. How df_transaction_json preserves the full parsed transaction for deep access via JSON functions 5. How df_file_name / df_row_number (from file_metadata) tie each row back to its source .edi file 6. How a single external table handles multiple X12 versions (00204, 00401) and multiple trading partners in one glob ## Prerequisites - X12 .edi files in a directory (any mix of 850/810/855/856/857/861/997/824 will work) - A zone of TYPE EXTERNAL; schemas created under it - `GRANT ADMIN` on the external tables to the querying user

Pitfalls

• materialized_paths uses lowercase `segment_element` names (e.g. `beg_3`, not `BEG-03` or `BEG_03`), uppercase or dashed names produce empty columns silently.
• BEG segments only appear in 850 Purchase Orders, BIG only in 810 Invoices, BSN only in 856 Ship Notices, columns for other transaction types will be NULL, so always filter with `WHERE beg_3 IS NOT NULL` (or the equivalent) before joining or aggregating.
• N1 is a repeating loop, materialized_paths without a repeating-segment mode returns only the FIRST occurrence. For multi-occurrence access use the repeating-segments demo (indexed/concatenate/to_json modes).
• The ISA envelope uses fixed-position separators (segment terminator ~, element separator *, component separator :). The parser auto-detects them from ISA-104/105; do not override unless the file is non-standard.
• X12 dates appear as YYYYMMDD strings (e.g. `20090827`), cast with `to_date(beg_5, '%Y%m%d')` before date arithmetic.
• Every CTT_1, BEG_3, BIG_2 value is a STRING even when numeric (counts, PO numbers), cast before SUM/AVG.

See Also

• CREATE_EXTERNAL_TABLE
• demo_edi_edifact_basics
• demo_edi_repeating_segments
• demo_edi_order_lifecycle