# PBP partd tier (cleaned view)

`medicare_semantic.vw_pbp_partd_tier` · Medicare Advantage, Part D, Medicaid · status partial

Cleaned source-faithful PBP partd tier rows: era-normalized, CMS codes decoded, with offered_status/numeric_amount_observability/null_amount_reason/annualization_policy/combined_limit_policy/confidence_tier. The agent reasoning surface for

**Grain.** One row per formulary tier position, per plan-segment, per quarterly PBP snapshot. The MINIMAL verified-unique key is contract_id + plan_id + segment_id + report_year + quarter + tier_id + tier_type_id. That 7-column combination yields exactly 792,027 distinct values = 792,027 total rows (verified). Dropping tier_type_id collapses to 675,843 (a plan can carry two parallel tier structures, type 1 and type 3, in the same snapshot — 57,422 plan-periods do), so tier_type_id is genuinely required. version is NOT part of the key: max distinct versions per grain key = 1 across all 792,027 keys, so it is functionally determined provenance, not a grain column.

**How to use it.** Compare what beneficiaries pay by drug tier across plans for the current year. Example: 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). This 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'. Always 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.**
- GRAIN 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.
- QUARTER 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.
- MOST 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.
- IRA 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.
- ALL 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.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract ID (e.g. H0022). 1,427 distinct. Identifies the MA/PDP organization contract; combine with plan_id+segment_id for the plan instance. |
| plan_id | string | Plan ID within the contract (e.g. '001'). 3-char string, NOT globally unique (645 distinct values reused across contracts). Only meaningful with contract_id. |
| segment_id | string | Plan benefit segment (regional sub-variant). Mostly '0' (90% of rows = 716,236); '1'-'N' for plans with geographic segments that vary benefits. Part of the plan identity. |
| version | double | CMS PBP file submission version for this snapshot (integer 1-26). Provenance only — functionally determined by the grain key (max 1 version per key). Multiple versions appear within a year because the PBP is resubmitted; each row carries the version of its snapshot. NOT a grain column. |
| report_year | double | PBP contract/benefit year (double, 2010-2026). THE primary history dimension. This is the plan year the benefits apply to, not a publish date. |
| quarter | double | Quarter of the PBP PUF release that this snapshot came from (1-4). Part of the grain: the same plan-year/tier reappears in each quarterly PUF re-release with identical data (verified: H0028/004/2019 tiers identical across Q1-Q4). Pre-2018 and 2021 carry only Q1 (annual); 2018-2020/2022-2024 carry multiple quarters. NOT calendar-quarter cost variation — it is a snapshot/vintage axis. |
| benefit_category | string | Constant = 'partd_tier' for every row. A discriminator inherited from a shared multi-benefit view schema; here it always marks Part D drug-tier rows. |
| cost_share_string | string | Always NULL in this view. Generic free-text cost-share field from the shared schema, not populated for Part D tiers. |
| copay_amount | double | Partially populated generic copay mirror (205,321 non-null). Equals retail_standard_copay_1m exactly (the non-IRA 30-day retail standard copay), populated only when that value exists. Convenience field — prefer the canonical retail_*/mail_*/oon_*/ltc_* columns for completeness; does NOT reflect the IRA value. |
| coinsurance_pct | double | Always NULL (0 non-null). Generic coinsurance field from the shared schema; for Part D, channel-specific coinsurance lives in oon_pharmacy_coins_1m / ltc_partner_coins_1m instead. |
| limit_text | string | Always NULL. Generic benefit-limit free text from the shared schema; unused for Part D tiers. |
| auth_required | boolean | Always NULL. Prior-authorization flag from the shared medical-benefit schema; not applicable/unpopulated at the Part D tier grain. |
| referral_required | boolean | Always NULL. Referral flag from the shared schema; unused for Part D tiers. |
| network_type | varchar(10) | Constant = 'in_network' for all rows. Inherited from the shared schema; Part D pharmacy channel is instead carried by the retail/mail/oon/ltc column families and tier_location_* codes, not this field. |
| max_oop_applies | boolean | Boolean, observed always true in samples. Whether the tier's cost-share counts toward the plan max-out-of-pocket. Mostly a constant placeholder from the shared schema. |
| limit_quantity | double | Always NULL. Generic quantity-limit numeric from the shared schema; unused for Part D tiers. |
| limit_unit | string | Always NULL. Generic unit-of-limit from the shared schema; unused here. |
| limit_period_code | string | Always NULL here (appears in profiler's candidate key only because NULL is constant). Generic limit-period code from the shared schema; unused for Part D tiers — do NOT treat as a key column. |
| limit_period_label | string | Always NULL. Human label for limit_period_code; unused for Part D tiers. |
| limit_quantity_per_year | double | Always NULL. Annualized quantity limit from the shared schema; unused here. |
| benefit_amount | double | Always NULL (0 non-null). Generic benefit-dollar field from the shared schema; Part D tier dollars are the copay columns, not this. |
| benefit_amount_kind | string | Always NULL. Type tag for benefit_amount; unused for Part D tiers. |
| benefit_amount_period_code | string | Always NULL (constant, hence in profiler candidate key spuriously). Period code for benefit_amount; unused — not a real key column. |
| benefit_amount_period_label | string | Always NULL. Human label for benefit_amount_period_code; unused. |
| benefit_amount_annualized | double | Always NULL. Annualized benefit dollars; unused for Part D tiers. |
| benefit_amount_pmpm | double | Always NULL (0 non-null). Per-member-per-month benefit amount from the shared schema; unused here. |
| normalization_status | string | Always NULL for this view. Would mark whether the shared-schema benefit normalization ran; it does not run for Part D tiers (the data is source-faithful raw codes). |
| normalization_basis | string | Always NULL. The basis used by benefit normalization; not applicable here. |
| aggregation_policy | string | Always NULL. How multiple source rows were aggregated; not applicable at this grain. |
| offered_status | varchar(15) | Constant = 'offered'. Every row represents an offered Part D tier; the view does not carry not-offered rows. |
| numeric_amount_observability | varchar(31) | Constant = 'offered_without_numeric_amounts'. A governance flag stating the GENERIC copay/coinsurance/benefit_amount columns lack numeric values; it does NOT mean cost-share is absent — the numbers live in the channel-specific retail/mail/oon/ltc/ira copay columns. |
| null_amount_reason | varchar(30) | Constant = 'unknown_requires_crosswalk'. Explains why the generic amount columns are null (the PBP encodes tier cost-share in channel-specific fields that require the LMI crosswalk to interpret). |
| annualization_policy | varchar(17) | Constant = 'not_numeric'. Governance flag; no annualization performed because the generic numeric fields are empty. |
| combined_limit_policy | varchar(29) | Constant = 'none_detected' in samples. Governance flag for combined in/out-of-network limit handling; not engaged for Part D tiers. |
| consumer_document_reconciliation_status | varchar(11) | Constant = 'not_checked'. Whether the tier was reconciled against a consumer-facing document (e.g. SB/EOC); not done for this view. |
| confidence_tier | varchar(23) | Constant = 'source_faithful'. Data-confidence label: values are the raw CMS PBP codes/amounts as filed, not modeled or imputed. |
| balance_ltc_pharmacy_copay_1m | double |  |
| balance_ltc_pharmacy_copay_daily | double |  |
| balance_mail_order_preferred_copay_1m | double |  |
| balance_mail_order_preferred_copay_2m | double |  |
| balance_mail_order_preferred_copay_3m | double |  |
| balance_mail_order_preferred_copay_daily | double |  |
| balance_mail_order_standard_copay_1m | double |  |
| balance_mail_order_standard_copay_2m | double |  |
| balance_mail_order_standard_copay_3m | double |  |
| balance_mail_order_standard_copay_daily | double |  |
| balance_model_status | string |  |
| balance_oon_pharmacy_copay_1m | double |  |
| balance_retail_preferred_copay_1m | double |  |
| balance_retail_preferred_copay_2m | double |  |
| balance_retail_preferred_copay_3m | double |  |
| balance_retail_preferred_copay_daily | double |  |
| balance_retail_standard_copay_1m | double |  |
| balance_retail_standard_copay_2m | double |  |
| balance_retail_standard_copay_3m | double |  |
| balance_retail_standard_copay_daily | double |  |
| extended_day_supply_all_drugs | boolean |  |
| extended_supply_first_fill_limited | boolean |  |
| insulin_cost_share_source | string |  |
| ira_columns_scope | varchar(33) |  |
| ira_insulin_cost_share_form | string |  |
| ira_insulin_cost_share_string | string |  |
| ira_ltc_partner_copay_1m | double | 30-day long-term-care pharmacy copay under the IRA standard benefit. 2024+ only. |
| ira_mail_order_preferred_coins_1m | double |  |
| ira_mail_order_preferred_coins_2m | double |  |
| ira_mail_order_preferred_coins_3m | double |  |
| ira_mail_order_preferred_copay_1m | double |  |
| ira_mail_order_preferred_copay_2m | double |  |
| ira_mail_order_preferred_copay_3m | double |  |
| ira_mail_order_standard_coins_1m | double |  |
| ira_mail_order_standard_coins_2m | double |  |
| ira_mail_order_standard_coins_3m | double |  |
| ira_mail_order_standard_copay_1m | double | 30-day mail-order standard copay under the IRA standard benefit. 2024+ only. |
| ira_mail_order_standard_copay_2m | double |  |
| ira_mail_order_standard_copay_3m | double | 90-day mail-order standard copay under the IRA standard benefit. 2024+ only. |
| ira_oon_pharmacy_copay_1m | double | 30-day out-of-network pharmacy copay under the IRA standard benefit. 2024+ only. |
| ira_retail_preferred_coins_1m | double |  |
| ira_retail_preferred_coins_2m | double |  |
| ira_retail_preferred_coins_3m | double |  |
| ira_retail_preferred_copay_1m | double |  |
| ira_retail_preferred_copay_2m | double |  |
| ira_retail_preferred_copay_3m | double |  |
| ira_retail_standard_coins_1m | double |  |
| ira_retail_standard_coins_2m | double |  |
| ira_retail_standard_coins_3m | double |  |
| ira_retail_standard_copay_1m | double | 30-day retail standard-pharmacy copay under the IRA-redesigned standard benefit. Populated only from report_year 2024 onward (0 before). Can DIFFER from the non-IRA value (e.g. Non-Preferred tier: non-IRA $99 vs IRA $35). |
| ira_retail_standard_copay_2m | double |  |
| ira_retail_standard_copay_3m | double | 90-day retail standard copay under the IRA standard benefit. 2024+ only. |
| ltc_partner_coins_1m | double | Non-IRA 30-day LTC pharmacy coinsurance percent for this tier. |
| ltc_partner_copay_1m | double | Non-IRA 30-day long-term-care (LTC) pharmacy copay for this tier. |
| mail_order_days_supply_1m | double |  |
| mail_order_days_supply_2m | double |  |
| mail_order_days_supply_3m | double |  |
| mail_order_days_supply_basis | string |  |
| mail_order_preferred_coins_1m | double |  |
| mail_order_preferred_coins_2m | double |  |
| mail_order_preferred_coins_3m | double |  |
| mail_order_preferred_coins_other_supply | double |  |
| mail_order_preferred_copay_1m | double |  |
| mail_order_preferred_copay_2m | double |  |
| mail_order_preferred_copay_3m | double |  |
| mail_order_preferred_copay_daily | double |  |
| mail_order_preferred_copay_other_supply | double |  |
| mail_order_preferred_other_supply_days | double |  |
| mail_order_standard_coins_1m | double |  |
| mail_order_standard_coins_2m | double |  |
| mail_order_standard_coins_3m | double |  |
| mail_order_standard_coins_other_supply | double |  |
| mail_order_standard_copay_1m | double | Non-IRA 30-day standard mail-order copay for this tier. |
| mail_order_standard_copay_2m | double |  |
| mail_order_standard_copay_3m | double | Non-IRA 90-day standard mail-order copay for this tier (often ~3x the 1-month). |
| mail_order_standard_copay_daily | double |  |
| mail_order_standard_copay_other_supply | double |  |
| mail_order_standard_other_supply_days | double |  |
| oon_pharmacy_coins_1m | double | Non-IRA 30-day out-of-network pharmacy COINSURANCE percent for this tier. The longest-available cost-share field — populated back to 2010 (when copay columns were not). Range 0-95%. |
| oon_pharmacy_copay_1m | double | Non-IRA 30-day out-of-network pharmacy copay for this tier. |
| oon_pharmacy_days_supply_1m | double |  |
| partd_catastrophic_statutory_basis | string |  |
| partd_catastrophic_statutory_coinsurance_pct | double |  |
| partd_catastrophic_statutory_min_copay_generic | double |  |
| partd_catastrophic_statutory_min_copay_other | double |  |
| partd_oop_threshold_amount | double |  |
| partd_oop_threshold_basis | string |  |
| partd_phase | varchar(31) |  |
| post_oop_amount_basis | string |  |
| post_oop_cost_share_structure | string |  |
| post_oop_covered_part_d_rule | string |  |
| post_oop_excluded_drug_cost_share_type | string |  |
| post_oop_excluded_drug_cost_share_type_code | string |  |
| post_oop_injectable_only_tier | boolean |  |
| post_oop_mmp_copay_maximum | double |  |
| post_oop_mmp_copay_minimum | double |  |
| post_oop_reference_pricing | boolean |  |
| post_oop_specialty_tier | boolean |  |
| retail_days_supply_1m | double |  |
| retail_days_supply_2m | double |  |
| retail_days_supply_3m | double |  |
| retail_days_supply_basis | string |  |
| retail_preferred_coins_1m | double |  |
| retail_preferred_coins_2m | double |  |
| retail_preferred_coins_3m | double |  |
| retail_preferred_copay_1m | double |  |
| retail_preferred_copay_2m | double |  |
| retail_preferred_copay_3m | double |  |
| retail_preferred_copay_daily | double |  |
| retail_standard_coins_1m | double |  |
| retail_standard_coins_2m | double |  |
| retail_standard_coins_3m | double |  |
| retail_standard_coins_other_supply | double |  |
| retail_standard_copay_1m | double | Non-IRA 30-day (1-month) copay at a standard (non-preferred) retail pharmacy for this tier. The most-used cost-share field. Populated ~2016 onward. Range observed $0-$250. |
| retail_standard_copay_2m | double |  |
| retail_standard_copay_3m | double | Non-IRA 90-day (3-month) standard retail pharmacy copay for this tier. |
| retail_standard_copay_daily | double |  |
| retail_standard_copay_other_supply | double |  |
| retail_standard_other_supply_days | double |  |
| senior_savings_model_insulin_cohort | int |  |
| senior_savings_model_participant | boolean |  |
| tier_benefit_type | string | PBP code for the benefit-type of the tier (mostly '4' = 705,242; also '2', '3'). CMS PBP enumeration requiring the LMI crosswalk to decode the exact meaning. |
| tier_cost_share_structure | string | PBP code for the cost-share structure: '2' (582,940, e.g. copay-based), '1' (202,013, e.g. coinsurance-based), with small counts of '3','4' and ~6,705 NULL. Decode via the LMI PBP description doc. |
| tier_drug_type | string | PBP code for the drug type(s) the tier covers ('01','10','11', etc.). CMS enumeration; decode via LMI crosswalk. |
| tier_formulary_model_desc | string | Free-text description of the plan's overall formulary model (e.g. 'Preferred Generic, Generic, Preferred Brand, Non-Preferred Drug, Specialty Tier'). Same string repeats across all tiers of a plan — describes the whole model, not just this tier. |
| tier_id | string | Tier POSITION number within the plan's formulary (string '1'-'7'). Never null. tier_id=1 is the lowest/cheapest tier (typically Preferred Generic). The same position can carry different labels across plans, so the number is the structural key, the label is descriptive. |
| tier_includes | string | PBP code flag (e.g. '1') indicating inclusion attributes of the tier. Often NULL in older years. Decode via LMI crosswalk. |
| tier_label_list | string | Human name(s) for the tier (e.g. 'Preferred Generic', 'Generic', 'Preferred Brand', 'Non-Preferred Drug', 'Specialty Tier', 'Select Care Drugs', 'Vaccines'). The consumer-facing tier name; varies by plan even at the same tier_id. |
| tier_location_ltc | string | PBP location/coverage code for long-term-care pharmacy (e.g. '1'). Decode via LMI crosswalk. |
| tier_location_mail_order_standard | string | PBP location/coverage code for standard mail-order. NULL when the tier has no standard mail-order benefit. |
| tier_location_oon | string | PBP location/coverage code for out-of-network pharmacy (e.g. '01'). Decode via LMI crosswalk. |
| tier_location_retail_standard | string | PBP location/coverage code for standard retail pharmacy (e.g. '111','110'). Encodes where/how the tier is covered at retail; decode via LMI crosswalk. NULL when not offered at that channel. |
| tier_type_id | string | Tier-structure scenario code. Two values: '1' (675,832 rows, the standard cost-share scenario) and '3' (116,195 rows, a second parallel scenario, e.g. preferred-vs-standard pharmacy or a different cost-share structure). REQUIRED in the grain — a plan can file both type 1 and type 3 for the same year (57,422 plan-periods do). |
| source_pbp_section | varchar(8) | Constant = 'MRX_TIER'. The PBP file section these rows derive from (Medicare Rx tier section). |
| source_silver_table | varchar(28) | Constant = 'medicare_silver.pbp_mrx_tier'. The single upstream silver table feeding this view. |
| lmi_authorities | string | URL to the LMI 'PBP Service Category Descriptions' PDF — the authoritative codebook for decoding the tier_* PBP code columns. Same authority URL on all rows. |
| source_url | string | CMS download URL of the originating PBP-benefits PUF zip (e.g. .../pbp-benefits-2018-q4.zip). 31 distinct = one per quarterly release; pins each row to its exact CMS source file. |
| sha256_content_hash | string | Content hash of the source PUF file for provenance/integrity. 31 distinct — one per quarterly source release (matches the 31 year-quarter snapshots). |
| statutory_insulin_cap_month_amount | double |  |
| statutory_insulin_cap_effective_from | date |  |
| statutory_insulin_cap_basis | string |  |
| tier_type_id_note |  | (see tier_type_id) — duplicate guard removed. |

Tools that read it: get_pbp_benefit

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