clear-pricer

Overview

Since 2021, US hospitals have been required to publish every price they charge as a machine-readable file. Since 2024 those files must follow a CMS schema. The data is public, mandated, and close to unusable: files run to gigabytes, conform to the schema unevenly, and put numbers in the same column that mean different things. The NPPES registry of US providers is public too, and nothing connects the two.

clear-pricer ingests three Chicago hospitals’ price files, the full NPPES registry and a synthetic FHIR population. It validates and reconciles them behind quality gates that fail the pipeline, and republishes a clean dataset that anyone can query with no account.

The part worth reading is not the pipeline. It is the schema-drift log — 23 entries written as each deviation was found, which is where the rest of the design came from.

The problem

A price file that parses is not a price file you can compare.

Two hospitals can both be schema-valid and still disagree about what a column means. One types CPT codes as HCPCS, which is technically correct since CPT is HCPCS Level I, and ambiguous anyway. One reports a median allowed amount over zero claims, which the specification explicitly says not to encode. One publishes negotiated rates equal to its own list price. A pipeline that accepts all of this and emits a tidy table has not cleaned the data; it has hidden the problem and put a number on it.

So the question this had to answer was not “can the files be parsed” but “which of these dollars are actually comparable, and how do you show your working when most of them are not.”

Constraints

Record, never drop. Every column, value or row the parsers could not map is kept and counted in a published conformance table. A pipeline that quietly discards what it does not understand produces a clean-looking dataset that cannot be audited.

Integrity fails the run; source findings get published. A missing required column, lost rows, quarantine above one per cent, a broken reference or a non-synthetic patient turns the run red. A hospital’s own deviations from the specification do not, because a gate that failed on every publisher defect would never go green — and those deviations are the finding.

Public and synthetic data only. Price files and NPPES are public. The clinical population is Synthea, and a gate enforces that it stays synthetic.

Reproducible means the same bytes on any machine. Not “runs twice on my laptop.”

Architecture

discovery      cms-hpt.txt, curl + ETag; a 5 GB unchanged file costs one 304
landing        content-addressed by sha256, so a changed file lands beside the old one
parsers        streaming (ijson); record, never drop
staging        Parquet + drift log + quarantine
nppes          type-2 slowly-changing history, change-data-capture, not truncate-and-reload
dbt build      64 gates, every one able to fail the run
warehouse      DuckDB local, Postgres served, parity checked on every publish
release        15 Parquet files + check values, byte-reproducible, self-verifying

The same CLI steps run under Airflow 3 locally and on a GitHub Actions schedule — two slots about twelve hours apart, with a separate freshness watchdog between them: stage each source, sync NPPES, stage FHIR, build behind the gates, publish, export, release, verify-release. DuckDB is the local engine and Postgres the served one, and every publish checks parity between them.

Charge rows published	7,371,416 across 3 hospitals
Providers under change-data-capture	9,839,369 NPIs, 9,884,252 versions
Synthetic FHIR resources parsed	239,818 across 24 types; every reference resolves, gated
Quality gates	64 dbt tests, every one able to fail the run
Tests	124 — unit, end-to-end, and mutation tests that plant a defect for each gate
Release	15 Parquet files including the provider change history, plus check_values.json; byte-identical built on a Linux runner and a Windows workstation

What the sources actually contained

The design rule was record, never drop, and the drift log is what it recorded.

The files do not quite follow the format. UChicago’s JSON starts with a byte-order mark, which RFC 8259 forbids, so a strict parser rejects the whole file; the parser strips it on every source and logs that it did. Rush’s discovery file lists a URL with no scheme. Rush’s file is served by a vendor endpoint with no size, date or ETag, so change detection hashes the content instead.

The same column means different things. Northwestern types 1,037 HCPCS-shaped codes as CPT, so the pipeline keeps the declared type and derives a code_family from each code’s shape, and every comparison joins on that. 92.8% of Northwestern’s payer rows — 6,523,094 of them — report a median allowed amount with a count of zero, which the specification says not to encode; those are nulled in the clean columns, kept raw, and gated so they cannot leak back.

Some dollars are not negotiated prices. 55.9% of Northwestern’s dollar-plus-percentage rates do not reconcile against its own list price, and 1.40M of them equal a zero-count median. Rush publishes 39,116 Medicare Advantage rates at the chargemaster price, and a negotiated rate equal to list is not a negotiation. Northwestern’s fee-schedule dollars include $0.01 for a service listed at $1,361. Northwestern also prices 2,124 whole surgical cases as items carrying a single CPT code, so a $67,556 case can masquerade as a pathology exam. Every price carries a rate_basis saying where its dollar came from.

The registry moves under you. The September NPPES full file claims coverage through the 13th and holds 46 records dated the 14th, and for one NPI it disagrees with the weekly file under the same date. Deactivations arrive as stubs — an NPI and a date with every other field blank — which, taken as published, would erase the provider’s history, so a deactivation now carries the last known identity forward. About 17% of weekly records change nothing but their update dates, and the version hash excludes those dates so no version is written.

