Claims & Billing

Healthcare EDI Transactions Guide

Electronic Data Interchange (EDI) is the backbone of healthcare billing. Master 837 claims, 835 remittance, 270/271 eligibility, and other HIPAA-mandated X12 transactions with database schemas and real examples.

HIPAA-Mandated EDI Transaction Sets

Transaction Set	Name	Direction	Purpose
837P	Professional Claim	Provider → Payer	Physician & outpatient claims
837I	Institutional Claim	Provider → Payer	Hospital & facility claims
837D	Dental Claim	Provider → Payer	Dental services claims
835	Remittance Advice	Payer → Provider	Payment explanation (ERA)
270	Eligibility Inquiry	Provider → Payer	Verify member eligibility
271	Eligibility Response	Payer → Provider	Return benefit details
276	Claim Status Request	Provider → Payer	Query claim status
277	Claim Status Response	Payer → Provider	Return claim status
278	Prior Authorization	Both	Request & respond to PA
834	Enrollment	Employer → Payer	Benefit enrollment/maintenance

837 Claim File Structure

Key 837 Segments

Segment	Name	Contains
ISA/GS	Interchange/Group Header	Sender/receiver IDs, control numbers
NM1*85	Billing Provider	NPI, name, address, tax ID
NM1*IL	Subscriber	Member ID, name, DOB, gender
CLM	Claim Information	Charge amount, place of service, claim type
HI*ABK	Diagnosis Codes	ICD-10-CM principal and secondary diagnoses
SV1/SV2	Service Line	CPT/HCPCS code, charge, units, modifier
DTP*472	Service Date	Date(s) of service

EDI Transaction Database Schema

The staging tables below receive parsed EDI data before it flows into your warehouse. Need to move this schema to a different platform? Convert your EDI staging table DDL across Snowflake, BigQuery, Oracle, and SQL Server with our free DDL Converter.

-- EDI Transaction Log (all inbound/outbound transactions)
CREATE TABLE edi_transaction (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_set     VARCHAR(10) NOT NULL,           -- 837P, 835, 270, 271, etc.
  direction           VARCHAR(10) NOT NULL,            -- INBOUND, OUTBOUND
  trading_partner_id  UUID,
  interchange_ctrl_no VARCHAR(20),                     -- ISA13
  group_ctrl_no       VARCHAR(20),                     -- GS06
  transaction_ctrl_no VARCHAR(20),                     -- ST02
  file_nm           VARCHAR(255),
  raw_payload         TEXT,                            -- Original EDI file
  status              VARCHAR(20) DEFAULT 'RECEIVED',  -- RECEIVED, VALIDATED, PROCESSED, ERROR
  error_detail        TEXT,
  received_at         TIMESTAMP DEFAULT NOW(),
  processed_at        TIMESTAMP,
  CONSTRAINT chk_direction CHECK (direction IN ('INBOUND','OUTBOUND'))
);

CREATE INDEX idx_edi_set ON edi_transaction(transaction_set, received_at);
CREATE INDEX idx_edi_ctrl ON edi_transaction(interchange_ctrl_no);

-- 837 Claim Header (parsed from 837P/I/D)
CREATE TABLE edi_claim_header (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  edi_transaction_id    UUID REFERENCES edi_transaction(id),
  clm_id              VARCHAR(50) UNIQUE NOT NULL,   -- CLM01

  -- Billing Provider
  billing_npi           VARCHAR(10),
  billing_tax_id        VARCHAR(20),
  billing_nm          VARCHAR(255),

  -- Subscriber / Patient
  member_id             VARCHAR(50),
  subscriber_nm       VARCHAR(255),
  patient_dob           DATE,
  patient_gender        CHAR(1),

  -- Claim Details
  total_charge          DECIMAL(12,2),
  place_of_service      VARCHAR(5),
  clm_type            VARCHAR(10),                   -- P, I, D
  principal_dx          VARCHAR(10),
  dx_codes              TEXT[],

  -- Dates
  service_from_dt       DATE,
  service_to_dt         DATE,
  received_dt           TIMESTAMP DEFAULT NOW()
);

-- 835 Remittance Line (payment detail per claim/service)
CREATE TABLE edi_835_payment (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  edi_transaction_id    UUID REFERENCES edi_transaction(id),
  clm_id              VARCHAR(50),
  service_line_no       INTEGER,
  procedure_code        VARCHAR(10),

  -- Amounts
  charged_amt           DECIMAL(12,2),
  allowed_amt           DECIMAL(12,2),
  paid_amt              DECIMAL(12,2),
  patient_resp_amt      DECIMAL(12,2),

  -- Adjustment Reason Codes (CAS segment)
  adj_reason_code_1     VARCHAR(10),                   -- CARC code
  adj_amt_1             DECIMAL(12,2),
  adj_reason_code_2     VARCHAR(10),
  adj_amt_2             DECIMAL(12,2),

  -- Remark Codes (MOA/LQ)
  remark_codes          TEXT[],                        -- RARC codes

  check_dt            DATE,
  check_number          VARCHAR(50)
);

