VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

Medicare Plan Finder — optional supplemental benefits (annual)

medicare_silver.mpf_optional_supplemental_annual·live·silver·annual·Medicare Advantage Plan Benefit Packages (PBP)
Rows
N/A
Columns
22
History
2020 (contract_year 2020 / effective_period 2020-01-01) to 2026 (contract_year 2026 / effective_period 2026-01-01). NOTE: 2024 is MISSING — years present are 2020, 2021, 2022, 2023, 2025, 2026 (the cy-2024 CMS file was not ingested).
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1CMS Medicare Plan Finder (MPF) Benefit Summary data for Medicare Advantage OPTIONAL SUPPLEMENTAL benefit packages — the extra-cost dental/vision/hearing/etc. riders a member can buy on top of their MA plan, broken out into one row per (benefit-service-category × cost-sharing-type) with the dollar cost-share.
  2. 2It is for comparing the price (added monthly premium, deductible, annual coverage cap) and scope of buy-up supplemental benefits across MA plans, organizations, and years.

Grain

KeyGrain
contract_idplan_idsegment_idcontract_yearpackage_descriptionpackage_numberservices_categorytype_of_cost_sharing
  1. 1One row per MA plan-segment per optional-supplemental PACKAGE per benefit SERVICE-CATEGORY per COST-SHARING TYPE, per contract year.
  2. 2Example: 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.
  3. 3The 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.
  4. 4The 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 read it

  1. 1Compare the buy-up cost of a supplemental benefit across plans for a year.
  2. 2Example — 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'.
  3. 3Use 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.
  4. 4Answers: 'which MA plans offer a comprehensive-dental buy-up and what extra monthly premium / deductible / annual cap do they charge?'

Traps

  1. 1GRAIN 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.
  2. 2TWO 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).
  3. 32024 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.
  4. 4cost_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.
  5. 5NO 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.
  6. 6NULLS 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').

Columns (22)

ColumnTypeMeaning
org_namestringMarketing/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_idstringCMS 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_idstringPlan 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_idstringPlan 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_yearintCalendar/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_descstringMA 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_numberstringOptional-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_descriptionstringHuman-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_categorystringThe 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_offeredstringThe 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_sharingstringWhich 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_sharestringThe 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_periodicitystringThe 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_perioddateProvenance 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_atdateDate 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_startdateBenefit-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_enddateBenefit-year end = Dec 31 of contract_year (always). Redundant with contract_year.
cms_vintage_sequenceintIntended 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_revisionintParser revision number, constant = 1 for every row. No re-parse history present.
parser_artifact_hashstringSHA-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_urlstringCMS 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_hashstringSHA-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

describe_data_source(source_fqn="medicare_silver.mpf_optional_supplemental_annual")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.mpf_optional_supplemental_annual WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND contract_year = '<value>' AND package_description = '<value>' AND package_number = '<value>' AND services_category = '<value>' AND type_of_cost_sharing = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keycontract_id,plan_id,segment_id
Joinhow_to_join(source_fqn="medicare_silver.mpf_optional_supplemental_annual")
Raw doorAthena (read-only via query_substrate): medicare_silver.mpf_optional_supplemental_annual
Company namesorg_name resolve to a permanent Vlada ID: resolve_entity(text=<name>); the company pages hold owners and holdings

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