VladaOmega
Sign inStart free
Curated datasets

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

vlada_curated.dim_geography·live·dim·static·U.S. Geography Crosswalk (ZIP ↔ County ↔ CBSA ↔ State)
Rows
33,791
Columns
35
History
static (2020 Census geography vintage; bulk-loaded 2026-04-04, synced 2026-04-17) to static (single snapshot, source_version='2020')
Card
yesgrain, traps, filters
Typed tools
0

What it is

  1. 1A static U.S.
  2. 2ZIP-code geographic crosswalk dimension: one row per 5-digit ZIP, mapping each ZIP up the standard Census geography hierarchy (ZIP -> county FIPS -> CBSA/CSA metro area -> state -> census region/division) plus a derived urban/rural class.
  3. 3In a healthcare-cost catalog it is the join bridge for putting cost facts (hospital/commercial rates, provider locations) onto county/metro/state geography.
  4. 4WARNING: about half of its columns are unpopulated placeholders (see gotchas) so it is a geography crosswalk only, NOT a demographics or Medicare-locality table despite those columns existing.

Grain

KeyGrain
zip_code
  1. 1One row per 5-digit U.S.
  2. 2ZIP code.
  3. 3Verified: 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.
  4. 4No time/version/is_current dimension exists (single static snapshot), so zip_code alone is the full minimal key.

How to read it

  1. 1Use it to roll cost facts up to county/metro/state.
  2. 2Best 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.
  3. 3Example: 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?'.
  4. 4Do NOT use it for demographics, lat/lon, or Medicare locality (all NULL)."

Traps

  1. 1ROUGHLY 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.
  2. 2CRITICAL 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.
  3. 3geo_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.
  4. 4'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.
  5. 5csa_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.
  6. 6Static 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.

Columns (35)

ColumnTypeMeaning
geo_idstringSurrogate 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_codestring5-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_latstringINTENDED ZIP centroid latitude but 100% NULL across all 33,791 rows (genuinely NULL, not empty-string). Unusable placeholder.
zip_centroid_lonstringINTENDED ZIP centroid longitude but 100% NULL across all rows. Unusable placeholder. For hospital coordinates use dim_hospital_external.latitude/longitude instead.
zip_populationstringINTENDED ZIP population but 100% NULL. Unusable placeholder.
zip_land_area_sqmistringINTENDED ZIP land area in square miles but 100% NULL. Unusable placeholder.
county_fipsstring5-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_namestringCounty name (or PR 'Municipio', LA 'Parish'), POPULATED for all rows. Paired with county_fips.
county_populationstringINTENDED county population but 100% NULL. Unusable placeholder.
cbsa_codestring5-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_namestringCBSA name (e.g. 'Aguadilla, PR'). NULL on the same 9,057 rural rows as cbsa_code.
cbsa_typestringCBSA 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_codestringCombined 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_namestringCSA name (e.g. 'Mayaguez-Aguadilla, PR'). NULL wherever csa_code is NULL (15,192 rows). 183 distinct CSAs.
state_codestring2-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_namestringFull state/territory name (e.g. 'Texas','Puerto Rico'), POPULATED for all rows. Paired 1:1 with state_code.
state_fipsstring2-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_regionstringCensus 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_divisionstringCensus 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_carrierstringINTENDED Medicare carrier/MAC code but 100% NULL. CRITICAL for cost work: this table CANNOT map ZIP to Medicare carrier despite the column existing.
medicare_localitystringINTENDED 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_namestringINTENDED Medicare locality name but 100% NULL. Unusable placeholder.
urban_ruralstringDerived 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_statusstringINTENDED metro-status flag but 100% NULL. Use urban_rural / cbsa_type instead.
median_household_incomestringINTENDED demographic but 100% NULL. Unusable placeholder.
poverty_ratestringINTENDED demographic but 100% NULL. Unusable placeholder.
uninsured_ratestringINTENDED demographic but 100% NULL. Unusable placeholder.
hospital_countstringINTENDED count of hospitals per ZIP but 100% NULL. Unusable placeholder (count hospitals from dim_hospital_external instead).
physician_densitystringINTENDED physician-density metric but 100% NULL. Unusable placeholder.
data_sourcestringProvenance, POPULATED for all rows; constant 'census_bureau' across all 33,791 rows.
source_versionstringSource vintage, POPULATED for all rows; constant '2020' (2020 Census geography) across all rows.
created_attimestampRow 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_attimestampUpdate timestamp; identical to created_at in 100% of rows (single constant). No row has been individually updated.
metadatastringFree-form JSON string (sourced from RDS JSONB), POPULATED but always the empty object literal '{}', so it carries no extra data.
_synced_attimestampPipeline 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

describe_data_source(source_fqn="vlada_curated.dim_geography")     # the card, live
query_substrate(sql="SELECT * FROM vlada_curated.dim_geography WHERE zip_code = '<value>' LIMIT 100")
ItemValue
Typed tools that read itnone; raw SQL
Identity keyzip_code
Joinhow_to_join(source_fqn="vlada_curated.dim_geography")
Raw doorAthena (read-only via query_substrate): vlada_curated.dim_geography

This page as markdown: /docs-md/data/vlada_curated_dim_geography.md · every page in one file: /llms-full.txt