# MA ma contract service area

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

One row per (contract_id, county) per month. Each row records the contract's organizational metadata (organization_name, plan_type, EGHP flag) plus the partial-county flag (TRUE when the contract covers only part of the county, per CMS's pu

**Grain.** One row per (Medicare contract) per (county) per (monthly snapshot). Verified: contract_id + county_fips + county_ssa + county_name_raw + report_month is exactly unique = 87,764,727 distinct = the full row_count (87,764,727). county_name_raw is needed only to break ties for ~160 rows where the county codes are blank (Connecticut's new planning regions); contract_id + county_fips + county_ssa + report_month alone is unique for 87,764,567 of 87,764,727 rows (20 residual code-blank groups). NOTE: county_fips alone is NOT enough (Los Angeles 06037 = SSA 05200 AND 05210, etc.), and county_ssa alone is NOT enough (85 SSA codes map to multiple FIPS) — both county codes are required.

**How to use it.** Answer '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. To 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. Always scope to a single report_month for a point-in-time view; keep county_fips/county_ssa as strings.

**Traps.**
- NEITHER 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).
- partial_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.
- There 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).
- County 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.
- This 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.
- Snapshot-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).

| column | type | meaning |
|---|---|---|
| contract_id | string | CMS Medicare contract identifier (e.g. H0504, R7444, E####). The plan-sponsor's contract with CMS. 1,824 distinct. Letter prefix = product family: H = standard/Local MA contract (1,785 contracts, ~98.8% of rows), R = Regional CCP/Regional PPO (37), E = Employer Direct PFFS (2). No 'S' (standalone Part D PDP) here — this is MA service area, not Part D. A contract groups many underlying plan benefit packages (PBP IDs, not present in this table). |
| county_fips | string | 5-character zero-padded Census/FIPS state+county code (e.g. '06037' = LA County). String, leading zeros significant — never cast to int. 3,641 distinct. Blank ('') for 8,076 rows where CMS has no standard FIPS: Alaska non-county boroughs/census areas (7,896 rows) and Connecticut's new planning regions (180 rows, 2024-09+). Never NULL, only empty string. NOT 1:1 with county_ssa. |
| county_ssa | string | 5-character zero-padded SSA (Social Security Administration) state+county code — CMS's legacy geography key (e.g. '05200'). 3,600 distinct. String, keep leading zeros. Blank ('') for 180 rows (the CT planning regions). A handful are 4-char (legacy padding). NOT 1:1 with county_fips: one FIPS can map to several SSA codes (LA = 05200/05210) and 85 SSA codes map to multiple FIPS — that is WHY both county codes are needed for the grain. |
| state_code | string | 2-letter USPS state/territory abbreviation (AZ, CA, TX...). 56 distinct (50 states + DC + territories like PR/GU/VI/etc.). Functionally derivable from the county codes; included for convenience. |
| report_month | date | The monthly snapshot date, always the 1st of the month (2006-10-01 ... 2026-04-01). This is the as-of period for the row and the primary time axis. Identical in value to observed_period and effective_period_start in 100% of rows. |
| organization_name_raw | string | Raw plan-sponsor / parent organization name as printed in the CMS file (e.g. 'CALIFORNIA PHYSICIANS\' SERVICE', 'HUMANA INSURANCE COMPANY'). Uppercase, unnormalized ('_raw'). Snapshot attribute, NOT static: 593 of 1,824 contracts show more than one org name over time (rebrands / M&A / DBA changes). Do not treat as a stable contract identity. |
| organization_type | string | CMS organization/contract category. 11 distinct values: 'Local CCP' (88% of rows), 'PFFS', 'Regional CCP', 'MSA', 'HCPP - 1833 Cost', 'Employer/Union Only Direct Contract PFFS', 'Demo', '1876 Cost', 'National PACE', 'RFB - PFFS', 'Employer Direct PFFS'. As-of-snapshot (25 contracts changed org_type over time). |
| plan_type | string | Product type of the contract. 25 distinct values: top are 'Local PPO' (41.8M), 'HMO/HMOPOS' (35.4M), 'PFFS', 'Regional PPO', 'MSA', cost plans (1876/1833), plus niche dual/special types (Medicare-Medicaid Plan, MN Senior Health Options, ESRD I/II, SHMO, PACE, CCRC, PSO, WI Partnership, etc.). Mostly stable per contract but 37 contracts changed plan_type over time, so it is an as-of-snapshot attribute, not a fixed key — which is why adding it does not refine the county-month grain. |
| partial_county | boolean | TRAP: intended to flag a contract that covers only part of a county. In THIS data it is 100% false (0 true, 0 null across all 87.76M rows) — effectively a dead/unparsed placeholder. Do not filter on it expecting partial-county plans. |
| eghp | boolean | TRAP: intended Employer Group Health Plan flag. Also 100% false (0 true, 0 null) — carries no information in this table. (Employer plans here are instead identifiable via organization_type / plan_type = 'Employer ...' or contract_id prefix 'E'.) |
| county_name_raw | string | Raw county / county-equivalent name from the CMS file (e.g. 'Pinal', 'Los Angeles'; for CT, planning-region names like 'CAPITOL', 'GREATER BRIDGEPORT'). Always populated (0 null, 0 empty) — the only ALWAYS-present geography field, so it is the reliable geo label and is the tie-breaker that makes the grain fully unique when county_fips/county_ssa are blank. |
| notes_raw | string | TRAP: intended free-text notes column. It is 100% NULL in this table (no non-null values exist). Dead column. |
| observed_period | date | Provenance: the period the source data was observed for. Identical to report_month (= 1st of month) in 100% of rows. Redundant encoding of the snapshot month. |
| asserted_at | date | Provenance: load/assert date of this silver build. Single constant value 2026-05-15 across ALL rows (one bulk backfill load). Not a per-snapshot timestamp — do NOT use it for time-series or as-of logic; use report_month. |
| effective_period_start | date | Provenance: start of the period the row is effective for. Identical to report_month (1st of month) in 100% of rows. Redundant with report_month/observed_period. |
| effective_period_end | date | Provenance: end of the effective period — always the LAST day of report_month (day >= 28 in 100% of rows). So [effective_period_start, effective_period_end] is simply the calendar month of the snapshot. |
| cms_vintage_sequence | int | Provenance: dedup/vintage sequence for re-released CMS files. Constant = 1 for every row (no contract month has been superseded by a later CMS re-issue). There is no is_current column and no multi-vintage dedup needed in current data. |
| parse_revision | int | Provenance: parser revision number. Constant = 1 for every row (single parse generation; nothing reparsed). |
| parser_artifact_hash | string | Provenance: sha256 of the parser artifact/version that produced the row. Varies by source-file era (older 2013 files use a different hash than 2021 files) but is a code-version fingerprint, not a data key. |
| source_url | string | Provenance: the exact CMS download URL of the monthly ZIP the row came from (e.g. .../ma-contract-service-area-statecounty-march-2021.zip). 234 distinct = exactly one source file per monthly snapshot. |
| sha256_content_hash | string | Provenance: sha256 of the downloaded source-file content. 234 distinct (one per month), 1:1 with source_url/report_month — the file-level integrity fingerprint. |

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