# PBP vision (cleaned view)

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

Cleaned source-faithful PBP vision 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 visi

**Grain.** One row per Medicare Advantage plan segment per vision sub-benefit per CMS release snapshot. Verified minimal unique key = (contract_id, plan_id, segment_id, report_year, quarter, vision_sub_benefit): 1,208,928 distinct combinations = 1,208,928 total rows, 0 duplicates. Each plan-snapshot fans out to exactly 6 sub-benefit rows (1 eye-exam from B17A + 5 eyewear from B17B). `quarter` is the within-year CMS release selector (the same plan-year reappears in up to 4 quarterly republished ZIPs); `version` is a redundant per-bid attribute and is NOT part of the key.

**How to use it.** Find the current (2026) eyewear allowance for a given MA plan without double-counting the shared cap: SELECT contract_id, plan_id, segment_id, max(benefit_amount) AS eyewear_cap, max(benefit_amount_period_code) AS period_code FROM medicare_semantic.vw_pbp_vision WHERE report_year=2026 AND vision_cap_source='combined_eyewear_cap' AND vision_offered=true GROUP BY 1,2,3. This returns the single combined eyewear dollar cap per plan (e.g. $300) and its CMS periodicity code (3='Every year'), correctly avoiding the 5x over-count you'd get by summing the per-sub-benefit rows. For multi-snapshot years, add a MAX(quarter) filter to dedup to the latest release.

