The 48-Hour Healthcare dbt Audit Playbook: Triage Claims Lag, Code Drift, and Timestamp Anomalies

The Anatomy of a Healthcare dbt Failure

In modern analytics engineering, standard SaaS practices teach teams to write modular SQL, test for uniqueness, and document tables. But when engineering teams apply standard e-commerce or product-analytics conventions to healthcare data—such as claims (EDI 837/835), clinical encounters (HL7/FHIR), and member eligibility—they inevitably face silent data corruption:

• The Claim Line Fan-Out: Composite keys on claims tables that omit line-level modifiers or claim adjustment sequence numbers, causing silent double-counting of paid amounts in executive dashboards.
• The Claims Latency Anachronism: Treating claims feeds as real-time event logs, completely ignoring the 48-hour clearinghouse and 3-to-6 week payer adjudication delay. Naive queries evaluate retrospective facts that no human operator could have known at the date of service.
• Dropped Timestamp Precision: Upstream ETL ingestion engines truncating timestamps to midnight (00:00:00), masking whether an adverse event occurred before or after an admission order.
• Retroactive Enrollment Drift: Member coverage records updated months after care delivery, breaking referential integrity between claims and active coverage spans.

When we conduct our 2-week Healthcare AI Readiness Diagnostic, the first 48 hours are dedicated to an intensive, deterministic pipeline audit. Below is the exact step-by-step framework and SQL test suite we deploy.

---

Phase 1 (Hours 0–12): Grain & Composite Key Validation

The single most common bug in healthcare warehouse models is an undefined or broken grain. In claims engineering, a "claim" is not a row. A claim header contains multiple claim lines, each of which can receive multiple adjustments, partial denials, and re-adjudications over time.

If a model's surrogate key is hashed across claim_id and line_number without including claim_sequence_number or adjudication_timestamp, incremental dbt runs will create duplicate rows or overwrite valid historical change records.

The Audit Test: Composite Grain Uniqueness

We run a strict surrogate key grain test verifying that the combination of natural keys produces zero duplicates:

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

models:
  - name: fct_claim_lines
    tests:
      - refhealth_dbt_healthcare_checks.surrogate_key_grain:
          key_columns:
            - claim_id
            - line_number
            - claim_sequence_number
            - adjudication_date

If this test returns rows, incremental merge strategies will fail, financial totals will diverge, and any downstream AI agent will generate inaccurate financial summaries.

---

Phase 2 (Hours 12–24): Bitemporal Enrollment & Latency Auditing

Healthcare data requires bitemporal modeling because all times tell you something critical about operational reality:

1. Date of Service (Event Time): What the patient experienced clinically during the encounter.
2. Submitted Date (Transaction Time): How efficiently the provider's billing office generates and submits bills.
3. Received Date (Queue Latency): How long the claim sat in clearinghouse batches or payer intake queues.
4. Paid / Adjudicated Date (Settlement Time): When financial liability was finalized.

If your data models use a single timestamp or overwrite dimensions without Slowly Changing Dimensions (SCD Type 2), historical state reconstruction becomes impossible.

The Audit Test: Bitemporal Enrollment Consistency

In member eligibility tables, coverage spans must respect chronological physics. We test that effective_date, end_date, and updated_date maintain logical integrity:

-- models/staging/stg_member_eligibility.yml
version: 2

models:
  - name: stg_member_eligibility
    tests:
      - refhealth_dbt_healthcare_checks.bitemporal_enrollment_consistency:
          effective_date: coverage_start_date
          end_date: coverage_end_date
          enrollment_date: submission_date
          updated_date: system_loaded_at

Contradictions here reveal retro-enrollment churn, retroactive cancellations, and payer batch lags that distort cohort analysis and risk adjustment calculations.

---

Phase 3 (Hours 24–36): Code System Validity & Timestamp Auditing

Upstream hospital and clearinghouse feeds frequently mangle medical code standards or drop timestamp granularity during CSV conversion:

• CMS NPI Checks: Provider directories frequently contain malformed National Provider Identifiers (NPIs)—missing leading zeros, string truncation, or invalid prefixes. An invalid NPI breaks provider credentialing and network adequacy models.
• ICD-10 & CPT Formatting: Diagnoses and procedure codes must conform to structural syntax (e.g. 3–7 alphanumeric characters for ICD-10-CM with proper decimal placement; 5-character CPT/HCPCS codes).
• The Midnight Default Trap: Legacy database extracts that lack time components frequently default timestamps to 00:00:00. If an admission timestamp reads 2026-04-15 00:00:00, clinical sequencing breaks.

-- models/staging/stg_inpatient_encounters.yml
version: 2

models:
  - name: stg_inpatient_encounters
    columns:
      - name: billing_provider_npi
        tests:
          - refhealth_dbt_healthcare_checks.cms_npi_format

      - name: primary_diagnosis_code
        tests:
          - refhealth_dbt_healthcare_checks.icd10_code_validity

      - name: encounter_start_at
        tests:
          - refhealth_dbt_healthcare_checks.midnight_default_timestamp:
              max_midnight_ratio: 0.02 # Flag if >2% of rows default to 00:00:00

---

Phase 4 (Hours 36–48): Claims-to-Member Referential Integrity

The final phase verifies that financial claims connect cleanly to member enrollment records. In real-world payer data, claims frequently arrive for members whose enrollment records are missing, expired, or not yet synced:

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

models:
  - name: fct_medical_claims
    tests:
      - refhealth_dbt_healthcare_checks.claim_member_referential_integrity:
          member_model: ref('dim_members')
          claim_member_id: member_id
          service_date: date_of_service
          effective_date: coverage_effective_date
          end_date: coverage_term_date

Orphaned claims point to broken Master Patient Index (MPI) matching, clearinghouse mapping failures, or delayed eligibility roster updates.

---

The Output: An Executive Decision Memo & Ready-to-Merge Scripts

At the conclusion of the 48-hour audit, our clients do not receive a vague 60-page PDF report. They receive:

1. Executive Decision Memo: A 2-page briefing for the CTO and VP of Data outlining the exact failure modes, regulatory exposure, and priority rank.
2. The Pipeline Blocker Matrix: Every identified grain bug, bitemporal contradiction, and code drift item ranked by operational severity and fix complexity.
3. Ready-to-Merge Execution Scripts & Starter Template: Production-tested dbt macros from our open-source refhealth-dbt-healthcare-checks package, plus a cloneable DuckDB reference implementation in refhealth-starter-dbt-project that client engineers can inspect and test immediately.