How to handle bad zip codes

[Jeff Smith]

My company acts as a third party vendor. A new cleint does not want us to cleanup geographic information that comes they send to us. 2 things happen as a result. 1) we can get multiple spellings for the same city, and 2) we can get Zip Codes that do not exist in the corresponding city or even state.

I have a Zip Code Dimension table that I load from an outside source. The source has the Zip Code, City, State, and County. In the Zip Code dimension, the hierarchy is Zip Code to City to County to State. This isn't perfect because zip codes can cross cities, counties and states, but it's what I was told to use.

In the past, I would ignore the City and State information that cames with the Member data (for example) and assign the dimension key base solely on the Zip Code. But the new client wants to keep the City, State, and Zip as it comes to us.

My gut says do 2 things - 1) keep using the Zip Code Dimension that I've built, 2) create a new geographic dimension that is essentially a Junk dimension based on the Zip Code, City, and State as recieved from the client.

I would love to get other people's comments.

[ngalemmo]

Option 2 sounds like a reasonable compromise.