Parse TRADACOMS UK retail EDI messages for orders, invoices, and deliveries
## Overview TRADACOMS (Trading Data Communications Standard) is the electronic data interchange standard developed by the UK Article Numbering Association (now GS1 UK) for the British retail sector. Introduced in 1982, TRADACOMS remains widely used by UK retailers, wholesalers, and suppliers for exchanging purchase orders, invoices, delivery notes, acknowledgments, and product data updates. Although officially superseded by EANCOM (an EDIFACT subset) in 1995, TRADACOMS continues to dominate UK grocery and general merchandise supply chains. DeltaForge reads TRADACOMS files through the unified EDI format handler. The format identifier in SQL is `EDI`, and the `edi_config` option with `ediFormat` set to `tradacoms` selects the Tradacoms dialect. Unlike EDIFACT, TRADACOMS uses a fixed delimiter set: '=' separates the segment tag from data, '+' separates elements, ':' separates components within an element, and the apostrophe (') terminates each segment. The envelope structure consists of a Transmission (STX/END) wrapping one or more Messages (MHD/MTR), with no intermediate functional group layer. ## Usage TRADACOMS files are registered as external tables using `CREATE EXTERNAL TABLE` with the `EDI` format: ```sql CREATE EXTERNAL TABLE IF NOT EXISTS zone.edi_demos.tradacoms_orders USING EDI LOCATION '{{data_path}}/*.edi' OPTIONS ( edi_config = '{"ediFormat": "tradacoms"}', file_metadata = '{"columns":["df_file_name","df_row_number"]}' ); ``` Once the external table is created, query it with standard SQL: ```sql SELECT df_transaction_json, df_transaction_id, df_file_name FROM zone.edi_demos.tradacoms_orders; ``` Use JSON functions on the `df_transaction_json` column for deep segment-level access to order lines, delivery details, and invoice data. ## Output Schema Each TRADACOMS message within a transmission produces one row. The default output columns follow the Tradacoms envelope structure: | Column | Description | |---|---| | STX envelope fields | Transmission header fields including sender/receiver identification, date, and reference numbers | | MHD fields | Message header fields including message type and reference | | df_transaction_json | Full message content as a JSON string | | df_transaction_id | Unique deterministic hash identifying the transaction | When `file_metadata` is configured, additional columns such as `df_file_name` and `df_row_number` are appended. ## Key Options - **edi_config**: JSON string with `ediFormat` set to `tradacoms`. This is the primary option that selects the Tradacoms dialect within the unified EDI reader. - **file_metadata**: JSON string specifying which system columns to inject, e.g., `{"columns":["df_file_name","df_row_number"]}`. - **strict**: Validates that END and MTR trailer counts match their corresponding STX and MHD headers. Disable for lenient parsing of malformed files. ## Common Use Cases TRADACOMS is used primarily in UK retail supply chain scenarios: - Purchase orders (ORDHDR/ORDERS) - Invoices (INVFIL/INVOIC) - Delivery notes (DLCFIL/DELHDR/DELIVR) - Acknowledgments (ACKHDR/ACKNOW) - Utility billing DeltaForge preserves all transmission control references, sender/receiver identification codes, and message headers to support reconciliation and audit workflows across the supply chain.