Claim headers do not match their lines. A header-versus-lines check over the synthetic claims found that Synthea’s Claim.total never equals the sum of its line items — 0 of 21,785 claims. Pharmacy lines carry no amount at all, and priced claims miss in both directions. Every EOB total matches its claim exactly, so the parser is reading the right fields. It is published as a measured finding rather than gated: a gate on it would fail every run on a pinned input.

Synthetic data needs checking too. One Synthea dataset used three different reference forms, and a resolver that knew only URNs reported 43,606 false dangling references. Multi-threaded Synthea is not byte-deterministic: two identical 16-core runs differed in one patient, found by comparing a hosted release against a local one.

Key decisions

All twenty-one are in DECISIONS.md with the rejected alternative beside each. The ones that shaped the rest:

Integrity gates fail; source findings are published. The split above is the whole design. It is also the decision most likely to look like a dodge, so the conformance table is published with the data: anyone can count the deviations themselves.

The price is the dollar, labelled by origin. Grounded in the CMS data dictionary, which tells hospitals to calculate and encode a payer-specific dollar where one can be calculated. The analysis added the second half: for comparisons, the methodology matters as much as the dollar.

Change-data-capture, not truncate-and-reload. NPPES lands in a type-2 history, so re-applying a file changes nothing and a weekly delta touches only its own records.

“Mapped” is measured, not declared. The FHIR parser records every field it reads, so the mapping report cannot drift from the code. It puts ExplanationOfBenefit at 5.3% mapped, which names the obvious next extension rather than hiding it.

The release proves its own grain. Every published table has a key that is gated unique and written down in docs/grain.md, so the grain is a test rather than a convention someone remembers. Each release also ships check_values.json — row counts, distinct keys, the charges-to-codes fan-out, integer-cent checksums — computed twice, once from the warehouse and once from the Parquet actually written, which must agree. The provider change history ships with it, because CMS publishes snapshots and not history.

Three read paths, sized to the job. Parquet on GitHub Releases for the full detail, a free-tier Postgres for the small served set, and FastAPI over the Parquet.

Results

NPI reconciliation, both directions. All 8 Type-2 NPIs the price files disclose resolve and verify against NPPES — 0.0% unresolved. In the other direction, NPPES holds 52 more active hospital NPIs registered under the same names at the same addresses or campuses, which puts disclosure coverage at 13.3%. The candidates are published with their evidence and a threshold sensitivity band.

Price variation. The same outpatient service’s list price differs a median 2.11× across the three hospitals, over 2,323 codes. For insured patients, who pays moves the price more than where they go: inside UChicago the same service varies a median 4.06× across payers, against a 1.55× typical gap between two hospitals.

Synthetic FHIR. Only 1.2% of synthetic claim lines share a code system with real price files. Clinical claims and billing prices barely share a vocabulary — which is a finding about the ecosystem, not about the generator.

How it’s verified

Gates in the run, not beside it. dbt build runs 64 tests that fail the run. A deliberately broken input turned the Airflow run red, publish never ran, and Postgres kept the previous build. Three broken fixtures fail the run in CI on every push, and each was checked to trip the gate it was meant to.

Idempotency, proven rather than assumed. Restaging a file gives byte-identical Parquet. Re-applying every NPPES file left the 9,855,257-row history identical by fingerprint.

Parity. Every publish checks counts, distinct keys and money sums between DuckDB and the served Postgres, and a mismatch fails the task.

Grain, proved. Every release table has a key that is a dbt gate. Two header-versus-lines gates run in both directions with a half-cent money tolerance: the price summary is re-derived from its charge lines, and claims are checked against their lines and their EOBs. Each new gate is tested against planted defects — an overcount, an orphan line, a cent off, an overlapping provider version — and has to catch every one.

A release that verifies itself. check_values.json is computed from the warehouse and from the written Parquet at export, and the two must agree. clear-pricer verify-release then recomputes it from a clean download. It catches a flipped byte, a file rewritten together with its manifest hash, and an edited check value; each of those is tested.

Reproducibility across machines. A GitHub runner and a workstation independently built release fingerprint 67efd3d2f5b0b88f… — 15 files, including the 9.88M-row provider history, byte-identical. Every committed figure regenerates from pinned inputs with one command.

Liveness, not just correctness. Every check above asks whether a run was right. The watchdog asks whether a run happened: it confirms the pipeline has succeeded within 26 hours, heals a missed day, and alerts. In its first week, 21 of 21 scheduled events fired and every pipeline run passed.

A stranger’s path. A fresh container with no clone and no credentials queried the release over HTTPS, including the full 7.37M-row fact table. CI does the clean-clone equivalent on every push.

What broke

