Methods

How the numbers on this site are produced: where the data comes from, how it moves through the model layers, what each mart's grain is, and which tests and checks run before a number is published. Written for data and analytics readers who want to judge the method, not just the charts.

Sources: CMS Medicare Physician & Other Practitioners public use file (PUF), CMS Medicare Part D Prescribers by Provider, CMS Medicare Monthly Enrollment, CMS Open Payments (General payments), NPPES NPI registry, the HHS-OIG List of Excluded Individuals/Entities (LEIE), CMS ICD-10-CM code descriptions (diagnosis-code reference), and the CMS Physician Fee Schedule relative value and physician time files (codes and numbers only). Public aggregates only — no patient-level data. Not medical, legal, or billing advice.

Lineage

public download (CSV/ZIP)            provenance JSON: URL, sha256, row count, pull time
        │
        ▼
bronze Parquet  (one file per source × release year, untransformed)
        │
        ▼
staging   stg_*   typed, renamed, de-duplicated; one model per source
        │
        ▼
marts     dim_* / fct_* / mart_*   joins and aggregates at a declared grain
        │
        ▼
extract   chart-safe marts only, copied to a DuckDB file the report site reads
        │
        ▼
report pages   SQL over marts only; every inline number is a query result

Built with dbt on DuckDB (memory capped at 2 GB, spilling to disk), so the full pipeline runs on a single machine. Staging, audit, and bronze tables never reach the site.

Inputs (as pulled)

Source Release / years Rows ingested
Medicare Physician & Other Practitioners — by provider × service 2022, 2023, 2024 9,755,427 / 9,660,647 / 9,781,673
Medicare Monthly Enrollment through June 2026 file 583,780 source rows → 754 state × year rows
Open Payments — General payments program years 2023, 2024 14,734,121 / 15,498,687
NPPES NPI registry September 2026 9,798,758
OIG LEIE (active exclusions) September 2026 pull 84,001
CMS ICD-10-CM Code Descriptions in Tabular Order FY2022–FY2027 95,528 / 96,795 / 97,296 / 97,584 / 98,186 / 98,403 (billable 72,750 / 73,674 / 74,044 / 74,260 / 74,719 / 74,879)
CMS PFS National Relative Value File (January release; codes and numeric fields only, AMA CPT descriptors dropped) CY2022, 2023, 2024 17,600 / 18,010 / 18,499
CMS PFS Final Rule Physician Time CY2022, 2023, 2024 9,476 / 9,484 / 9,525
CMS Medicare Part D Prescribers — by Provider (address and beneficiary demographic splits dropped) 2022, 2023, 2024 1,332,309 / 1,380,665 / 1,416,883

Row counts come from the provenance record written at download time; each record also stores the file's sha256 so a rebuild can prove it used the same input.

Model grain (Medicare narrative marts)

Mart One row per Key modeling choice
mart_medicare_spend_yoy_by_state state × service year payment = Σ(avg Medicare payment × services) at provider × HCPCS; YoY vs prior year
mart_medicare_spend_yoy_by_specialty provider type × service year dense YoY: new-in-year types count with prior $0 (yoy_status), so YoY $ sums reconcile with the state mart; volume floors live in page queries
mart_medicare_spend_yoy_by_hcpcs HCPCS code × service year same dense YoY (codes no longer billed get a $0 absent_in_year row); page-level volume floors
mart_medicare_high_growth_npi NPI (latest year vs prior) ranked by dollar increase; framed as leads, benign explanations first
mart_medicare_spend_enrollment_by_state state × year denominator = annual FFS Part B enrollment, not total Medicare
mart_medicare_spend_enrollment_correlation year state-level correlation of spend vs enrollment
mart_medicare_leie_overlay NPI × service year LEIE join on NPI only — no name matching
mart_leie_npi_coverage LEIE segment share of LEIE records that carry an NPI (bounds the overlay)
mart_open_payments_leie_overlay NPI × program year NPI-only join; transparency context, not a billing finding

Implied annual physician time (indicator input)

