The synthetic Medicare claims dataset used in this project has 167k medical claims and 2.3k eligibility rows. It’s clean, well-formed, and deliberately simple. And it still took a week to get a working dbt pipeline from raw to analytics-ready marts.
That gap between “clean data” and “working pipeline” is worth examining.
The schema is genuinely complex
A single medical claim has up to 25 diagnosis code fields, 25 procedure code fields, multiple revenue codes, a bill type, a place of service, NDC drug codes, rendering and billing provider NPIs, and line-level detail that rolls up differently depending on claim type.
Institutional claims (hospital stays) and professional claims (physician services) share the same underlying table but use different fields. An inpatient claim has a DRG code, discharge disposition code, and admission type. A professional claim has a CPT code, a service facility NPI, and a modifier. They coexist in the same claims table and need to be separated before almost any analytical operation.
The Tuva Core mart does this split correctly, but understanding why it does what it does requires knowing that this is how actual Medicare claims work — it’s not an artifact of the data format, it’s the structure of the billing system.
The reference tables are load-bearing
Healthcare analytics depends on reference tables:
- ICD-10-CM diagnosis codes (~70,000 codes, updated every October)
- CPT/HCPCS procedure codes (~10,000 active codes)
- Revenue codes (~1,000 codes, specific to institutional claims)
- Place of service codes (~30 codes)
- CMS HCC mapping (~10,000 ICD-10 to HCC crosswalk entries)
- CCSR groupings for ICD-10 and CPT
Without these, you can count claims and sum paid amounts. You cannot do anything clinical.
Tuva includes all of these as seeds. On DuckDB with remote S3 storage, the seed load is the slowest part of the initial setup — dbt seed on the full reference table set takes 4-5 minutes on first run. There’s also an issue where dbt show triggers a seed reload from S3 on every invocation, which adds unexpected latency during development if you’re using it to preview model results.
Enrollment data matters as much as claims
The 2.3k eligibility rows are small relative to 167k claims, but they’re critical. Every claims analysis needs to be denominator-corrected by enrollment: “how many qualifying members were enrolled during this period” is the denominator for every rate.
HEDIS measures, PMPM spend, and RAF risk profiles all require correct denominator identification. A patient who disenrolled mid-year should only appear in the denominator for the months they were enrolled. Getting this wrong inflates or deflates rates by amounts that matter in real-world Medicare Advantage or MSSP/ACO contracts, where small rate changes drive significant payment adjustments.
Date logic is everywhere
Eligibility periods, service dates, claim processing dates, plan year boundaries, performance periods — every measure has its own date logic. HEDIS measures have specific measurement periods (calendar year for most, rolling 12 months for some). HCC RAF is calculated on calendar year claims. Readmissions are 30-day windows post-discharge.
In the synthetic dataset all of this is clean. In real Medicare data, claims arrive months after service (the Medicare timely filing limit is 12 months), eligibility termination dates are retroactively adjusted, and the same service can straddle a plan year boundary.
What “data quality” actually means for claims
The Tuva DQI mart surfaces fill rates per field, but the real data quality issues in claims data aren’t mostly about missing fields. They’re about:
- Diagnosis codes in ICD-9 format in a field that expects ICD-10
- Dates where
service_end_date < service_start_date - Claims with no matching eligibility record during the service period
- Provider NPIs that don’t exist in the NPPES registry
- Revenue codes that don’t match the claim type (revenue codes only appear on institutional claims)
- Line-level paid amounts that don’t sum to the header-level paid amount
The synthetic dataset passes all of these checks by construction. Real payer data routinely fails several of them, and the failures are often correlated — if the source extract has a date format issue, it typically affects all claims in a batch.
The structural conclusion
Healthcare data is complex not because it was designed badly, but because it encodes a genuinely complex system. Claims capture what a provider billed, not what happened clinically. Eligibility data is managed by plan administrators who correct records retroactively. Reference tables change annually when CMS publishes updates.
A pipeline that works on synthetic data proves the structural logic is correct. Whether it handles real-world messiness is a separate question — one that only gets answered when you connect to actual payer data. The synthetic dataset is the best possible starting point, but it doesn’t reveal the edge cases that require the most engineering effort.