# MA mpf benefit summary annual

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

One row per (contract, plan, segment, year, service category, service). Cost_share is human-readable (e.g. "$25 copay", "20% coinsurance", "$0 copay", "Not covered"). Includes Original Medicare baseline rows for FFS comparison. Categories f

**Grain.** One row per (contract_id, plan_id, segment_id, contract_year) plan-year x (category_name, service_desc, network_description) benefit line x distinct cost_share/flag variant. IMPORTANT: this table has NO unique key. Even using every plan key AND every descriptive column (org_name, plan_type_desc, category_name, service_desc, cost_share, network_description, limit_flag, authorization_flag, referral_flag), the physical 2,775,723 rows collapse to only 2,240,145 distinct combinations -- 535,578 rows are exact byte-for-byte duplicates (up to 7 identical copies of the same plan-benefit line, e.g. a Kaiser H0524/043 2021 'Comprehensive dental / Not covered' row appears 7x). The duplication comes from a finer source grain (plan x county/service-area) that was flattened away and not carried into this table. Treat the grain as the deduped logical row above and ALWAYS query with SELECT DISTINCT or GROUP BY; do not COUNT(*) raw rows as benefit lines.

**How to use it.** "What does each 2026 PPO plan charge for an in-network specialist visit?" -> SELECT DISTINCT org_name, contract_id, plan_id, segment_id, cost_share, authorization_flag, referral_flag FROM mpf_benefit_summary_annual WHERE contract_year=2026 AND category_name='Doctor visits' AND service_desc='Specialist' AND network_description='In-Network'. Use SELECT DISTINCT (not COUNT(*)) because of the duplicate rows, and note 2026 encodes the auth/referral flags as 'Yes'/'No'/'N/A' whereas 2020-2024 use '0'/'1'/'2' and 2025 uses '0.0'/'1.0'/'2.0'. Join to other mpf_/Stars tables on (contract_id, plan_id, segment_id, contract_year); the synthetic 'Original Medicare' contract H0001 is the FFS baseline for benchmarking.

**Traps.**
- NO unique key exists. Physical rows (2,775,723) exceed distinct combinations of plan-key + ALL descriptive columns (2,240,145) by 535,578 -- up to 7 byte-identical duplicate rows per plan-benefit line. The source grain is finer (plan x county/service-area) and was flattened away. ALWAYS SELECT DISTINCT / GROUP BY; never COUNT(*) raw rows as a benefit or plan count.
- The auth/referral/limit flags are encoded DIFFERENTLY in every era: '0'/'1'/'2' (2020-2024), '0.0'/'1.0'/'2.0' (2025), and 'No'/'Yes'/'N/A' (2026, auth/referral only). Decode 0=No, 1=Yes, 2=N/A. Cross-year filters/aggregations on these flags are WRONG unless you normalize the encodings first.
- plan_type_desc AND category_name are free-text and vintage-drifted -- the same plan-type or benefit category appears under multiple spellings/casings across years ('Local Preferred Provider Organization' vs 'Local PPO'; 'Comprehensive dental' vs 'Comprehensive Dental'). Canonicalize (lower/trim/map) before grouping or you will split one category/plan-type into several.
- cost_share is FREE TEXT, not numeric, and 0% null: it mixes percentages, dollar copays, deductible/MOOP amounts, prose ('Not covered'), and even embedded HTML ('<br />'); the same logical value differs by year ('$0' vs '0' vs '0.0'). You must parse it to get numbers.
- Single snapshot, no versioning: asserted_at=2026-05-16, cms_vintage_sequence=1, parse_revision=1 for every row, and there is NO is_current column. The provenance columns (sha256/source_url/parser hashes) only vary by contract_year (one file each) and cannot be used to dedup the within-year duplicate rows or to pick a 'latest' version.
- contract H0001 / plan 001 / segment 000 labelled 'Original Medicare' is a SYNTHETIC fee-for-service baseline injected by CMS for comparison (present 2021-2026, ~36-37 rows/yr), not a real MA plan -- include it for benchmarking, exclude it (plan_type_desc <> 'Original Medicare') when you want only real MA plans.
- network_description NULL and service_desc NULL carry meaning, not just absence: NULL network = single-network (HMO) or plan-level line (deductible/MOOP); NULL service_desc (~61% of rows) = category-level benefit with no sub-service breakdown.

