# MA plan payment — county rollup

`medicare_silver.ma_plan_payment_county` · Medicare Advantage, Part D, Medicaid · status live · 140,291 rows

CMS county-level summary of MA Part C payments and risk: average PMPM federal payment, average risk score, and average rebate dollars, broken out by county, plan type, and SNP type. The public benchmark for what Medicare pays MA plans in a county.

**Grain.** One row per payment year x county (SSA code) x plan type x SNP type. Verified: count(*) = count(distinct payment_year, ssa_county_code, plan_type, snp_plan_type) = 140,291, fully unique. Because there is exactly one CMS publication per year (bronze_publication_id is 1:1 with payment_year), payment_year is the minimal time key and bronze_publication_id is redundant. Each row is an AVERAGE across all the contracts/plans of that type in that county-year, not an individual plan.

**How to use it.** Benchmark what Medicare pays MA plans in a county and how it has grown. Example: SELECT payment_year, round(avg(avg_ab_pmpm_payment),0) AS avg_ab_pmpm, round(avg(avg_rebate_pmpm_payment),0) AS avg_rebate FROM medicare_silver.ma_plan_payment_county WHERE ssa_county_code='05060' AND plan_type='HMO' AND snp_plan_type='NOT_SNP' GROUP BY payment_year ORDER BY payment_year; -- returns the year-over-year average A+B PMPM payment and rebate for general HMO plans in Contra Costa County CA. Pair with a state/county filter and plan_type to compare MA payment generosity across markets, or filter snp_plan_type<>'NOT_SNP' to study Special Needs Plan economics.

**Traps.**
- ssa_county_code is the SSA county scheme, NOT FIPS. Autauga AL is '01000' here but FIPS 01001; codes step by 10. Do NOT join this directly to FIPS-keyed tables without an SSA<->FIPS crosswalk, or every county will mismatch.
- Each row is an AVERAGE across all contracts/plans of that type in the county-year, not an individual MA plan or contract. There is no contract_id/plan_id here. For plan-level detail you need a different table — this is the county aggregate only.
- bronze_publication_id, sha256_content_hash, asserted_at, and parser_artifact_hash are all exactly 1:1 with payment_year (one CMS file per year). bronze_publication_id appears in the profiler's 'unique key' but is redundant — the true minimal grain is payment_year x ssa_county_code x plan_type x snp_plan_type (140,291 rows). There is no is_current column and no within-year duplication, so no snapshot dedup is required.
- snp_plan_type='NOT_SNP' is an explicit value meaning a general (non-Special-Needs) plan, not a missing/null. Filtering snp_plan_type IS NOT NULL will NOT remove general plans; to get only SNPs use snp_plan_type <> 'NOT_SNP'. The other three values map to D-SNP (Dual-Eligible), C-SNP (Chronic or Disabling Condition), I-SNP (Institutional).
- avg_rebate_pmpm_payment = 0 is meaningful, not missing: it is structural for MSA plans (all 1,262 rows) and frequent for PFFS. Treating 0 as null/excluding it will bias rebate analyses. There are no actual nulls anywhere in the table.
- state_abbreviation has 55 distinct values including territories (GU, PR, VI), DC, and a sentinel '0' (a non-state placeholder). Don't assume exactly 50 states; filter the '0' row if you need real states only.
- county_name is descriptive and can vary in spelling across years (e.g. SSA 24380 MN = 'Lake Of Woods' vs 'Lake Of  Woods'). Always key/join on ssa_county_code (+ payment_year), never on county_name.

| column | type | meaning |
|---|---|---|
| payment_year | int | MA payment/calendar year the figures apply to (int). Range 2015-2024, 10 distinct years, one CMS file per year. THIS is the time grain. |
| ssa_county_code | string | 5-digit SSA/CMS county code (string, zero-padded, e.g. '01000'=Autauga AL, '05000'=Alameda CA). NOT FIPS (FIPS for Autauga is 01001) — codes increment by 10 within a state and the first 2 digits are an SSA state index. 3,224 distinct codes, each maps to exactly one (state, county). This is the county join key. |
| state_abbreviation | string | 2-letter state/territory code derived from the county (string). 55 distinct values: 50 states + DC + territories GU, PR, VI, plus a sentinel '0' (non-state placeholder). No nulls. Redundant with ssa_county_code (functionally determined by it). |
| county_name | string | Human-readable county name (string, no nulls). Descriptive only — NOT part of the key. Spelling can drift across years (e.g. SSA 24380 MN appears as both 'Lake Of Woods' and 'Lake Of Woods' with a double space). |
| plan_type | string | MA plan product type (string). Exactly 6 values: HMO (52,568 rows), Local PPO (42,954), Regional PPO (34,211), PFFS (9,292), MSA (1,262), OTHER (4). Part of the grain key. |
| snp_plan_type | string | Special Needs Plan category (string). Exactly 4 values: NOT_SNP (87,377 = general/non-SNP plans), Dual-Eligible (33,853 = D-SNP), Chronic or Disabling Condition (12,077 = C-SNP), Institutional (6,984 = I-SNP). 'NOT_SNP' is an explicit category, never null. Part of the grain key. |
| avg_ab_pmpm_payment | double | Average Parts A+B per-member-per-month payment dollars CMS pays plans of this type in this county-year (double). Range $211.27-$1,649.43, mean ~$824, no nulls, no zeros. Rises over time ($714 avg in 2015 -> $895 in 2024). This is the core 'what Medicare pays' cost figure. |
| avg_part_c_risk_score | double | Average CMS-HCC Part C risk score for enrollees in this county/plan-type/SNP cell (double, ~1.0 = average-risk beneficiary). Range 0.014-4.70, mean ~1.25. Higher = sicker/higher-paid population; SNP cells run higher. Creeps up over years (1.15->1.38) reflecting coding intensity. No nulls. |
| avg_rebate_pmpm_payment | double | Average rebate dollars PMPM (double) — the portion of the bid-below-benchmark savings returned to the plan to fund extra benefits. Range $0-$627.95, mean ~$96, 3,432 zero rows. Zero is by design for MSA plans (all 1,262 = $0) and common for PFFS (1,746 zero); HMO/Regional PPO almost never zero. Grows sharply over time ($36 in 2015 -> $179 in 2024). No nulls. |
| source_url | string | Provenance: the exact CMS .zip URL the year's data was extracted from (string). Exactly one per year; URL pattern changes across years (e.g. .../2015-payment-data.zip, .../files/zip/2024-plan-payment-zip-file.zip). |
| sha256_content_hash | string | Provenance: SHA-256 of the source file content (string). Exactly one distinct value per payment_year — identifies the specific source artifact ingested. |
| bronze_publication_id | string | Provenance: internal bronze-layer publication identifier (string, e.g. 'ma_plan_payment_2024_53379284'). EXACTLY 1:1 with payment_year — looks like a key but adds no grain beyond payment_year. The profiler reports it in the unique key only because it is collinear with the year. |
| asserted_at | timestamp | Provenance: timestamp the row was written into the silver layer (timestamp UTC). One distinct value per payment_year (e.g. all 2015 rows = 2026-05-18 19:34:01 UTC). A pipeline/load timestamp, NOT a CMS publication date. |
| parser_artifact_hash | string | Provenance: hash of the parser/code version used to extract the file (string, e.g. '3f6230c020473afb'). One distinct value per year; used for lineage/reproducibility. |

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