# Geography Hierarchy (ZIP → county → CBSA → CSA → state)

`vlada_curated.dim_geography` · Curated datasets · status live · 33,791 rows

The geography spine: ZIP → county (FIPS) → CBSA → CSA → state, with centroid, population, and land area. The geographic join key for county/market rollups across the substrate.

**Grain.** One row per 5-digit U.S. ZIP code. Verified: 33,791 rows = 33,791 distinct zip_code = 33,791 distinct geo_id, all zip_code values exactly 5 chars. geo_id is just the string 'ZIP_' + zip_code (confirmed equal in all 33,791 rows), so it is a redundant restated key, not an independent grain. No time/version/is_current dimension exists (single static snapshot), so zip_code alone is the full minimal key.

**How to use it.** Use it to roll cost facts up to county/metro/state. Best join is a fact's 5-digit ZIP to g.zip_code to attach county_fips, cbsa_code/cbsa_name (metro market), csa_code, and the metro/micro/rural class. Example: SELECT g.cbsa_name, g.urban_rural, COUNT(*) FROM some_fact f JOIN dim_geography g ON f.provider_zip = g.zip_code GROUP BY 1,2 -- answers 'which metro area and urban-vs-rural bucket does each provider/rate sit in?'. Do NOT use it for demographics, lat/lon, or Medicare locality (all NULL)."

**Traps.**
- ROUGHLY HALF THE COLUMNS ARE 100% NULL PLACEHOLDERS (verified empty across all 33,791 rows): every demographic (zip_population, county_population, median_household_income, poverty_rate, uninsured_rate, hospital_count, physician_density), every geospatial field (zip_centroid_lat/lon, zip_land_area_sqmi), ALL THREE Medicare-locality fields (medicare_carrier, medicare_locality, medicare_locality_name), and metro_status. The data dictionary lists these as if they exist, but the data does not. Treat this as a ZIP->county->CBSA/CSA->state crosswalk ONLY.
- CRITICAL for cost analysis: this table CANNOT map ZIP to Medicare physician-fee-schedule locality or carrier (those columns are all NULL). Anyone needing ZIP->Medicare-locality must use the dedicated Medicare fee-schedule benchmark tables, NOT this dim.
- geo_id is NOT an independent key: it is literally 'ZIP_' + zip_code in 100% of rows. The minimal grain is zip_code alone (33,791 rows). Do not treat geo_id and zip_code as if they could diverge.
- 'urban_rural' value 'large_metro' is misleading: it means 'in any metro CBSA' (a 1:1 relabel of cbsa_type='metro'), NOT a large-population tier; small metros like Aguadilla, PR are tagged 'large_metro'. The three classes large_metro/micropolitan/rural are exactly metro/micro/non-CBSA.
- csa_code is stored with a spurious trailing '.0' (e.g. '364.0') from float-to-string coercion; strip it before joining to clean CSA codes. cbsa_code does not have this issue.
- Static single snapshot: data_source='census_bureau' and source_version='2020' are constant; created_at=updated_at=_synced_at are each one constant timestamp. There is NO is_current flag, version, or temporal/price dimension (pure reference geography). Each ZIP maps to exactly ONE county (real ZIPs can cross counties), so it is a simplified crosswalk.

