PBP partd tier (cleaned view)

What it is

1. 1A semantic view over CMS Medicare Advantage Part D Plan Benefit Package (PBP) data that flattens each Part D plan's prescription-drug formulary TIER structure (Preferred Generic, Generic, Preferred Brand, Non-Preferred, Specialty, etc.) and the member cost-share (copay/coinsurance) charged at each tier across pharmacy channels.
2. 2It is the drug-benefit cost-design layer of the PBP, used to compare what beneficiaries pay out-of-pocket by drug tier across plans, regions, channels, and years.

Grain

How to read it

1. 1Compare what beneficiaries pay by drug tier across plans for the current year.
2. 2Example: SELECT contract_id, plan_id, tier_id, tier_label_list, retail_standard_copay_1m, ira_retail_standard_copay_1m, mail_order_standard_copay_3m FROM medicare_semantic.vw_pbp_partd_tier WHERE report_year=2026 AND quarter=1 AND tier_type_id='1' ORDER BY contract_id, plan_id, CAST(tier_id AS integer).
3. 3This returns each plan's formulary ladder (Preferred Generic -> Specialty) with the 30-day retail copay (both the standard and the IRA-standard-benefit figure) and the 90-day mail-order copay, answering 'how much does a member pay for a generic vs a specialty drug under this plan, at retail vs mail order, this year'.
4. 4Always filter to one quarter per year (quarters are duplicate snapshots) and read cost-share from the retail_/mail_/oon_/ltc_/ira_ columns, never from the always-null generic copay/coinsurance fields.

Traps

1. 1GRAIN TRAP: tier_id alone is NOT unique within a plan-snapshot. tier_type_id is a required part of the key (1=standard scenario, 3=second scenario; 57,422 plan-periods carry BOTH). The true minimal key is the 7 columns contract_id+plan_id+segment_id+report_year+quarter+tier_id+tier_type_id (=792,027 rows exactly). version is NOT in the key (1 version per key, functionally determined). The profiler's reported key wrongly included the all-NULL limit_period_code / benefit_amount_period_code / confidence_tier columns — those are constants, not key columns.
2. 2QUARTER IS A SNAPSHOT/VINTAGE AXIS, NOT cost variation: the same plan-year tier reappears with IDENTICAL cost-share in each quarterly PUF re-release (verified). To get one row per plan-year tier, pick the latest quarter per report_year (there is no is_current flag — dedup by MAX(quarter) within report_year, or by MAX(version)). Pre-2018 and 2021 only have quarter=1.
3. 3MOST GENERIC BENEFIT COLUMNS ARE DEAD HERE: benefit_amount, benefit_amount_pmpm, coinsurance_pct, cost_share_string, limit_*, auth_required, referral_required are ALWAYS NULL, and network_type/offered_status/benefit_category/numeric_amount_observability/null_amount_reason/confidence_tier are CONSTANT (inherited from a shared multi-benefit view schema). The real cost-share lives ONLY in the channel-specific families: retail_*/mail_order_*/oon_*/ltc_* (non-IRA) and ira_* (IRA). The governance flag 'offered_without_numeric_amounts' refers only to the dead generic columns, NOT to absence of cost data.
4. 4IRA vs NON-IRA COPAY SPLIT: ira_* copay columns are populated ONLY from report_year 2024 onward (0 before) and reflect the IRA-redesigned standard benefit / $2,000 OOP-cap era; they can differ materially from the matching non-IRA column (e.g. Non-Preferred tier non-IRA $99 vs IRA $35). copay_amount mirrors the NON-IRA retail_standard_copay_1m only, so do not use copay_amount as the IRA figure. For 2010-2015 only the coinsurance columns (oon_pharmacy_coins_1m, etc.) carry data — copay columns start ~2016.
5. 5ALL tier_* CODE COLUMNS ARE RAW CMS PBP ENUMERATIONS (tier_benefit_type, tier_cost_share_structure, tier_drug_type, tier_includes, tier_location_*). They are 'source_faithful' (not decoded). Decode them with the LMI 'PBP Service Category Descriptions' PDF linked in lmi_authorities. tier_label_list is the only human-readable tier name, and it can vary by plan at the same tier_id position. This view is the Part D DRUG-tier slice only (source_pbp_section=MRX_TIER); medical-benefit categories live in sibling vw_pbp_* views.

Columns (178)

Query it

describe_data_source(source_fqn="medicare_semantic.vw_pbp_partd_tier")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.vw_pbp_partd_tier WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' AND tier_id = '<value>' AND tier_type_id = '<value>' LIMIT 100")