The problem
Under the Transparency in Coverage rule, every major US health insurer has to publish its negotiated prices every month. United Healthcare complies. The files are enormous, deeply nested, and shaped for the letter of the regulation rather than for anyone trying to read them. The data is public in the sense that it is downloadable, and private in every sense that matters.
The constraint that made it interesting
The obvious approach is a Spark cluster: throw enough memory at the files to hold them, then transform. That works, and it costs more per month than the entire rest of the project. The interesting constraint was self-imposed — process hundreds of gigabytes without ever loading a file into memory, so the whole thing could run on infrastructure small enough that nobody would have to justify it.
That one decision determined the architecture. If you cannot hold the file, you cannot seek around inside it, which means every stage has to be a stream, and every stage has to tolerate being interrupted partway through a file that takes hours to read.
The approach
A hybrid pipeline that processes locally and stores cheaply. Playwright drives the UHC portal to discover the current month's files, because the URLs are generated rather than listed. A streaming parser walks the JSON incrementally and flattens the nested rate structures as it goes. Output lands in Parquet on S3, where DuckDB and Athena can query it in place with no download step, and a Next.js dashboard sits on top so the answer to “what does this procedure cost across these hospitals” is a filter rather than a project.
One thing that was harder than expected
The first version processed files synchronously, one after another, and it was fine until it wasn’t. A single file can take hours; a stall halfway through meant starting over, and the failures were rarely clean. Provider-reference sections large enough to stall the loader, files that timed out with no clear cause, and database deadlocks under concurrent writes all showed up in production rather than in testing.
The fix was to stop treating a file as a unit of work and start treating it as a
sequence of claimable ones. A PostgreSQL-backed work queue using
SELECT … FOR UPDATE SKIP LOCKED
lets distributed workers claim files atomically, with heartbeats to detect workers that
died mid-file and exponential backoff to retry the ones that failed for transient
reasons. Discovery, download, and processing became separate workers that scale
independently.
There is also a genuinely unglamorous discovery in here: the source files are not structurally consistent. The same insurer publishes files with the top-level keys in different orders, so the parser cannot assume it will meet the schema before it meets the data. Handling that is most of the difference between a pipeline that works on the file you tested and one that works next month.