# PBP partb rx drugs (cleaned view)

`medicare_semantic.vw_pbp_partb_rx_drugs` · Medicare Advantage, Part D, Medicaid · status live

Cleaned source-faithful PBP partb rx 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

**Grain.** One row per MA plan benefit package per CMS PBP release file. The minimal verified-unique key is (contract_id, plan_id, segment_id, report_year, quarter): 189,385 rows = 189,385 distinct combinations. report_year is mandatory in the key (dropping it collapses to 55,564). quarter is a republish/snapshot dimension: for report_years 2008-2017, 2021, 2025, 2026 there is only Q1 (one row per plan-year), but 2018-2020 and 2022-2024 were re-published all 4 quarters, so a plan appears up to 4x in those years -- same benefit, different source file.

**How to use it.** Get the current (2026) in-network Part B drug member cost-share for every MA plan, deduped to one row per plan: SELECT contract_id, plan_id, segment_id, coinsurance_pct, copay_amount, cost_share_string, ira_insulin_copay_month_amount FROM medicare_semantic.vw_pbp_partb_rx_drugs WHERE report_year = 2026 AND quarter = (SELECT MAX(quarter) FROM medicare_semantic.vw_pbp_partb_rx_drugs WHERE report_year = 2026). Answers 'what does a beneficiary pay (e.g. 20% coinsurance) for Part-B-administered drugs under each MA plan, and which plans apply the IRA $35 insulin cap?'

