Medicare Advantage, Part D, Medicaid

PBP otc (cleaned view)

medicare_semantic.vw_pbp_otc·live·semantic_view·annual

Rows

N/A

Columns

103

History

2010 to 2026

Card

yesgrain, traps, filters

Typed tools

What it is

1A long-history (2010-2026) Medicare Advantage plan-benefit-package (PBP) view describing each plan's Over-The-Counter (OTC) supplemental benefit, sourced from the public CMS PBP PUF (section B13).
2For every MA contract/plan/segment it records, per OTC component, whether the benefit is offered, its dollar allowance + period (when the plan files one), cost-share, quantity limits, and a rich set of CMS indicator flags (cap present, carry-forward, delivery mode, NRT/naloxone, covered-list).
3It is the OTC slice of a normalized PBP semantic layer for analyzing MA supplemental-benefit generosity and cost.

Grain

Key Grain

Key	Grain
`contract_idplan_idsegment_idreport_yearquarterotc_sub_benefit`	1One row per MA plan-segment per PBP report year per intra-year release snapshot (quarter) per OTC sub-benefit component. 2Verified: COUNT(*) = 503,753 exactly equals COUNT(DISTINCT contract_id, plan_id, segment_id, report_year, quarter, otc_sub_benefit), and a GROUP BY on those 6 columns yields max 1 row per group across all 503,753 groups. 3A plan-snapshot fans out into up to 4 component rows (otc_list_coverage, otc_items_allowance, otc_nicotine_replacement_therapy, otc_naloxone). version is constant within each grain group (not a key). 4Only otc_items_allowance carries a dollar amount; the other three components are indicator-only.

contract_idplan_idsegment_idreport_yearquarterotc_sub_benefit

1One row per MA plan-segment per PBP report year per intra-year release snapshot (quarter) per OTC sub-benefit component.
2Verified: COUNT(*) = 503,753 exactly equals COUNT(DISTINCT contract_id, plan_id, segment_id, report_year, quarter, otc_sub_benefit), and a GROUP BY on those 6 columns yields max 1 row per group across all 503,753 groups.
3A plan-snapshot fans out into up to 4 component rows (otc_list_coverage, otc_items_allowance, otc_nicotine_replacement_therapy, otc_naloxone). version is constant within each grain group (not a key).
4Only otc_items_allowance carries a dollar amount; the other three components are indicator-only.

How to read it

1Get current-year OTC allowance generosity per MA plan: SELECT contract_id, plan_id, segment_id, benefit_amount, benefit_amount_period_label, benefit_amount_annualized, benefit_amount_pmpm FROM vw_pbp_otc WHERE report_year=2026 AND quarter=(SELECT MAX(quarter) FROM vw_pbp_otc WHERE report_year=2026) AND otc_sub_benefit='otc_items_allowance' AND benefit_amount IS NOT NULL ORDER BY benefit_amount_annualized DESC — answers 'which 2026 Medicare Advantage plans offer the richest OTC dollar allowance, normalized to an annual figure.' Filtering to MAX(quarter) avoids snapshot double-counting; restricting to otc_items_allowance is required because only that component holds dollars.

Traps

1Grain is 6 columns including quarter and otc_sub_benefit. (contract_id, plan_id, segment_id) is NOT unique (only 92,166 plan-year keys vs 503,753 rows). Each plan-snapshot fans into up to 4 sub-benefit rows; do not treat one plan as one row.
2There is NO is_current/snapshot flag. 'quarter' is an intra-year CMS PUF republication, and snapshot years (2018, 2019, 2023 = quarters 1-4; 2024 = 1,3,4; 2020 = 1,2,4) repeat the SAME plan data each quarter (amounts are stable across quarters, max distinct amount per plan-year = 1). Counting all quarters 2-4x inflates plan counts. To get one current row per plan-year, filter to MAX(quarter) within each report_year.
3Dollars live on ONE sub-benefit only: benefit_amount / _annualized / _pmpm are non-null exclusively on otc_sub_benefit='otc_items_allowance' (and even there only ~47%). The other three components (otc_list_coverage, otc_nicotine_replacement_therapy, otc_naloxone) are indicator-only and carry $0/null amounts by design — never SUM benefit_amount across sub-benefits or read OTC spend off the list/NRT/naloxone rows.
4A null benefit_amount usually does NOT mean missing data — read null_amount_reason: 'in_kind_or_vendor_administered' (250,819 rows; OTC delivered via vendor card so no filed $), 'unlimited', 'not_offered', or 'unknown_requires_crosswalk'. offered_status='offered_unknown' (160k rows) also means the offered flag is genuinely indeterminate, not absent.
5Sub-benefit availability is year-gated: otc_nicotine_replacement_therapy rows exist only report_year>=2019, otc_naloxone only 2025-2026. Their indicator columns (otc_nrt_indicator, otc_naloxone_indicator) are null for ~85-98% of the table because those rows simply don't exist in earlier years — not because the value is missing.
6Several columns are degenerate/empty and carry no signal in this view: benefit_category (always 'otc'), network_type (always 'in_network'), confidence_tier (always 'source_faithful'), consumer_document_reconciliation_status (always 'not_checked'), and lmi_authorities (always ''). Don't filter on them expecting variation.
7Use report_year as the time axis, NOT quarter — the profiler mislabels 'quarter' as the time column. Period labels like 'Every three months' refer to the allowance refill period, not a calendar quarter; annualize via benefit_amount_annualized to compare plans.

