# Provider-type → procedure (HCPCS) profile

`vlada_curated.dim_taxonomy_procedures` · Curated datasets · status live

Which HCPCS each provider type performs, with provider_count, total_services, and rank. Helps map a specialty to its typical procedures (and sanity-check provider↔procedure plausibility).

**Grain.** One row per (provider_type x hcpcs_cd) — i.e. one row per Medicare specialty + procedure-code pair, restricted to pairs billed by at least 5 distinct providers. 28,605 rows = 103 provider types x their respective procedure lists (2 to 1,538 codes each). Both (provider_type, hcpcs_cd) AND (provider_type, rank) are independently verified minimal unique keys (each = 28,605 distinct). The profiler's reported (provider_type, hcpcs_cd, provider_count) is a non-minimal superset; provider_count is a measure, not part of the key.

**How to use it.** "SELECT hcpcs_cd, provider_count, total_services, rank FROM vlada_curated.dim_taxonomy_procedures WHERE provider_type = 'Family Practice' ORDER BY rank LIMIT 10" answers 'what are the most common procedures Family Practice physicians bill to Medicare, and how many providers bill each' (returns 99214 then 99213 ~69K providers, etc.). To sanity-check whether a specialty plausibly performs a given code (ghost-rate filter): "SELECT provider_count FROM vlada_curated.dim_taxonomy_procedures WHERE provider_type = :pt AND hcpcs_cd = :code" — a hit means >=5 distinct Medicare providers of that type bill it; no row means rare or suppressed (treat as weak negative, not definitive).

**Traps.**
- rank is ordered by provider_count DESC, NOT by total_services. Verified: 13,824 rows violate total_services-descending order, but 0 rows violate provider_count-descending order. If you want 'highest-volume procedure for a specialty', sort by total_services yourself — do not trust rank=1 for that.
- provider_count = 5 is a CENSORSHIP FLOOR, not real data. The builder drops every (specialty, code) pair billed by <5 distinct providers (HAVING COUNT(DISTINCT npi) >= 5). So absence of a row does NOT mean 'no provider of that type performs the code' — it may just be a rare/suppressed pairing. Use this table for 'is this common', not 'is this impossible'.
- No time column at all. The output schema deliberately drops data_year and loaded_at — this is a single STATIC snapshot blending CMS years 2021, 2022, 2023 (deduped to the most-recent year per NPI+HCPCS+place in the upstream dim_provider_service before aggregation). There is no is_current, no snapshot, no history; it is fully overwritten on each rebuild.
- provider_count and total_services count DISTINCT NPIs / SUM services across the blended 2021-2023 window, not a single year. A provider billing the code in any of the three years is counted once. So these are coverage/footprint figures, not an annual rate.
- This is Medicare Part B FEE-FOR-SERVICE utilization only (no Medicare Advantage, no commercial, no Part A/inpatient). provider_type is CMS's specialty descriptor, not a NUCC/clinical taxonomy code despite the table name 'dim_taxonomy_procedures'. It is a specialty->procedure footprint map, NOT a fee schedule or a rate table — it carries no prices.
- Sibling relationship: this is a pre-aggregated rollup of vlada_curated.dim_provider_service (14.2M rows, the granular NPI x HCPCS x place-of-service table with hcpcs_desc, state, zip, and Medicare allowed/payment amounts). For descriptions, geography, NPIs, or dollar amounts, query dim_provider_service; this table only has counts and the rank.

| column | type | meaning |
|---|---|---|
| provider_type | string | CMS Medicare provider/specialty taxonomy label (the Rndrng_Prvdr_Type field from the CMS source), e.g. 'Addiction Medicine', 'Nurse Practitioner', 'Family Practice', 'Physical Therapist in Private Practice', 'Ambulance Service Provider', 'Clinic or Group Practice'. 103 distinct values. NOT a clinical NUCC taxonomy code — it is CMS's own free-text specialty descriptor (note quirky spacing like 'Allergy/ Immunology'). Never null. Partition dimension: each provider_type owns an independent, dense rank 1..N. |
| hcpcs_cd | string | The HCPCS / CPT procedure code billed by that specialty. Always exactly 5 characters: numeric CPT (e.g. '99214', '97110') and HCPCS Level II alphanumeric (e.g. 'G2087', 'G0439'). 4,895 distinct codes; a code recurs across many specialties (e.g. '99213' appears in 77 provider types), so hcpcs_cd alone is NOT a key. No description column here — join to dim_provider_service.hcpcs_desc for the procedure name. Never null. |
| provider_count | bigint | COUNT(DISTINCT npi) — number of distinct providers of that provider_type who billed that HCPCS code to Medicare FFS across 2021-2023. Range 5 to 118,245. The value 5 is a hard FLOOR, not a real minimum: the build applies HAVING COUNT(DISTINCT npi) >= 5, so any specialty/code pair billed by fewer than 5 providers is dropped entirely. This is the popularity measure rank is computed from. |
| total_services | decimal(38,0) | SUM(Tot_Srvcs) — total number of services (line-item service counts, summing beneficiaries x times performed) for that specialty/code pair across all providers and years. decimal(38,0), range 57 to 102,684,527. A volume measure. It does NOT drive rank (rank is by provider_count, not total_services — they frequently disagree). |
| rank | bigint | ROW_NUMBER() OVER (PARTITION BY provider_type ORDER BY provider_count DESC) — the popularity rank of this procedure WITHIN its provider_type, 1 = most providers bill it. Dense 1..N with NO duplicates within a type (ROW_NUMBER, ties broken arbitrarily/deterministically, not RANK/DENSE_RANK), and max(rank) per provider_type always equals that type's row count (verified 0 mismatches). Range 1..1538. It is a per-specialty ordinal, NOT a global rank and NOT meaningful across provider_types. |

Query it: `query_substrate` with `SELECT ... FROM vlada_curated.dim_taxonomy_procedures WHERE ... LIMIT 100`; read `describe_data_source("vlada_curated.dim_taxonomy_procedures")` first.