← All work

Case study · Data engineering · Healthcare analytics

Medicare provider benchmarks, on autopilot

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.

  • Python
  • PostgreSQL
  • SQL
  • pandas
  • Claude API
  • GitHub Actions
  • Power BI
$120.1B Medicare Part B payments, 2024 +6.4% vs 2023
1.30M providers benchmarked +3.0% vs 2023
$329 paid per patient seen by a provider +10.2% vs 2023 · $282 in 2022
3.79M rows loaded for 2022–2024 0 rejected by quality checks

The problem

Public data, private effort

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

Six steps, zero clicks

Each step is plain Python or SQL with its own tests (44 in total, including a real Postgres run in CI).

  1. 1

    Discover

    Reads CMS's public catalog and finds each year's dataset ID by title, so new releases are picked up without code changes.

  2. 2

    Extract

    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.

  3. 3

    Validate

    Rejects rows with missing keys, non-numeric or negative values and duplicate NPIs, and records every rejection by reason.

  4. 4

    Load

    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.

  5. 5

    Analyze

    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.

  6. 6

    Report

    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

Where the money goes

Real output from the latest run. Comparisons use medians, so a handful of extreme billers can't skew a specialty.

The same specialty can cost 12× more in one state

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).

  • Above national
  • Below national
Hematology-Oncology · NV12.1×
Medical Oncology · MN10.2×
Hematology-Oncology · NE9.7×
Nuclear Medicine · FL7.3×
Hematology-Oncology · VA5.9×
Cardiology · ME0.41×
Occupational Therapist in Private Practice · MN0.35×
Clinical Laboratory · AL0.32×
Speech Language Pathologist · MN0.29×
Clinical Laboratory · WA0.26×
0.25×0.5×1×2×4×8×16×
View as table
SpecialtyStateProvidersMedian $/patientNational medianIndex
Hematology-OncologyNV54$3,766$31212.05×
Medical OncologyMN111$3,033$29810.17×
Hematology-OncologyNE28$3,019$3129.66×
Nuclear MedicineFL23$623$857.32×
Hematology-OncologyVA239$1,850$3125.92×
CardiologyME103$61$1500.41×
Occupational Therapist in Private PracticeMN341$223$6300.35×
Clinical LaboratoryAL62$59$1830.32×
Speech Language PathologistMN76$222$7710.29×
Clinical LaboratoryWA55$48$1830.26×

Biggest specialty shifts since 2023

Change in median payment per patient. Clinical specialties with at least 100 providers; facility, supplier and catch-all billing categories are excluded.

  • Increase
  • Decrease
Dentist−18.1%
Undersea and Hyperbaric Medicine+13.1%
Geriatric Psychiatry−12.5%
Licensed Clinical Social Worker+11.0%
Interventional Radiology−9.2%
−20.0%−10.0%0%+10.0%+20.0%
View as table
SpecialtyProvidersPaymentsPayments vs 2023Median $/patient vs 2023
Dentist203$4.6M−52.5%−18.1%
Undersea and Hyperbaric Medicine114$23.1M+4.7%+13.1%
Geriatric Psychiatry164$8.5M−10.7%−12.5%
Licensed Clinical Social Worker16,435$317.7M+1.9%+11.0%
Interventional Radiology2,560$376.0M−0.2%−9.2%

39% of outlier flags trace back to drug costs

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.

  • Mainly Part B drugs
  • Other services
  • Drug detail suppressed
Nurse Practitioner2,782
Physician Assistant1,131
Emergency Medicine996
Ambulance Service Provider562
Internal Medicine426
Diagnostic Radiology407
Podiatry308
Optometry292
View as table
SpecialtyFlagsMainly drugsOther servicesSuppressed
Nurse Practitioner2,7821,620371791
Physician Assistant1,131462407262
Emergency Medicine99637833126
Ambulance Service Provider56205620
Internal Medicine4262589969
Diagnostic Radiology407335153
Podiatry30814443121
Optometry29212910

Flags are statistical screening signals for review, never conclusions about any provider. This page shows aggregates only.

Auto-generated briefing · Claude

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

Built against docs, fixed against reality

The first run against live CMS data surfaced problems no test fixture had. Each one became a fix and a regression test.

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.

Catch-all categories topped the rankings

"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.

Patients were counted more than once

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.

Outliers needed context

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.

The model put a number on the wrong row

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.

Windows broke the very last step

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

Have data you re-pull by hand every month?

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.