# MA ma state county penetration

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

One row per (county, month). Each row records eligibles (Medicare-eligible population), enrolled (MA-enrolled population), and penetration (enrolled / eligibles). Includes both FIPS-based and SSA-based county identifiers, plus human-readabl

**Grain.** One row per county (FIPS) per monthly report. The minimal verified unique key is (fips, report_month): 691,560 rows = 691,560 distinct (fips, report_month) groups, with a proven max duplicate count of 1. fips alone has 3,533 distinct values across 215 monthly snapshots; report_month spans 215 months (2008-06 .. 2026-04).

**How to use it.** Trend MA penetration for a county over time: SELECT report_month, eligibles, enrolled, penetration FROM medicare_silver.ma_state_county_penetration WHERE fips='04021' AND suppression_reason_code='none' ORDER BY report_month. Answers 'how has Medicare Advantage market share in Pinal County, AZ evolved month over month?' For a clean national county cross-section, add WHERE fips NOT IN ('00000','0') AND suppression_reason_code='none' to drop the Pending rollup bucket and small-cell-suppressed cells.

**Traps.**
- The grain is (fips, report_month) — NOT any of the period columns the profiler latched onto. report_month, observed_period, and effective_period_start are three identical copies of the same month (0 mismatches), and effective_period_end is just that month's last day; don't mistake them for separate dimensions.
- ssa / (ssa, report_month) looks like an alternate key but is NOT unique: Rota, Saipan and Tinian (N. Mariana Islands) all collapse to ssa '63050', giving ~471 duplicate (ssa, month) pairs. Join and dedup on fips, never on ssa. Likewise fipsst is inconsistently zero-padded ('01' vs '1', '00' vs '0'), so aggregate by substring(fips,1,2) or state_name, not raw fipsst.
- Suppression is real and asymmetric: 13,675 rows are 'small_cell_suppressed' → enrolled is NULL but enrolled_lower_bound=0 / enrolled_upper_bound=10 (CMS masks counts of 1-10). penetration is also NULL for these. Treating NULL enrolled as 0 will understate enrollment; use the [0,10] band. A further 270 unsuppressed ('none') rows also have NULL enrolled (early territory months + the Pending bucket where eligibles are known but enrollment wasn't reported).
- penetration is mostly 0-1 but 68 rows exceed 1.0 (max 2.136, e.g. Pinal County AZ in 2010 where reported enrolled ~18k > eligibles ~8.5k). This is an upstream CMS data quirk, not a computed error — clamp or flag if you need a guaranteed 0-1 share. Also 300 rows have enrolled > eligibles overall.
- There is no vintage/revision/is_current dedup to do: cms_vintage_sequence=1, parse_revision=1, and asserted_at is a single date (2026-05-15) for every row. It is one immutable backfilled snapshot of 215 monthly CMS files (one source_url + sha256 per month). A 'Pending State/County Designation' rollup row (fips '00000', sometimes unpadded '0', ssacnty '999') exists in many months as a not-yet-geocoded bucket — exclude it (and the territory rows) for clean county-level CONUS analysis.

| column | type | meaning |
|---|---|---|
| fipsst | string | State portion of FIPS (first 2 chars). 64 distinct values — note it is NOT consistently zero-padded across the history (you see both '01'/'1', '04'/'4', '00'/'0'), so do not GROUP BY fipsst expecting 50-ish clean states; derive from substring(fips,1,2) or use state_name. Includes territories 60/66/69/72/78. |
| fipscnty | string | County portion of FIPS (last 3 chars). '000' for the Pending/rollup bucket. |
| report_month | date | First day of the report month (date). Half the grain key. 215 distinct months, monthly cadence 2008-06-01 .. 2026-04-01. Identical in value to observed_period and effective_period_start (0 mismatches) — those are redundant copies. |
| state_name | string | Human-readable state/territory name. Has casing/spelling drift over time ('Pending State Designation' vs 'PENDING STATE DESIGNATION', 'Pending County Design' truncation) — treat as descriptive, not a key. |
| county_name | string | Human-readable county name (e.g., Autauga, Baldwin). Descriptive only; same casing/truncation drift caveat. |
| fips | string | 5-char county FIPS code = fipsst//fipscnty. THE county identifier and half the grain key. 3,533 distinct. Caveat: not always zero-padded — a special rollup bucket appears as both '00000' and '0' in different months (see gotchas). |
| ssast | string | SSA state code (CMS's legacy Social Security Administration geography numbering, distinct from FIPS). |
| ssacnty | string | SSA county code. '999' marks the Pending/unknown-county rollup bucket; '000' = the state's primary county slot in SSA numbering. |
| ssa | string | 5-char SSA code = ssast//ssacnty. An alternate geography key but NOT unique per month: the three Northern Mariana Islands counties (Rota/Saipan/Tinian) all map to ssa '63050', so (ssa, report_month) has ~471 collisions and is finer than the true grain. Use fips, not ssa, as the join key. |
| eligibles | int | Count of Medicare beneficiaries eligible for MA in that county-month (the denominator). Never null, never zero in this data. This is total Medicare eligibility (FFS + MA), so penetration = enrolled/eligibles. |
| enrolled | int | Count enrolled in an MA/Part-C/cost plan (the numerator). NULL when small_cell_suppressed (13,675 rows) OR when the territory/Pending bucket did not report enrollment (270 'none' rows). When present and unsuppressed it equals both bound columns. |
| enrolled_lower_bound | int | Lower bound of enrolled. Equals enrolled exactly for unsuppressed rows. For small_cell_suppressed rows it is 0 (the CMS small-cell band is [0,10]). |
| enrolled_upper_bound | int | Upper bound of enrolled. Equals enrolled for unsuppressed rows; 10 for every suppressed row (CMS masks counts 1-10 as the range 0-10). Use the [lower,upper] band to reason about suppressed cells instead of treating enrolled NULL as zero. |
| penetration | double | MA penetration rate = enrolled/eligibles, as a fraction (0-1 typically; double). Range observed 0.0016 .. 2.136, avg ~0.284. NULL whenever enrolled is NULL (13,946 rows). 68 rows exceed 1.0 (a known CMS source quirk where reported enrolled > eligibles, e.g. Pinal County AZ 2010 ≈ 2.1×) — clamp/flag if you need a clean 0-1 rate. |
| suppression_reason_code | string | Why a cell is masked. Two values: 'none' (677,885 rows, normal) and 'small_cell_suppressed' (13,675 rows, count was 1-10 so enrolled is hidden and bounds are [0,10]). This is the column to filter on to drop suppressed cells. |
| observed_period | date | Redundant copy of report_month (0 mismatches). Part of Vlada's temporal-provenance envelope; carries no extra info here. |
| asserted_at | date | When Vlada asserted/loaded this snapshot. Single constant value 2026-05-15 for the entire table — confirms this is one backfilled load, not an incrementally appended feed. |
| effective_period_start | date | Start of the month the data is effective for — identical to report_month (0 mismatches). Vlada bitemporal-envelope field. |
| effective_period_end | date | Last day of the report month (e.g., 2021-05-31 for May-2021). Just the month-end of report_month. |
| cms_vintage_sequence | int | Intended to order CMS re-releases of the same month. Constant = 1 everywhere — there are no superseding vintages, so no 'latest vintage' filter is needed. |
| parse_revision | int | Parser re-run revision counter. Constant = 1 everywhere; no reprocessed rows exist. |
| parser_artifact_hash | string | Hash of the parser artifact used. 215 distinct values — effectively one per monthly file, not a row-level discriminator. |
| source_url | string | CMS download URL for that month's ZIP (e.g., https://www.cms.gov/files/zip/ma-statecounty-penetration-may-2021.zip). Exactly 215 distinct = one file per month (1:1 with report_month), confirming the monthly-snapshot lineage. |
| sha256_content_hash | string | SHA-256 of the source file contents. 215 distinct = one per monthly file; provenance/integrity, not a grain column. |

Tools that read it: get_market_share

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