EDI 837 vs 835 Claims Modeling in dbt: Reconciliation, Reversals, and Denial Rates Without Row Multiplication

When analytics engineers transition into healthcare from tech or e-commerce, the first instinct is to treat a medical claim like an invoice and its payment like a Stripe transaction receipt. They see an upstream table of billing submissions (EDI 837) and a table of remittance advices (EDI 835), and they write the obvious query: select * from 837 inner join 835 on claim_id.

Within two weeks, finance is in a panic. Total charged revenue has doubled, the reported Medical Loss Ratio (MLR) has swung wildly by 18 points, and the executive dashboard reports an alarming 42% denial rate for a health system whose real net denial rate is under 6%.

In healthcare, an 837 is not an invoice and an 835 is not a receipt. They are asynchronous, multi-party negotiation messages spanning months, clearinghouses, adjudication engines, and contractual write-offs. If your dbt models do not account for claim frequency codes, adjustment segment cardinality, and overpayment recoupments, your downstream reporting is guaranteed to corrupt.

Here is how senior healthcare data teams model the relationship between EDI 837 and 835 in dbt without row explosion, phantom denials, or broken financial ratios.

---

1. The Core Asymmetry: What 837 and 835 Actually Represent

Before writing a single line of SQL, you have to understand who generated the data and what operational objective they were trying to accomplish:

• EDI 837 (Health Care Claim): The provider or billing vendor’s assertion of work performed. It records the Date of Service, clinical diagnosis codes (ICD-10-CM), procedure codes (HCPCS/CPT), billed charges, and rendering/billing National Provider Identifiers (NPI). Crucially, billed charges in an 837 are aspirational—providers bill their gross chargemaster rates, knowing the contracted rate is significantly lower.
• EDI 835 (Health Care Claim Payment/Advice): The payer’s adjudicated response. It details what the payer agreed to pay, what the member owes (deductible, copay, coinsurance), contractual write-offs, and which claim adjustment reason codes (CARCs) explain the variance. It also carries bulk Electronic Funds Transfer (EFT) settlement trace numbers and batch provider adjustments.

The two streams do not arrive together, they do not update atomically, and their relationship is almost never 1:1.

---

2. Trapdoor #1: Claim Resubmissions & Frequency Code Bitemporality

When a provider submits a claim with missing documentation, the payer rejects or denies it. The revenue cycle team corrects the error and resubmits it as a replacement claim.

In the 837 CLM05-3 element, the standard mandates a Claim Frequency Code:

• 1 = Original Claim
• 7 = Replacement of Prior Claim
• 8 = Void / Cancel of Prior Claim

If your staging models simply aggregate all rows by patient_control_number or claim_id, you will count the $450 charge on the original claim, the $450 charge on the replacement claim, and perhaps a void transaction—instantly tripling your billed volume.

In dbt, you must separate the transactional audit ledger from the current net financial state. In your marts layer, use a deterministic window function to isolate the current valid version:

-- models/marts/fct_medical_claims_current.sql
with all_claim_versions as (
    select
        claim_id,
        claim_line_number,
        claim_frequency_code,
        submission_date,
        adjudication_date,
        date_of_service,
        billed_charge_amount,
        paid_amount,
        claim_status,
        -- Rank newest adjudication cycle first; break ties with frequency code hierarchy (8 > 7 > 1)
        row_number() over (
            partition by claim_id, claim_line_number
            order by 
                adjudication_date desc,
                submission_date desc,
                case claim_frequency_code
                    when '8' then 3
                    when '7' then 2
                    when '1' then 1
                    else 0
                end desc
        ) as version_rank
    from {{ ref('stg_claims_adjudicated') }}
)

select
    claim_id,
    claim_line_number,
    claim_frequency_code,
    date_of_service,
    -- If the latest transaction was a void (8), net financial value is zero
    case when claim_frequency_code = '8' then 0.0 else billed_charge_amount end as net_billed_charge_amount,
    case when claim_frequency_code = '8' then 0.0 else paid_amount end as net_paid_amount,
    claim_status
from all_claim_versions
where version_rank = 1

---

3. Trapdoor #2: The CAS Segment Multiplication Trap

This is the single most common mistake in healthcare data warehouses.

In an EDI 835 remittance, every difference between the billed charge and the paid amount is articulated in Claim Adjustment Segments (CAS). Each CAS segment pairs a Claim Adjustment Group Code with one or more Claim Adjustment Reason Codes (CARC) and amounts:

• CO (Contractual Obligation): Contracted fee schedule write-offs (e.g., CARC 45 = charges exceed fee schedule limit).
• PR (Patient Responsibility): What the patient owes (CARC 1 = deductible, CARC 2 = coinsurance, CARC 3 = copay).
• OA (Other Adjustments): Coordination of benefits, secondary payer adjustments.
• PI (Payer Initiated Reductions): Medical necessity reductions, administrative penalties.
• CR (Correction and Reversals): Overpayment reversals.

A single claim line item often carries three distinct CAS adjustments: a $120 contractual write-off (CO-45), a $30 patient copay (PR-3), and a $15 secondary coordination adjustment (OA-23).

The disaster: If an analytics engineer flattens these CAS segments directly into the primary claim fact table, that single claim line multiplies into three rows. If another downstream analyst runs sum(paid_amount) or count(distinct claim_id) without knowing the table is grouped at the (claim_line, cas_group, carc_code) grain, every financial summary is instantly corrupted.

The Solution: Two-Table Normalized Dimensional Bridge