int_implied_minutes_npi_year, one row per rendering NPI × service year (2022–2024): 3,531,627 rows covering 1,393,382 NPIs. It is an intermediate table that feeds the scored indicator below. It is not published on its own.

  • Formula: implied minutes = Σ over the NPI's PUF lines of services × the fee schedule's physician minutes per service for that code in the same calendar year. Two bases sit side by side. Total time covers pre-service, intra-service and immediate post-service minutes, plus the post-op visits bundled into 10- and 90-day surgical globals. Intra-service time is the conservative floor.
  • No fan-out: the time file has one row per code × year (tested), and a reconciliation test recomputes every NPI-year's services and minutes from the line-level data.
  • Nothing imputed: codes with no time value (drugs and biologics, labs, ambulance, supplies, most G-codes, anesthesia) count as unmatched and add no minutes. Every row carries matched services and matched payment as shares of the total. Overall, 51.3% of services and 74.2% of payment fall on codes that have a time value. Codes listed at 0 minutes count as matched, with zero time.
  • Known overstatement, kept visible: the PUF has no modifiers, so a code that splits into professional and technical components gets full physician time on every service, even when a claim was technical-only. Those minutes sit in their own columns (11.2% of all implied minutes) so the scoring step can leave them out. Minutes from add-on codes and the facility vs. office split are separate columns as well.
  • Hours per week = implied hours ÷ 50 working weeks. The 50 is an assumption, stored on every row as a parameter.
  • Distribution (total time, all NPI-years): median 4.4 hours/week, 90th percentile 22, 99th percentile 66. Medians are low because the PUF only covers Medicare fee-for-service and suppresses small cells, so this is a floor on Medicare-billed time, not a full workload. A high value is an input for review, not evidence of wrongdoing.
  • Global surgery, kept visible: 10- and 90-day global surgery codes carry 8.4% of all implied minutes. Without modifiers, a line can be the surgery alone or the post-op care alone (for example, cataract follow-up co-managed by an optometrist) and still gets the full package time, so these minutes sit in their own columns too.

Scored indicator: implausible annual time

mart_implausible_time_indicators, one row per individual NPI. It is built on a conservative basis that drops both known overstatements, the professional/technical-split minutes and the global-surgery minutes. Organization NPIs are left out because they pool many clinicians. An NPI-year is eligible when codes with a time value carry at least half of its payment. An NPI enters the mart when its conservative implied time is above 80 hours a week in at least one year.

  • Who clears the floor: 3,035, 3,026 and 3,441 NPI-years in 2022, 2023 and 2024, out of about 1.1 million eligible NPI-years each year. That is 5,000 NPIs in all, and 1,675 of them are above the floor in all three years. Their median peak is 101 hours a week.
  • What the conservative basis removes: on the professional/technical-only exclusion, 3,402 over-80 NPI-years had cataract surgery (66984) as their largest source of minutes. On the conservative basis there are none, which is the co-management effect described above.
  • Score (0 to 1), parts kept as columns: peak total time (0.30 at 120+ hours a week, 0.15 at 100+), peak intra-service time (0.20 at 80+, 0.10 at 60+), persistence (0.25 for three years over the floor, 0.125 for two), peer extremity (0.15 at the 99.9th percentile or above within specialty and year), and time coverage (0.10 when at least 80% of payment is time-matched in every over-floor year). Bands are high at 0.60 or above, medium at 0.35 or above, and low otherwise: 2,000 high, 1,043 medium and 1,957 low.
  • Benign explanations first: fee-schedule times are typical-case survey estimates, not a measure of any claim. Clinical staff do much of the work in care-management and remote-monitoring codes, which are the largest source of minutes in the peak year for 298 NPIs. Timed therapy units can involve assistants, and they top the list for 927 NPIs. Incident-to billing and team care can also put other clinicians' work under one NPI. Each row carries its top code, those two flags and a context note. A row is an indicator for review, not a finding of wrongdoing, and no provider-level page is published from it.

Excluded but still prescribing (Part D indicator)

