# PBP partd drugs (cleaned view)

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

Cleaned source-faithful PBP partd drugs 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 Medicare plan (contract_id + plan_id + segment_id) per ingested CMS PBP source FILE. The minimal verified-unique key is (contract_id, plan_id, segment_id, sha256_content_hash, quarter) = 208,537 distinct = exactly the 208,537 row_count. sha256_content_hash (equivalently source_url) identifies the physical CMS release file/snapshot; quarter is required only because one 2022 file got materialized under two quarter labels. There is NO is_current flag — this is stacked release-snapshot history (2010-2026, gap at 2020), so a plan appears once per file it was published in. A clean "one row per plan-year" slice has 104,320 distinct (contract_id, plan_id, segment_id, report_year), reached by deduping to the latest source_url per plct-year.

**How to use it.** To 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. (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.**
- NO 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.
- This 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.
- The 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).
- The '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.
- ~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.
- Code 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).
- Filter 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).

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract ID (e.g. H0028); 1,806 distinct. Part of the plan key, NOT unique alone. |
| plan_id | string | 3-digit plan benefit package ID within the contract (e.g. 001); 656 distinct. Part of the plan key. |
| segment_id | string | Plan segment ID (service-area segment), usually '0'; 26 distinct. Third element of the contract/plan/segment (CPS) plan key. |
| version | double | The plan's PBP submission/filing version number (double, observed 1-27). Varies PER PLAN within a single source file (e.g. in the 2026 file one plan is v2, another v5) — it is NOT a file-level marker and NOT needed to make rows unique. Higher counts at low values (most plans v1-6). |
| report_year | double | Contract/benefit year the PBP describes (2010-2026; 2020 missing). This is the real time axis (the profiler mislabeled 'quarter' as the time column). Pre-2018 = one annual file; 2018-2019 & 2023 = 4 quarterly files; 2021/2022/2024 = 2-3 mid-year releases; 2025/2026 = single annual file. |
| quarter | double | Release-quarter label (1-4) of the source file. UNRELIABLE / quasi-derived: pre-2018 and 2025/2026 annual files are forced to quarter=1; and the 2022-quarter-2 file was loaded under BOTH quarter=1 and quarter=2 (8,336 rows each), which is the only reason quarter is needed in the unique key. Do not treat as a true calendar quarter. |
| benefit_category | string | Constant = 'partd_drugs' for all 208,537 rows. This view is the Part-D-drugs slice of a broader benefit superschema. |
| cost_share_string | varchar(22) | Constant = 'see_per_tier_breakdown'. A pointer/sentinel: actual per-tier copays/coinsurance live in a separate per-tier table, not here. |
| copay_amount | double | 100% NULL. Inherited numeric-benefit column from the superschema; never populated in this plan-level drug view. |
| coinsurance_pct | double | 100% NULL. Inherited placeholder; no coinsurance carried here. |
| limit_text | double | Declared double but 100% NULL (type mismatch artifact). Unused. |
| auth_required | boolean | 100% NULL. Prior-auth flag not populated for Part-D plan-summary rows. |
| referral_required | boolean | 100% NULL. Inherited placeholder, unused here. |
| network_type | varchar(10) | Constant = 'in_network' for all rows. Out-of-network drug cost shares are not represented as separate rows. |
| max_oop_applies | boolean | 100% NULL. Inherited placeholder. |
| limit_quantity | double | 100% NULL. Inherited quantity-limit placeholder. |
| limit_unit | string | 100% NULL. Inherited placeholder. |
| limit_period_code | string | 100% NULL. Inherited placeholder (profiler listed it as a candidate key only because null-coalescing made it look like a discriminator). |
| limit_period_label | string | 100% NULL. Inherited placeholder. |
| limit_quantity_per_year | double | 100% NULL. Inherited placeholder. |
| benefit_amount | double | 100% NULL. Inherited numeric placeholder; no dollar benefit amounts in this view. |
| benefit_amount_kind | string | 100% NULL. Inherited placeholder. |
| benefit_amount_period_code | string | 100% NULL. Inherited placeholder (also a profiler false-candidate key). |
| benefit_amount_period_label | string | 100% NULL. Inherited placeholder. |
| benefit_amount_annualized | double | 100% NULL. Inherited placeholder. |
| benefit_amount_pmpm | double | 100% NULL. Inherited per-member-per-month placeholder. |
| normalization_status | string | 100% NULL here (the per-row normalization outcome is not set because there are no numeric amounts to normalize). |
| normalization_basis | string | 100% NULL. Inherited placeholder. |
| aggregation_policy | string | 100% NULL for every row. Inherited placeholder. |
| offered_status | varchar(15) | Constant = 'offered' for all rows. A warranty stamp meaning the benefit category is offered/applicable; carries no per-row signal. |
| numeric_amount_observability | varchar(31) | Constant = 'offered_without_numeric_amounts'. Explicitly states this view has structure but no dollar figures — confirms the all-null numeric columns are by design. |
| null_amount_reason | varchar(30) | Constant = 'unknown_requires_crosswalk'. Why the numeric amounts are null: they require an external per-tier crosswalk not joined in this view. |
| annualization_policy | varchar(17) | Constant = 'not_numeric'. No annualization performed because there are no numeric amounts. |
| combined_limit_policy | varchar(29) | Constant = 'none_detected'. No combined-limit handling applied. |
| consumer_document_reconciliation_status | varchar(11) | Constant = 'not_checked'. Rows have not been reconciled against consumer-facing documents (e.g. Summary of Benefits). |
| confidence_tier | varchar(23) | Constant = 'source_faithful'. All values are passed through verbatim from CMS PBP without re-derivation; codes are raw, not decoded/validated. |
| alt_deductible_amount | double | 100% NULL. Inherited numeric placeholder for an alternative deductible amount. |
| alt_deductible_amount_resolved | double |  |
| alt_deductible_basis | varchar(54) |  |
| alt_deductible_charge | string | Raw CMS PBP code (string, e.g. '1','3') indicating the alternative-deductible charge type; null when not applicable. Not decoded. |
| balance_cost_share_structure | string |  |
| balance_covered_tiers | string |  |
| balance_deductible_amount | double |  |
| balance_deductible_option | string |  |
| balance_excluded_extended_supply_tiers | string |  |
| balance_excluded_mail_order_tiers | string |  |
| balance_model_attested | boolean |  |
| balance_model_status | varchar(49) |  |
| balance_opt_in_flexibilities | string |  |
| balance_opt_in_other_description | string |  |
| deductible_applies_to_all_tiers | boolean |  |
| deductible_excluded_tiers | string |  |
| deductible_excluded_tiers_code | string |  |
| deductible_phase_alt_coinsurance_pct | double |  |
| deductible_phase_alt_copay_amount | double |  |
| deductible_phase_alt_cost_share_basis | varchar(36) |  |
| deductible_phase_alt_cost_share_scope | varchar(66) |  |
| deductible_phase_alt_cost_share_string | string |  |
| deductible_phase_alt_cost_share_structure | varchar(36) |  |
| deductible_phase_alt_cost_share_structure_code | string |  |
| deductible_phase_cost_share_same_as_initial_coverage | boolean |  |
| dsnp_buydown_attestation_basis | varchar(36) |  |
| dsnp_buydown_attested | boolean |  |
| first_fill_policy | string | Raw CMS PBP first-fill policy code (string, e.g. '2'). Pass-through code, not decoded to text. |
| formulary_model_desc | string | Human-readable tier list for the formulary model (e.g. 'Preferred Generic, Generic, Preferred Brand, Non-Preferred Drug, Specialty Tier'). NOT a strict 1:1 decode of formulary_model_type — the same code (e.g. 5A4) maps to 2-3 slightly different desc strings across vintages. |
| formulary_model_type | string | CMS PBP formulary structure code (string, e.g. '5F','5A','4A','5F4'; leading digit = tier count, suffix = model variant). 42.7% null. The main real payload field. |
| formulary_tier_count | string | Number of formulary tiers as a string ('1'-'7', most commonly '5' then '6'); 38.1% null. Mirrors the leading digit of formulary_model_type. |
| gap_coverage_tier | string | Raw CMS PBP coverage-gap bit-flag string (e.g. '011111', one flag per tier indicating gap coverage); 72.7% null. Pass-through, not decoded. |
| has_alt_coverage_limit | boolean | Boolean: plan declares an alternative coverage limit. Nullable (null on rows lacking the MRX detail section). |
| has_excluded_drugs | boolean | Boolean: plan excludes certain drugs from coverage. Nullable. |
| has_gap_coverage | boolean | Boolean: plan offers coverage in the Part-D coverage gap ('donut hole'). 50.7% null (null where the detail section is absent or plan has no Part-D). |
| ira_coverage_tier | string | Raw CMS PBP bit-flag string for IRA (Inflation Reduction Act) coverage by tier (e.g. '0111110'); appears in recent years. Pass-through code. |
| ltc_attestation | string | Long-term-care pharmacy attestation as a raw code string (e.g. '1'). Pass-through, not decoded. |
| offers_part_d | boolean | Boolean: whether the plan offers Part-D drug coverage. 183,102 true / 25,435 false. When false (and on some sparse-detail true rows), the formulary/gap/deductible payload columns are NULL — the most important filter column in the table. |
| oon_cost_share_struct | string | Raw CMS PBP code (string, e.g. '1','2') describing the out-of-network cost-share structure. Pass-through code, nullable. |
| otc_meds_pay_with_premium | boolean | Boolean: OTC meds paid via premium. Nullable on sparse rows. |
| part_d_benefit_type | string | CMS PBP Part-D benefit type code (string '1'-'4'; '4'=Enhanced Alternative is most common at 112,059, then '1' Defined Standard). 31.5% null (null when offers_part_d=false or detail section absent). |
| partd_enhanced_coverage_demo | string | Raw code for the Part-D Enhanced/VBID-style coverage demonstration; mostly null. Pass-through. |
| partd_network_location | string | Raw CMS PBP 6-digit bit-flag string for pharmacy network locations offered (e.g. '110101','101101'); 31.5% null. Pass-through, not decoded into named pharmacy types. |
| partd_offering_axis | varchar(18) | Constant = 'plan_level_summary' for all rows. Declares the grain explicitly: each row is a plan-level Part-D summary, NOT a per-tier or per-drug row. |
| post_oop_cost_share_rule | varchar(48) |  |
| post_oop_cost_share_rule_scope | varchar(39) |  |
| post_oopt_cost_share_struct | string | Raw CMS PBP code (string) for cost-share structure AFTER the out-of-pocket threshold (catastrophic phase). Pass-through, nullable. |
| pre_icl_cost_share_struct | string | Raw CMS PBP code (string) for cost-share structure BEFORE the Initial Coverage Limit. Pass-through, nullable. |
| reduced_cost_share_items | string | Raw CMS PBP bit-flag string (e.g. '101','0110') for reduced-cost-share items (e.g. for LIS/supplemental). Pass-through code, nullable. |
| snp_zero_cost_share_attestation_basis | varchar(36) |  |
| snp_zero_cost_share_attested | boolean |  |
| vaccine_attestation | string | Part-D vaccine attestation as a raw code string (e.g. '1'); appears in recent years. Pass-through. |
| source_pbp_section | varchar(3) | Constant = 'MRX' for all rows. Identifies the CMS PBP section this view is built from (MRX = the Part-D / managed Rx section). This is why only Part-D structural fields are populated. |
| source_silver_table | varchar(23) | Constant = 'medicare_silver.pbp_mrx'. The upstream silver table feeding this view (provenance stamp). |
| lmi_authorities | string | Constant single URL to the LMI CY2026 PBP Service Category Descriptions PDF — the external authority doc for decoding the PBP codes. Provenance/reference stamp, identical on every row. |
| source_url | string | The exact CMS PBP zip file this row was ingested from (e.g. .../pbp-benefits-2022-quarter-2.zip). Together with the CPS key + quarter it forms the unique grain; identifies the release snapshot. Use it to dedup stacked history. |
| sha256_content_hash | string | Content hash of the source file; equivalent to source_url for identity (both give 200,201 distinct file-rows, 208,537 with quarter). The snapshot fingerprint; part of the verified unique grain. |

Tools that read it: get_pbp_benefit

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