CREATE INDEX idx_835_claim ON edi_835_payment(clm_id);

SQL Query Examples

835 Denial Rate by Adjustment Reason Code

SELECT
  adj_reason_code_1                         AS carc_code,
  COUNT(*)                                  AS denial_count,
  SUM(charged_amt)                          AS total_charged,
  SUM(adj_amt_1)                            AS total_adjusted,
  ROUND(AVG(paid_amt / NULLIF(charged_amt,0)) * 100, 1) AS avg_pay_pct
FROM edi_835_payment
WHERE paid_amt = 0
  AND adj_reason_code_1 IS NOT NULL
  AND check_dt >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY adj_reason_code_1
ORDER BY denial_count DESC
LIMIT 15;

EDI Transaction Volume by Type

SELECT
  transaction_set,
  direction,
  DATE_TRUNC('week', received_at)  AS week,
  COUNT(*)                          AS transaction_count,
  SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) AS error_count,
  ROUND(100.0 * SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END)
    / COUNT(*), 2)                  AS error_rate_pct
FROM edi_transaction
WHERE received_at >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY transaction_set, direction, week
ORDER BY week DESC, transaction_count DESC;

EDI 835 CARC and RARC Lookup Table Schema

Claim Adjustment Reason Codes (CARC) and Remittance Advice Remark Codes (RARC) are published by CMS and updated quarterly. Storing them in a reference table lets you join directly in SQL for human-readable denial reports without hard-coding descriptions in application logic.

CREATE TABLE carc_remark_code (
  code        VARCHAR(10) PRIMARY KEY,
  code_type   VARCHAR(4) NOT NULL, -- CARC or RARC
  description TEXT NOT NULL,
  category    VARCHAR(50),
  effective_dt DATE,
  term_dt      DATE
);

-- Example: join to 835 payments for readable denial reports
SELECT
  p.clm_id,
  p.adj_reason_code_1              AS carc_code,
  c.description                    AS denial_reason,
  c.category,
  p.charged_amt,
  p.paid_amt
FROM edi_835_payment p
LEFT JOIN carc_remark_code c
  ON p.adj_reason_code_1 = c.code
  AND c.code_type = 'CARC'
WHERE p.paid_amt = 0
  AND p.check_dt >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY p.check_dt DESC;

270/271 Eligibility Verification Schema

The 270 transaction requests eligibility and benefit information from a payer; the 271 returns the response. Storing both sides lets you calculate response latency, track coverage changes over time, and pre-adjudicate claims before submission.

CREATE TABLE edi_270_eligibility_request (
  id              UUID PRIMARY KEY,
  transaction_id  UUID REFERENCES edi_transaction(id),
  subscriber_id   VARCHAR(50),
  member_dob      DATE,
  service_type_cd VARCHAR(10),
  inquiry_dt      DATE,
  payer_id        VARCHAR(10),
  status          VARCHAR(20)
);

CREATE TABLE edi_271_eligibility_response (
  id                  UUID PRIMARY KEY,
  request_id          UUID REFERENCES edi_270_eligibility_request(id),
  coverage_active_flg BOOLEAN,
  plan_nm             VARCHAR(255),
  eff_dt              DATE,
  term_dt             DATE,
  copay_amt           NUMERIC(8,2),
  deductible_amt      NUMERIC(12,2),
  out_of_pocket_amt   NUMERIC(12,2),
  response_dt         TIMESTAMP
);

CREATE INDEX idx_270_subscriber ON edi_270_eligibility_request(subscriber_id, inquiry_dt);
CREATE INDEX idx_271_request    ON edi_271_eligibility_response(request_id);

Tip: add a computed column for response_latency_ms as EXTRACT(EPOCH FROM (response_dt - inquiry_dt::TIMESTAMP)) * 1000 to monitor SLA compliance against payer response time requirements.

EDI File Parsing Best Practices for Data Engineers

Choose the right parser library. For Python pipelines, the x12 library (pyx12 or the TigerShark fork) handles segment tokenization and loop hierarchy for all HIPAA transaction sets. For 835 files specifically, hl7apy works for HL7 but you want an X12-native parser for EDI — the two standards have different delimiters and envelope structures. Always read the ISA segment first to detect the component separator, element separator, and repetition separator before parsing the rest of the file.

Store raw before you parse. Write the original EDI file bytes to an object store (S3, GCS, Azure Blob) and record the path in your edi_transaction.file_nm column before attempting any parsing. If your schema changes or a parser bug mis-maps a segment, you can re-parse from the raw file without re-requesting the transaction from the trading partner. This is especially critical for 835 files — payers rarely resend remittances.