Float cents. Rush truncates derived dollars where the first rule rounded them, and in floating point the difference is slightly more than a cent, so 1,037 rows were mis-flagged as unreconciled. Reconciliation now compares within one cent of the exact product, with regression tests for truncation, rounding and a two-cent miss.

Three idempotency bugs in the change-data-capture. Two were caught by reading the code before running it. The third was caught by the re-apply proof itself — NPPES publishing one NPI in two files under the same date. A proof that can fail is worth more than one that is assumed.

A leaked password. During setup a failed connection test printed a database DSN, password included, because the driver echoes it. The password was rotated, every connection now runs under a guard that redacts DSN passwords from errors, there is a regression test for the exact case, and the history was scanned clean before the repository went public.

“Deterministic” on one day. On the second day the hosted run cut a new release with no upstream change. The synthetic population had grown by a day of simulated history, because Synthea’s end date defaults to today, and the cross-machine proof had compared two builds from the same day. The end date is pinned now: reproducible on any machine and any day. Hunt for the clock.

“Deterministic” on one machine. Two builds on one workstation agreed; a hosted build did not, in 3 of 13 files. Row-level diffs traced it to thread scheduling in an aggregate, multi-threaded Synthea, and a ledger that included machine-specific proof runs. All three were fixed and the fingerprints then matched across machines.

A late run, read as a dropped one. The morning after going live there was no sign of the 11:17 UTC run, four hours and forty-three minutes after it was due, and I called it dropped. GitHub documents scheduled runs as best-effort — they can be delayed or dropped — and nothing was watching either way. The response was to monitor the outcome rather than the trigger: two daily slots twelve hours apart, and a separate watchdog on the last successful run. Past 26 hours it re-enables the workflow, dispatches a catch-up run, and fails on purpose so the failure reaches someone. It was proven by forcing it stale.

A week of run history then corrected the diagnosis. That first run fired at 16:49, five and a half hours late, and every one of the next twenty scheduled events fired too — 21 of 21, each between two and a half and eight hours after its cron time. Nothing has been dropped. The watchdog’s 26-hour threshold is what makes that lateness harmless, so the design still holds; the reasoning behind it was half wrong. A job that does not start cannot report its own failure — and before calling a run missing, know how late the platform normally runs.

A machine-local counter in a published table. Rebuilding the release on a second machine produced one differing file. The NPPES file ledger numbered its rows 1, 2, 3, 4, 9 on the workstation and 1 to 5 on the runner: the raw counter was also counting re-apply proof runs, which are never published. The sequence is now renumbered over the published rows and gated gap-free, and the two builds then matched byte for byte. A counter that counts things the reader cannot see is not a column, it is a local variable that escaped.

A plausible wrong answer. The first cut of the analysis said Rush was the most expensive hospital on 398 of 534 codes. Asking what kind of dollar each negotiated rate actually was exposed list-price Medicare Advantage rates, case packages and unit artifacts. The analysis now opens with a table of what each dollar is, because the first answer was well-formed, confident and wrong.

Limits

Three hospitals, one metro. Scoped to Northwestern Memorial, Rush and UChicago Medical Center, so the analysis says “three Chicago hospitals”, not “Chicago”.

Price files carry NPIs only in the header, so the unresolved rate is measured over 8 NPIs. It becomes a meaningful rate at larger scale.

No claims volumes. Every code counts once in the analysis, so nothing here is weighted by how often a service is actually delivered.

Northwestern’s contracted prices are not comparable as published, so the contracted comparison is Rush against UChicago only.

FHIR is synthetic by construction, enforced by a gate, so it demonstrates the path rather than any clinical finding.

Not modelled yet: NPPES secondary practice locations, and ExplanationOfBenefit adjudication — the payment detail.

Hosting. The served Postgres is a free tier that pauses without activity. The scheduled runs have kept it awake so far, which is an observation rather than a guarantee. The API is not deployed to a public host.

Scheduling is late, and best-effort. GitHub fired every scheduled event in the first week, but between two and a half and eight hours after the cron time, so “daily at 11:17” means “some time that afternoon”. All three schedules — both pipeline slots and the watchdog — run on GitHub Actions, so a platform-wide outage takes them down together. A missed day is healed and alerted, not prevented.

Links

The repository is linked at the top of this page, and the data is on its releases — no account, no clone:

import duckdb   # pip install duckdb
R = "https://github.com/tjromack/clear-pricer/releases/latest/download"
duckdb.sql(f"SELECT * FROM '{R}/rpt_npi_reconciliation.parquet'").show()

clear-pricer verify-release --tag latest recomputes a release’s check values from a clean download, and docs/grain.md documents the key each published table is gated on. docs/schema-drift-log.md is the 23-entry record of what the sources actually did, DECISIONS.md carries all twenty-one decisions with their rejected alternatives, docs/BUILD-LOG.md records each break as it happened, and docs/QUERY.md has the other read paths. The analysis is written up as Same code, different price.