**Traps.**
- DO NOT SUM benefit_amount across the 6 vision_sub_benefit rows of a plan-snapshot. For the 5 eyewear sub-benefits the same combined cap is repeated on each row (vision_cap_source='combined_eyewear_cap'); verified a $300 cap appears identically on 5 rows. The table flags this itself via aggregation_policy ('...do_not_sum') and combined_limit_policy='shared_across_components'. To get a plan's true eyewear cap, take one distinct value where vision_cap_source='combined_eyewear_cap', not a SUM.
- There is NO is_current column and `version` does NOT select the latest record. A plan-year can appear in up to 4 quarterly re-release snapshots (quarter maps 1:1 to source_url / sha256). To get one row per plan-year you must filter to MAX(quarter) per (contract_id, plan_id, segment_id, report_year); otherwise you multi-count plans that were republished. `version` (0-27) is just a bid-amendment attribute and is redundant for the grain.
- Periodicity codes are non-intuitive and SHARED by limit_period_code and benefit_amount_period_code: 1=Every THREE years, 2=Every two years, 3=Every year, 4=Every six months, 5=Every three months, 6=Other, 7=Every month. Code 1 is NOT annual. The *_period_label columns are NULL for many older years even when the code is present, so derive the label from the code yourself.
- Most rows have no usable dollar figure: only ~58K (5%) are 'all_offered_numeric'; benefit_amount_pmpm is 100% NULL and benefit_amount_annualized is populated on only ~58K rows (annualization_policy='annualized_safe'). 610K benefit_amount values have a NULL period, so they cannot be annualized. Check numeric_amount_observability / null_amount_reason before trusting a cap; null_amount_reason='combined_limit_elsewhere' means the cap is on a sibling row.
- NULLs and labels carry meaning and traps: vision_offered/auth_required NULL = genuinely unknown (offered_unknown; pre-2018 multiselect auth bitmask in vision_auth_raw like '01000' that couldn't be binarized), NOT false. segment_id has an empty-string '' value (30,444 rows) distinct from '0'. vision_offered_label flips between 'True/False' and 'Yes/No' across years for the same code - group on the vision_offered boolean or vision_offered_code, never the label. confidence_tier ('source_faithful') and consumer_document_reconciliation_status ('not_checked') are constants: values are faithful to the CMS bid but NOT reconciled against consumer SB/EOC documents.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS MA contract ID. Prefixes observed: H = local HMO/PPO (1,933 contracts, 98% of rows), R = regional PPO (37), E = employer/EGWP (2). All Medicare Advantage; combines with plan_id+segment_id to identify a plan. |
| plan_id | string | 3-char plan number within the contract (e.g. '001','007'). 656 distinct values; NOT unique on its own - only meaningful inside a contract_id. |
| segment_id | string | PBP plan segment (sub-area of a plan that can carry different benefits). Mostly '0' (90% of rows); also '1'-'6' for segmented plans, plus EMPTY STRING '' (30,444 rows, not NULL) in some years. Required for grain uniqueness. |
| version | double | CMS PBP bid version/amendment number for that snapshot (0-27). An attribute of the row, NOT a snapshot selector - it is redundant for grain (dropping it leaves the key still unique). Do not use it to pick the 'latest' record. |
| report_year | double | Plan/benefit year the bid applies to (2001-2026). This is the TRUE time dimension - use it, not `quarter`, for year-over-year analysis. Double-typed. |
| quarter | double | Which CMS republished release ZIP within the report_year (1-4), mapping 1:1 to source_url/sha256. 2001-2017 + 2021/2025/2026 have only quarter 1; 2018-2020,2022-2024 have multiple. NOT a calendar quarter of service - it is a re-release/snapshot index. There is NO is_current flag; pick MAX(quarter) per report_year for the freshest snapshot. |
| benefit_category | string | Constant = 'vision' (this is the vision-only view). |
| cost_share_string | string | Raw text cost-share descriptor from the bid. Populated for only 331,963 rows (27%); NULL means cost-share was not populated in the bid (see cost_share_populated_in_bid). |
| copay_amount | double | Member copay in dollars. Populated for 325,532 rows only. NULL where no copay applies / not populated in bid. |
| coinsurance_pct | double | Member coinsurance percent. Very sparse - only 7,955 rows. Vision is overwhelmingly copay- or cap-based, rarely coinsurance. |
| limit_text | string | Human-readable frequency limit string, e.g. '1.0 pairs/6' or '1.0 exams/3' (the number after the slash is the CMS periodicity code, not a count). Parsed into limit_quantity/limit_unit/limit_period_code. |
| auth_required | boolean | Boolean prior-authorization required. NULL for ~45% of rows - chiefly pre-2018 plans whose CMS field was a multiselect bitmask (see vision_auth_raw / vision_auth_source_status) that could not be collapsed to a clean yes/no. |
| referral_required | boolean | Boolean referral required to access the vision benefit. |
| network_type | varchar(10) | Constant = 'in_network'. This view carries only in-network terms; out-of-network is not represented. |
| max_oop_applies | boolean | Whether the benefit counts toward the plan MOOP. Effectively all NULL in samples - not reliably populated for vision. |
| limit_quantity | double | Numeric frequency allowance (e.g. 1.0 = one pair/exam). Parsed from limit_text; NULL when no quantity limit is stated. |
| limit_unit | string | Unit of the frequency limit, e.g. 'pairs','exams'. Can be populated even when limit_quantity is NULL (default unit for the sub-benefit). |
| limit_period_code | string | CMS periodicity code for the frequency limit. CAUTION on the mapping: 1=Every three years, 2=Every two years, 3=Every year, 4=Every six months, 5=Every three months, 6=Other/Describe, 7=Every month. (Code 1 is NOT annual.) NULL for 902,818 rows (no periodic limit / not in section). |
| limit_period_label | string | Decoded label for limit_period_code. WARNING: NULL in many older years even when the code is present - trust limit_period_code, derive the label yourself. |
| limit_quantity_per_year | double | Frequency normalized to an annual count. Almost entirely NULL - normalization to per-year was not applied for most vision rows. |
| benefit_amount | double | The maximum-plan-benefit dollar cap (benefit_amount_kind is constant 'maximum_plan_benefit'). 598,730 non-null, range $0-$4,000, median ~$182. CRITICAL: for the 5 eyewear sub-benefits this is frequently the SAME shared combined-eyewear cap repeated on each row (vision_cap_source='combined_eyewear_cap') - do NOT sum across sub-benefits. |
| benefit_amount_kind | string | Constant = 'maximum_plan_benefit'. Confirms benefit_amount is a coverage cap, not a member cost or an allowance the member pays. |
| benefit_amount_period_code | string | CMS periodicity code for the cap (same code system as limit_period_code: 3=Every year, 2=Every two years, 1=Every three years, etc). NULL for 610,182 rows - meaning many caps have no stated period and therefore cannot be annualized. |
| benefit_amount_period_label | string | Decoded label for the cap period. Frequently NULL even when the code is present (label coverage is partial); use the code. |
| benefit_amount_annualized | double | Cap normalized to an annual figure. Only 58,292 rows populated - annualization was only safe for a small subset (see annualization_policy='annualized_safe'); NULL elsewhere. |
| benefit_amount_pmpm | double | Per-member-per-month version of the cap. 100% NULL - the column exists but is never populated for vision. |
| normalization_status | varchar(56) | How far the amount/frequency was normalized: 'amount_and_period_exposed_not_annualized' (538K), 'not_populated' (382K), 'frequency_only_no_amount' (142K), 'no_quantity_limit_columns_for_upgrades' (87K), 'normalized' (61K). Tells you how usable the numeric fields are. |
| normalization_basis | varchar(43) | Constant = 'cms_periodicity_code' - all period normalization is anchored on the CMS periodicity code system. |
| aggregation_policy | varchar(60) | Whether sub-benefit rows may be summed. Dominant value 'component_or_shared_cap_do_not_sum' (1.08M) plus 'shared_eyewear_cap_do_not_sum' - both explicitly say DO NOT SUM benefit_amount across the 6 sub-benefits. This is the built-in anti-double-count guard. |
| offered_status | varchar(15) | Tri-state offered flag: 'offered' (780,738), 'not_offered' (371,592), 'offered_unknown' (56,598). The 'offered_unknown' rows are where vision_offered is NULL. |
| numeric_amount_observability | varchar(31) | Whether usable dollar amounts exist: 'offered_without_numeric_amounts' (779,089 - benefit offered but no $ in bid), 'not_offered' (371,592), 'all_offered_numeric' (58,247). Only ~5% of rows expose complete numeric amounts. |
| null_amount_reason | string | Why benefit_amount is NULL: 'not_null_numeric_amount' (492,810 = amount IS present), 'not_offered' (371,546), 'offered_no_numeric' (161,286), 'combined_limit_elsewhere' (128,367 = cap lives on another sub-benefit row), 'unknown_requires_crosswalk' (47,165), 'unlimited' (7,754). Essential for interpreting NULL caps. |
| annualization_policy | varchar(17) | 'not_numeric' (most rows) or 'annualized_safe' (~58K). Only 'annualized_safe' rows have a trustworthy benefit_amount_annualized. |
| combined_limit_policy | varchar(29) | Constant 'shared_across_components' - reinforces that eyewear sub-benefits share a single combined limit. |
| consumer_document_reconciliation_status | varchar(11) | Constant = 'not_checked' - these values have not been reconciled against consumer-facing SB/EOC documents (PBP-bid-faithful only). |
| confidence_tier | varchar(23) | Constant = 'source_faithful' - every value is a faithful read of the CMS bid, with no inference/override applied. |
| coinsurance_pct_max | double |  |
| copay_amount_max | double |  |
| cost_share_populated_in_bid | boolean | Boolean: was a cost-share value present in the bid for this row. False for 876,965 (72%), True for 331,963. When False, NULL copay/coinsurance means 'not stated', not 'free'. |
| source_amount_column | string | Provenance: the exact raw PBP column expression the benefit_amount came from, e.g. coalesce(pbp_b17b_indv_maxplan_amt_egl, pbp_b17b_comb_maxplan_amt). Lets you trace any number to the underlying bid field. |
| source_period_column | string | Provenance: the raw PBP column expression behind benefit_amount_period_code (e.g. coalesce(pbp_b17b_indv_maxplan_per_egl, pbp_b17b_comb_maxplan_per)). |
| vision_amount_source | string |  |
| vision_auth_raw | string | Raw CMS authorization code. Pre-2018 it is a 5-position multiselect bitmask (e.g. '01000','00111'); 2018+ it is binary '1'(yes)/'2'(no). This is why auth_required is often NULL - the bitmask can't be cleanly binarized. |
| vision_auth_source_status | varchar(43) | Tags how vision_auth_raw should be read: 'pre_2018_multiselect_or_post_2018_binary_12', 'post_2018_binary_12', or 'pre_2018_multiselect_interpreted'. Use it to decide whether auth_required is reliable. |
| vision_benefit_family | string | Coarse grouping: 'eyewear' (1,007,440 = the 5 B17B sub-benefits) or 'eye_exam' (201,488 = B17A). 1:1 with source_pbp_section. |
| vision_benefit_type_code | string | CMS benefit-type code: 1=Additional, 2=Mandatory, 3=Optional, 4=Both. NULL for 647,461 rows (the eye-exam family and rows where CMS did not carry the code). |
| vision_benefit_type_label | string | Decoded label for vision_benefit_type_code (Mandatory/Optional/Additional/Both). NULL wherever the code is NULL. |
| vision_cap_present | boolean | Tri-state: TRUE a per-period dollar cap applies, FALSE no cap, NULL unknown. Note TRUE can co-occur with a NULL benefit_amount when the cap is shared/recorded elsewhere - read together with vision_cap_source. |
| vision_cap_reference_code | string | How the cap is expressed: 2='Plan-specified amount per period' (the common case), 1='Covered under Eye Exams Category 17a' (cap deferred to the exam section). NULL when no cap reference. |
| vision_cap_reference_label | string | Decoded label for vision_cap_reference_code. |
| vision_cap_source | string | Which underlying cap this row's amount represents: 'combined_eyewear_cap' (517K - the SHARED cap repeated across the 5 eyewear rows), 'eye_exam_max_plan_benefit', 'legacy_eyewear_max_plan_benefit', or individual_* caps (per-component). THE key field for deciding whether two rows' caps are the same physical cap. |
| vision_copay_amount_max | double |  |
| vision_eyewear_combined_cap_description | string |  |
| vision_eyewear_max_amount | double |  |
| vision_eyewear_max_basis | string |  |
| vision_eyewear_max_filed | boolean |  |
| vision_eyewear_max_period_code | string |  |
| vision_glasses_offered | boolean |  |
| vision_limit_period_description | string |  |
| vision_limit_unlimited | boolean | Tri-state: TRUE the benefit is unlimited (no frequency cap, code '1'), FALSE limited ('2'), NULL not stated (687,937 rows). 214,881 unlimited. |
| vision_limit_unlimited_code | string | Raw CMS code behind vision_limit_unlimited: '1'=True/unlimited, '2'=False/limited. |
| vision_limit_unlimited_label | string | Decoded label ('True'/'False') for the unlimited flag. |
| vision_offered | boolean | Primary 'is this vision sub-benefit offered' boolean. TRUE 780,738 / FALSE 371,592 / NULL 56,598 (=offered_unknown). USE THIS BOOLEAN, not the label, for filtering. |
| vision_offered_code | string | Raw CMS offered code: '1'=offered, '2'=not offered. Consistent across years (unlike the label). |
| vision_offered_label | varchar(5) | Decoded label - INCONSISTENT across years ('True'/'False' in some, 'Yes'/'No' in others) for the same code. Do not group on this; group on vision_offered or vision_offered_code. |
| vision_package_id | string |  |
| vision_package_match_basis | string |  |
| vision_package_name | string |  |
| vision_package_type_code | string |  |
| vision_package_type_label | string |  |
| vision_pooled_group_amount | double |  |
| vision_pooled_group_category_codes | string |  |
| vision_pooled_group_count | bigint |  |
| vision_pooled_group_equals_b17a_cap | boolean |  |
| vision_pooled_group_index | int |  |
| vision_pooled_group_name | string |  |
| vision_pooled_group_period_code | string |  |
| vision_section_d_category_codes | string |  |
| vision_section_d_group_index | int |  |
| vision_section_d_group_name | string |  |
| vision_service_name | string |  |
| vision_sub_benefit | string | The specific vision benefit line - the grain driver. Exactly 6 values, each on 201,488 rows: routine_eye_exam (B17A), contact_lenses, eyeglass_frames, eyeglass_lenses, eyeglasses_lenses_and_frames, eyewear_upgrades (all B17B). |
| vision_targeting | varchar(36) |  |
| source_pbp_section | string | Source CMS PBP subsection: 'B17A' (eye exams, 201,488 rows) or 'B17B' (eyewear, 1,007,440). Deterministic from vision_sub_benefit / vision_benefit_family. |
| source_silver_table | varchar(59) | Constant = 'medicare_silver.pbp_section_b17_eye_exams_wear' - the single silver table this view is built from. |
| lmi_authorities | string | Placeholder for Local-Market-Intelligence source authorities. EMPTY STRING '' for 100% of rows - not yet populated for vision (carries no information today). |
| source_url | string | Provenance: the exact CMS PBP ZIP this row was extracted from (e.g. https://www.cms.gov/files/zip/pbp-benefits-2023-quarter-4.zip). 40 distinct URLs = 40 distinct snapshots, 1:1 with (report_year, quarter) and with sha256_content_hash. |
| sha256_content_hash | string | Content hash of the source ZIP for integrity/lineage. 40 distinct, perfectly 1:1 with source_url and (report_year, quarter). |
| vendor_tier_class | string |  |
| vendor_tier_standard_max | double |  |
| vendor_tier_standard_max_period | string |  |
| vendor_tier_standard_max_annual | double |  |
| vendor_tier_preferred_max | double |  |
| vendor_tier_preferred_max_period | string |  |
| vendor_tier_preferred_max_annual | double |  |
| vendor_tier_preferred_vendor | string |  |
| vendor_tier_filed_max_annual | double |  |
| vendor_tier_filed_tier | string |  |
| vendor_tier_headline_max | double |  |
| vendor_tier_headline_basis | string |  |
| vendor_tier_source | string |  |
| vendor_tier_evidence | string |  |

Tools that read it: get_pbp_benefit

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