**Traps.**
- NO is_current column and NO single 'live' row per plan-year. In republish years (2018-2020, 2022-2024) a plan appears up to 4 times -- once per quarter (quarter 1-4) -- with IDENTICAL benefit values but different source_url/sha256_content_hash. To get one row per plan per benefit-year you MUST dedup, e.g. keep MAX(quarter) per (contract_id,plan_id,segment_id,report_year). Summing/AVG-ing across rows without this 4x-counts those years.
- report_year (not quarter) is the real time axis and is REQUIRED in the grain key. The profiler reported quarter as the time column and offered a candidate key (contract_id,plan_id,segment_id,limit_period_code,benefit_amount_period_code,confidence_tier[,quarter]) that is NOT unique (55,564 of 189,385) and is built from all-NULL/constant placeholder columns. The true minimal key is (contract_id, plan_id, segment_id, report_year, quarter).
- Roughly a dozen columns are pure CONSTANTS in this view (benefit_category, network_type, offered_status, numeric_amount_observability, null_amount_reason, annualization_policy, combined_limit_policy, consumer_document_reconciliation_status, confidence_tier, partb_rx_drugs_sub_benefit, source_pbp_section, source_silver_table, lmi_authorities) and another ~10 are 100% NULL (benefit_amount*, limit_*, referral_required, max_oop_applies, normalization_*). These are shared semantic-view template fields; do not filter or join on them expecting signal.
- Nulls carry temporal meaning. The IRA insulin columns (has_ira_insulin_coinsurance, ira_insulin_*) are 100% NULL before report_year 2024, then populated -- a null means 'pre-Inflation-Reduction-Act / not applicable', not false; the $35 cap only appears 2024+. Chemo coinsurance columns are null before report_year 2010 (chemo cost-share was copay-only earlier). Filtering on these silently drops all older years.
- coinsurance_pct (71.3% filled) is the headline member cost-share, but coinsurance_pct_max is filled MORE (85.4%): ~26,695 rows have the _max set while the headline coinsurance_pct is null, because the _min/_max columns aggregate across the standard + chemo + insulin sub-amounts. Use coinsurance_pct/copay_amount for the plain plan-level cost-share; use the _min/_max for the full range. version is exactly one value per grain key (the CMS file version), so it is redundant with the key, not a grain dimension.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare Advantage contract ID (e.g. H0028, E5088; H-/R-/S-/E- prefixes for various plan types). 1,813 distinct. Part of grain. |
| plan_id | string | Plan ID within the contract (3-char, e.g. '001','801'). 656 distinct values. contract_id+plan_id+segment_id identifies the plan benefit package (PBP). Part of grain. |
| segment_id | string | PBP segment number (service-area sub-segment of a plan). 26 distinct; '0' on 92% of rows (174,647); >0 for plans segmented by geography/premium. Part of grain. |
| version | double | CMS PBP file version number for that release (1-27). Exactly one value per (contract,plan,segment,report_year,quarter), so it is redundant with the grain, NOT a grain dimension. |
| report_year | double | Plan/contract benefit year, 2008-2026. The real time axis (annual). Tracks the source file year. Part of grain. NOTE: stored as double (2008.0). |
| quarter | double | CMS PBP republish quarter (1-4) of the source file within report_year. A snapshot/version dimension, not a benefit-quarter: same plan-year republished in multiple quarters carries identical benefit data but a different source file/hash. Most years have only quarter=1. Part of grain. Stored as double. |
| benefit_category | string | CONSTANT = 'partb_rx_drugs' on every row. Template/scaffolding column identifying this view's benefit family; carries no per-row signal. |
| cost_share_string | string | Human-readable rendering of the member cost-share, e.g. '20.0%', '0.0%', '$0 copay', '$5.0 copay + 20.0%', '$45.0 copay'. Filled 73.9%. The display-friendly summary of copay_amount + coinsurance_pct. |
| copay_amount | double | Flat dollar copay for the Part B drug benefit. Filled only 5.2% (most plans use coinsurance, not copay). Dominant value $0; also $5/$45/$10 etc. |
| coinsurance_pct | double | Member coinsurance percentage -- the PRIMARY cost-share mechanism here, filled 71.3%. 20% dominates (90,053 rows = standard Medicare Part B drug coinsurance); 0% for plans that waive it (43,266). |
| limit_text | string | 100% NULL in this view. Template column for free-text benefit limits; not used for Part B Rx. |
| auth_required | boolean | Boolean: prior authorization required for the benefit. Filled 93.4% (false on the sample rows shown). |
| referral_required | boolean | 100% NULL. Template column; not applicable to this drug benefit. |
| network_type | varchar(10) | CONSTANT = 'in_network' on every row. This view captures only in-network cost-share; no out-of-network rows exist here. |
| max_oop_applies | boolean | 100% NULL. Template column (does this benefit count toward the MOOP); not populated for Part B Rx. |
| limit_quantity | double | 100% NULL. Template quantity-limit column; unused here. |
| limit_unit | string | 100% NULL. Template column (unit for limit_quantity); unused. |
| limit_period_code | string | Template period code for a benefit limit. NULL here; appears in the profiler's candidate key only as a coalesced placeholder, NOT a real grain column. |
| limit_period_label | string | 100% NULL. Human label for limit_period_code; unused for this benefit. |
| limit_quantity_per_year | double | 100% NULL. Template annualized quantity limit; unused. |
| benefit_amount | double | 100% NULL. Template column for a plan-paid benefit dollar amount; this is a cost-share view, so it is empty. |
| benefit_amount_kind | string | 100% NULL. Template (kind of benefit_amount); unused. |
| benefit_amount_period_code | string | Template period code for benefit_amount. NULL; like limit_period_code it is only a coalesced placeholder in the profiler's candidate key, not real grain. |
| benefit_amount_period_label | string | 100% NULL. Label for benefit_amount_period_code; unused. |
| benefit_amount_annualized | double | 100% NULL. Template annualized benefit dollar; unused. |
| benefit_amount_pmpm | double | 100% NULL. Template per-member-per-month benefit dollar; unused. |
| normalization_status | string | 100% NULL in this view. Template provenance field describing numeric normalization; not set for this benefit. |
| normalization_basis | string | 100% NULL. Template field (basis of normalization); unused. |
| aggregation_policy | string | 100% NULL. Template field describing how multiple sub-amounts were aggregated; unused here. |
| offered_status | varchar(15) | CONSTANT = 'offered' on every row. Every row represents a plan that offers the Part B Rx benefit; no 'not_offered' rows in this view. |
| numeric_amount_observability | varchar(31) | CONSTANT = 'offered_without_numeric_amounts'. Provenance flag: benefit is offered but a fully reconciled numeric amount is not asserted (headline numbers come from parsed cost-share strings). Same for all rows. |
| null_amount_reason | varchar(30) | CONSTANT = 'unknown_requires_crosswalk'. Explains why amount fields can be null -- a crosswalk would be needed to resolve them. Same on every row. |
| annualization_policy | varchar(17) | CONSTANT = 'not_numeric'. The benefit amount is not annualized because it is not a numeric dollar benefit. Same for all rows. |
| combined_limit_policy | varchar(29) | CONSTANT = 'none_detected'. No combined/shared benefit limit detected. Same for all rows. |
| consumer_document_reconciliation_status | varchar(11) | CONSTANT = 'not_checked'. Whether the value was reconciled against a consumer-facing doc (SB/EOC); not done for this benefit. Same on every row. |
| confidence_tier | varchar(23) | CONSTANT = 'source_faithful'. Confidence label: value is faithfully carried from the CMS source (no inference). Same on every row; appears in the profiler candidate key but is not a real grain column. |
| chemo_coinsurance_pct_max | double | Max coinsurance % for the Part B chemotherapy/anti-cancer drug sub-benefit. Filled from report_year 2010 onward (PBP schema change). 20% dominates (158,471). |
| chemo_coinsurance_pct_min | double | Min coinsurance % for the chemo drug sub-benefit. Same 2010+ availability as the max. |
| chemo_copay_amount_max | double | Max flat copay for the chemo drug sub-benefit. Present even in 2008-2009 (when chemo coinsurance was not yet a field); sparse thereafter. |
| chemo_copay_amount_min | double | Min flat copay for the chemo drug sub-benefit. |
| coinsurance_applies_to | varchar(22) |  |
| coinsurance_pct_max | double | Max coinsurance % across the benefit's sub-amounts (standard + chemo + insulin). Filled 85.4% -- HIGHER than coinsurance_pct (71.3%): 26,695 rows have this set while the headline coinsurance_pct is null, because it aggregates across sub-benefits. |
| coinsurance_pct_min | double | Min coinsurance % across the benefit's sub-amounts. Filled ~71.3%. |
| coinsurance_source_field | varchar(19) |  |
| copay_amount_max | double | Max flat copay across the benefit's sub-amounts. Filled ~5.2% (copay is rare for this benefit). |
| copay_amount_min | double | Min flat copay across sub-amounts. Filled ~5.2%. |
| copay_applies_to | varchar(22) |  |
| has_coinsurance | boolean | Boolean: coinsurance applies to the benefit. Filled 94.7%. The dominant cost-share signal for Part B drugs. |
| has_copay | boolean | Boolean: a flat copay applies. TRUE on 9,983 rows and aligns with copay_amount (only 189 TRUE rows lack a numeric copay). Filled 94.7%. |
| has_deductible | boolean | Boolean: a deductible applies to the benefit. Filled 94.6%. |
| has_ira_insulin_coinsurance | boolean | Boolean for the Inflation Reduction Act insulin coinsurance provision. 100% NULL until report_year 2024, then populated -- a null here means 'pre-IRA / not applicable', not 'false'. |
| ira_insulin_coinsurance_pct_max | double | Max coinsurance % under the IRA insulin cap rule. Populated only report_year 2024+. |
| ira_insulin_coinsurance_pct_min | double | Min coinsurance % under the IRA insulin cap rule. 2024+ only. |
| ira_insulin_copay_amount_max | double | Max copay under the IRA insulin provision. 2024+ only. |
| ira_insulin_copay_amount_min | double | Min copay under the IRA insulin provision. 2024+ only. |
| ira_insulin_copay_month_amount | double | The IRA monthly insulin out-of-pocket cap. Populated 2024+; value is $35 on 22,866 rows (the statutory IRA insulin cap), with a few $0/$25. The headline IRA-insulin number. |
| partb_rx_drugs_sub_benefit | string | CONSTANT = 'partb_drugs' on every row. Sub-benefit label within the B15 section; no per-row variation in this view. |
| partd_home_infusion_bundled_supplemental | boolean |  |
| source_pbp_section | varchar(3) | CONSTANT = 'B15'. The CMS PBP PUF section this benefit is parsed from (B15 = Medicare Part B Rx Drugs). Same on every row. |
| source_silver_table | varchar(46) | CONSTANT = 'medicare_silver.pbp_section_b15_partb_rx_drugs'. The upstream silver table this gold view is built from; provenance/lineage pointer. |
| lmi_authorities | string | CONSTANT, single pipe-delimited string of LMI (Local Market Intelligence) authority document URLs (CY2026 PBP Service Category Descriptions PDF + Part C Bid Review Memorandum). The semantic-definition citations; identical on all rows. |
| source_url | string | URL of the CMS PBP PUF zip the row came from. 33 distinct; tracks report_year and quarter (e.g. pbp-benefits-2008.zip; pbp-benefits-2023-quarter-3.zip; pbp-benefits-2026.zip). Confirms quarter is the source-file/republish dimension. |
| sha256_content_hash | string | Content hash of the source file. Differs per (report_year,quarter) source file -- so the 4 quarterly copies of one plan-year have DIFFERENT hashes despite identical benefit data. A provenance/dedup fingerprint, not a benefit attribute. |
| statutory_insulin_cap_month_amount | double |  |
| statutory_insulin_cap_effective_from | date |  |
| statutory_insulin_cap_basis | string |  |

Tools that read it: get_pbp_benefit

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