# Medicare Plan Finder — optional supplemental benefits (annual)

`medicare_silver.mpf_optional_supplemental_annual` · Medicare Advantage, Part D, Medicaid · status live

Medicare Plan Finder optional supplemental benefit packages by plan and year: package, service category, whether offered, cost-share type and amount. The optional-supplemental benefit detail layer for MA plans.

**Grain.** One row per MA plan-segment per optional-supplemental PACKAGE per benefit SERVICE-CATEGORY per COST-SHARING TYPE, per contract year. Example: Humana H0028/013/000, 2022, "Package #1", "Preventive dental", "Monthly Premium" = $22.90 is one row; the matching "Deductible" line for that same package+service is a separate row. The minimal verified key is contract_id + plan_id + segment_id + contract_year + package_description + package_number + services_category + type_of_cost_sharing, which yields 49,544 unique combinations. The raw table is 57,780 rows because contract years 2022 and 2023 contain ~8,236 EXACT byte-for-byte duplicate rows (a source/parse artifact); de-duplicating on the business columns recovers the true 49,544-row grain.

**How to use it.** Compare the buy-up cost of a supplemental benefit across plans for a year. Example — added monthly premium for comprehensive dental, 2026: SELECT DISTINCT org_name, contract_id, plan_id, package_description, cost_share FROM medicare_silver.mpf_optional_supplemental_annual WHERE contract_year=2026 AND lower(services_category) LIKE '%comprehensive dental%' AND type_of_cost_sharing='Monthly Premium'. Use SELECT DISTINCT (to drop the 2022/2023 exact-dup artifact), filter to one type_of_cost_sharing so cost_share has consistent units, and strip '$'/handle 'N/A' before treating cost_share as a number. Answers: 'which MA plans offer a comprehensive-dental buy-up and what extra monthly premium / deductible / annual cap do they charge?'

**Traps.**
- GRAIN IS POLLUTED BY EXACT DUPLICATE ROWS: the table has 57,780 raw rows but only 49,544 distinct business rows. Contract years 2022 and 2023 contain ~8,236 byte-for-byte identical rows (every column equal — verified by counting DISTINCT across all business columns). There is NO disambiguating column. ALWAYS SELECT DISTINCT on the business columns (or GROUP BY the grain) before counting/aggregating, or you will over-count plans, premiums, and benefit counts in 2022/2023.
- TWO INCOMPATIBLE FORMAT ERAS sharing one schema. OLD format (2020-2023): package_number, nmc_services_offered, and maximum_plan_periodicity are 100% NULL; packages are only the generic 'Package #1..#5'. NEW format (2025-2026): those three columns are populated, package_description holds real plan-specific names, and a third cost-sharing type 'Maximum Plan Coverage' (annual cap) appears. Any query spanning years must handle both shapes; the grain key needs BOTH package_description (old) and package_number (new).
- 2024 IS MISSING. Years present are 2020, 2021, 2022, 2023, 2025, 2026 — there is a hole at 2024 (the cy-2024 CMS file was never ingested; source_url list confirms it). Trend/year-over-year analysis must not assume contiguous years.
- cost_share IS A STRING WITH UNITS THAT DEPEND ON ANOTHER COLUMN. Its meaning (monthly premium $ vs deductible $ vs annual coverage cap $) is set by type_of_cost_sharing, and its formatting is inconsistent across years (bare '22.9' vs '$44.00'), with 'N/A' meaning zero/none. You must filter to a single type_of_cost_sharing and strip '$'/handle 'N/A' before any arithmetic — never AVG/SUM cost_share raw.
- NO is_current FLAG AND NO SNAPSHOT HISTORY. cms_vintage_sequence, parse_revision are constant=1 and asserted_at is constant (2026-05-16) for every row — this is a single static backfill, one source file per year. Do not look for a current-snapshot filter; the period columns (observed_period, effective_period_start/end) are just contract_year restated and carry no independent information.
- NULLS CARRY MEANING, NOT MISSINGNESS. maximum_plan_periodicity is NULL except on 'Maximum Plan Coverage' rows (NULL = 'not a coverage-cap row'); package_number/nmc_services_offered NULL = 'old-format year, use package_description'. services_category and plan_type_desc have case/wording variants across years that must be normalized before grouping (e.g. 'Comprehensive dental' vs 'Comprehensive Dental').

