VladaOmega
Sign inStart free
Medicare Advantage, Part D, Medicaid

MA plan payment — county rollup

medicare_silver.ma_plan_payment_county·live·silver·annual·Medicare Advantage Plan Payments
Rows
140,291
Columns
14
History
2015 to 2024
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A CMS-published, county-level summary of Medicare Advantage (Part C) plan payments and risk, giving the average per-member-per-month (PMPM) federal payment, the average risk score, and the average rebate dollars for MA plans, broken out by county, plan type, and Special-Needs-Plan type, for payment years 2015-2024.
  2. 2It is the public benchmark for what Medicare pays MA plans to cover a beneficiary in a given county.

Grain

KeyGrain
payment_yearssa_county_codeplan_typesnp_plan_type
  1. 1One row per payment year x county (SSA code) x plan type x SNP type.
  2. 2Verified: count(*) = count(distinct payment_year, ssa_county_code, plan_type, snp_plan_type) = 140,291, fully unique.
  3. 3Because 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.
  4. 4Each row is an AVERAGE across all the contracts/plans of that type in that county-year, not an individual plan.

How to read it

  1. 1Benchmark what Medicare pays MA plans in a county and how it has grown.
  2. 2Example: 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.
  3. 3Pair 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

  1. 1ssa_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.
  2. 2Each 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.
  3. 3bronze_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.
  4. 4snp_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).
  5. 5avg_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.
  6. 6state_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.
  7. 7county_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.

Columns (14)

ColumnTypeMeaning
payment_yearintMA 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_codestring5-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_abbreviationstring2-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_namestringHuman-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_typestringMA 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_typestringSpecial 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_paymentdoubleAverage 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_scoredoubleAverage 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_paymentdoubleAverage 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_urlstringProvenance: 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_hashstringProvenance: SHA-256 of the source file content (string). Exactly one distinct value per payment_year — identifies the specific source artifact ingested.
bronze_publication_idstringProvenance: 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_attimestampProvenance: 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_hashstringProvenance: 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

describe_data_source(source_fqn="medicare_silver.ma_plan_payment_county")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.ma_plan_payment_county WHERE payment_year = '<value>' AND ssa_county_code = '<value>' AND plan_type = '<value>' AND snp_plan_type = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keyssa_county_code
Joinhow_to_join(source_fqn="medicare_silver.ma_plan_payment_county")
Raw doorAthena (read-only via query_substrate): medicare_silver.ma_plan_payment_county

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