CareTrace Medicare Utilization

Description

Medicare Part B (physician/supplier) utilization data at two grains: a national per-procedure rollup, and a per-provider (per-NPI) headline rollup. The two files aren't unioned because they don't share a row shape — one is a code-level aggregate, the other an entity-level aggregate — so each ships as its own table under this product.

  • Source: CMS Medicare Physician & Other Practitioners (Part B) public use files.
  • Refresh cadence: annual, aligned to the CMS Part B performance-year release.
  • Row count: 1,858,793 across 2 files (6,471 + 1,852,322).
  • Vintage: 2024 (CMS Part B performance year for both files).

Table: medicare_utilization_national

National rollup of Medicare Part B utilization by HCPCS/CPT procedure code, for the latest performance year. One row per HCPCS code.

Snowflake: HEALTHPARSE_DATA.MEDICARE_UTILIZATION.MEDICARE_UTILIZATION_NATIONAL

Column Type Description Notes
hcpcs_code VARCHAR HCPCS/CPT procedure or service code. Primary key of this table.
hcpcs_description VARCHAR Plain-text description of the procedure/service. —
n_providers BIGINT Distinct count of NPIs (providers) who billed this code in the performance year. —
total_services BIGINT Total service/unit count billed for this code, summed across all providers nationally. —
total_medicare_payment BIGINT Total Medicare-allowed payment for this code nationally, in dollars (rounded). Computed as services × average Medicare payment per service, summed.
avg_submitted_charge DOUBLE National weighted-average submitted (billed) charge per service for this code. Submitted charge is what providers billed, not what Medicare paid — expect this to run higher than the effective Medicare rate.
performance_year INTEGER The CMS performance year this rollup covers. 2024 for the current export — single year, not a time series.

Table: medicare_utilization_by_npi

Per-provider (per-NPI) headline utilization rollup — one row per NPI (both individual providers and organizational billing entities).

Snowflake: HEALTHPARSE_DATA.MEDICARE_UTILIZATION.MEDICARE_UTILIZATION_BY_NPI

Column Type Description Notes
npi VARCHAR National Provider Identifier. Primary key of this table; joins to Provider Exclusions (npi), 990 officer data has no direct NPI link.
provider_first_name / provider_last_name VARCHAR Provider's name as registered with NPPES. Null for organizational (type 2 / entity) NPIs — see provider_entity_type.
provider_credentials VARCHAR Self-reported credentials (e.g. "MD", "D.C.", "NP"). Populated for only ~61% of rows (1,123,376 of 1,852,322) — not every NPPES registration includes credentials.
primary_specialty VARCHAR Provider's primary specialty/taxonomy as billed. Populated for nearly all rows (1,852,320 of 1,852,322).
state / city VARCHAR Practice location state/city associated with this NPI's billing. —
provider_entity_type VARCHAR I (Individual, NPI type 1) or O (Organization, NPI type 2). Determines whether provider_first_name/provider_last_name are meaningful.
total_payment DOUBLE Total Medicare Part B payment received by this NPI across all billed codes in the performance year, in dollars. —
total_services DOUBLE Total service/unit count billed by this NPI. —
total_beneficiaries DOUBLE Distinct count of Medicare beneficiaries this NPI billed for. Subject to CMS's own small-cell suppression upstream — very low counts may already be masked in the source file before it reaches this table.
affiliation_count INTEGER Count of facility/hospital affiliations on record for this NPI. Sourced from a separate affiliations table; 0 is a valid value (no affiliation on record), not necessarily missing data.

Example queries (Snowflake)

-- 1. National procedure-volume ranking: top 20 HCPCS codes by total
--    Medicare payment.
SELECT "hcpcs_code", "hcpcs_description", "n_providers", "total_services", "total_medicare_payment"
FROM HEALTHPARSE_DATA.MEDICARE_UTILIZATION.MEDICARE_UTILIZATION_NATIONAL
ORDER BY "total_medicare_payment" DESC
LIMIT 20;

-- 2. Highest-volume individual providers in a specialty/state.
SELECT "npi", "provider_first_name", "provider_last_name", "provider_credentials",
       "city", "total_services", "total_beneficiaries", "total_payment"
FROM HEALTHPARSE_DATA.MEDICARE_UTILIZATION.MEDICARE_UTILIZATION_BY_NPI
WHERE "primary_specialty" = 'General Surgery'
  AND "state" = 'TX'
  AND "provider_entity_type" = 'I'
ORDER BY "total_payment" DESC
LIMIT 25;

-- 3. Highest-billing providers nationally by total Medicare payment — a
--    candidate list for outlier or program-integrity review.
SELECT "npi", "provider_last_name", "primary_specialty", "state", "total_payment", "total_services"
FROM HEALTHPARSE_DATA.MEDICARE_UTILIZATION.MEDICARE_UTILIZATION_BY_NPI
WHERE "total_payment" > 500000
ORDER BY "total_payment" DESC
LIMIT 25;