| column | type | meaning |
|---|---|---|
| org_name | string | Marketing/parent organization name (e.g. 'Humana', 'UnitedHealthcare', 'Anthem Blue Cross and Blue Shield', 'Kaiser Permanente'). 241 distinct values. Free-text label, NOT a stable key — the same insurer appears under multiple spellings/sub-brands ('Anthem Blue Cross' vs 'Anthem Blue Cross and Blue Shield'). Join/group on contract_id for the stable carrier identity, not org_name. |
| contract_id | string | CMS Medicare Advantage contract ID, e.g. 'H0028', 'H8849' (H/R/S/E prefix). 326 distinct. Part of the plan identity key. Alone it is NOT the grain (one contract has many plans/packages). |
| plan_id | string | Plan benefit package number within the contract, 3-char zero-padded string e.g. '013', '008', '017'. 331 distinct values. contract_id + plan_id + segment_id together identify the MA plan. |
| segment_id | string | Plan segment ID, 3-char zero-padded e.g. '000', '001'. Most plans are '000'; non-zero segments are geographic/service-area sub-divisions of the same plan. Only 17 distinct values but it IS load-bearing for the grain (dropping it collapses 49,544 -> 43,476 rows). Treat '000' as 'no segmentation'. |
| contract_year | int | Calendar/plan year the benefit applies to (int): 2020, 2021, 2022, 2023, 2025, 2026. This is the real time dimension and the partition by source file (one CMS zip per year). 2024 is absent. |
| plan_type_desc | string | MA plan type, free-text and INCONSISTENTLY coded across format eras: old years use long labels ('Local Preferred Provider Organization', 'Private Fee for Service', '1876 Cost') while new years use short codes ('Local PPO', 'PFFS', 'MSA') for the same types. Top value 'HMO'. Normalize before grouping (e.g. 'Local Preferred Provider Organization' == 'Local PPO'). |
| package_number | string | Optional-supplemental package number, e.g. '001','002','003'. POPULATED ONLY IN NEW-FORMAT YEARS 2025-2026 (NULL for all 2020-2023 rows; ~48,446 of 57,780 rows are NULL). NULL here means 'old format, use package_description instead'. Load-bearing for the grain together with package_description. |
| package_description | string | Human-readable name of the buy-up package. Old years (2020-2023) only have generic 'Package #1'..'Package #5'. New years (2025-2026) carry real plan-specific names ('MyOption DEN972', 'Dental Platinum Rider', 'Dental Gold/Silver', 'NCAL Advantage Plus'). Load-bearing: dropping it collapses 49,544 -> 36,870 because old years key on it and some new-year packages differ only by name. |
| services_category | string | The supplemental benefit category this row prices: dental (Comprehensive / Preventive / Diagnostic and Preventive), Eyewear, Eye exams, Hearing aids/exam, Acupuncture, Chiropractic, Transportation, Wellness/fitness, OTC items, Meal benefit, Point of Service (POS), Worldwide emergency, etc. CASE/WORDING VARIES BY YEAR ('Comprehensive dental' vs 'Comprehensive Dental', 'Hearing aids' vs 'Hearing Aids') — lower/normalize before grouping. Part of the grain. |
| nmc_services_offered | string | The specific CMS PBP benefit line-items included in this service category, as a comma-joined code:label string (e.g. '16c1: Restorative Services, 16c2: Endodontics, ...' for comprehensive dental; '17b1: Contact Lenses, 17b2: Eyeglasses...' for eyewear; '18b1: Prescription Hearing Aids'; 'POS: POS option'). The 16x/17x/18x prefixes map to CMS PBP service-area sections. POPULATED ONLY IN 2025-2026 (NULL for 2020-2023). Descriptive detail of the package, NOT needed for uniqueness. |
| type_of_cost_sharing | string | Which price dimension this row reports. Exactly 3 values: 'Monthly Premium' (added $/month to buy the package, 31,651 rows), 'Deductible' (annual deductible, 24,434 rows), and 'Maximum Plan Coverage' (annual benefit dollar cap, 1,695 rows, NEW FORMAT 2025-2026 ONLY). Part of the grain — a plan-package-service appears as multiple rows, one per cost-sharing type. The cost_share value's UNITS depend entirely on this column. |
| cost_share | string | The dollar amount, stored as a MESSY STRING (not numeric) whose meaning is set by type_of_cost_sharing: added monthly premium, the deductible, or the annual coverage cap. Formatting is INCONSISTENT across years — bare numbers ('22.9', '38') in old years vs currency strings ('$44.00', '$1500.00') in new years. 'N/A' is common for Deductible (22,387 rows = no deductible). Must be parsed/cleaned (strip '$', handle 'N/A') before any math. |
| maximum_plan_periodicity | string | The period over which a 'Maximum Plan Coverage' cap applies — values 'Every year', 'Other, Describe', or NULL. Meaningful ONLY for type_of_cost_sharing='Maximum Plan Coverage' rows; NULL (56,085 of 57,780 rows) for Monthly Premium / Deductible rows and for all pre-2025 years. NULL here carries the meaning 'not a coverage-cap row'. Not needed for grain uniqueness. |
| observed_period | date | Provenance date = Jan 1 of contract_year (always). 100% redundant with contract_year. Carries no extra information; do not use as a separate time axis. |
| asserted_at | date | Date Vlada ingested/asserted the record. Constant '2026-05-16' for ALL 57,780 rows (single backfill load). Not a per-record update timestamp. |
| effective_period_start | date | Benefit-year start = Jan 1 of contract_year (always). The profiler picks this as the time column, but it is just contract_year restated. 6 distinct values matching the 6 years. |
| effective_period_end | date | Benefit-year end = Dec 31 of contract_year (always). Redundant with contract_year. |
| cms_vintage_sequence | int | Intended source-vintage counter, but constant = 1 for every row. There is NO multi-vintage / snapshot history and NO is_current flag in this table — only one ingest of one file per year exists, so no snapshot dedup is needed. |
| parse_revision | int | Parser revision number, constant = 1 for every row. No re-parse history present. |
| parser_artifact_hash | string | SHA-256 of the parser artifact/build that produced the rows. One distinct value per contract_year (the parser used for that year's file). Provenance/lineage only. |
| source_url | string | CMS download URL of the source ZIP, exactly one per year, of the form https://www.cms.gov/files/zip/cy-<YEAR>-mpf-benefit-summary.zip. Confirms one official CMS MPF Benefit Summary file per contract_year (and confirms 2024 is missing). |
| sha256_content_hash | string | SHA-256 of the source file content. One distinct hash per contract_year — i.e. one source file per year. Provenance/integrity only, not a row key. |

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