mart_leie_excluded_prescribing, one row per NPI × prescribing year (2022–2024). It applies the excluded-but-still-billing method to Medicare Part D. The exclusion side is unchanged: OIG LEIE records that carry an NPI, every NPI with a reinstatement on any record removed, the earliest exclusion per NPI, and a join on NPI only. Each match is kept when the NPI appears in Part D Prescribers by Provider in the exclusion year or later. Measures are claims, 30-day fills, days' supply, gross drug cost (before manufacturer rebates) and beneficiaries. CMS blanks beneficiary counts of 1 to 10, so those stay empty, never zero, with a flag on the row.

  • Who matches: 156 NPI-years covering 154 NPIs, all individual prescribers: 57, 44 and 55 in 2022, 2023 and 2024. Gross drug cost on those rows is $52.2M ($20.5M, $16.8M and $14.9M) across 237,953 claims. The beneficiary count is suppressed on 38 rows.
  • Score (0 to 1), same weights and bands as the billing indicator: NPI match 0.50, name match 0.25, state match 0.15, and 0.10 when the prescribing year is after the exclusion year. High is 0.90 or above, medium 0.65 or above, low otherwise: 125 high, 30 medium and 1 low. The name matches on 146 rows and the state on 134.
  • Benign explanations first: 152 of the 156 rows fall in the exclusion year itself. The file is annual, so that year's prescribing can come before the exclusion date. Each row carries the share of the year on or after the exclusion date (median 0.28 for same-year rows). Spread evenly over the year, about $5.4M of the $24.1M on same-year rows would fall after the exclusion date, so most of those dollars likely predate it. Only 4 rows, for 2 NPIs, come from a later year. They hold $28.1M of the cost, and both NPIs carry an OIG waiver on their exclusion record for the state they prescribe in. A waiver can let an excluded individual keep treating program beneficiaries there, so the waiver is shown on every row. Other ordinary causes: a reinstatement or waiver newer than the list pulled, pharmacies recording the wrong prescriber NPI, and refills of prescriptions written before the exclusion.
  • Overlap with billing: 23 of the 154 NPIs also appear in the excluded-but-still-billing list, and each row is flagged.
  • A row is an indicator for review, not a finding of wrongdoing, and no provider-level page is published from it.

Terminology history (ICD-10-CM, SCD Type 2)

dim_icd10cm_code_history keeps the history of every ICD-10-CM code across the six annual CMS releases loaded (FY2022–FY2027), one row per code version.

Browse and download the history by code, release, and chapter on ICD-10-CM Code History.

Column Meaning
icd10cm_history_sk surrogate key, md5 of code + valid_from_fy
code natural key (no decimal point, e.g. E119)
short_description, long_description, is_billable tracked attributes; any change opens a new version
valid_from_fy / valid_to_fy first release the version applies to (inclusive) / first release it no longer applies to (exclusive); valid_to_fy is null for current rows
valid_from_date / valid_to_date the same bounds as dates (FY N starts October 1 of N − 1)
is_current version appears in the latest loaded release
change_type why the version started: baseline, added, reinstated, description_revised, billable_changed, billable_and_description_changed
end_reason why it closed: superseded (a newer version follows in the next release) or deleted (the code is absent from the next release)
source_release the CMS release that introduced the version

Why not a dbt snapshot. A snapshot records change when the pipeline runs, so its validity dates would describe our load schedule, not the code set. CMS publishes the code set as discrete yearly releases, so history is derived release over release: each code's attributes are hashed per release and compared with its previous release; a version starts on first appearance, on a changed hash, or when a code returns after missing one or more releases. Consecutive releases with the same hash collapse into one row (a gaps-and-islands grouping).

Caveats. Codes present in FY2022, the first loaded release, are labeled baseline because their true start predates the loaded history. Only the annual October files are loaded, so mid-year April addenda don't appear as separate versions. A header code that gains child codes typically flips is_billable from true to false. For example, Y0701 (husband, perpetrator of maltreatment and neglect) was billable in FY2022 and became a header in FY2023, so it has two rows.

What changed in each release

mart_icd10cm_release_changes rolls the history up to one row per release × ICD-10-CM chapter × change event. A version that opens in a release counts under its change_type. A code that's in one release and missing from the next counts as deleted in the release it's missing from. Chapters come from a 22-row seed of the Tabular List chapter ranges, matched on each code's first three characters. Chapter 17 grew past Q99 in FY2026 and FY2027 (new QA0 and QA1 genetic-disorder categories); the seed uses the FY2027 range, and a test shows every loaded code falls in exactly one chapter.

No Results

