EDI 837 Claims Guide for Data Engineers
Everything a healthcare data engineer needs to work with EDI 837 claims data — 837P vs 837I differences, production staging DDL, Snowflake schema design, dbt models, and SQL for denial and payment analytics.
What Is EDI 837?
The EDI 837 is the HIPAA-mandated X12 5010A1 electronic transaction used to submit healthcare claims from providers to payers, clearinghouses, and billing services. It is the primary source of claims data in virtually every healthcare data warehouse.
Three variants exist — 837P (Professional), 837I (Institutional), and 837D (Dental) — each covering different care settings and mapping to different form types. Understanding the structural differences is essential for designing schemas that handle all claim types cleanly.
In data pipelines, 837 files arrive from clearinghouses such as Change Healthcare, Availity, or Waystar, or directly from provider billing systems. Most teams implement a raw staging layer that preserves the original file before parsing into typed, conformed tables.
837P vs 837I — Key Differences
| Attribute | 837P (Professional) | 837I (Institutional) |
|---|---|---|
| Form equivalent | CMS-1500 | UB-04 |
| Service codes | CPT / HCPCS | Revenue codes (REV CD) |
| Provider type | Physician, ancillary | Hospital, SNF, FQHC |
| Diagnosis positions | Up to 12 | Up to 24 |
| Bill type code | Not used | Required (e.g. 0111) |
| Condition codes | Not used | Required for many payers |
| Occurrence codes | Not used | Admit date, onset date |
| Value codes | Not used | Semi-private room days etc |
| DRG assignment | No | Yes (inpatient) |
| Attending physician | Optional | Required |
EDI 837 Staging Table DDL
Use a three-layer architecture: raw (original file text), staged (parsed typed columns), and conformed (joined to dimensions). The DDL below covers the staged layer for 837P.
-- EDI 837 Raw Staging (all variants) CREATE TABLE edi_837_raw ( raw_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), interchange_ctrl_no VARCHAR(9) NOT NULL, -- ISA13 grp_ctrl_no VARCHAR(9) NOT NULL, -- GS06 txn_ctrl_no VARCHAR(9) NOT NULL, -- ST02 claim_type VARCHAR(1) NOT NULL, -- P, I, D raw_content VARCHAR NOT NULL, -- full EDI file text file_nm VARCHAR(200), received_ts TIMESTAMP NOT NULL DEFAULT NOW(), source_system_cd VARCHAR(50), processed_flg BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE (interchange_ctrl_no, grp_ctrl_no, txn_ctrl_no) ); -- EDI 837P Claim Header (Professional) CREATE TABLE edi_837p_claim_header ( clm_key UUID PRIMARY KEY DEFAULT gen_random_uuid(), raw_id UUID REFERENCES edi_837_raw(raw_id), clm_id VARCHAR(38) NOT NULL, -- CLM01 — provider claim number clm_type_cd VARCHAR(1) NOT NULL, -- P=Professional billng_prvdr_npi VARCHAR(10) NOT NULL, billng_prvdr_nm VARCHAR(200), billng_prvdr_tax_id VARCHAR(9), rndrng_prvdr_npi VARCHAR(10), rfrrng_prvdr_npi VARCHAR(10), mbr_id VARCHAR(20) NOT NULL, mbr_first_nm VARCHAR(100), mbr_last_nm VARCHAR(100), mbr_dob DATE, sbscrbr_id VARCHAR(20), grp_nbr VARCHAR(20), payer_id VARCHAR(20) NOT NULL, svc_dt_from DATE NOT NULL, svc_dt_to DATE, prim_diag_cd VARCHAR(7) NOT NULL, sec_diag_cd VARCHAR(7), diag_cd_3 VARCHAR(7), diag_cd_4 VARCHAR(7), icd_vrsn VARCHAR(5) NOT NULL, -- ICD10 or ICD9 pos_cd VARCHAR(2), -- place of service auth_nbr VARCHAR(30), bld_amt DECIMAL(12,2) NOT NULL, -- total billed claim_freq_cd VARCHAR(1), -- 1=original, 7=corrected rec_creat_ts TIMESTAMP NOT NULL DEFAULT NOW(), rec_updt_ts TIMESTAMP NOT NULL DEFAULT NOW() ); -- EDI 837P Service Line CREATE TABLE edi_837p_svc_line ( svc_line_key UUID PRIMARY KEY DEFAULT gen_random_uuid(), clm_key UUID REFERENCES edi_837p_claim_header(clm_key), line_nbr INTEGER NOT NULL, proc_cd VARCHAR(10) NOT NULL, -- CPT/HCPCS proc_modifier_1 VARCHAR(2), proc_modifier_2 VARCHAR(2), svc_dt DATE NOT NULL, units_qty DECIMAL(8,2) NOT NULL, bld_amt DECIMAL(12,2) NOT NULL, pos_cd VARCHAR(2), rndrng_prvdr_npi VARCHAR(10), diag_cd_ptr_1 INTEGER, -- pointer to header diag diag_cd_ptr_2 INTEGER, rev_cd VARCHAR(4) -- 837I only ); CREATE INDEX idx_837p_clm_id ON edi_837p_claim_header(clm_id); CREATE INDEX idx_837p_mbr ON edi_837p_claim_header(mbr_id, svc_dt_from); CREATE INDEX idx_837p_npi ON edi_837p_claim_header(billng_prvdr_npi); CREATE INDEX idx_837p_svc ON edi_837p_svc_line(clm_key, svc_dt);
Convert this DDL for BigQuery or Databricks using the free DDL Converter →
dbt Model for EDI 837 Claims
models/staging/stg_edi_837p.sql
{{ config(materialized='view', schema='staging') }}
with source as (
select * from {{ source('edi', 'edi_837p_claim_header') }}
),
staged as (
select
clm_key,
clm_id,
billng_prvdr_npi,
rndrng_prvdr_npi,
rfrrng_prvdr_npi,
mbr_id,
sbscrbr_id,
grp_nbr,
payer_id,
svc_dt_from as svc_dt,
svc_dt_to,
prim_diag_cd,
sec_diag_cd,
icd_vrsn,
pos_cd,
auth_nbr,
bld_amt,
claim_freq_cd,
-- Derived
case claim_freq_cd
when '1' then 'ORIGINAL'
when '7' then 'CORRECTED'
when '8' then 'VOID'
else 'UNKNOWN'
end as claim_freq_desc,
rec_creat_ts,
rec_updt_ts
from source
)
select * from stagedmodels/staging/stg_edi_837p.yml
version: 2
models:
- name: stg_edi_837p
columns:
- name: clm_id
tests: [not_null, unique]
- name: billng_prvdr_npi
tests:
- not_null
- dbt_utils.expression_is_true:
expression: "length(billng_prvdr_npi) = 10"
- name: prim_diag_cd
tests:
- not_null
- dbt_utils.expression_is_true:
expression: "length(prim_diag_cd) between 3 and 7"
- name: icd_vrsn
tests:
- accepted_values:
values: ['ICD9', 'ICD10']
- name: bld_amt
tests:
- dbt_utils.expression_is_true:
expression: "bld_amt >= 0"SQL Queries for Claims Analytics
Denial Rate by Provider
SELECT
h.billng_prvdr_npi,
COUNT(DISTINCT h.clm_id) AS total_claims,
SUM(h.bld_amt) AS total_billed,
SUM(CASE WHEN r.clm_sts_cd = 'DENIED'
THEN 1 ELSE 0 END) AS denied_claims,
ROUND(100.0 * SUM(CASE WHEN r.clm_sts_cd = 'DENIED'
THEN 1 ELSE 0 END)
/ NULLIF(COUNT(DISTINCT h.clm_id), 0), 2) AS denial_rate_pct
FROM edi_837p_claim_header h
LEFT JOIN claim_adjudication r ON r.clm_id = h.clm_id
WHERE h.svc_dt_from BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY h.billng_prvdr_npi
ORDER BY denial_rate_pct DESC;Top Diagnosis Codes by Billed Amount
SELECT
h.prim_diag_cd,
d.icd10_desc,
COUNT(DISTINCT h.clm_id) AS claim_count,
COUNT(DISTINCT h.mbr_id) AS member_count,
SUM(h.bld_amt) AS total_billed,
AVG(h.bld_amt) AS avg_billed_per_claim
FROM edi_837p_claim_header h
LEFT JOIN dim_icd10 d ON d.icd10_cd = h.prim_diag_cd
WHERE h.svc_dt_from >= DATEADD(year, -1, CURRENT_DATE)
GROUP BY h.prim_diag_cd, d.icd10_desc
ORDER BY total_billed DESC
LIMIT 20;Duplicate Claim Detection
-- Detect potential duplicate claims
-- Same member, provider, service date, procedure, and billed amount
SELECT
mbr_id,
billng_prvdr_npi,
svc_dt_from,
prim_diag_cd,
bld_amt,
COUNT(*) AS duplicate_count,
array_agg(clm_id) AS clm_ids
FROM edi_837p_claim_header
GROUP BY mbr_id, billng_prvdr_npi, svc_dt_from, prim_diag_cd, bld_amt
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC;EDI 837 Data Quality Rules
- →Never store NPI as INTEGER: NPI is exactly 10 numeric digits but leading zeros are significant. Always VARCHAR(10).
- →Never store ICD codes as INTEGER: ICD-10 codes contain letters and are 3-7 chars. Always VARCHAR(7).
- →Always store both ICD version and code: Without icd_vrsn you cannot safely join to ICD crosswalk tables for pre-2015 claims.
- →Validate billed amounts are non-negative: Negative bld_amt values indicate voids or reversals — handle them separately in your pipeline.
- →Deduplicate on interchange + transaction control numbers: Not just clm_id — same claim_id can appear in corrected resubmissions with different control numbers.
- →Validate diagnosis code pointer references: Service line diag_cd_ptr_1 must reference a valid position in the header diagnosis list.
Frequently Asked Questions
What is an EDI 837 transaction?
The EDI 837 is the HIPAA-mandated X12 electronic transaction set used to submit healthcare claims from providers to payers. There are three variants: 837P (Professional) for physician and outpatient services on CMS-1500 forms, 837I (Institutional) for hospital and facility claims on UB-04 forms, and 837D (Dental) for dental claims. All use X12 5010A1 standard.
What is the difference between 837P and 837I?
The 837P (Professional) covers physician, outpatient, and ancillary services billed on a CMS-1500 form. It uses service-line level detail with procedure codes and modifiers. The 837I (Institutional) covers inpatient hospital, skilled nursing, and outpatient hospital services billed on a UB-04 form. It uses revenue codes, condition codes, and occurrence codes not present in 837P. In your data warehouse, you typically need separate staging tables or a discriminator column to handle both variants.
What data type should I use to store EDI 837 monetary amounts?
Always use DECIMAL(12,2) or NUMERIC(12,2) for all monetary fields in EDI 837 — billed_amt, allowed_amt, paid_amt, and patient_resp_amt. Never use FLOAT or DOUBLE due to floating-point precision errors that cause reconciliation failures. In Snowflake and BigQuery use NUMBER(12,2). Store amounts exactly as received from the 837 file without rounding.
How do I store EDI 837 diagnosis codes in Snowflake?
Store ICD-10 diagnosis codes as VARCHAR(7) — never INTEGER. Use separate columns for each diagnosis position: prim_diag_cd, sec_diag_cd through diag_cd_12 for professional claims, and up to diag_cd_24 for institutional claims. Always store the ICD version alongside the codes in an icd_vrsn column (ICD9 or ICD10) to support crosswalk queries for historical claims data.
What is the best way to handle EDI 837 deduplication?
Use the interchange control number (ISA13), group control number (GS06), and transaction set control number (ST02) as a composite natural key for deduplication at the raw file level. At the claim level, use clm_id (CLM01 segment) combined with billing_npi and service_date. For production pipelines, implement a hash of key claim fields and store it alongside the record to detect resubmissions and corrected claims efficiently.
How do I build a dbt model for EDI 837 claims?
Use a three-layer approach: a raw source model that preserves the original parsed fields with minimal transformation, a staging model that applies type casting, column renaming to ISO-11179 standards, and basic cleaning, and a mart-level fact_claim_header model that joins to dim_member, dim_provider, and dim_facility. Add dbt tests for not_null on clm_id and billing_npi, unique on clm_id, accepted_values for icd_vrsn, and expression_is_true for length(npi) = 10.