Columns (103)

Column	Type	Meaning
`contract_id`	string	CMS MA contract id, e.g. H0104, H3347, E5088 (E-prefix = employer/EGWP). 1,680 distinct. Part of grain.
`plan_id`	string	Plan id within the contract, e.g. 001, 007, 027 (zero-padded string). 656 distinct values; only unique together with contract_id+segment_id. Part of grain.
`segment_id`	string	Plan benefit segment, mostly '0'; 26 distinct. Part of grain — identifies the PBP segment when a plan is segmented by geography.
`version`	double	PBP submission/bid version number (double, 1-26). Constant within a grain group (one version per plan-snapshot); NOT a grain key. Reflects which bid revision the row came from.
`report_year`	double	Contract/benefit year the PBP applies to (2010-2026). THE real history axis of this table (17 years). Use this, not 'quarter', for time-series.
`quarter`	double	Intra-year CMS PUF release snapshot (1-4), NOT a separate benefit period. Older years have only quarter=1; snapshot years (2018/2019/2023 have 1-4, 2020/2024 have 3) republish the SAME plan-year data each quarter. A trap: counting all quarters 2-4x-inflates plans. Dedup by MAX(quarter) per plan-year to get one current snapshot.
`benefit_category`	string	Always 'otc' — this view is the OTC slice only. Degenerate single-value column.
`cost_share_string`	string	Raw cost-share text as filed. Null in nearly all rows (OTC is usually $0 cost-share / allowance-based).
`copay_amount`	double	Generic per-visit copay from the PBP cost-share block. Largely null for OTC; the OTC-specific copay lives in otc_copay_amount_min/max.
`coinsurance_pct`	double	Generic coinsurance percent. Largely null for OTC.
`limit_text`	string	Raw free-text limit description from the bid. Largely null.
`auth_required`	boolean	Boolean: prior authorization required. Largely null for OTC.
`referral_required`	boolean	Boolean: referral required. Largely null for OTC.
`network_type`	varchar(10)	Always 'in_network' — degenerate single-value column for this view (OTC is in-network only).
`max_oop_applies`	boolean	Boolean: does this benefit count toward the plan MOOP. Largely null.
`limit_quantity`	double	Numeric quantity limit on the benefit (with limit_unit/limit_period). Sparsely populated.
`limit_unit`	string	Unit for limit_quantity (e.g. items/dollars). Sparsely populated.
`limit_period_code`	string	CMS code for the limit period (e.g. per month/quarter/year). Raw code paired with limit_period_label.
`limit_period_label`	string	Human label for limit_period_code, e.g. 'Every three months'.
`limit_quantity_per_year`	double	limit_quantity normalized to an annual figure. Sparsely populated.
`benefit_amount`	double	The OTC dollar allowance AS FILED for its period (e.g. 950.0 = $950 'Every three months'). Populated ONLY on otc_items_allowance rows, and there only ~47% (83,457 of 179,479) — null elsewhere. Range $0-$3,900; median ~$57. THE core cost figure.
`benefit_amount_kind`	varchar(14)	Classifies the amount: 'otc_allowance' (179,479 rows = the otc_items_allowance component, may still have null amount) vs 'indicator_only' (324,274 rows = the 3 non-dollar components). Tells you whether a dollar value is even possible for the row.
`benefit_amount_period_code`	string	CMS code for the allowance period. Raw code paired with the label below.
`benefit_amount_period_label`	string	Period the benefit_amount covers: 'Every three months' (most common), 'Every month', 'Every year', 'Every six months', 'Every three years'. Drives annualization.
`benefit_amount_annualized`	double	benefit_amount normalized to a yearly dollar figure (e.g. $950/quarter -> $3,800/yr). Range $0-$5,136. Use THIS to compare allowances across plans on equal footing.
`benefit_amount_pmpm`	double	Allowance expressed per-member-per-month (annualized/12, e.g. $3,800/yr -> $316.67). Convenience normalization for cost modeling.
`normalization_status`	varchar(36)	How the amount was derived: 'normalized' (83,457, has a clean dollar value), 'component_indicator_only' (324,274, no dollar by design), 'not_populated' (90,807, allowance component but bid left it blank), 'offered_no_numeric_allowance' (5,215, offered but unlimited/non-numeric).
`normalization_basis`	varchar(24)	What field the value/indicator was built from, e.g. 'cms_indicator_field'. Provenance of the normalization.
`aggregation_policy`	varchar(60)	How this row should be aggregated, e.g. 'indicator_component_not_financial_cap' — warns that indicator rows must not be summed as if they were dollar caps.
`offered_status`	varchar(15)	Tri-state offered flag for the component: 'offered' (188,207), 'not_offered' (155,354), 'offered_unknown' (160,192, offered flag indeterminate in the bid). Key column for 'does this plan have OTC'.
`numeric_amount_observability`	varchar(31)	Why a dollar amount is/ isn't present: 'all_offered_numeric' (83,457, clean dollars), 'offered_without_numeric_amounts' (264,942, offered but vendor/in-kind so no $), 'not_offered' (155,354).
`null_amount_reason`	varchar(30)	Explains a null benefit_amount: 'in_kind_or_vendor_administered' (250,819 — OTC run through a vendor card, no filed $), 'not_offered' (155,354), 'not_null_numeric_amount' (83,457 — i.e. amount IS present), 'unlimited' (5,215), 'unknown_requires_crosswalk' (8,908). A null amount is NOT missing data — read this column.
`annualization_policy`	varchar(17)	Rule applied to annualize, e.g. 'not_numeric' for indicator rows. Governs benefit_amount_annualized.
`combined_limit_policy`	varchar(29)	Whether this benefit shares a limit with another, e.g. 'none_detected'. Provenance/aggregation guard.
`consumer_document_reconciliation_status`	varchar(11)	Always 'not_checked' — placeholder; these PBP values have not been cross-checked against consumer SB/EOC documents. Degenerate single-value column.
`confidence_tier`	varchar(23)	Always 'source_faithful' — every row is a faithful copy of the CMS bid (no inference). Degenerate single-value column.
`cost_share_populated_in_bid`	boolean	Boolean: did the bid populate a cost-share block for this benefit. Mostly false for OTC (allowance-based, $0 cost-share).
`otc_amount_source`	string
`otc_base_offered`	boolean	Plan-level boolean: does the plan offer a base OTC benefit at all (distinct from the per-component otc_offered). Yes 261,661 / No 217,423 / null 24,669.
`otc_base_offered_code`	string	Raw CMS code behind otc_base_offered ('1'=Yes,'2'=No).
`otc_base_offered_label`	varchar(3)	Human label for otc_base_offered ('Yes'/'No').
`otc_benefit_family`	varchar(16)	Which family this row's component belongs to: 'otc_list' (list_coverage), 'otc_items' (items_allowance), 'otc_drug_subitem' (NRT + naloxone). Mirrors otc_sub_benefit at a coarser level.
`otc_benefit_type_code`	string	CMS code for the OTC benefit type. Populated where the component is offered (~88,673 per applicable component).
`otc_benefit_type_label`	varchar(10)	Human label for otc_benefit_type_code.
`otc_cap_carry_forward`	boolean	Boolean: does an unused OTC allowance carry forward to the next period. Populated mainly on items_allowance rows.
`otc_cap_carry_forward_code`	string	Raw CMS code for carry-forward.
`otc_cap_carry_forward_label`	string	Human label for carry-forward (Yes/No).
`otc_cap_present`	boolean	Boolean: is there a spending cap on the OTC allowance. Populated on otc_items_allowance rows (88,673); null for the indicator-only components.
`otc_cap_present_code`	string	Raw CMS code for cap presence.
`otc_cap_present_label`	string	Human label for cap presence (Yes/No).
`otc_cap_source`	string	Where the cap value/flag was sourced from. Sparsely populated.
`otc_coinsurance_pct_max`	double
`otc_component_detail_status`	varchar(23)	Describes the detail level captured for the component, e.g. 'list_coverage_indicator'. Metadata about completeness of the component row.
`otc_copay_amount_max`	double	Upper bound of the OTC item copay range. When populated, almost always equals the min and is $0 (36,994 rows at 0/0); range exists for the rare plan with varying item copays.
`otc_copay_amount_min`	double	Lower bound of the OTC item copay range (mostly $0). Pair with _max; equal min=max means a single flat copay.
`otc_delivery_mode_code`	string	CMS code for OTC delivery mode (e.g. mail/retail/catalog). Very sparsely populated (~4,244 rows).
`otc_delivery_mode_label`	string	Human label for delivery mode.
`otc_in_package_aggregate_max`	boolean
`otc_list_covered`	boolean	Boolean specific to the otc_list_coverage component: is the OTC item list covered. Populated only on otc_list_coverage rows (88,673); null (415,080) on all other components by design.
`otc_list_covered_code`	string	Raw CMS code for list-covered.
`otc_list_covered_label`	string	Human label for list-covered (Yes/No); null on non-list rows.
`otc_list_source`	string	Source of the covered OTC item list. Sparsely populated.
`otc_naloxone_indicator`	boolean	Boolean specific to the otc_naloxone component: is OTC naloxone offered. Populated only on otc_naloxone rows (8,711), and that sub-benefit exists ONLY in report_year 2025-2026. Null (495,042) elsewhere.
`otc_naloxone_indicator_code`	string	Raw CMS code for naloxone indicator.
`otc_naloxone_indicator_label`	string	Human label for naloxone indicator (Yes/No).
`otc_nrt_attestation_present`	boolean	Boolean: is an NRT (nicotine replacement therapy) attestation present in the bid. Tied to the NRT component.
`otc_nrt_attestation_raw`	string	Raw NRT attestation text from the bid. Sparsely populated.
`otc_nrt_indicator`	boolean	Boolean specific to the otc_nicotine_replacement_therapy component: is OTC NRT offered. Populated only on NRT rows (75,606); the NRT sub-benefit exists only report_year 2019+. Null (428,147) elsewhere.
`otc_nrt_indicator_code`	string	Raw CMS code for NRT indicator.
`otc_nrt_indicator_label`	string	Human label for NRT indicator (Yes/No).
`otc_offered`	boolean	Row/component-level boolean: is THIS specific OTC component offered (distinct from plan-level otc_base_offered). Fill varies by component (e.g. 170,571 on items_allowance, 75,606 on NRT). Pairs with otc_offered_code/label and offered_status.
`otc_offered_code`	string	Raw CMS code for otc_offered.
`otc_offered_label`	varchar(3)	Human label for otc_offered ('Yes' 188,207 / 'No' 155,354 / null 160,192).
`otc_package_aggregate_amount`	double
`otc_package_aggregate_annualized`	double
`otc_package_aggregate_category_codes`	string
`otc_package_aggregate_period_code`	string
`otc_package_aggregate_period_label`	string
`otc_package_id`	string
`otc_package_match_basis`	string
`otc_package_name`	string
`otc_package_type_code`	string
`otc_package_type_label`	string
`otc_pooled_group_amount`	double
`otc_pooled_group_category_codes`	string
`otc_pooled_group_count`	bigint
`otc_pooled_group_index`	int
`otc_pooled_group_name`	string
`otc_pooled_group_period_code`	string
`otc_pooled_group_period_label`	string
`otc_pooled_with`	string
`otc_section_d_category_codes`	string
`otc_section_d_group_index`	int
`otc_section_d_group_name`	string
`otc_sub_benefit`	string	THE component dimension and part of grain. 4 values: 'otc_list_coverage' & 'otc_items_allowance' (all years), 'otc_nicotine_replacement_therapy' (2019+), 'otc_naloxone' (2025-2026 only). Each value drives which indicator columns are populated; only otc_items_allowance carries a dollar amount.
`otc_targeting`	varchar(36)
`source_amount_column`	string	Name of the upstream silver/PBP column the benefit_amount was read from. Lineage; null on indicator rows.
`source_period_column`	string	Name of the upstream column the period was read from. Lineage.
`source_pbp_section`	string	Which PBP section the row came from: 'B13B' (487,753 = modern format) or 'B13C' (16,000 = older 2010-era OTC layout). A format/version marker, not a benefit difference.
`source_silver_table`	varchar(59)	Always 'medicare_silver.pbp_section_b13_other_services' — the single upstream silver table feeding this view.
`lmi_authorities`	string	Intended to hold LMI (low-income / authorities) tagging, but EMPTY ('') for every row in this view. Carries no information here.
`source_url`	string	CMS download URL of the PBP PUF zip the row came from (31 distinct = 31 release files, one per report_year/quarter snapshot). Provenance.
`sha256_content_hash`	string	Content hash of the source file (31 distinct, 1:1 with source_url). Provenance/integrity, not a row key.

Query it

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

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