The first four columns count every code in the file, header codes included. The last three count billable codes only, which is how the annual update announcements count: a billable code that gains children turns into a header, so it counts as no longer billable even though it's still in the file. On that basis the FY2026 and FY2027 rows match the published update tallies: 487 new, 28 invalidated and 38 revised titles for FY2026, and 190, 30 and 4 for FY2027. Those tallies come from the CMS addenda, which this pipeline doesn't load. Each year's newly billable minus no longer billable also equals the change in billable rows between releases recorded at download.

Chapters with the most added codes, FY2023–FY2027 combined:

No Results

Current row counts

No Results

Tests

309 dbt tests run with every dbt build. A failing test skips the models downstream of it, and the site extract is only refreshed from a clean build.

Type Count What it guards
not_null 185 keys and measures are populated
unique / unique_combination_of_columns 40 each mart holds exactly its declared grain (e.g. one row per state × year)
accepted_values 38 service and prescribing years limited to the loaded releases; Part D entity codes and suppression flags; NPI-match flags are true; YoY status labels; ICD-10-CM change, end and release-event labels; fee-schedule status, modifier and global-period codes
accepted_range 21 rates, shares, scores, RVUs and minutes stay within valid bounds
relationships 5 foreign keys resolve to their dimension
expression_is_true 1 Part D prescriber NPIs are 10 digits
singular tests 19 see below

Singular tests:

  • Enrollment covers every PUF year — each service year in the spend data has annual enrollment for all 50 states + DC, so per-enrollee figures never silently drop a state.
  • LEIE coverage matches the dimension — distinct NPIs in the coverage mart's "All LEIE records" segment equal the row count of the LEIE NPI dimension.
  • Open Payments overlay only holds LEIE NPIs — every NPI in the Open Payments × LEIE overlay exists in the LEIE NPI dimension.
  • National YoY totals reconcile — per service year, national payment and net YoY $ change agree within $1 across the state, specialty and HCPCS marts and the staging total (catches specialties/codes new in a year being dropped from YoY sums).
  • Staging matches what was downloaded — for every source (and every release year of the multi-year files), the staging row count equals the row count recorded in the provenance file at download time, exactly. The test reads the provenance JSON directly, so a truncated download, a short Parquet, or a staging filter that quietly drops rows fails the build. It also fails if a provenance record has no matching staging check, so a new source can't skip it.
  • ICD-10-CM history has no overlapping windows — for each code, no two versions' validity windows overlap, and every closed window ends after it starts.
  • ICD-10-CM current rows match the latest release — every code in the latest release has exactly one current version with the same descriptions and billable flag, and no code missing from that release is marked current.
  • ICD-10-CM history round-trips to the releases — expanding each version back over the releases it covers reproduces the staged releases exactly: the same code × release pairs and the same attributes. Checked that the three history tests fail: on a copy with one deleted code reopened as current and one version's end moved a year later, they returned 1, 1 and 6 failing rows.
  • Every ICD-10-CM code maps to one chapter — each code in each loaded release falls in exactly one chapter range of the seed (no gaps, no overlaps). It caught the FY2026 QA0 codes when chapter 17 still ended at Q99.
  • Release changes reconcile to the files — for each release and chapter, the change in code count since the prior release (counted from the staged files) equals codes added plus reinstated minus deleted in the release-change mart. Checked that it fails: with one chapter's FY2027 additions removed, it returned one failing row.
  • Exclusion indicator rows are in scope — three tests on the excluded-but-still-billing mart: no reinstated NPIs, no billing year before the exclusion year, and every row reconciles to the PUF × LEIE overlay with identical payment and services (and every eligible overlay row appears).
  • Excluded-but-still-prescribing indicator is consistent: score parts match their flags and sum to the total, the band matches its cut-offs, no row comes before the exclusion year, and no reinstated NPI appears. Every row also reconciles to a fresh Part D × exclusion-list join with the same claims, gross drug cost and beneficiaries, and suppressed counts stay empty rather than zero. Checked that it fails: one dropped row, one raised score, one doubled cost and one suppressed count set to zero produced four failing rows.
  • Peer-outlier indicator is consistent — score parts sum to the total score, and every flagged NPI-year meets the beneficiary and peer-group floors and outlier thresholds, with per-NPI year counts reconciling to the NPI-year rows.
  • Implied annual time is consistent: minutes are never negative, and matched services never exceed total services. Intra-service minutes never exceed total minutes, and the facility and office splits add up. Every NPI-year's services and minutes also reconcile to a line-level recomputation, which catches join fan-out and dropped rows. Checked that it fails: one dropped NPI-year, one NPI with doubled services and minutes, and one row with matched services above total each produced failing rows. It also checks that conservative hours never exceed the professional/technical-excluded hours.
  • Implausible-time indicator is consistent: score parts sum to the total, the band matches its cut-offs, every row clears the 80-hour floor on the conservative basis, and the set of NPIs equals the eligible over-floor NPIs in the intermediate table. Checked that it fails: one dropped NPI and one score raised by 0.1 each produced failing rows.
  • Publish gate — a switchable test that passes by default and can be forced to fail, to prove that a failing build never replaces the published warehouse tables.

