Curated datasets

Provider-type → procedure (HCPCS) profile

vlada_curated.dim_taxonomy_procedures·live·dim·static·Billing Code Intelligence

Rows

N/A

Columns

History

static (blended CMS data years 2021-2023; no time column persisted) to static (blended CMS data years 2021-2023; no time column persisted)

Card

yesgrain, traps, filters

Typed tools

What it is

1A specialty-to-procedure crosswalk: for each CMS Medicare provider type (physician specialty), the list of HCPCS/CPT procedure codes that providers of that type bill, with how many distinct providers bill each code and the total service volume.
2Derived from CMS "Medicare Physician & Other Practitioners — by Provider and Service" (Part B FFS, years 2021-2023 blended).
3Built and used as a Tier-2 "ghost rate" filter — it tells the resolution engine which procedures a given specialty plausibly performs, so a commercial rate for an NPI not in Medicare FFS can be sanity-checked against its specialty's normal procedure footprint.

Grain

Key Grain

Key	Grain
`provider_typehcpcs_cd`	1One 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. 228,605 rows = 103 provider types x their respective procedure lists (2 to 1,538 codes each). 3Both (provider_type, hcpcs_cd) AND (provider_type, rank) are independently verified minimal unique keys (each = 28,605 distinct). 4The profiler's reported (provider_type, hcpcs_cd, provider_count) is a non-minimal superset; provider_count is a measure, not part of the key.

provider_typehcpcs_cd

1One 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.
228,605 rows = 103 provider types x their respective procedure lists (2 to 1,538 codes each).
3Both (provider_type, hcpcs_cd) AND (provider_type, rank) are independently verified minimal unique keys (each = 28,605 distinct).
4The 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 read it

1"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.).
2To 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

1rank 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.
2provider_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'.
3No 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.
4provider_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.
5This 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.
6Sibling 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.

Columns (5)

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

describe_data_source(source_fqn="vlada_curated.dim_taxonomy_procedures")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.dim_taxonomy_procedures WHERE provider_type = '<value>' AND hcpcs_cd = '<value>' LIMIT 100")

Item	Value
Typed tools that read it	none; raw SQL
Identity key	`hcpcs_cd`
Join	`how_to_join(source_fqn="vlada_curated.dim_taxonomy_procedures")`
Raw door	Athena (read-only via query_substrate): vlada_curated.dim_taxonomy_procedures

This page as markdown: /docs-md/data/vlada_curated_dim_taxonomy_procedures.md · every page in one file: /llms-full.txt