U.S. hospitals are legally required to publish their prices — and they comply in the least usable way possible. This was the machine that read those files anyway: a pipeline that ingested thousands of sprawling, inconsistent price disclosures and rebuilt them into a single, source-cited place to compare what a procedure actually costs. Here's how it worked — and the licensing wall that retired it.
Since the CMS Hospital Price Transparency rule, every U.S. hospital must post a machine-readable file (MRF) of its prices — gross, cash, and the rates negotiated with each insurer. The letter of the law is met. The spirit is not.
There is no enforced schema, so the files are a zoo. One hospital ships a tidy 2 MB CSV; the next ships a 2 GB nested JSON with hundreds of plan tiers per line. Column names, encodings, file hosting, and the very definition of a "price" all differ from facility to facility. A patient cannot read these. That gap — between published and usable — was the entire opportunity.
Follow a single line item — a knee MRI at a fictional hospital — from a raw federal file to a clean, cited number a patient can actually read. Scroll to advance the pipeline.
Locate the hospital's MRF — from the CMS dataset, by crawling the hospital's own transparency page, or via a manual URL handoff when it's hidden.
Download it — with a real-browser fallback for bot walls and link-wrappers — then decompress. Many files don't fit in memory.
Sniff the shape before trusting it: which CMS schema version, JSON vs CSV vs spreadsheet, and where the price columns actually live.
Stream every line item out with an on-disk SQL engine, so a multi-gigabyte file is queried — not loaded. One raw row for our procedure:
The hard part. Map the hospital's wording to a canonical procedure, collapse the plan tiers to a representative median, and strip component fees that make a price look misleadingly low.
Score the row before it's allowed near the site: is the file fresh, is this a real facility price, and is it a duplicate of a file shared across a hospital system?
Write the clean row to Postgres for serving — and archive the original file to object storage, plus a columnar snapshot, so every number stays traceable to its source forever.
Render it as a page a person can read — statically generated, fast, and cited back to the exact file and ingest date.
Half the battle is finding the file at all. The starting point is the CMS dataset, but coverage is patchy, so the crawler also reads each hospital's own transparency page, probes likely paths, and unwraps link-protection redirects.
When a file was genuinely hidden, a manual-handoff queue let a human drop in the URL — then the pipeline took over.
Download with retries and a failure taxonomy (blocked, moved, dead), falling back to a real headless browser for hosts that refuse plain requests. Gzipped multi-gigabyte files are streamed to disk rather than held in memory.
"Published" and "reachable" are not the same thing. A surprising share of compliance lived behind bot walls.
Before parsing, sniff the file: which CMS schema version, JSON vs CSV vs spreadsheet, delimiter, encoding, and where the actual price columns live. Guessing wrong here poisons everything downstream.
No enforced schema means detection has to be defensive — assume the file is lying about its own shape until proven otherwise.
Extract every line item using an on-disk SQL engine, so a file far larger than available RAM is queried in place. A single hospital can yield tens of thousands of rows, each with its own gross, cash, and a fan of negotiated rates.
DuckDB over the raw file meant a 6 GB JSON was a query, not a crash.
Free-text descriptions get mapped to a canonical procedure. The dozens of plan-specific negotiated rates collapse to a median with a published range. And separate professional or component fees are flagged and excluded, so a facility price isn't quietly understated.
This stage is where "a thousand spreadsheets" becomes "one comparable number" — and where most of the judgment lives.
A rubric gates every row: Is the file's self-reported date stale? Is this a real facility price or a placeholder? Is it a duplicate of a file shared across a multi-hospital system — which would otherwise double-count a price as if two hospitals agreed?
Trust signals — payer count, shared-tier flags, file staleness — were surfaced on the page, not hidden. The number you saw came with its own caveats.
Clean rows land in serverless Postgres for fast reads. In parallel, the original file and a columnar snapshot are pushed to object storage with retention — so any number on the site can always be traced back to the exact bytes it came from.
Provenance was the product. "Trust us" is worthless in pricing; "here's the source file and the date" is the whole pitch.
Pages were statically generated and incrementally revalidated — fast to load, cheap to host, friendly to search engines. Every price linked back to its source file and ingest date, with plain-language context for what gross, cash, and negotiated actually mean.
From a 6 GB federal file to a number a patient can read in under a second — that was the entire job.
Nothing exotic — the difficulty was never the framework, it was the data. The stack was chosen to keep a solo operator's cost and maintenance near zero while staying fully traceable.
To compare a procedure across hospitals, you need a shared key — and the universal key for medical procedures is CPT, a code set copyrighted and licensed by the American Medical Association. The pipeline could read the files. It couldn't escape the license on the one thing that made the files comparable.
It's a structural problem, not a personal one. The data hospitals are required to publish is gated, for comparison purposes, behind a code set that's licensed per user — and "every patient in America" isn't a number you can license. That tension is the most interesting thing this project surfaced, and it's still unresolved across the industry.
Discovery, fetching, multi-format parsing, normalization, quality scoring, storage, and serving — designed, built, and operated solo.
Thousands of inconsistent, multi-gigabyte files turned into one clean, comparable, source-cited dataset.
Every number traceable to its original file and date — with trust signals surfaced, not buried.
Spotting a structural licensing wall, engaging it honestly, and retiring cleanly instead of building on sand.