The problem
Joining CMS hospital data to a FIPS county reference table should be straightforward — until you discover that CMS county names diverge from the standardized reference in ways that are too numerous and too case-specific to handle with a generic cleaning function, but too structured to hardcode in SQL. The divergences are a mix of source data typos, abbreviations, missing words, historical county name changes, and territorial naming quirks:
| CMS value | FIPS reference |
|---|---|
| NORTH SLOPE BOROUH | North Slope (typo in source) |
| E. BATON ROUGE | East Baton Rouge (abbreviation) |
| LAKE OF WOODS | Lake Of The Woods (missing word) |
| OGLALA LAKOTA COUNTY | Shannon (officially renamed county) |
| THE DISTRICT | Washington DC (nonstandard name) |
The pattern
A seed table of explicit corrections combined with a normalized join key keeps the overrides version-controlled, auditable, and easy to extend without touching SQL logic.
Step 1 — The seed
csv
-- seeds/county_name_overrides.csv
state_abbreviation,source_county_name,standardized_county_name
AK,NORTH SLOPE BOROUH,North Slope
LA,E. BATON ROUGE,East Baton Rouge
MN,LAKE OF WOODS,Lake Of The Woods
SD,OGLALA LAKOTA COUNTY,SHANNON
DC,THE DISTRICT,WASHINGTON DC
-- ...60 rows total, one per known mismatch
Each row maps a CMS county name to the name the FIPS reference expects, scoped by state abbreviation to handle cases where the same county name appears in multiple states.
Step 2 — Normalize both sides in staging
Rather than joining on raw strings, a normalize_county_name macro standardizes both the source and override values before comparison, and whitespace is stripped entirely so that spacing inconsistencies like LAKE OF WOODS (double space) do not cause missed matches:
sql
-- models/staging/reference/stg_reference__county_name_overrides.sql
select
trim(upper(state_abbreviation)) as state_abbreviation,
trim(source_county_name) as source_county_name,
trim(standardized_county_name) as standardized_county_name,
regexp_replace(
{{ normalize_county_name('source_county_name') }}, '\\s+', ''
) as source_county_join_key,
regexp_replace(
{{ normalize_county_name('standardized_county_name') }}, '\\s+', ''
) as standardized_county_join_key
from {{ source('reference', 'county_name_overrides') }}
The same macro is applied when staging the CMS hospital data, so both sides produce consistent join keys before the override lookup runs.
Step 3 — Apply the override and resolve the join key in the intermediate model
sql
-- models/intermediate/int_hospitals_enriched.sql (simplified)
select
gen_info.facility_id,
gen_info.county_join_key as original_county_join_key,
overrides.standardized_county_join_key,
coalesce(
overrides.standardized_county_join_key,
gen_info.county_join_key
) as resolved_county_join_key,
fips.county_fips_code as county_fips,
case
when fips.county_fips_code is not null then true
else false
end as is_county_fips_mapped
from stg_cms__hospital_general_information as gen_info
left join stg_reference__county_name_overrides as overrides
on gen_info.county_join_key = overrides.source_county_join_key
and gen_info.state_abbreviation = overrides.state_abbreviation
left join stg_reference__fips_lookup as fips
on coalesce(
overrides.standardized_county_join_key,
gen_info.county_join_key
) = fips.county_join_key
and gen_info.state_abbreviation = fips.state_abbreviation
The is_county_fips_mapped boolean makes it easy to measure coverage and surface any remaining unmapped records for investigation downstream.
Why a seed rather than a CASE statement
The seed is version-controlled and diff-able — when a new mismatch surfaces after a source data refresh, a PR adding one row to the CSV creates an auditable fix history. No SQL logic changes. The is_county_fips_mapped flag in the downstream model gives you a built-in quality check: a drop in match rate after a source update is immediately visible and traceable back to specific rows.
When this applies
Any time you’re joining across systems using entity names as keys — geographic names, product categories, provider identifiers, clinical codes — where one system’s naming is inconsistent with the reference you’re joining to, and the mismatch list is finite and discoverable from the source data.