| column | type | meaning |
|---|---|---|
| org_name | string | Parent organization / carrier marketing name for the contract (e.g. 'Kaiser Permanente', 'UPMC for Life', 'Original Medicare'). Free text, NOT stable per contract across years: 241 of 1,014 contract_ids carry more than one org_name over time (rebrands/acquisitions/relabeling). Use as a label, not a join key. |
| contract_id | string | CMS Medicare Advantage contract identifier. 1,014 distinct. Prefixes observed: 'H' (986 contracts, standard local MA/HMO/PPO incl. the synthetic Original-Medicare contract H0001) and 'R' (28 contracts, Regional PPO). No S/E/D prefixes present (this table is MA benefit data, not standalone PDP/employer). Part of the plan key; not unique on its own. |
| plan_id | string | Plan benefit package (PBP) number within the contract, zero-padded 3 chars (e.g. '001', '043'). 564 distinct values but only meaningful in combination with contract_id (+segment+year). Not unique on its own. |
| segment_id | string | Service-area segment within a plan, zero-padded 3 chars. '000' for the overwhelming majority (2,414,868 of 2,775,723 rows); non-zero segments ('001'..'011') exist only for PPO/regional plans whose benefits vary by geographic segment. Part of the plan key; usually '000'. |
| plan_type_desc | string | Plan type label (HMO, Local PPO, HMOPOS, Regional PPO, Cost Plan, PFFS, MSA, Medicare-Medicaid Plan, 'Original Medicare', etc.). GOTCHA: free-text and vintage-drifted -- the SAME plan-key shows different labels across years (3,362 plan-keys have >1 value) because CMS changed wording: 'Local Preferred Provider Organization'->'Local PPO', 'HMO with POS Option'->'HMOPOS', 'Private Fee for Service'->'PFFS', 'Medical Savings Account'->'MSA'. Normalize before grouping by plan type. |
| contract_year | int | The plan/benefit calendar year (2020-2026). This is the canonical time dimension and aligns 1:1 with effective_period_start/end (year Y -> Y-01-01..Y-12-31) and observed_period (Y-01-01). One source ZIP file per contract_year (7 files, 7 sha256 hashes). |
| category_name | string | Benefit category label, 32 distinct values (Vision, Comprehensive dental, Mental health services, Diagnostic procedures/lab services/imaging, Doctor visits, Health plan deductible, Maximum out-of-pocket..., etc.). GOTCHA: NOT normalized across vintages -- several values are case/wording variants of one category: 'Comprehensive dental' vs 'Comprehensive Dental'; 'Diagnostic and Preventive dental' vs 'Diagnostic and Preventive Dental'. Lower-case / canonicalize before grouping. |
| service_desc | string | Sub-service within the category (70 distinct, e.g. 'Specialist', 'Primary', 'Lab services', 'Endodontics', 'Diagnostic radiology services (e.g., MRI)'). NULL for ~61% of rows (1,702,642) -- meaning the benefit is captured only at category level with no sub-service breakdown. A NULL here is structural, not missing-data. |
| limit_flag | string | Whether the benefit has a quantity/dollar limit. GOTCHA: encoding varies by contract_year -- '1'/'0' (int strings, 2020-2024), '1.0'/'0.0' (decimal strings, 2025), and large NULL share (1,719,487 rows, mostly categories where a limit is inapplicable). 1=limited, 0=not limited. Normalize ('1'='1.0') for cross-year use. (Note: unlike auth/referral, 2026 limit_flag was not observed as Yes/No text in the sampled distribution.) |
| authorization_flag | string | Whether prior authorization is required for the benefit. THREE-valued and encoded DIFFERENTLY each year: 2020-2024 use '0'/'1'/'2'; 2025 uses '0.0'/'1.0'/'2.0'; 2026 uses 'No'/'Yes'/'N/A'. Decode: 0=No, 1=Yes, 2=N/A (verified by cross-year counts). ~190K NULL. Any cross-year analysis MUST normalize these encodings. |
| referral_flag | string | Whether a referral is required for the benefit. Same three-valued, year-dependent encoding as authorization_flag: '0'/'1'/'2' (2020-2024), '0.0'/'1.0'/'2.0' (2025), 'No'/'Yes'/'N/A' (2026); decode 0=No, 1=Yes, 2=N/A (confirmed: 2024 '2'=101,682 maps to 2026 'N/A'=129,962). ~190K NULL. Normalize before cross-year comparison. |
| network_description | string | Network tier the cost_share applies to: 'In-Network' (1,040,965), 'Out-of-Network' (623,164), or NULL (1,111,594). NULL is meaningful: it marks single-network plans (HMO) and plan-level lines (deductible/MOOP) where the in/out split does not apply. For PPO/POS plans each benefit appears as separate In-Network and Out-of-Network rows with different cost_share. |
| cost_share | string | The enrollee's cost as FREE TEXT (13,859 distinct, 0 nulls). Mixed formats: percentages ('20%'), dollar copays ('$30 copay per visit'), deductible/MOOP dollar amounts ('$3,400 In-network', '$10,000 In and Out-of-network<br />$6,700 In-network' -- note embedded HTML), and prose ('Not covered', 'Yes, contact plan for further details'). GOTCHA: same value rendered differently by year ('$0' vs '0' vs '0.0' for a $0 deductible). NOT numeric -- must be parsed/regex'd to extract amounts. |
| observed_period | date | Date the benefit is observed/effective-from; always Jan 1 of contract_year (e.g. 2023-01-01). Redundant with contract_year and effective_period_start. A provenance/as-of date, not an independent dimension. |
| asserted_at | date | Ingest/assertion date of this silver build. Constant 2026-05-16 for ALL rows -- the entire table is a single ingest/snapshot. Not a versioning column; carries no SCD/is_current semantics. |
| effective_period_start | date | First day the plan year is in effect; Jan 1 of contract_year. The profiler's chosen time column; aligns 1:1 with contract_year. Span 2020-01-01 .. 2026-01-01, annual cadence. |
| effective_period_end | date | Last day of the plan year; Dec 31 of contract_year. Together with effective_period_start it just brackets the contract_year; no partial-year periods exist. |
| cms_vintage_sequence | int | Source-vintage sequence number. Constant = 1 for every row. There is only one vintage per contract_year and one overall ingest; this column does NOT distinguish snapshots and cannot be used to pick a 'latest' version. |
| parse_revision | int | Parser revision number. Constant = 1 for every row. No re-parses recorded; not useful for dedup or version selection. |
| parser_artifact_hash | string | SHA-256 of the parser artifact that produced the row. 7 distinct values, one per contract_year file. Provenance only. |
| source_url | string | CMS download URL of the source ZIP, e.g. 'https://www.cms.gov/files/zip/cy-2023-mpf-benefit-summary.zip'. Exactly 7 distinct URLs = one file per contract_year (CY2020-CY2026). Lineage column; functionally determined by contract_year. |
| sha256_content_hash | string | SHA-256 of the source file content. 7 distinct values (one per contract_year ZIP). Provenance/integrity; identical across all rows from the same year, so it does not help distinguish the duplicate benefit rows within a year. |

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