# Medicare Part B Drug Spending (by HCPCS × year)

`drug_silver.fact_drug_spending_part_b` · Drugs and formularies · status live · 3,164 rows

The CMS Medicare Part B Spending by Drug public-use file — physician- administered (Part B) drug spend per HCPCS J-code per year: total_spending, dosage units, claims, beneficiaries, avg spend per unit/claim/beneficiary, the ASP price, and unit-cost trend (YoY change + CAGR). The Part B counterpart to the Part D spending PUF.

**Grain.** One row per HCPCS drug code per data year. Minimal verified key is (hcpcs_code, data_year): 3,164 distinct combinations = 3,164 total rows (every row is is_current=true; no history/dupes). hcpcs_description, brand_name, generic_name never vary within a code, so they are attributes of the code, not part of the grain. The profiler's 9-column key is a superset padded with provenance columns that are constant across the whole table.

**How to use it.** Benchmark a Part B drug's Medicare cost and trend. Example: SELECT data_year, total_spending_usd, total_beneficiaries, avg_spending_per_dosage_unit_usd FROM drug_silver.fact_drug_spending_part_b WHERE hcpcs_code='90670' ORDER BY data_year; answers 'how did Medicare spending and per-unit price for Prevnar 13 move 2019->2023?' (spending collapses $389M->$14M as Prevnar 20 displaces it, while per-unit price rises). For a code's headline 2023 ASP price and long-run growth, read avg_dy23_asp_price_usd and cagr_avg_spending_per_dsg_unt_19_23_pct from any single row of that code (they're constant). To rank top Part B drug spend: SELECT hcpcs_code, hcpcs_description, total_spending_usd FROM ... WHERE data_year=2023 ORDER BY total_spending_usd DESC.

**Traps.**
- avg_dy23_asp_price_usd, chg_avg_spending_per_dsg_unt_22_23_usd, and cagr_avg_spending_per_dsg_unt_19_23_pct are NOT per-year values — they are single full-period / 2023-snapshot trend figures stamped IDENTICALLY onto all 5 year-rows of a code (verified on Prevnar 13: total_spending falls 389M->14M across years while these three stay 257.99 / 0.0026 / 0.041). For a per-year price use avg_spending_per_dosage_unit_usd; only read the three trend columns once per code (e.g. WHERE data_year=2023).
- The grain is (hcpcs_code, data_year) = 3,164 rows. Do NOT add hcpcs_description/brand_name/generic_name or any provenance column to a join/dedup key — descriptions/names are constant per code and every provenance column (source_id, source_url, sha256, bronze_object_key, s3_version_id, cms_vintage_sequence, parse_revision, asserted_at, vintage_tag) has exactly ONE value across the whole table. The profiler's 9-column 'verified grain' is a padded superset.
- CMS privacy suppression: rows with <11 claims/beneficiaries are dropped by CMS, so total_claims and total_beneficiaries floor at 11 and low-volume drugs are MISSING entirely (absence != zero spending). Also each year has a different drug roster (559 codes in 2019 -> 734 in 2023), so a code missing in an early year may simply not have existed/met threshold then.
- Nulls carry meaning: generic_name null (~8%) = vaccine/biologic/blood product with no listed generic; chg_22_23 null = no 2022 baseline; cagr_19_23 null = no 2019 baseline (i.e. newer drug). Do not coalesce these to 0.
- This is a single CMS Release-Year-2025 vintage (asserted_at=2025-04-30, data through 2023), loaded once: is_current is true for all rows and cms_vintage_sequence=1, so the bitemporal columns are present but inert. Don't assume multiple vintages exist. data_year/observed_period/effective_period_start/effective_period_end are 1:1 redundant — pick one (data_year) for filtering.
- Percent columns are FRACTIONS not percentages despite '_pct'/'_usd' suffixes: cagr 0.041 = 4.1%, chg_22_23 -0.045 = -4.5% (the chg column name says _usd but the values are ratios). This is Medicare-paid (allowed) amount data, NOT commercial or list/AWP price — pair with the commercial substrate to compute commercial-to-Medicare drug ratios.

| column | type | meaning |
|---|---|---|
| hcpcs_code | string | HCPCS code identifying the Part B drug/biological (e.g. '90670' Prevnar 13, '90662' Fluzone High-Dose). Part of the grain. 734 distinct codes; not all codes appear in all years (559 in 2019 growing to 734 in 2023 as new drugs enter). |
| data_year | int | Calendar year the spending/utilization is measured over. Part of the grain. 5 values: 2019-2023. Maps 1:1 to observed_period / effective_period_start (Jan 1) and effective_period_end (Dec 31). |
| hcpcs_description | string | Short CMS text label for the HCPCS code (e.g. 'Pneumococcal vaccine, 13-valent'). One description per code (verified: zero codes have >1 description); functionally a code attribute, never null. |
| brand_name | string | CMS-listed brand/trade name(s) of products billed under the code (e.g. 'Prevnar 13', 'Fluzone High-Dose 2017-2018*'). Constant per code across years. Never null. A trailing '*' is a CMS convention flagging the drug may also be billed under other/multiple codes or has noted billing caveats. |
| generic_name | string | Generic/active-ingredient name (e.g. 'Pneumoc 13-Val Conj-Dip Crm/PF'). Constant per code. NULLABLE — ~8% null (536 of 559 populated in 2019, 684 of 734 in 2023); null typically for vaccines/biologics/blood products where CMS lists no single generic. A null here means 'no generic name on file,' not a data error. |
| total_spending_usd | double | Total Medicare Part B spending (allowed amount: program + beneficiary cost share) for the drug in that year, USD. Never null. Wide range $3.26 to $5.43B. THE primary measure. |
| total_dosage_units | double | Total dosage units administered (HCPCS-defined billing units; the unit differs by drug, so do NOT compare units across drugs). Never null. Range 11 to 921M. |
| total_claims | int | Total number of Part B claims for the drug that year. Never null. Minimum observed is 11 because CMS suppresses cells with <11 claims (privacy floor). |
| total_beneficiaries | int | Distinct Medicare beneficiaries who received the drug that year. Never null. Also floored at 11 by CMS suppression. Usually <= total_claims (a beneficiary can have multiple claims). |
| avg_spending_per_dosage_unit_usd | double | total_spending_usd / total_dosage_units — Medicare's effective per-unit price that year (the key Part-B price benchmark). Range $0.011 to $485,160. |
| avg_spending_per_claim_usd | double | total_spending_usd / total_claims for the year. Derived average. |
| avg_spending_per_beneficiary_usd | double | total_spending_usd / total_beneficiaries for the year. Derived average. |
| outlier_flag | boolean | Boolean CMS flag marking the drug as a spending/price outlier (per CMS PUF methodology) for that year-row. Never null; ~9-10% true per year (49-64 of the rows). Same code can be flagged in some years and not others. |
| avg_dy23_asp_price_usd | double | TRAP: the drug's average 2023 (DY2023) ASP-based payment price, USD — a SINGLE 2023-snapshot value STAMPED ONTO EVERY year-row for that code (verified identical across all 5 years per code). It is NOT the per-year price; for the per-year price use avg_spending_per_dosage_unit_usd. Range $0.011 to $494,962. |
| chg_avg_spending_per_dsg_unt_22_23_usd | double | TRAP: percent change in average spending per dosage unit from 2022 to 2023 (a fraction, e.g. -0.045 = -4.5%). A full-period trend value CONSTANT across all year-rows of a code, not a year-specific delta. NULLABLE when the drug lacks a 2022 or 2023 baseline (e.g. 78 of 734 codes null in 2023) — null means new/insufficient-history drug. |
| cagr_avg_spending_per_dsg_unt_19_23_pct | double | TRAP: compound annual growth rate of average spending per dosage unit 2019->2023 (fraction, e.g. 0.041 = 4.1%). Single full-period value CONSTANT across all year-rows of a code. NULLABLE when no 2019 baseline (new drugs). Range -0.63 to 13024 (extreme positive values reflect drugs going from near-zero to high price). |
| observed_period | date | Date = Jan 1 of data_year (the measurement period anchor). 5 distinct values, redundant with data_year. |
| asserted_at | date | Date CMS asserted/published this vintage = 2025-04-30 for ALL rows (single value). Bitemporal 'assertion time' marker, not row-specific. |
| effective_period_start | date | Jan 1 of data_year. Used as the table's temporal column. Redundant with data_year/observed_period. |
| effective_period_end | date | Dec 31 of data_year (5 distinct values). Closes the annual measurement window. |
| cms_vintage_sequence | int | Integer CMS-release vintage sequence; constant = 1 for all rows (only one vintage ingested). Would increment if a newer CMS release were layered in. |
| parse_revision | int | Internal parser revision number; constant = 1 across the table. |
| parser_artifact_hash | string | SHA256 of the parser artifact that produced these rows; single value for the whole table (governance/lineage). |
| source_url | string | CMS download URL of the source CSV; single value: the RY25 P06 V10 DYT23 HCPCS file dated 250430 on data.cms.gov. |
| sha256_content_hash | string | SHA256 of the source file content; single value (one source file). Provenance/integrity. |
| source_id | string | Logical source identifier; constant = 'medicare_part_b_spending_puf' for every row. |
| is_current | boolean | Bitemporal current-version flag. 100% true here (3,164/3,164) — only one vintage loaded, so there are no superseded rows. Filtering is_current=true is the correct convention but currently a no-op. |
| bronze_object_key | string | S3 key of the bronze (raw landed) object this silver row derives from; single value (drug/bronze/medicare_part_b_spending_puf/2025/...). Lineage. |
| s3_version_id | string | S3 object version id of the bronze file; single value. Lineage. |
| vintage_tag | string | Vintage label; literal string 'none' for all rows (no special vintage tagging applied). |
| drug_source_version_id | string | Drug source version id — 100% NULL in this table (0 non-null of 3,164). Placeholder column from the shared drug-silver schema, unused for this PUF. |

Tools that read it: ira_mfp_exposure

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