# Medicare Part D Spending by Drug (PUF)

`drug_silver.fact_drug_spending_part_d` · Drugs and formularies · status live

The Medicare Part D Spending by Drug public-use file — annual aggregate Part D spend, claim counts, beneficiary counts, and unit cost trend per drug. The macro drug-spend lens (which drugs drive Part D dollars and how unit costs move year over year), complementing the NDC-level pricing tables.

**Grain.** One row per (brand_name, generic_name, manufacturer_name, data_year) — i.e. one drug (brand+generic) per manufacturer-slice per calendar year, where manufacturer_name='Overall' is itself a row holding the all-manufacturers aggregate. Verified: COUNT(DISTINCT(brand_name,generic_name,manufacturer_name,data_year)) = 60,695 = exact row_count. Every (brand,generic,year) group has exactly one 'Overall' row (16,368 total) plus zero-or-more specific-manufacturer rows (44,327 total). Dropping any one of the four columns breaks uniqueness.

**How to use it.** Top 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'. For a manufacturer-competition view drop the Overall filter and use manufacturer_name<>'Overall'. For 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.**
- DOUBLE-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.
- DOUBLE-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.
- SUPPRESSION 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.
- REDUNDANT/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.
- brand_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.

| column | type | meaning |
|---|---|---|
| brand_name | string | Drug brand / trade name as reported by CMS (e.g. 'Abilify', 'Ziagen', '1st Tier Unifine Pentips'). 3,474 distinct. Part of the grain. The same generic can appear under several brands (brand 'Abacavir' vs brand 'Ziagen', both generic_name 'Abacavir Sulfate'), which is exactly why brand_name is needed in the key. Includes diabetic supplies (pen needles), not only pharmaceuticals. |
| generic_name | string | Active-ingredient / generic name (e.g. 'Abacavir Sulfate', 'Pen Needle, Diabetic'). 1,938 distinct. Part of the grain. Multiple brands share a generic; multiple generics can share a brand string, so neither brand nor generic alone is unique. |
| manufacturer_name | string | Drug manufacturer/labeler name (962 distinct INCLUDING the sentinel 'Overall'). THE KEY DIMENSION: value 'Overall' (16,368 rows) is the all-manufacturers AGGREGATE for that brand+generic+year; any other value (44,327 rows) is one specific manufacturer's slice. Names are CMS-truncated (e.g. 'Aurobindo Pharm', 'Camber Pharmace'). Part of the grain. |
| data_year | int | Calendar year the spending pertains to. Int, 5 values 2019-2023. Part of the grain. Rows grow by year (2019=10,211 ... 2023=14,309) as the drug universe expands. This is the real time axis (observed_period/effective_period_* are all derived from it). |
| total_manufacturers | int | Count of distinct manufacturers contributing to the row. On an 'Overall' row = number of manufacturers that made the drug that year (1-45, e.g. Abacavir 2019 = 5). On a specific-manufacturer row it is always 1. Never null. |
| total_spending_usd | double | Aggregate gross Medicare Part D spending in USD for that brand+generic+manufacturer+year (sum of ingredient cost + dispensing/sales tax across all claims; before manufacturer rebates). Double. NULL for 217 rows (CMS privacy suppression, <11 claims). On 'Overall' rows ≈ sum of the specific-manufacturer rows. |
| total_dosage_units | double | Total dosage units dispensed (pills, mL, grams, etc. — unit varies by drug, not standardized across rows). Double. NULL for the same 217 suppressed rows. Denominator for the weighted per-dosage-unit average. |
| total_claims | int | Number of Part D claims (prescription fills, including refills) for the row. Int. NULL for 217 suppressed rows. CMS suppresses when <11 claims. |
| total_beneficiaries | int | Number of distinct Medicare beneficiaries who received the drug. Int. NULL for 1,959 rows — suppressed MORE aggressively than claims/spending (CMS hides beneficiary counts under 11), so beneficiary nulls strictly exceed the 217 spending nulls. |
| avg_spend_per_dosage_unit_weighted_usd | double | CMS-computed WEIGHTED average spending per dosage unit (weighted across claims; not a simple spending/units ratio). Double. NULL for 217 suppressed rows. This is the metric the change/CAGR trend columns track. |
| avg_spend_per_claim_usd | double | Average spending per claim. Double. Verified = total_spending_usd / total_claims exactly (precomputed but trivially derivable). NULL for the 217 suppressed rows. |
| avg_spend_per_beneficiary_usd | double | Average spending per beneficiary = total_spending_usd / total_beneficiaries. Double. NULL for 2,176 rows — inherits the heavier beneficiary suppression (more nulls than the per-claim metric). |
| outlier_flag | boolean | Boolean CMS flag marking the drug as an outlier on year-over-year spend-per-dosage-unit change. True=5,911, False=54,535, NULL=249. NULL = 217 fully-suppressed rows + 32 rows that have spending but no outlier determination. |
| chg_avg_spend_per_dsg_unt_22_23_usd | double | FIXED 2022->2023 change in weighted avg spend per dosage unit. GOTCHA: this is a single per-DRUG trend value DENORMALIZED onto ALL FIVE year-rows of that brand+generic+manufacturer — identical on the 2019,2020,2021,2022 and 2023 rows. It is NOT a per-year delta. 1,679 nulls (drugs lacking both endpoints). |
| cagr_avg_spend_per_dsg_unt_19_23_pct | double | FIXED 2019->2023 compound annual growth rate of weighted avg spend per dosage unit (fraction, e.g. 0.0079 = 0.79%/yr). Same denormalization trap as the change column: one per-drug value repeated on every year-row. 1,184 nulls (drugs not present across the full 2019-2023 span). |
| observed_period | date | Date = Jan 1 of data_year. Fully derived from data_year (redundant). Provenance/temporal bookkeeping, not an independent dimension. |
| asserted_at | date | Date the data was asserted/published by CMS — constant 2025-05-01 for every row (the RY2025 release date). Not a per-row event date. |
| effective_period_start | date | Date = Jan 1 of data_year (the profiler's chosen time column). Redundant with data_year/observed_period. |
| effective_period_end | date | Date = Dec 31 of data_year. Marks the row as covering a full calendar year. Redundant with data_year. |
| cms_vintage_sequence | int | Ingest vintage sequence number. Constant = 1 (only one vintage of this PUF is loaded). Would increment if a newer CMS release were ingested. |
| parse_revision | int | Parser revision number. Constant = 1. Provenance bookkeeping. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the row. Constant across all rows (single parse run). Provenance/lineage only. |
| source_url | string | CMS download URL of the source CSV. Constant = the RY2025 P04 V10 DY23 By-Generic-Manufacturer (BGM) file (DSD_PTD_RY25_P04_V10_DY23_BGM.csv). Confirms the whole table comes from ONE wide CSV unpivoted into long form. |
| sha256_content_hash | string | SHA-256 of the raw source file content. Constant (one source file). Used for content-addressed dedup/integrity. |
| source_id | string | Logical source identifier. Constant = 'medicare_part_d_spending_puf'. Identifies the dataset family. |
| is_current | boolean | Boolean current-vintage flag. 100% True (60,695/60,695). Only one vintage is loaded so it never filters anything today, but it is the dedup hook for when a future CMS release supersedes this one — filter is_current=true to stay forward-safe. |
| bronze_object_key | string | S3 key of the bronze (raw) object backing the row. Constant. Lineage pointer to bronze storage. |
| s3_version_id | string | S3 object version id of the bronze file. Constant (single file/version). Lineage/integrity. |
| vintage_tag | string | Vintage label. Constant literal string 'none' (not NULL) for every row. Currently carries no information. |
| drug_source_version_id | string | Intended drug-source version identifier — 100% NULL (60,695/60,695). A dead/unpopulated provenance column for this table; do not rely on it. |

Tools that read it: ira_mfp_exposure, recall_spend_window

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