# MA snp data

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

One row per (contract, plan, segment) per month. Each row records plan enrollment count, plan_type (HMO/HMOPOS/PPO/PFFS/etc.), the SNP category (Chronic/Dual/Institutional), and for chronic-SNPs the specific specialty disease(s) the plan ta

**Grain.** One row per SNP plan-segment per monthly CMS snapshot. The minimal practical key is contract_id + plan_id + segment_id + report_month, which yields 185,924 distinct values across 185,930 total rows. The 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. There 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 use it.** To 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). For a single plan's enrollment trend, filter contract_id/plan_id (lpad to 3) and order by report_month."

**Traps.**
- contract_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.
- plan_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.
- There 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).
- plan_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.
- specialty_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.
- report_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.
- This 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.

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS 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_id | string | Plan 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_id | string | Plan 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_month | date | First 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_raw | string | Sponsoring 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_type | string | CMS 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_raw | string | Marketing 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_type | string | Product 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_name | string | Free-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_raw | string | Service-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_enrollment | int | Reported 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_type | string | SNP 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_diseases | string | Target 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_period | date | Provenance: 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_at | date | Provenance: 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_start | date | Provenance: 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_end | date | Provenance: 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_sequence | int | Provenance: 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_revision | int | Provenance: parser revision number. CONSTANT = 1 for every row. Reserved for reprocessing; currently single-valued. |
| parser_artifact_hash | string | Provenance: SHA-256 of the parser code/artifact that produced the row (lineage of the extraction logic). Same hash within a load batch. |
| source_url | string | CMS 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_hash | string | Provenance: 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: `query_substrate` with `SELECT ... FROM medicare_silver.snp_data WHERE ... LIMIT 100`; read `describe_data_source("medicare_silver.snp_data")` first.