# Legacy Medicare ZIP → Payment Locality Crosswalk (superseded)

`medicare_silver.medicare_zip_to_locality` · Medicare Advantage, Part D, Medicaid · status live · 31,913 rows

Legacy RDS-mirrored ZIP-to-locality surface retained for compatibility only. It is not the canonical PFS ZIP locality resolver because it collapses big-metro ZIPs into rest-of-state localities; use cms_zip5_locality / medicare_silver.medicare_zip5_locality instead.

**Grain.** One row per 5-digit ZIP code (CY2025 snapshot). VERIFIED: 31,913 rows = 31,913 distinct zip_code = 31,913 distinct id, so zip_code alone is the minimal natural key (id is just a surrogate). Every ZIP maps to exactly one (state, locality_number) Medicare locality. There is no per-ZIP time dimension — the whole table is a single 2025 vintage.

**How to use it.** Map a provider ZIP to its locality-adjusted Medicare geography, then chain to GPCI for the cost adjustment. Example: SELECT z.zip_code, z.locality_name, g.work_gpci, g.pe_gpci, g.mp_gpci FROM medicare_zip_to_locality z JOIN medicare_gpci_locality g ON z.state=g.state AND z.locality_number=g.locality_number AND g.calendar_year=2025 WHERE z.zip_code='10001'; → returns NY / 'MANHATTAN' with work=1.065, pe=1.166, mp=1.656, answering 'which Medicare payment locality and geographic price index applies to a service billed at ZIP 10001'.

**Traps.**
- locality_number is NOT a key by itself (only 15 distinct values, e.g. '99' means 'REST OF <STATE>' in 15 different states). The real Medicare locality identity is (state, locality_number) = exactly 64 distinct combos. Never group or join by locality_number alone.
- is_statewide_locality is BROKEN: false for 100% of rows. Do not use it to detect statewide localities — use locality_number='00' (the actual whole-state code, present in 17 states) instead.
- Four columns are 100% NULL/empty in this snapshot: cbsa_code, cbsa_name, rural_urban_code, and effective_date. The schema lists them but there is no data — do not build logic on them. (The dated, GPCI-bearing version of effective_date lives in sibling medicare_gpci_locality.)
- Single-vintage snapshot: calendar_year is constant 2025 and there is no is_current / version column. created_at, asserted_at, source_file_id, mirror_source are all single constants (load/provenance metadata, not business dates). Treat the whole table as CY2025.
- This table has NO prices or GPCI values — it is purely the ZIP→locality lookup. To get the actual Medicare cost adjustment you must join on (state, locality_number) to medicare_gpci_locality (work/pe/mp GPCI; join is 100% complete — 64/64 localities matched) and then to medicare_physician_fee_schedule. Don't confuse it with its sibling medicare_county_to_locality (county-keyed, used when you have county not ZIP).

| column | type | meaning |
|---|---|---|
| id | bigint | Surrogate bigint row key from the source RDS table. Unique (31,913 distinct) but carries no business meaning — do NOT use for joins; join on zip_code or (state, locality_number). |
| zip_code | string | 5-digit USPS ZIP code, stored as a string. The true grain. 100% populated, all exactly 5 chars, all numeric (e.g. '35004','10001'). This is the natural lookup key. |
| state | string | 2-letter USPS state code (e.g. 'AL','NY','CA'). 51 distinct values (50 states + DC). 100% populated. Part of the Medicare locality key (state, locality_number). |
| county | string | County name for the ZIP (e.g. 'St. Clair','Jefferson'). 100% populated. Descriptive geography; NOT a Medicare payment dimension (Medicare prices by locality, not county) — for county-level locality mapping use sibling medicare_county_to_locality. |
| city | string | City/place name for the ZIP (e.g. 'Acmar','Adamsville'). 100% populated. Descriptive only. |
| cbsa_code | string | Core-Based Statistical Area (metro) code. 100% NULL in this snapshot — column exists in schema but is entirely unpopulated. Do not rely on it. |
| cbsa_name | string | CBSA/metro name. 100% NULL — entirely unpopulated. |
| rural_urban_code | string | Rural/urban classification (e.g. RUCA-style). 100% NULL — entirely unpopulated. |
| locality_number | string | 2-digit Medicare carrier-locality code WITHIN a state (e.g. '00','01','75','99'). Only 15 distinct values and NOT unique on its own — meaningful only combined with state. '00' = whole-state locality (17 states), '99' = 'REST OF <STATE>' catch-all (15 states), other codes = specific metros (e.g. NY '01' MANHATTAN, FL '03' FORT LAUDERDALE, TX '31' AUSTIN). The join key into medicare_gpci_locality. |
| locality_name | string | Human-readable locality name (e.g. 'ALABAMA','MANHATTAN','REST OF CALIFORNIA','FORT LAUDERDALE'). 64 distinct = exactly the number of distinct (state, locality_number) pairs, so it is a clean label for the locality. Some names carry a trailing '**' footnote marker (1,426 rows, 5 names, e.g. 'SOUTH DAKOTA**','WYOMING**') — a CMS source annotation, not part of the name. |
| calendar_year | bigint | Fee-schedule vintage year. Constant 2025 for every row — this is a single-year snapshot, not a history. |
| effective_date | date | Intended locality effective date. 100% NULL in this table (unpopulated here; the dated version lives in medicare_gpci_locality.effective_date = 2025-01-01). |
| is_statewide_locality | boolean | Boolean flag intended to mark whole-state localities. TRAP: it is FALSE for 100% of rows (0 true), so it is broken/unpopulated and does NOT actually flag statewide localities. To identify statewide localities use locality_number='00' instead. |
| source_file_id | bigint | FK to the ingested source file. Constant value 4 across the whole table (single source file = one CMS ZIP-to-locality file). |
| created_at | timestamp | Ingest timestamp from the upstream RDS load. Single constant value (2026-04-04 19:41:42.910) for all rows — load metadata, not a business date. |
| asserted_at | date | Date this snapshot was asserted/mirrored into the lake. Single constant value (2026-06-03) — mirror metadata. |
| mirror_source | string | Provenance string identifying the upstream source: constant 'rds:vlada_enterprise.public.medicare_zip_to_locality'. Confirms this Glue/Iceberg table is a mirror of the hospital-server RDS table. |

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