Load incrementally using control numbers. The ISA13 interchange control number is unique per trading partner and session. Use it as your idempotency key in the staging load: INSERT ... ON CONFLICT (interchange_ctrl_no) DO NOTHING. For high-volume 837 pipelines, partition your staging table by received_at date and run incremental loads hourly rather than processing the full file history on each run.

Handle malformed segments defensively. Real-world EDI files from clearinghouses often contain non-standard line endings, extra whitespace in element positions, or segments out of the expected loop order. Wrap segment parsing in try/except blocks, log the raw segment text and its position in the file to an edi_parse_error table, and continue processing the remainder of the file. A single bad SV1 service line should not drop an entire 837 claim batch — quarantine the error row and alert ops rather than failing the whole pipeline.

EDI Best Practices

•Validate before submitting: Use a clearinghouse or EDI scrubber to catch 837 errors before they reach the payer — rejection at the interchange level delays payment
•Store raw EDI payloads: Archive original 837 and 835 files — required for audits and essential for investigating payment disputes
•Map CARC and RARC codes: Maintain a lookup table for all Claim Adjustment Reason Codes and Remittance Advice Remark Codes to automate denial categorization
•Reconcile 835 to 837: Match every 835 payment back to the originating 837 claim — unmatched payments and unpaid claims signal data gaps
•Monitor 277CA acknowledgments: The 277 Claim Acknowledgment tells you whether your 837 was accepted or rejected by the payer before adjudication

Frequently Asked Questions

What is healthcare EDI?

Healthcare EDI (Electronic Data Interchange) is the standardized electronic exchange of healthcare data between trading partners — providers, payers, clearinghouses, and pharmacies. HIPAA mandates use of X12 EDI standards for most administrative transactions including claims, eligibility, and remittances.

What is the difference between 837P, 837I, and 837D?

837P (Professional) is used for physician and outpatient services, 837I (Institutional) is used for hospital and facility claims (UB-04 equivalent), and 837D (Dental) is used for dental claims. All three are X12 5010A1 standard but have different segment requirements.

What is an EDI 835?

The EDI 835 (Health Care Claim Payment/Advice) is the electronic remittance advice (ERA) sent by payers to providers detailing how claims were adjudicated and paid. It contains claim-level and service-level payment details, adjustment reason codes, and CAS segments explaining any reductions.

What is a clearinghouse in healthcare EDI?

A clearinghouse is an intermediary that translates and routes EDI transactions between providers and payers. Providers send 837 claims to the clearinghouse which validates, reformats, and forwards them to hundreds of different payers. Major clearinghouses include Change Healthcare, Availity, and Waystar.

What are ANSI X12 transaction sets?

ANSI X12 is the American National Standards Institute standard for EDI. HIPAA requires specific X12 transaction sets: 837 (claims), 835 (remittance), 270/271 (eligibility), 276/277 (claim status), 278 (prior authorization), and 834 (enrollment). The current mandated version is 5010.

How do I store EDI 837 data in Snowflake?

Use a three-layer architecture: a raw staging layer that stores the original EDI file text in a VARCHAR column alongside control numbers and received timestamp; a parsed layer with typed columns (DATE, NUMBER, VARCHAR) mapped from each X12 segment; and a conformed layer that joins to your member, provider, and facility dimensions. In Snowflake, use VARIANT for arrays like dx_codes, NUMBER(12,2) for all monetary amounts, and DATE rather than TIMESTAMP for service dates to simplify partition pruning. Load incrementally using the interchange_ctrl_no as your deduplication key.

What is the best database schema for EDI 835 remittance data?

The edi_835_payment table above is the production-proven starting point. Key design decisions: store charged_amt, allowed_amt, paid_amt, and patient_resp_amt as separate columns so you can compute payment variance without string parsing; capture adj_reason_code_1 through adj_reason_code_3 as individual columns rather than a JSON blob so denial queries stay simple; store remark_codes as an array for RARC values; and always carry edi_transaction_id back to the raw file so you can re-parse if the schema changes. Add a foreign key to edi_claim_header on clm_id to enable 837-to-835 reconciliation.

How do I query CARC denial codes from EDI 835 data?

Join edi_835_payment to carc_remark_code on adj_reason_code_1 = code WHERE code_type = 'CARC'. Filter WHERE paid_amt = 0 to isolate full denials, or WHERE paid_amt < charged_amt AND adj_reason_code_1 IS NOT NULL for partial reductions. Group by carc_code and the description from your lookup table to get human-readable denial summaries. The SQL example in the "835 Denial Rate" section above shows the full query pattern with 90-day windowing and average payment percentage.