Fix Mismatched Zip/State Combination

What Does It Do?

The OUD-DS3 best practice is for address zip code and state combinations to stay in-sync and in alignment with US Postal Service data.

This integrity check identifies active address records where the zip code and state combination do not align with USPS so that they can be reviewed and updated. 

How Is This Done?

This integrity check is run using a SQL Server view called [INTEGRITY_CHECKS].[dbo].[V-INTEGRITY CHECK-ADDRESS-Fix Mismatched Zip State], which is located in DB-OUD > Databases > INTEGRITY_CHECKS > Views.

That view queries active US  address records for individuals, except Business, 2nd Bus[iness], and UM-Campus addresses where the address state is not in alignment with USPS data for that address’s zip code when compared to the USPS mapping of both physical and delivery zip codes to states.

These records need to be reviewed individually for correctness (either the zip code or the state could be wrong), but the USPS state for that delivery and physical zip-5 are provided in the output. Note that there are only 19 instances where the delivery and physical zip codes correspond to different states, and several thousand instances where the delivery zip code has no corresponding physical address or state (it only exists for delivery/mailing purposes).

Resources and References

S:\IDS\MES\SQL Scripts\DATA QUALITY\ADDRESS

Physical vs. Delivery Address

USPS Mapping of ZIP Codes by Area and District codes (using version last updated 2024-07-01)

Snapshot GUID: BBDW.FACT_CONSTITUENTADDRES.CONSTITUENTADDRESSSYSTEMID