MA pbp mrx

What it is

1. 1The plan-level Part D (Medicare prescription-drug) benefit record from CMS's Plan Benefit Package (PBP) filings — Section MRx — describing each Medicare Advantage / PDP plan's drug coverage design: whether it offers Part D, basic vs enhanced-alternative benefit type, deductible, formulary/tier model, coverage-gap and IRA-era cost-share structures, utilization-management flags, and rewards/incentive programs.
2. 2It is the canonical 'what is this plan's drug benefit shape' table for Medicare-Advantage cost analysis, and the parent of the per-tier pbp_mrx_tier table.

Grain

How to read it

1. 1Pick ONE snapshot, then read plan-level Part D design.
2. 2Example: SELECT bid_id, mrx_drug_ben_yn, mrx_benefit_type, mrx_alt_ded_amount FROM medicare_silver.pbp_mrx WHERE report_year=2026 AND quarter=1 AND cms_vintage_sequence=1 AND mrx_drug_ben_yn='1' — returns, for every 2026 Medicare plan that offers Part D, its benefit type (4=Enhanced Alternative) and Part D deductible (e.g.
3. 3615.0 = the CY2026 standard).
4. 4To analyze actual generic/brand copays per formulary tier and pharmacy channel, join bid_id+snapshot to the sibling table medicare_silver.pbp_mrx_tier (this table does NOT carry usable tier copays).
5. 5To trend a plan over years, group by report_year keeping one release per year (e.g.
6. 6MIN(quarter)/MIN(cms_vintage_sequence)).

Traps

1. 1NOT a tier/copay table. This is the PLAN-LEVEL Part D record (~8K plans/year). The per-tier × per-pharmacy generic/brand copay & coinsurance amounts live in the SIBLING table pbp_mrx_tier (~27-33K rows = one per plan×tier). The mrx_gen_* copay matrix here is ~empty (mrx_gen_in_1m: 632 non-null of 208,537). NEVER read 'the plan's drug copay' from this table, and never sum tier copays here.
2. 2Grain needs FOUR columns, not the obvious ones. Minimal verified key = (bid_id, report_year, quarter, cms_vintage_sequence) [= contract_id+plan_id+segment_id+report_year+quarter+cms_vintage_sequence], 208,537 distinct = 208,537 rows. Dropping cms_vintage_sequence creates 16,307 dup rows (2021 Q1 & 2022 Q1 were each loaded at two vintages from two CMS ZIPs); dropping quarter creates 8,336 dups. bid_id is unique only WITHIN one snapshot (8,326 in 2025 Q1).
3. 3The profiler's reported grain is WRONG/superset — it leaned on mrx_grp_id, mrx_grp_type_id, mrx_v_bid_attestation, etc. mrx_grp_id and mrx_grp_type_id are 100% NULL across all 208,537 rows (this table is never exploded to benefit-group rows), so those columns spuriously appeared to 'add uniqueness.' Ignore them as keys.
4. 4No is_current column and NO dedup applied — every quarterly CMS release is STACKED. There are 30 snapshots (report_year × quarter × cms_vintage_sequence). To get 'the current plan benefits' you MUST filter, e.g. report_year=2026 AND quarter=1 AND cms_vintage_sequence=1 (or pick MAX vintage per year). Querying without a snapshot filter double/triple-counts plans and mixes 16 years.
5. 5Massive year-dependent column sparsity driven by documented PBP layout churn. Of 356 columns most are NULL for any given year: CY2022-Q2 moved 42 cols OUT of pbp_mrx into siblings (pbp_vbid, pbp_mrx_dmodel, pbp_mrx_ssm); CY2024 'New PBP' redesign reshaped Section D; CY2026 discontinued VBID and removed the coverage-gap (IRA). Examples observed: mrx_formulary_yn/mrx_basic_plan_yn/mrx_alt_drug_type 100% NULL in 2026; mrx_quantity_limits only 2010-2021; mrx_form_tier_model_5 only 2013-2016. Always check non-null coverage for your target year before trusting a column.
6. 6Report year 2020 is entirely MISSING (data jumps 2019→2021). This is the documented CY2020 CMS filename quirk (the file shipped as 'pbp_mrx 2.txt' with the canonical 'pbp_mrx.txt' absent), so 2020 was not ingested. Don't treat the gap as data loss for other years.
7. 7mrx_drug_ben_yn='2' (~12% of plans) means the plan offers NO Part D benefit — those rows are shells with all cost/deductible fields NULL (verified: 0 such rows have mrx_alt_ded_amount set). Filter to mrx_drug_ben_yn='1' before analyzing drug cost-share.
8. 8Identifier columns are zero-padded strings, not numbers: plan_id='001', segment_id='0', pbp_a_plan_type='01', orgtype='08'. Join/compare as strings. version, report_year, quarter, mrx_*_amt are doubles. Missing values are true SQL NULL (no '.' or '' sentinel leakage — verified).

Columns (380)

Query it

describe_data_source(source_fqn="medicare_silver.pbp_mrx")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.pbp_mrx WHERE bid_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' AND cms_vintage_sequence = '<value>' LIMIT 100")