Medicare Advantage, Part D, Medicaid

PBP hearing (cleaned view)

medicare_semantic.vw_pbp_hearing·live·semantic_view·annual benefit years, each refreshed up to quarterly

Rows

N/A

Columns

History

2001 to 2026

Card

yesgrain, traps, filters

Typed tools

What it is

1A normalized, analytics-ready view of Medicare Advantage (Part C) HEARING benefits — exams, fittings, prescription hearing aids, and OTC hearing aids — derived from CMS Plan Benefit Package (PBP) Public Use Files section B18.
2One semantic row per plan per benefit-year per CMS release-quarter per hearing sub-benefit, carrying member cost-share (copay/coinsurance), the plan dollar allowance/cap, frequency limits, per-ear cap scope, and offered/auth flags.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquarterhearing_sub_benefit`	1One row per (contract_id, plan_id, segment_id, report_year, quarter, hearing_sub_benefit) — i.e., one row per MA plan-segment, per benefit year, per CMS PBP PUF release-quarter, per hearing sub-benefit (routine exam, aid fitting/eval, Rx hearing aid by ear-type, OTC aid, batteries). 2This 6-column set is VERIFIED unique and minimal: it yields exactly 632,961 distinct combinations = the full row_count of 632,961, and dropping ANY one of the six breaks uniqueness (e.g. dropping quarter collapses to 366,850, dropping hearing_sub_benefit to 201,488). source_pbp_section is redundant given hearing_sub_benefit; version is functionally determined by the grain (every grain row has exactly 1 version) and is NOT a key.

contract_idplan_idsegment_idreport_yearquarterhearing_sub_benefit

1One row per (contract_id, plan_id, segment_id, report_year, quarter, hearing_sub_benefit) — i.e., one row per MA plan-segment, per benefit year, per CMS PBP PUF release-quarter, per hearing sub-benefit (routine exam, aid fitting/eval, Rx hearing aid by ear-type, OTC aid, batteries).
2This 6-column set is VERIFIED unique and minimal: it yields exactly 632,961 distinct combinations = the full row_count of 632,961, and dropping ANY one of the six breaks uniqueness (e.g. dropping quarter collapses to 366,850, dropping hearing_sub_benefit to 201,488). source_pbp_section is redundant given hearing_sub_benefit; version is functionally determined by the grain (every grain row has exactly 1 version) and is NOT a key.

How to read it

1Get current MA hearing-aid allowances for 2026: SELECT contract_id, plan_id, segment_id, benefit_amount, benefit_amount_period_label, benefit_amount_annualized, hearing_cap_per_ear_label FROM medicare_semantic.vw_pbp_hearing WHERE report_year=2026 AND quarter=1 AND hearing_sub_benefit='prescription_hearing_aids_all_types' AND hearing_offered AND normalization_status='normalized'.
2Answers 'what dollar amount does each MA plan allow toward prescription hearing aids this year, and over what period'.
3(Pin a single quarter to avoid the ~4x quarterly-snapshot duplication; gate normalization_status='normalized' to get real dollar allowances; read hearing_cap_per_ear_label before comparing the dollars.)

Traps

1quarter is the CMS RELEASE quarter, not a coverage quarter. The same plan-year benefit is stored once PER quarterly re-publication, so a benefit identical all year appears as up to 4 near-duplicate rows that differ ONLY by quarter (verified: a 4-row group was byte-identical except quarter=1,2,3,4). There is NO is_current/is_latest column. To get one row per plan, pick a single (report_year, quarter) — e.g. the MAX(quarter) available for that year — before aggregating, or you will multiply-count plans by ~4x.
2version looks like a key but is NOT: it is functionally determined by the grain (exactly 1 version per grain row) and the view already selects the applicable version. A single quarter shows 16-22 distinct version values only because different plans have different amendment counts. Never join on version or add it to a dedup key.
3benefit_amount (plan dollar ALLOWANCE, e.g. $5,000 toward aids) and copay_amount/coinsurance_pct (member COST-SHARE, e.g. $50 exam copay) are two different economic concepts that mostly populate on different rows (allowance on B18B aids, copay on B18A exams). benefit_amount is NULL unless normalization_status='normalized' (~78k of 633k rows); a NULL is meaningful (see null_amount_reason: 'combined_limit_elsewhere' / 'not_offered'), not missing data. Also do NOT sum benefit_amount across a plan's hearing sub-benefits — aggregation_policy='component_or_shared_cap_do_not_sum' warns the cap may be shared. And ALWAYS read hearing_cap_per_ear_label before comparing allowances ('per ear' vs 'both ears combined' changes the effective dollar by ~2x).
4The two B18A sub-benefits routine_hearing_exam and hearing_aid_fitting_evaluation are a deterministic FAN-OUT of one source row (identical 201,488 counts, same source column, same offered/copay). They are not independent benefits — counting both double-counts B18A. Likewise replacement_batteries exists ONLY in report_year 2001 (vestigial) and otc_hearing_aids / section B18C only from 2025 onward (the OTC regulatory change), so sub-benefit availability is era-dependent.
5Many columns are DEGENERATE constants for the entire hearing view and carry zero filtering power: benefit_category='hearing', network_type='in_network', benefit_amount_kind='maximum_plan_benefit', normalization_basis='cms_periodicity_code', aggregation_policy, combined_limit_policy, consumer_document_reconciliation_status='not_checked', confidence_tier='source_faithful', source_silver_table, and lmi_authorities=''. Do not filter on them expecting variation. Conversely, NULLs in auth_required/referral_required/max_oop_applies mean 'unknown/not captured here' (raw auth lives in hearing_auth_raw), and hearing_offered_code can be NULL while hearing_offered_label='Yes' (3,803 derived OTC rows) — prefer the boolean hearing_offered as the clean coverage flag.

Columns (83)

Column	Type	Meaning
`contract_id`	string	CMS MA contract ID (e.g. H0150). 1,972 distinct. Part of grain. Join key to plan/enrollment tables.
`plan_id`	string	Plan ID within the contract (e.g. 001). 656 distinct values but NOT unique on its own. Part of grain.
`segment_id`	string	Plan segment (service-area split). Often empty string '' for pre-~2018 years and '0' later; 27 distinct. Part of grain — do NOT drop it (dropping it collapses 632,961→602,837).
`version`	double	CMS bid/PUF version number for that plan within the release-quarter (1..22+). Functionally determined by the grain (exactly 1 version per grain row) — the view already picks the applicable version, so it is a snapshot tag, NOT a join key or grain dimension. Higher version counts in a quarter just reflect how many plans had that many amendments.
`report_year`	double	Benefit/contract year the row describes (2001-2026). The primary time dimension (the profiler mislabels 'quarter' as the time column — use report_year).
`quarter`	double	CMS PBP PUF RELEASE quarter (1-4) within report_year — which quarterly re-publication this snapshot came from, NOT a quarter of coverage. Mature years have all of 1-4; 2025/2026 currently only quarter 1. Part of grain because each quarterly release is retained as a separate row even when the benefit is identical across quarters.
`benefit_category`	string	Constant literal 'hearing' for the whole view (this is the hearing slice of a broader PBP family of views).
`cost_share_string`	string	Human-readable member cost-share, e.g. '$15.0 copay'. NULL when not offered or amount not specified. Parsed into copay_amount/coinsurance_pct.
`copay_amount`	double	Member fixed-dollar copay to use the benefit (e.g. $50 for a routine hearing exam). Populated mainly for B18A exams. NULL for not_offered rows and for most aid rows that use an allowance instead.
`coinsurance_pct`	double	Member coinsurance percentage (rare — only ~2,219 rows). NULL otherwise.
`limit_text`	string	Raw frequency-limit text, e.g. '1.0 exams/3' (1 exam every 3rd period). Parsed into limit_quantity/limit_unit/limit_period_code. NULL when no limit specified.
`auth_required`	boolean	Prior-authorization required flag. Largely NULL in this view (raw auth captured in hearing_auth_raw instead); treat NULL as unknown.
`referral_required`	boolean	Referral-required flag. Largely NULL — unknown rather than false.
`network_type`	varchar(10)	DEGENERATE: constant 'in_network' for all 632,961 rows. This view carries only in-network terms.
`max_oop_applies`	boolean	Whether benefit counts toward MOOP. Largely NULL (unknown).
`limit_quantity`	double	Numeric quantity of the frequency limit (e.g. 1.0 exams). NULL when no limit.
`limit_unit`	string	Unit of the limit, e.g. 'exams', 'aids'. NULL when no limit.
`limit_period_code`	string	CMS periodicity code for the limit (e.g. '3'). Decoded by limit_period_label. NULL when no limit.
`limit_period_label`	string	Decoded limit period: 'Every year','Every three years','Every two years','Other, Describe', etc. NULL ~346,877 rows (no frequency limit captured).
`limit_quantity_per_year`	double	Limit quantity normalized to a per-year basis (e.g. 1 exam/3yr is still stored; per-year derivation). NULL when not numeric/no limit.
`benefit_amount`	double	The PLAN DOLLAR ALLOWANCE/CAP toward the benefit (kind = maximum_plan_benefit), e.g. $5,000 toward hearing aids. This is the coverage allowance, NOT member cost-share. Populated only when normalization_status='normalized' (~78k rows, mostly B18B aids); NULL otherwise (exams typically use copay instead).
`benefit_amount_kind`	string	DEGENERATE: constant 'maximum_plan_benefit' — benefit_amount is always a max plan allowance, never a deductible/other kind.
`benefit_amount_period_code`	string	CMS periodicity code for the allowance period. NULL when no allowance.
`benefit_amount_period_label`	string	Decoded allowance period: 'Every year','Every two years','Every three years', etc. NULL when no dollar allowance (~554,532).
`benefit_amount_annualized`	double	benefit_amount normalized to an annual figure (e.g. $5,000/2yr → $2,500/yr). Use this to compare allowances across plans with different cap periods. NULL when no allowance.
`benefit_amount_pmpm`	double	Per-member-per-month dollarization of the allowance. Essentially always NULL in this view.
`normalization_status`	varchar(24)	How the amount was normalized: 'normalized' (~78k, has a real benefit_amount), 'frequency_only_no_amount' (~234k, offered with a limit but no dollar amount), or 'not_populated' (~320k). Gate on ='normalized' when you need a dollar allowance.
`normalization_basis`	varchar(43)	DEGENERATE: constant 'cms_periodicity_code' — period normalization is driven by CMS periodicity codes.
`aggregation_policy`	varchar(60)	DEGENERATE: constant 'component_or_shared_cap_do_not_sum' — a WARNING flag: hearing components (exam vs fitting vs aid) may share a cap, so do NOT sum benefit_amount across the sub-benefit rows of one plan.
`offered_status`	varchar(15)	'offered' (387,680), 'not_offered' (205,787), or 'offered_unknown' (39,494). Mirrors hearing_offered. For not_offered rows all cost-share is NULL/zero; for offered_unknown nothing is populated.
`numeric_amount_observability`	varchar(31)	Why amounts are/aren't present: 'offered_without_numeric_amounts' (~351k), 'not_offered' (~206k), 'all_offered_numeric' (~76k, has benefit_amount).
`null_amount_reason`	string	Why benefit_amount is NULL: 'combined_limit_elsewhere' (shared cap, ~351k), 'not_offered' (~206k), or 'not_null_numeric_amount' (~76k = amount IS present). A NULL amount here is meaningful, not missing data.
`annualization_policy`	varchar(17)	'not_numeric' (~557k) or 'annualized_safe' (~76k, the annualized fields are trustworthy).
`combined_limit_policy`	varchar(29)	DEGENERATE: constant 'shared_across_components' — limits/caps are shared across the hearing components.
`consumer_document_reconciliation_status`	varchar(11)	DEGENERATE: constant 'not_checked' — these benefits have not been reconciled against consumer-facing plan documents.
`confidence_tier`	varchar(23)	DEGENERATE: constant 'source_faithful' — every row is a faithful transcription of the CMS source (no inference/imputation tier present).
`coinsurance_pct_max`	double
`copay_amount_max`	double
`cost_share_populated_in_bid`	boolean	Boolean: whether the cost-share fields were populated in the underlying bid. Helps distinguish a true $0/no-copay from a blank source.
`cost_share_zero_reason`	string
`hearing_aid_value_form`	string
`hearing_amount_source`	string
`hearing_auth_raw`	string	Raw CMS auth indicator string, e.g. '00001','01000' (pre-2018 multi-select bitfield) — preserved verbatim; see hearing_auth_source_status for how to read it.
`hearing_auth_source_status`	varchar(43)	How to interpret hearing_auth_raw: 'pre_2018_multiselect_or_post_2018_binary_12' (~618k) vs 'post_2025_binary_12' (~15k, OTC). Code semantics changed across eras — do not compare raw auth strings across this boundary.
`hearing_benefit_family`	string	High-level family: 'hearing_exam', 'prescription_hearing_aid', 'otc_hearing_aid', 'hearing_aid_accessory'. Coarser than hearing_sub_benefit.
`hearing_benefit_type_code`	string	CMS benefit-type code: 1=Additional, 2=Mandatory, 3=Optional, 4=Both. NULL (~293k) where the source did not classify (e.g. older/exam rows). Decoded by hearing_benefit_type_label.
`hearing_benefit_type_label`	string	Decoded benefit type: 'Mandatory'(327k),'Optional','Additional','Both'. NULL ~293k. ('Mandatory' = basic/required supplemental, 'Optional' = buy-up).
`hearing_cap_network_scope`	string
`hearing_cap_per_ear_code`	string	How the dollar cap is scoped per ear: 1=Per ear, 2=One single ear, 3=Both ears combined. NULL (~579k) when no cap. Decoded by hearing_cap_per_ear_label.
`hearing_cap_per_ear_label`	string	Decoded cap scope: 'Both ears combined','Per ear','One single ear'. NULL when no cap. CRITICAL for interpreting benefit_amount (a $2,000 'per ear' cap ≈ $4,000/both).
`hearing_cap_present`	boolean	Boolean: whether a dollar cap exists for this benefit.
`hearing_cap_reference_code`	string	What the cap references: 1=Covered under Hearing Exams Category 18a, 2=Plan-specified amount per period. NULL (~561k) when no cap.
`hearing_cap_reference_label`	string	Decoded cap reference: 'Plan-specified amount per period' (most), 'Covered under Hearing Exams Category - 18a'. NULL when no cap.
`hearing_cap_source`	varchar(37)	Which category the cap is sourced from: 'hearing_exam_category_cap' (B18A), 'prescription_hearing_aid_category_cap' (B18B), 'otc_hearing_aid_cap' (B18C). Tracks the section the cap belongs to.
`hearing_limit_unlimited`	boolean	Boolean: benefit is explicitly unlimited (no frequency cap). True ~53k.
`hearing_limit_unlimited_code`	string	CMS code 1=Yes(unlimited)/2=No. NULL (~293k) where not specified. Decoded by hearing_limit_unlimited_label.
`hearing_limit_unlimited_label`	string	'Yes'/'No' (unlimited?). NULL where not specified.
`hearing_offered`	boolean	Boolean: is this hearing sub-benefit offered by the plan. True(387,680)/False(205,787)/NULL(39,494=unknown). The primary 'is it covered' flag.
`hearing_offered_code`	string	Raw CMS offered code 1=Yes/2=No. NULL ~43,297. NOTE: can be NULL while hearing_offered_label='Yes' for 3,803 OTC rows (derived from other signals) — see gotchas.
`hearing_offered_label`	varchar(3)	'Yes'/'No' offered. Yes=387,680 / No=205,787 / NULL=39,494. Slightly more 'Yes' than the raw code has (3,803 derived) — prefer hearing_offered (boolean) as the clean flag.
`hearing_otc_indicator`	boolean	Boolean: row is an OTC hearing-aid benefit. True only ~14,990 rows (B18C, 2025+). NULL for all non-OTC rows.
`hearing_otc_indicator_code`	string	OTC code 1=Yes. NULL for non-OTC rows (~617,971).
`hearing_otc_indicator_label`	string	'Yes' for OTC rows; NULL otherwise.
`hearing_package_id`	string
`hearing_package_name`	string
`hearing_package_type_code`	string
`hearing_pooled_group_amount`	double
`hearing_pooled_group_category_codes`	string
`hearing_pooled_group_period_code`	string
`hearing_rx_otc_choice_code`	string
`hearing_rx_otc_choice_required`	boolean
`hearing_section_d_group_index`	int
`hearing_section_d_group_name`	string
`hearing_service_selected_in_18a`	boolean
`hearing_sub_benefit`	string	THE finest benefit dimension and part of grain. 8 values: routine_hearing_exam, hearing_aid_fitting_evaluation (these two are a deterministic fan-out of B18A — identical counts 201,488 each), prescription_hearing_aids_all_types, prescription_hearing_aids_over/inner/outer_ear, otc_hearing_aids (2025+), replacement_batteries (2001 ONLY).
`hearing_targeting`	varchar(36)
`source_amount_column`	string	Provenance: the raw PBP column the amount came from, e.g. 'pbp_b18a_maxplan_amt'. Maps row to its source field.
`source_period_column`	string	Provenance: the raw PBP period column, e.g. 'pbp_b18a_maxplan_per'.
`source_pbp_section`	string	PBP section: B18A (exams, 402,976), B18B (Rx aids, 214,995), B18C (OTC aids, 14,990). Redundant with hearing_sub_benefit (derivable) — NOT needed in the grain.
`source_silver_table`	varchar(63)	DEGENERATE: constant 'medicare_silver.pbp_section_b18_hearing_exams_aids' — the single upstream silver table this view is built from.
`lmi_authorities`	string	EMPTY ('') for all 632,961 rows — the LMI (lawful-monetary-interpretation/authority) annotation is not populated for the hearing view. Do not rely on it here.
`source_url`	string	The CMS PBP PUF download URL the row came from (e.g. .../pbp-benefits-2001.zip). 40 distinct = 40 file releases, 1:1 with (report_year,quarter).
`sha256_content_hash`	string	Content hash of the source file. 40 distinct, 1:1 with source_url and (report_year,quarter) — identifies the exact CMS release/snapshot for provenance.

Query it

describe_data_source(source_fqn="medicare_semantic.vw_pbp_hearing")     # the card, live
query_substrate(sql="SELECT * FROM medicare_semantic.vw_pbp_hearing WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_year = '<value>' AND quarter = '<value>' AND hearing_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_hearing")`
Raw door	Athena (read-only via query_substrate): medicare_semantic.vw_pbp_hearing
Note	Cleaned PBP reasoning surface (categories/hearing/contract.yaml); raw is pbp_section_*.

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