Medicare Part D Spending by Drug (PUF)

What it is

1. 1CMS Medicare Part D Drug Spending PUF (Release Year 2025, by Brand/Generic/Manufacturer): annual gross Medicare Part D spending, utilization (claims, dosage units, beneficiaries) and CMS-computed average-cost + trend metrics for each drug, broken out both as an all-manufacturers 'Overall' total and per individual manufacturer.
2. 2It is the Part D drug-cost benchmark layer of the substrate (sibling to the Part B / ASP and NADAC drug tables).

Grain

How to read it

1. 1Top Medicare Part D drugs by 2023 spend, all-manufacturer view: SELECT brand_name, generic_name, total_spending_usd, total_claims, avg_spend_per_claim_usd, total_beneficiaries FROM drug_silver.fact_drug_spending_part_d WHERE data_year=2023 AND manufacturer_name='Overall' AND is_current ORDER BY total_spending_usd DESC LIMIT 20; — answers 'which drugs drive Medicare Part D cost and what does Medicare pay per fill/beneficiary'.
2. 2For a manufacturer-competition view drop the Overall filter and use manufacturer_name<>'Overall'.
3. 3For multi-year cost trend, filter manufacturer_name='Overall' and pivot total_spending_usd / avg_spend_per_dosage_unit_weighted_usd across data_year (do NOT use the denormalized chg/cagr columns per-year).

Traps

1. 1DOUBLE-COUNTING TRAP #1 (Overall vs manufacturer): manufacturer_name='Overall' rows are aggregates that already sum the specific-manufacturer rows for the same brand+generic+year. NEVER SUM across manufacturers without filtering — total Part D spend for a drug-year = the single Overall row, OR the sum of the non-Overall rows, but never both together. 16,368 Overall + 44,327 specific = 60,695.
2. 2DOUBLE-COUNTING TRAP #2 (denormalized trend columns): chg_avg_spend_per_dsg_unt_22_23_usd and cagr_avg_spend_per_dsg_unt_19_23_pct are FIXED per-drug trend values copied onto ALL FIVE year-rows (2019..2023) of a drug — verified identical across years. They are NOT per-year deltas. Take them from one row (e.g. the 2023 row); summing or averaging them across data_year multiplies them ~5x.
3. 3SUPPRESSION NULLS carry meaning (CMS privacy, <11 threshold): total_spending/claims/dosage_units are NULL on 217 rows; total_beneficiaries is NULL on 1,959 rows and avg_spend_per_beneficiary on 2,176 — beneficiary-based fields are suppressed MORE than claims-based ones. Treat NULL as 'suppressed small cell', not zero; COUNT/SUM silently drop them.
4. 4REDUNDANT/DEAD provenance columns: observed_period, effective_period_start (=Jan 1 of data_year), effective_period_end (=Dec 31) and asserted_at (=2025-05-01) are all derived constants — use data_year as the time axis. drug_source_version_id is 100% NULL, vintage_tag is the literal 'none', and is_current is 100% True; all other provenance columns (source_url, sha256, s3_version_id, etc.) are single constants because the entire table is one wide CMS CSV (DSD_PTD_RY25...DY23_BGM.csv) unpivoted into long form.
5. 5brand_name is essential to the key and NOT redundant with generic_name: the same generic_name appears under multiple brands (e.g. generic 'Abacavir Sulfate' under brand 'Abacavir' AND brand 'Ziagen', each with its own Overall row), and avg_spend_per_dosage_unit_weighted_usd is CMS's claim-WEIGHTED average (not total_spending/total_dosage_units), so don't recompute it; avg_spend_per_claim_usd, however, equals total_spending_usd/total_claims exactly.

Columns (30)

Query it

describe_data_source(source_fqn="drug_silver.fact_drug_spending_part_d")     # the card, live
query_substrate(sql="SELECT * FROM drug_silver.fact_drug_spending_part_d WHERE brand_name = '<value>' AND generic_name = '<value>' AND manufacturer_name = '<value>' AND data_year = '<value>' LIMIT 100")