Validation checks

  • Row counts at ingest: every download records its row count in provenance (table above), so a short or truncated file is visible before it is modeled.
  • Hashes: each input file's sha256 is stored, so a changed upstream file shows up as a provenance diff rather than an unexplained number change.
  • Ingest-to-staging row counts (automated): all 27 provenance records (PUF 2022–2024, Open Payments 2023–2024, enrollment, NPPES, LEIE, hospital and county files, ICD-10-CM FY2022–FY2027, PFS RVU and physician time CY2022–CY2024, and Part D Prescribers 2022–2024) match their staging row counts with a gap of zero rows (dbt test). Checked that it fails: a provenance count edited down by one row, plus a removed record, produced two failing rows.
  • Internal reconciliation (automated): per service year, national payment and net YoY change agree across the state, specialty and HCPCS marts and staging within $1 (dbt test).
  • External CMS benchmark (2026-10-13): national Medicare payment totals from our Provider-and-Service staging vs CMS Geography and Service National aggregates (table below). Ours as a share of CMS: 82.8% (2022), 82.6% (2023), 80.5% (2024). Gap is expected — see notes under the table.

National payment totals vs CMS Geography (National)

Payment on both sides is Avg_Mdcr_Pymt_Amt × Tot_Srvcs (Geography does not publish a total-payment column; our staging derives the same measure from the Provider-and-Service PUF). CMS side: rows where Rndrng_Prvdr_Geo_Lvl = National, summed per release year.

Year Our staging CMS Geography National Diff (CMS − ours) Ours as % of CMS
2022 $89.04B $107.57B $18.52B 82.8%
2023 $93.72B $113.48B $19.76B 82.6%
2024 $97.29B $120.80B $23.51B 80.5%

Exact cents (staging / CMS National): 2022 $89,042,856,516.32 / $107,567,787,280.15; 2023 $93,721,075,813.28 / $113,481,810,729.70; 2024 $97,290,311,766.38 / $120,801,671,723.87. Staging totals match the state YoY mart national sums (same grain as assert_national_yoy_totals_reconcile).

Why the gap is expected. CMS states that Geography (and Provider) aggregates "are not restricted to the redacted data reported in the Physician and Other Practitioners Dataset but are aggregated based on all Medicare Part B non-institutional claims (excluding DMEPOS)" (methodology). Our source is the Provider-and-Service PUF, which suppresses provider × HCPCS cells with fewer than 11 beneficiaries. So the staging sum is a coverage floor under the unredacted national total — a validation finding, not a modeling error. Charts and story figures stay on the redacted PUF; this benchmark bounds how much national spend those charts can see.

Provenance for the CMS downloads (URL, sha256, row counts, filter, derivation): provenance/cms_geography_national_benchmark.json (pulled 2026-10-13). Geography was not loaded into the warehouse for this check.

What the data can't tell you

  • The PUF is aggregated to provider × HCPCS × year and suppresses small cells (fewer than 11 beneficiaries), so low-volume providers are undercounted. Against CMS Geography National totals, our staging payment sum covers about 81–83% of the unredacted national figure (see External CMS benchmark above).
  • Fee-for-service only: Medicare Advantage spending is not in the PUF, and MA growth shrinks the FFS denominator.
  • No beneficiary-level data, diagnoses, or claim dates.
  • LEIE overlap is limited to records that carry an NPI, so the overlay is a floor, not a full count.
  • An overlap or a growth outlier is a lead for review, not evidence of wrongdoing.