| column | type | meaning |
|---|---|---|
| geo_id | string | Surrogate key, ALWAYS literally 'ZIP_' concatenated with zip_code (verified equal in all 33,791 rows). Redundant alias of zip_code; use it only to join to other dims that reference geo_id (e.g. dim_hospital_system.hq_geo_id). Carries no info beyond the ZIP. |
| zip_code | string | 5-digit U.S. ZIP code, zero-padded string (length is always 5, e.g. '00601'). The true grain key; 33,791 distinct values. Stored as string so leading zeros are preserved (NE/PR ZIPs start with 0). |
| zip_centroid_lat | string | INTENDED ZIP centroid latitude but 100% NULL across all 33,791 rows (genuinely NULL, not empty-string). Unusable placeholder. |
| zip_centroid_lon | string | INTENDED ZIP centroid longitude but 100% NULL across all rows. Unusable placeholder. For hospital coordinates use dim_hospital_external.latitude/longitude instead. |
| zip_population | string | INTENDED ZIP population but 100% NULL. Unusable placeholder. |
| zip_land_area_sqmi | string | INTENDED ZIP land area in square miles but 100% NULL. Unusable placeholder. |
| county_fips | string | 5-digit county FIPS code (state2 + county3), string, POPULATED for all rows (0 null). Its first 2 chars equal state_fips in 100% of rows. 3,211 distinct counties. Each ZIP maps to exactly one county here (a simplification; real ZIPs can span counties). |
| county_name | string | County name (or PR 'Municipio', LA 'Parish'), POPULATED for all rows. Paired with county_fips. |
| county_population | string | INTENDED county population but 100% NULL. Unusable placeholder. |
| cbsa_code | string | 5-digit Core-Based Statistical Area (metro/micro area) code. NULL for 9,057 rows (26.8%), exactly the ZIPs not in any CBSA (i.e. urban_rural='rural'). 928 distinct CBSAs when present. |
| cbsa_name | string | CBSA name (e.g. 'Aguadilla, PR'). NULL on the same 9,057 rural rows as cbsa_code. |
| cbsa_type | string | CBSA class: 'metro' (18,475 rows) or 'micro' (6,259 rows); NULL for the 9,057 rural/non-CBSA rows. Aligns 1:1 with urban_rural (metro<->large_metro, micro<->micropolitan, NULL<->rural). |
| csa_code | string | Combined Statistical Area code (aggregation of adjacent CBSAs). NULL for 15,192 rows (45%); many CBSAs belong to no CSA. Stored with a trailing '.0' (e.g. '364.0') from float-to-string conversion, so strip it before joining to clean CSA codes. CSA never appears without a CBSA (0 such rows). |
| csa_name | string | CSA name (e.g. 'Mayaguez-Aguadilla, PR'). NULL wherever csa_code is NULL (15,192 rows). 183 distinct CSAs. |
| state_code | string | 2-letter USPS state/territory abbreviation (e.g. 'TX','PR'), POPULATED for all rows. 56 distinct = 50 states + DC + 5 territories (PR, GU, VI, MP, AS). Largest TX 1,989 ZIPs; smallest AS 1 ZIP. |
| state_name | string | Full state/territory name (e.g. 'Texas','Puerto Rico'), POPULATED for all rows. Paired 1:1 with state_code. |
| state_fips | string | 2-digit state FIPS code (e.g. '72'=PR), POPULATED for all rows. Equals the first 2 chars of county_fips in 100% of rows. |
| census_region | string | Census region, POPULATED for all rows. 5 values: South (11,590), Midwest (10,150), Northeast (6,103), West (5,799), plus a non-standard 'Territories' (149) for PR/GU/VI/MP/AS. |
| census_division | string | Census division, POPULATED for all rows. 11 values (9 standard divisions e.g. 'South Atlantic','Pacific', plus 'Caribbean' for PR/VI and 'Pacific Islands' for GU/MP/AS). |
| medicare_carrier | string | INTENDED Medicare carrier/MAC code but 100% NULL. CRITICAL for cost work: this table CANNOT map ZIP to Medicare carrier despite the column existing. |
| medicare_locality | string | INTENDED Medicare physician-fee-schedule locality code (the GPCI geography) but 100% NULL. CRITICAL: do NOT use this table to attach Medicare locality to a ZIP; the data is absent. Use the Medicare fee-schedule benchmark tables instead. |
| medicare_locality_name | string | INTENDED Medicare locality name but 100% NULL. Unusable placeholder. |
| urban_rural | string | Derived urban/rural class, POPULATED for all rows. 3 values: 'large_metro' (18,475), 'rural' (9,057), 'micropolitan' (6,259). NAME IS MISLEADING: 'large_metro' means 'in any metro CBSA' (a 1:1 relabel of cbsa_type='metro', including small metros like Aguadilla PR); it is NOT a population-size tier. Equivalent to cbsa_type. |
| metro_status | string | INTENDED metro-status flag but 100% NULL. Use urban_rural / cbsa_type instead. |
| median_household_income | string | INTENDED demographic but 100% NULL. Unusable placeholder. |
| poverty_rate | string | INTENDED demographic but 100% NULL. Unusable placeholder. |
| uninsured_rate | string | INTENDED demographic but 100% NULL. Unusable placeholder. |
| hospital_count | string | INTENDED count of hospitals per ZIP but 100% NULL. Unusable placeholder (count hospitals from dim_hospital_external instead). |
| physician_density | string | INTENDED physician-density metric but 100% NULL. Unusable placeholder. |
| data_source | string | Provenance, POPULATED for all rows; constant 'census_bureau' across all 33,791 rows. |
| source_version | string | Source vintage, POPULATED for all rows; constant '2020' (2020 Census geography) across all rows. |
| created_at | timestamp | Row creation timestamp; a single constant value (2026-04-04 01:44:15 UTC) in all rows, confirming a one-shot bulk load with no row-level history. |
| updated_at | timestamp | Update timestamp; identical to created_at in 100% of rows (single constant). No row has been individually updated. |
| metadata | string | Free-form JSON string (sourced from RDS JSONB), POPULATED but always the empty object literal '{}', so it carries no extra data. |
| _synced_at | timestamp | Pipeline timestamp when the row was synced from RDS geography_hierarchy into this Iceberg dim; single constant value (2026-04-17 18:45:59 UTC). Operational lineage, not business data. |

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