Medicare Advantage, Part D, Medicaid

PBP vision (cleaned view)

medicare_semantic.vw_pbp_vision·live·semantic_view·annual

Rows

N/A

Columns

102

History

2001 (report_year) to 2026 (report_year)

Card

yesgrain, traps, filters

Typed tools

What it is

1A semantic (gold) view of Medicare Advantage VISION benefits drawn from CMS Plan Benefit Package (PBP) section B17 (B17A eye exams + B17B eyewear).
2It exposes, per MA plan and per vision sub-benefit, whether the benefit is offered, its maximum-plan-benefit dollar cap, frequency limits, prior-auth/referral rules, and member cost-share (copay/coinsurance) - all normalized from raw CMS bid codes with heavy provenance/normalization metadata.
3Built for agents pricing or comparing MA supplemental vision coverage.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquartervision_sub_benefit`	1One row per Medicare Advantage plan segment per vision sub-benefit per CMS release snapshot. 2Verified 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. 3Each plan-snapshot fans out to exactly 6 sub-benefit rows (1 eye-exam from B17A + 5 eyewear from B17B). 4`quarter` is the within-year CMS release selector (the same plan-year reappears in up to 4 quarterly republished ZIPs); 5`version` is a redundant per-bid attribute and is NOT part of the key.

contract_idplan_idsegment_idreport_yearquartervision_sub_benefit

1One row per Medicare Advantage plan segment per vision sub-benefit per CMS release snapshot.
2Verified 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.
3Each plan-snapshot fans out to exactly 6 sub-benefit rows (1 eye-exam from B17A + 5 eyewear from B17B).
4`quarter` is the within-year CMS release selector (the same plan-year reappears in up to 4 quarterly republished ZIPs);
5`version` is a redundant per-bid attribute and is NOT part of the key.

How to read it

1Find 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.
2This 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.
3For multi-snapshot years, add a MAX(quarter) filter to dedup to the latest release.

Traps

1DO 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.
2There 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.
3Periodicity 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.
4Most 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.
5NULLs 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.

Columns (102)

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

Query it

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

Item	Value
Typed tools that read it	get_pbp_benefit
Identity key	`contract_id,plan_id,segment_id`
Join	`how_to_join(source_fqn="medicare_semantic.vw_pbp_vision")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.vw_pbp_vision
Note	Cleaned PBP reasoning surface (categories/vision/contract.yaml); raw is pbp_section_*.

This page as markdown: /docs-md/data/medicare_semantic_vw_pbp_vision.md · every page in one file: /llms-full.txt