MA ma contract service area

What it is

1. 1A monthly time-series of which Medicare Advantage / Medicare health-plan contracts are APPROVED to be offered (their service-area footprint) in each U.S. county, sourced from CMS's "MA Contract Service Area by State/County" monthly enrollment-data files.
2. 2It is the geographic coverage/footprint spine for MA contracts — it says "contract X is available in county Y in month Z" plus the contract's org name, org type and plan type.
3. 3It contains NO prices, rates, premiums, benefits, or enrollment counts.

Grain

How to read it

1. 1Answer 'what MA plans / which carriers are available to a Medicare beneficiary in a given county right now, and what plan type are they?' Example: SELECT DISTINCT contract_id, organization_name_raw, organization_type, plan_type FROM medicare_silver.ma_contract_service_area WHERE county_fips='06037' AND report_month=DATE '2026-04-01' ORDER BY organization_name_raw; -> the list of MA contracts approved in Los Angeles County for the latest snapshot, with sponsor and product type.
2. 2To trend a carrier's geographic footprint over time: SELECT report_month, COUNT(DISTINCT county_fips) counties FROM medicare_silver.ma_contract_service_area WHERE organization_name_raw LIKE 'HUMANA%' GROUP BY 1 ORDER BY 1.
3. 3Always scope to a single report_month for a point-in-time view; keep county_fips/county_ssa as strings.

Traps

1. 1NEITHER county code alone is a grain component. county_fips is NOT 1:1 with county_ssa: 15 FIPS codes map to multiple SSA codes (Los Angeles 06037 -> SSA 05200 AND 05210), and 85 SSA codes map to multiple FIPS. Using only county_fips+contract+month leaves 226,649 'duplicate' rows; using only county_ssa is worse (1.1M). You need BOTH county codes (the true minimal practical key is contract_id+county_fips+county_ssa+report_month, unique for all but 160 rows; add county_name_raw for a perfectly unique key).
2. 2partial_county, eghp, and notes_raw are DEAD columns: partial_county and eghp are 100% false (never true, never null) and notes_raw is 100% null across all 87.76M rows. They look meaningful from their names but carry zero signal — likely unparsed/placeholder. Do not build filters on them.
3. 3There is NO is_current flag and effectively no snapshot dedup to do: cms_vintage_sequence=1 and parse_revision=1 for every row, and asserted_at is a single constant (2026-05-15, the one bulk backfill). The 'history' is the monthly report_month series itself, not a slowly-changing dimension. For time-series use report_month; ignore asserted_at/effective_period_* (they are redundant: report_month = observed_period = effective_period_start = 1st of month, and effective_period_end = month end).
4. 4County codes are ZERO-PADDED STRING codes (mostly 5-char). Keep them as strings and preserve leading zeros — casting to int corrupts them. Blank values are empty string '' (never NULL) and MEAN something: blank county_fips = a geography CMS hasn't crosswalked to a standard county code, specifically Alaska non-county boroughs/census areas (7,896 rows) and Connecticut's new planning regions (180 rows, 2024-09 onward). county_name_raw is still populated in those cases, so use it as the geography label.
5. 5This is a COVERAGE / FOOTPRINT table, not a cost table — it has NO premiums, rates, benefits, payment amounts, or enrollment counts. For a healthcare-COST catalog its role is the join spine that answers 'which MA contracts are offered in county X in month Z'. Join contract_id to other Medicare-silver tables for plan benefits/enrollment, and to the FIPS/SSA county codes for geographic rollups; this is not a fact table of dollars.
6. 6Snapshot-varying attributes: organization_name_raw (593/1,824 contracts change name over time), plan_type (37 change), and organization_type (25 change) are as-of-the-month values, not fixed properties of a contract. Don't treat them as a stable contract dimension across years; pin to a report_month or take the latest. Also: monthly coverage is near-continuous 2006-10 to 2026-04 but has a couple of missing months (2006 starts in Oct with 3 months; 2022 has 11 months; 2026 is partial with 4 months through April).

Columns (21)

Query it

describe_data_source(source_fqn="medicare_silver.ma_contract_service_area")     # the card, live
query_substrate(sql="SELECT * FROM medicare_silver.ma_contract_service_area WHERE contract_id = '<value>' AND county_fips = '<value>' AND county_ssa = '<value>' AND county_name_raw = '<value>' AND report_month = '<value>' LIMIT 100")