PBP partd drugs (cleaned view)

What it is

1. 1A schema-normalized, plan-level view of Medicare Advantage / Part-D PRESCRIPTION-DRUG benefit STRUCTURE, sourced from the MRX section of the CMS Plan Benefit Package (PBP) Public Use Files.
2. 2One row per MA/PDP plan per ingested release file, summarizing whether the plan offers Part-D and its formulary/gap/deductible structure (as raw CMS PBP codes).
3. 3It is a benefit-DESIGN catalog, NOT a per-drug price or per-tier dollar table.

Grain

How to read it

1. 1To get the current (2026) Part-D formulary structure for each MA/PDP plan without double-counting: SELECT contract_id, plan_id, segment_id, offers_part_d, part_d_benefit_type, formulary_model_type, formulary_model_desc, formulary_tier_count, has_gap_coverage FROM medicare_semantic.vw_pbp_partd_drugs WHERE report_year = 2026 AND offers_part_d = true.
2. 2(2026 is a single-file year so no dedup needed; for multi-file years like 2023 add a QUALIFY ROW_NUMBER() OVER (PARTITION BY contract_id,plan_id,segment_id ORDER BY source_url DESC)=1 to keep one snapshot.) This answers 'what does each plan's drug benefit look like (how many tiers, what formulary model, does it cover the gap)' — but NOT 'what does drug X cost', which this view does not contain.

Traps

1. 1NO dollar amounts: every numeric cost column (copay_amount, coinsurance_pct, benefit_amount, benefit_amount_pmpm, limit_quantity, auth_required, max_oop_applies) is 100% NULL. This is by design (numeric_amount_observability='offered_without_numeric_amounts', cost_share_string='see_per_tier_breakdown'). This view tells you a plan's Part-D STRUCTURE/formulary, not its prices — per-tier copays live in a different table. Do not use it for cost benchmarking.
2. 2This is stacked snapshot history with NO is_current flag. A plan appears once per ingested CMS release file, so 6 years (2018,2019,2021,2022,2023,2024) have 2-4 files and will DOUBLE/QUADRUPLE-count plans if you don't dedup. For one-row-per-plan-year, pick the latest source_url per (contract_id,plan_id,segment_id,report_year). report_year 2020 is entirely absent.
3. 3The unique key looks like CPS+report_year+quarter but is NOT — that gives 192,230 not 208,537. The minimal verified key is (contract_id,plan_id,segment_id,sha256_content_hash,quarter)=208,537. The extra ~16k dupes are the same plan in two files of the same year (an 'updated' release plus a 'quarter' release).
4. 4The 'quarter' column is unreliable: pre-2018 and 2025/2026 annual files are all forced to quarter=1, and the single file pbp-benefits-2022-quarter-2.zip was loaded under BOTH quarter=1 and quarter=2 (identical 8,336-row copies) — that double-load is the ONLY reason quarter is needed to make the key unique. Treat quarter as a release-label, not a real calendar quarter; report_year is the true time axis.
5. 5~18 columns are CONSTANTS (benefit_category=partd_drugs, network_type=in_network, partd_offering_axis=plan_level_summary, offered_status=offered, confidence_tier=source_faithful, numeric_amount_observability, null_amount_reason, annualization_policy, combined_limit_policy, consumer_document_reconciliation_status, source_pbp_section=MRX, source_silver_table, lmi_authorities, etc.). They carry zero per-row signal — don't filter or group on them expecting variation.
6. 6Code columns (formulary_model_type, part_d_benefit_type, gap_coverage_tier, ira_coverage_tier, partd_network_location, reduced_cost_share_items, *_cost_share_struct, attestations) are RAW CMS PBP codes / bit-flag strings passed through verbatim (confidence_tier='source_faithful') and NOT decoded — you must crosswalk them against the CMS PBP data dictionary / the lmi_authorities PDF. formulary_model_desc is the only decoded text and it is NOT a strict 1:1 map of formulary_model_type (same code yields 2-3 desc variants across vintages).
7. 7Filter on offers_part_d: 25,435 rows are MA plans that do NOT offer Part-D (offers_part_d=false), and on those rows the entire formulary/gap/deductible payload is NULL. part_d_benefit_type is also null on some offers_part_d=true rows whose source file lacked the MRX detail section (e.g. 2026 H0016).

Columns (93)

Query it

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