VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA snp data

medicare_silver.snp_data·live·silver·monthly·Medicare Advantage Plan Benefit Packages (PBP)
Rows
N/A
Columns
22
History
2007-05 to 2026-04
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A monthly historical census of every Medicare Advantage Special Needs Plan (SNP) in the U.S., parsed from CMS's monthly "SNP Comprehensive Report" enrollment files.
  2. 2Each row is one SNP plan-segment in one month, carrying its identity (contract/plan/segment), sponsor and plan names, plan/organization type, SNP subtype (Dual-Eligible / Chronic-or-Disabling / Institutional), target chronic conditions, service-area geography/state, and reported member enrollment.
  3. 3It is a plan-attribute + enrollment DIMENSION for the Medicare/MA side of the cost substrate; it does NOT contain rates, premiums, or any dollar cost.

Grain

KeyGrain
contract_idplan_idsegment_idreport_month
  1. 1One row per SNP plan-segment per monthly CMS snapshot.
  2. 2The minimal practical key is contract_id + plan_id + segment_id + report_month, which yields 185,924 distinct values across 185,930 total rows.
  3. 3The 6 residual collisions are all contract R1548 (a UnitedHealthcare Regional PPO D-SNP spanning North Carolina and Virginia) where CMS reports two component-state enrollment slices in a single monthly file but the table carries no per-component-state column to separate them; to make the key strictly unique you must add plan_enrollment (the distinguishing value) to the 4-column key.
  4. 4There is no is_current column and no resnapshotting — each month has exactly one source file (228 files = 228 distinct months = 228 distinct URLs), so report_month alone fully selects a snapshot.

How to read it

  1. 1To get the current SNP landscape by type: SELECT snp_type, COUNT(*) AS plans, SUM(plan_enrollment) AS members FROM medicare_silver.snp_data WHERE report_month = (SELECT MAX(report_month) FROM medicare_silver.snp_data) GROUP BY snp_type — answers 'how many active Medicare Advantage Special Needs Plans of each kind exist now and how many members they cover' (Apr 2026: 1,800 plans, ~8.27M members, led by Dual-Eligible).
  2. 2For a single plan's enrollment trend, filter contract_id/plan_id (lpad to 3) and order by report_month."

Traps

  1. 1contract_id+plan_id+segment_id+report_month is NOT strictly unique: 6 rows collide (all contract R1548, a regional PPO D-SNP across NC+VA) where CMS splits one regional plan into two component-state enrollment rows in a single monthly file with no column to tell them apart. Add plan_enrollment to dedupe, or just COUNT/SUM rather than assume 1 row per plan-month.
  2. 2plan_id is inconsistently zero-padded: 3,263 rows store it as '1'/'01' instead of '001'. The same logical plan appears in both forms across months, so ALWAYS lpad(plan_id,3,'0') before joining to other CMS contract/plan tables or you will drop or fan-out rows.
  3. 3There is NO is_current flag and NO snapshot dedup needed — every monthly file is a single vintage (cms_vintage_sequence=1, parse_revision=1, one sha256/source_url per month). To get 'current' plans just filter report_month = MAX(report_month) (Apr 2026 → 1,800 plans).
  4. 4plan_enrollment NULL means CMS small-cell suppression (<11 members), NOT zero and NOT missing-by-error: there are literally zero rows with enrollment below 11. Treating NULL as 0 understates plan counts; SUM(plan_enrollment) silently excludes ~14.5k suppressed small plans.
  5. 5specialty_diseases NULL is meaning-bearing: it is populated only for C-SNPs ('Chronic or Disabling Condition'); NULL for Dual/Institutional means 'no target disease applies', not missing data. The values themselves are free-text and not standardized across years.
  6. 6report_month, effective_period_start, effective_period_end (=month-end), and observed_period are all derived from the same period and 100% redundant; asserted_at/cms_vintage_sequence/parse_revision are constants. Do not treat any of these provenance/date fields as independent dimensions.
  7. 7This table has NO cost, rate, or premium data — it is a SNP plan census + enrollment dimension. Use it to identify/segment SNP plans and join to actual Medicare cost/benefit tables; do not expect to read prices here.

Columns (22)

