Charge-to-payment reconciliation across three 837 healthcare claims (professional, dental, institutional) and one 835 remittance advice. Two external tables extract CLM/SV1/SV2/SV3 service-line charges and BPR/CLP/CAS/SVC payment details; queries JOIN on claim reference to compute write-off percentages and match SUM(charge) against SUM(payment).
-- Table 1: claim charges (837 service lines).
-- HIPAA segments (NM1, CLM, SV1/SV2/SV3, CAS, HL, HI, ...) are NOT in the engine's
-- default repeating_segments list, declare them explicitly or multi-occurrence
-- collisions resolve to last-write-wins on the column key. Pair with
-- repeating_segment_mode = 'first' (or whichever semantics fit) since the default
-- is 'last'.
CREATE EXTERNAL TABLE IF NOT EXISTS external.edi_demos.claims_header
USING EDI
LOCATION '/path/to/edi/*.edi'
OPTIONS (
edi_config = '{
"ediFormat": "x12",
"repeating_segment_mode": "first",
"repeating_segments": ["NM1", "CLM", "SV1", "SV2", "SV3", "CAS", "DTP", "REF", "HL", "HI", "AMT"],
"materialized_paths": [
"clm_1", "clm_2",
"sv1_1", "sv1_2",
"sv2_1", "sv2_2",
"sv3_1", "sv3_2"
]
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- Table 2: remittance detail (835 payment + adjustments)
CREATE EXTERNAL TABLE IF NOT EXISTS external.edi_demos.claims_remittance
USING EDI
LOCATION '/path/to/edi/*.edi'
OPTIONS (
edi_config = '{
"ediFormat": "x12",
"repeating_segment_mode": "first",
"repeating_segments": ["NM1", "CLP", "CAS", "SVC", "DTM", "REF", "AMT", "BPR", "TRN"],
"materialized_paths": [
"bpr_1", "bpr_2",
"clp_1", "clp_2", "clp_3",
"cas_1", "cas_2", "cas_3",
"svc_1", "svc_2", "svc_3"
]
}',
file_metadata = '{"columns":["df_file_name","df_row_number"]}'
);
-- Query 1: claim charge ranking (cast STRING -> DOUBLE)
SELECT
df_file_name,
st_1 AS txn_type,
clm_1 AS patient_account,
CAST(clm_2 AS DOUBLE) AS total_charge
FROM external.edi_demos.claims_header
WHERE clm_1 IS NOT NULL
ORDER BY total_charge DESC;
-- Query 2: high-value claims via HAVING after SUM of service-line charges
SELECT
clm_1 AS patient_account,
SUM(CAST(COALESCE(sv1_2, sv2_2, sv3_2) AS DOUBLE)) AS total_service_charge
FROM external.edi_demos.claims_header
WHERE clm_1 IS NOT NULL
GROUP BY clm_1
HAVING SUM(CAST(COALESCE(sv1_2, sv2_2, sv3_2) AS DOUBLE)) > 100.0
ORDER BY total_service_charge DESC;
-- Query 3: charge-to-payment reconciliation via JOIN
SELECT
h.clm_1 AS patient_account,
CAST(h.clm_2 AS DOUBLE) AS billed,
CAST(r.clp_3 AS DOUBLE) AS payer_charge,
CAST(r.bpr_2 AS DOUBLE) AS total_payment,
CAST(h.clm_2 AS DOUBLE) - CAST(r.bpr_2 AS DOUBLE) AS write_off
FROM external.edi_demos.claims_header h
JOIN external.edi_demos.claims_remittance r
ON h.clm_1 = r.clp_1
WHERE h.clm_1 IS NOT NULL AND r.clp_1 IS NOT NULL;
-- Query 4: adjustment reason breakdown
SELECT
cas_1 AS group_code,
cas_2 AS reason_code,
COUNT(*) AS adjustment_count,
SUM(CAST(cas_3 AS DOUBLE)) AS total_adjustment
FROM external.edi_demos.claims_remittance
WHERE cas_1 IS NOT NULL
GROUP BY cas_1, cas_2
ORDER BY total_adjustment DESC;## When to Use Use this demo for HIPAA X12 financial analytics, any workflow that needs to reconcile what was billed (837) against what was paid (835) or compute contractual write-offs (CAS adjustments). It is the best template when you need SQL aggregates (SUM, HAVING), type casts on monetary strings, and multi-table JOINs on EDI data. The two-table split is deliberate: SV1/SV2/SV3 populate per claim type (professional/institutional/dental) and BPR/CLP/CAS/SVC populate only on the 835, so keeping them in separate materialized views avoids wide sparse rows. ## What You Will Learn 1. How to model an EDI workflow as two narrow tables (header + remittance) rather than one wide sparse table 2. How to materialize claim-type-specific service lines (SV1 professional, SV2 institutional, SV3 dental) in one table and query with COALESCE or UNION 3. How to CAST EDI amount strings (CLM_2, CLP_3, BPR_2, SVC_2, SVC_3) to DOUBLE for arithmetic, every numeric element ships as STRING 4. How to JOIN `claims_header.clm_1 = claims_remittance.clp_1` to correlate a claim with its payment 5. How to compute write-off percentage = (billed - paid) / billed using CAST and HAVING to filter high-value discrepancies 6. How CAS_1 group codes (OA=Other Adjustments, CO=Contractual, PR=Patient Responsibility) and CAS_2 reason codes (23, 94, 45, ...) decode into adjustment categories ## Prerequisites - HIPAA 5010 X12 .edi files: at least one 837 and one 835 - Familiarity with materialized_paths (see demo_edi_x12_supply_chain_basics) - An understanding that CLM_1 (claim submitter id) in an 837 equals CLP_1 (claim reference) in the corresponding 835