Never flatten CAS segments into your primary claim fact table. Instead, model two clean tables:

1. fct_claims (Grain: 1 row per claim line): Store the collapsed, balanced financial summary columns directly on the line: billed_charge_amount = paid_amount + patient_due_amount + contractual_adjustment_amount + other_adjustment_amount.
2. fct_claim_adjustments (Grain: 1 row per claim line adjustment reason): A child bridge table containing the granular breakdown of every CAS group, CARC, and Remittance Advice Remark Code (RARC).

-- models/staging/stg_835_cas_adjustments.sql
with raw_cas as (
    select * from {{ ref('raw_edi_835_cas') }}
),

aggregated_to_claim_line as (
    select
        claim_id,
        claim_line_number,
        remittance_trace_number,
        sum(case when adjustment_group_code = 'CO' then adjustment_amount else 0.0 end) as total_contractual_adjustment_amount,
        sum(case when adjustment_group_code = 'PR' then adjustment_amount else 0.0 end) as total_patient_responsibility_amount,
        sum(case when adjustment_group_code in ('OA', 'PI') then adjustment_amount else 0.0 end) as total_other_adjustment_amount,
        sum(case when adjustment_group_code = 'CR' then adjustment_amount else 0.0 end) as total_reversal_adjustment_amount
    from raw_cas
    group by 1, 2, 3
)

select * from aggregated_to_claim_line

This preserves exact reconciliation on the parent fact table while allowing revenue cycle analysts to drill into the child table whenever they need to analyze specific denial reasons.

---

4. Trapdoor #3: Takebacks, Negative Paid Lines, and PLB Segments

In standard accounting, a vendor payment is positive. In healthcare remittances, negative payments are routine.

When a payer audits historical claims and determines that a provider was overpaid for an encounter six months ago, they rarely send an invoice requesting a refund check. Instead, they execute an automatic recoupment or takeback on the provider’s next electronic funds transfer.

This manifests in two ways in EDI 835 feeds:

1. Claim-Level Reversals: The 835 issues a claim payment line with a negative paid_amount (e.g., -$250.00) referencing the original historical claim ID.
2. Provider-Level Adjustments (PLB Segments): The payer deducts money from the batch check at the provider tax ID level rather than tying it to a specific claim line. A PLB segment might deduct $5,000 for “Overpayment Recovery” (PLB reason code WO or FB) from a $40,000 EFT check covering 200 different patient claims.

If your data models drop negative payment amounts or assume paid_amount >= 0, you will overstate your net collections and cause reconciliation mismatches between your data warehouse and your bank account ledger.

In dbt, your tests must explicitly accommodate legitimate negative claims adjustments while asserting that the parent financial balance equation remains closed:

-- models/marts/_marts_models.yml
version: 2

models:
  - name: fct_claims
    tests:
      - dbt_utils.expression_is_true:
          name: assert_claims_balance_equation
          expression: >
            abs(billed_charge_amount - (
              paid_amount + 
              patient_due_amount + 
              contractual_adjustment_amount + 
              other_adjustment_amount
            )) < 0.02
      - refhealth_dbt_healthcare_checks.surrogate_key_grain:
          grain_columns:
            - claim_id
            - claim_line_number

---

5. Trapdoor #4: The “Denial Rate” Illusion

Ask three executives at a health system or health-tech startup what their denial rate is, and you will get three wildly different numbers: 5%, 18%, and 45%.

Why? Because in naive claims modeling, any claim that arrives with an adjustment or a zero payment is marked as “denied.” But in actual revenue cycle operations, there are three distinct categories:

To compute a true net denial rate that executive leadership can trust, you must aggregate across the full lifecycle of the encounter:

-- models/marts/fct_encounter_denial_metrics.sql
with claim_lifecycle as (
    select
        encounter_id,
        count(distinct claim_id) as total_submission_cycles,
        max(case when submission_cycle = 1 and paid_amount = 0.0 and denial_reason_code is not null then 1 else 0 end) as was_denied_first_pass,
        sum(billed_charge_amount) as total_gross_billed,
        sum(paid_amount) as final_adjudicated_paid_amount,
        sum(contractual_adjustment_amount) as total_contractual_allowance,
        sum(patient_due_amount) as total_patient_due
    from {{ ref('fct_medical_claims_lifecycle') }}
    group by 1
)

select
    count(case when was_denied_first_pass = 1 then 1 end)::float / nullif(count(*), 0) as first_pass_denial_rate,
    count(case when final_adjudicated_paid_amount = 0.0 and total_patient_due = 0.0 and total_contractual_allowance < total_gross_billed then 1 end)::float / nullif(count(*), 0) as final_unresolved_denial_rate
from claim_lifecycle

---

6. How This Fits the Governed Data Foundation (Layer 2)

In the Healthcare Data AI Operating System (HDAIOS) v1.0, claims and remittances reside inside the Governed Data Foundation (Layer 2).

If your Layer 2 claims models carry compound grain defects or uncalibrated denial rates, any downstream initiative is doomed:

• Actuarial Metrics (Layer 1): Medical Loss Ratios (MLR) and Allowed Per Member Per Month (PMPM) costs will be overstated due to duplicated line items. (Inspect our open-source implementation in refhealth-starter-dbt-project v1.1.0 to see how monthly plan metrics are cleanly isolated).
• Clinical & Operational AI (Layer 4): Automated care coordination agents or prior authorization workflows reading corrupted claims history will hallucinate prior denial reasons or trigger erroneous patient outreach.