The catalog wasn't shaped like the docs
Discovery found zero datasets: CMS lists each year as its own entry with a release date in the title. We rewrote the matcher for the live layout and added a test built from the real catalog.
Case study · Data engineering · Healthcare analytics
An automated pipeline that pulls 3.8 million rows of public Medicare data, benchmarks every specialty and state with SQL, screens for statistical outliers, and writes an executive briefing with Claude. It runs weekly with no one touching it.
The problem
CMS publishes what Medicare pays every clinician in the country: about 1.3 million providers a year, 70-plus columns each. It's free, but answering a simple question like "how does oncology in Nevada compare with the rest of the country?" means downloading gigabytes, cleaning them and rebuilding the same pivot tables every year.
The goal was a pipeline that does all of it unattended: find the newest files, load them safely, compute fair comparisons, flag what deserves a closer look, and hand a decision-maker a one-page briefing plus a live Power BI model.
How it works
Each step is plain Python or SQL with its own tests (44 in total, including a real Postgres run in CI).
Reads CMS's public catalog and finds each year's dataset ID by title, so new releases are picked up without code changes.
Pages through the CMS API 5,000 rows at a time, requesting only the 11 columns it needs, with retries and a row-count check against CMS before anything loads.
Rejects rows with missing keys, non-numeric or negative values and duplicate NPIs, and records every rejection by reason.
Replaces each year in a single Postgres transaction via COPY: 1.3M rows in about 20 seconds, and a failed load leaves the old data intact.
SQL views compute medians by specialty, state and year, and a robust z-score (median/MAD on a log scale) to flag unusual payment patterns.
Renders the tables in code, then asks Claude to write the summary from aggregate numbers only. Emails the briefing and refreshes the Power BI exports.
What the 2024 data shows
Real output from the latest run. Comparisons use medians, so a handful of extreme billers can't skew a specialty.
Median payment per patient for a specialty in one state, relative to that specialty's national median (log scale; states with at least 20 providers).
| Specialty | State | Providers | Median $/patient | National median | Index |
|---|---|---|---|---|---|
| Hematology-Oncology | NV | 54 | $3,766 | $312 | 12.05× |
| Medical Oncology | MN | 111 | $3,033 | $298 | 10.17× |
| Hematology-Oncology | NE | 28 | $3,019 | $312 | 9.66× |
| Nuclear Medicine | FL | 23 | $623 | $85 | 7.32× |
| Hematology-Oncology | VA | 239 | $1,850 | $312 | 5.92× |
| Cardiology | ME | 103 | $61 | $150 | 0.41× |
| Occupational Therapist in Private Practice | MN | 341 | $223 | $630 | 0.35× |
| Clinical Laboratory | AL | 62 | $59 | $183 | 0.32× |
| Speech Language Pathologist | MN | 76 | $222 | $771 | 0.29× |
| Clinical Laboratory | WA | 55 | $48 | $183 | 0.26× |
Change in median payment per patient. Clinical specialties with at least 100 providers; facility, supplier and catch-all billing categories are excluded.
| Specialty | Providers | Payments | Payments vs 2023 | Median $/patient vs 2023 |
|---|---|---|---|---|
| Dentist | 203 | $4.6M | −52.5% | −18.1% |
| Undersea and Hyperbaric Medicine | 114 | $23.1M | +4.7% | +13.1% |
| Geriatric Psychiatry | 164 | $8.5M | −10.7% | −12.5% |
| Licensed Clinical Social Worker | 16,435 | $317.7M | +1.9% | +11.0% |
| Interventional Radiology | 2,560 | $376.0M | −0.2% | −9.2% |
9,255 providers were flagged for unusually high payment per patient against their specialty peers. 3,597 of them are mostly Part B drug payments (expensive infused or injected drugs), and CMS suppresses the drug detail for 1,765. Top 8 specialties shown.
| Specialty | Flags | Mainly drugs | Other services | Suppressed |
|---|---|---|---|---|
| Nurse Practitioner | 2,782 | 1,620 | 371 | 791 |
| Physician Assistant | 1,131 | 462 | 407 | 262 |
| Emergency Medicine | 996 | 37 | 833 | 126 |
| Ambulance Service Provider | 562 | 0 | 562 | 0 |
| Internal Medicine | 426 | 258 | 99 | 69 |
| Diagnostic Radiology | 407 | 3 | 351 | 53 |
| Podiatry | 308 | 144 | 43 | 121 |
| Optometry | 292 | 1 | 291 | 0 |
Flags are statistical screening signals for review, never conclusions about any provider. This page shows aggregates only.
Total Medicare payments reached $120.06B in 2024, up 6.4% from the prior year, while the provider count rose 3.0% to 1,296,739. Provider-patient pairs, which count a patient once for each provider seen, fell 3.5% to 365.0 million, so payment per patient seen by a provider climbed 10.2% to $328.94. Among specialties with at least 100 providers, the median payment per patient fell 18.1% for Dentist (with total payments down 52.5%) and rose 13.1% for Undersea and Hyperbaric Medicine. State-level cost indexes were widest in oncology, with Hematology-Oncology in NV at 12.05x the national median ($3,766 vs $312) and Medical Oncology in MN at 10.17x ($3,033 vs $298), while Clinical Laboratory in WA was lowest at 0.26x ($48 vs $183). The analysis statistically flagged 9,255 providers for review (robust z >= 4.5), of which 3,597 (39% of flags) are driven mainly by Part B drug payments, and CMS suppresses the drug detail for 1,765 flags; these flags indicate unusual payment patterns that warrant review and are not evidence of wrongdoing.
Written by the model from aggregate figures only. The model gets labeled, pre-computed facts, and every percentage it writes is checked against them before the briefing ships.
What real data taught us
The first run against live CMS data surfaced problems no test fixture had. Each one became a fix and a regression test.
Discovery found zero datasets: CMS lists each year as its own entry with a release date in the title. We rewrote the matcher for the live layout and added a test built from the real catalog.
"Unknown Supplier" with 39 providers led the specialty movers. Excluding organization-type and catch-all categories and raising the minimum to 100 providers surfaced real clinical trends.
Summing patients across providers double-counts anyone who sees several doctors, so "365M beneficiaries" was wrong. The report now says "per patient seen by a provider", and the model is told the same.
The most extreme flags were mostly drug spend: one provider's payments were 90% Part B drugs. Pulling CMS's drug payment field lets each flag show its drug share, so reviewers don't chase the wrong lead.
An early briefing gave dentists the −0.2% that belonged to another specialty. The model now gets labeled facts instead of raw decimals, and any percentage it can't trace back to a fact triggers a retry, then a fallback to the template.
The first run loaded 3 years of data, then crashed writing the report: Windows defaults to a legacy text encoding that can't store ≥ or —. Every file read and write now names UTF-8, verified under the Windows default.
Next
The same pattern works for claims, scheduling, payroll or any public dataset: pull it, check it, benchmark it, and get a briefing in your inbox.