ColumnTypeMeaning
contract_idstringCMS Medicare contract ID (e.g. H0150, R1548). Part of the grain. First letter encodes plan footprint: H = local plan (182,572 rows / 870 contracts), R = regional PPO (3,358 rows / 12 contracts). 882 distinct contracts overall. Join key to other CMS contract/plan tables.
plan_idstringPlan benefit package ID within the contract (e.g. '019', '001'). Part of the grain. STORED INCONSISTENTLY: 182,667 rows are 3-char zero-padded but 3,263 rows are not ('1' and '01' forms), so the same logical plan can appear as '1' and '001'. lpad(plan_id,3,'0') before joining to other plan-level tables. 430 distinct raw values.
segment_idstringPlan segment / sub-area ID (a plan can be split into county/region segments). Part of the grain. 8 distinct values '0'-'7'; '0' dominates (179,787 rows = 96.7%). Usually '0' for unsegmented plans.
report_monthdateFirst day of the CMS reporting month this snapshot belongs to (e.g. 2008-01-01). The snapshot/period key; 228 distinct months Jan 2007-style through Apr 2026. IDENTICAL in every row to effective_period_start and observed_period.
contract_name_rawstringSponsoring organization / parent insurer name as printed in the CMS file (e.g. 'VIVA HEALTH, INC.', 'UNITED HEALTHCARE INSURANCE COMPANY'). Free-text, all-caps, not normalized; can change spelling across months for the same contract.
organization_typestringCMS organization/coordinated-care category. 3 values: 'Local CCP' (181,616 = 97.7%), 'Regional CCP' (3,358), 'Demo' (956). Correlates with contract_id prefix (R = Regional CCP).
plan_name_rawstringMarketing name of the specific plan as printed by CMS (e.g. 'HealthSpring OptimaCare', 'UnitedHealthcare Dual Complete RP (Regional PPO D-SNP)'). Free-text, descriptive; not a stable key (renamed across years).
plan_typestringProduct type. 16 distinct values; dominant are HMO (136,965), Local PPO (20,836), HMOPOS (14,861), HMO/HMOPOS (8,508), Regional PPO (3,358). Long tail of state demonstration types (MN Senior Health Options, WI Partnership Program, SHMO, ESRD II, PSO, CCRC, etc.).
plan_geographic_namestringFree-text service-area label (county list, region, or descriptive phrase, e.g. 'Maricopa and Pima Counties', 'North Carolina and Virginia'). Highly varied — 2,824 distinct values. Descriptive, NOT a clean geo key; do not parse for state — use state_list_raw instead.
state_list_rawstringService-area state code(s) as a raw string (e.g. 'CA', 'NC'). MEANING-BEARING NULL BY VINTAGE: 100% NULL in 2007, ~67% NULL in 2008, then fully populated 2009 onward. A NULL is an early-file coverage gap, not 'no state'. 9,947 NULLs total, all pre-2009.
plan_enrollmentintReported SNP member count for that plan-segment-month. Range 11 to 228,018, median ~1,094 when present. CMS SMALL-CELL SUPPRESSION: there are ZERO rows below 11; counts under 11 are blanked, surfacing as one of the 14,541 NULLs (never 0). Sum of latest month (Apr 2026) = 8,274,866 across 1,800 plans. This is the only numeric measure; it is also what distinguishes the 6 R1548 duplicate-key rows.
snp_typestringSNP eligibility subtype — never null. 3 values: 'Dual-Eligible' (D-SNP, 115,298 = 62%), 'Chronic or Disabling Condition' (C-SNP, 45,408), 'Institutional' (I-SNP, 25,224). The defining SNP classification.
specialty_diseasesstringTarget chronic condition(s) for the plan. MEANING-BEARING NULL: populated for essentially all C-SNPs (45,403 of 45,408 'Chronic or Disabling' rows) and almost never for Dual (8,524 of 115,298) or Institutional (0) — NULL means the SNP type doesn't declare a target disease. Free-text and inconsistent across years (e.g. 'Diabetes' vs 'Diabetes Mellitus' vs 'Cardio DIS CHF and Diabetes'; also stray labels like 'FIDE SNP', 'PBP A Platino YN'). 132,003 NULLs.
observed_perioddateProvenance: period the data was observed for. REDUNDANT — equals report_month in 100% of rows. Bitemporal-modeling field; carries no extra information over report_month.
asserted_atdateProvenance: date this record was asserted/loaded into the silver layer. CONSTANT 2026-05-15 for all 185,930 rows (single bulk load). Not a per-row event date and not a dedup discriminator.
effective_period_startdateProvenance: start of the plan-data effective window. REDUNDANT — equals report_month (and observed_period) in 100% of rows. Profiler's default time column; min 2007-05-01, max 2026-04-01.
effective_period_enddateProvenance: end of the effective window = the month-end of report_month (228 distinct values, min 2007-05-31, max 2026-04-30). Purely derived from the period; carries no information beyond report_month.
cms_vintage_sequenceintProvenance: CMS vintage sequence for re-issued files. CONSTANT = 1 for every row — there is only one vintage of each monthly file in this table. Not a dedup/supersession key here.
parse_revisionintProvenance: parser revision number. CONSTANT = 1 for every row. Reserved for reprocessing; currently single-valued.
parser_artifact_hashstringProvenance: SHA-256 of the parser code/artifact that produced the row (lineage of the extraction logic). Same hash within a load batch.
source_urlstringCMS download URL of the originating monthly SNP report ZIP (e.g. https://www.cms.gov/files/zip/snp-comprehensive-report-july-2022.zip). 228 distinct URLs = exactly one file per month. Source provenance / audit.
sha256_content_hashstringProvenance: SHA-256 of the raw source file contents (228 distinct = one per month). Constant across all rows from the same monthly file; used to verify the source bytes / detect re-downloads.

Query it

describe_data_source(source_fqn="medicare_silver.snp_data")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.snp_data WHERE contract_id = '<value>' AND plan_id = '<value>' AND segment_id = '<value>' AND report_month = '<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.snp_data")
Raw doorAthena (read-only via query_substrate): medicare_silver.snp_data
NoteGrain-governed; live in Glue / query_substrate. Source family snp_data.

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