I have not yet found an ideal solution in dealing with data quality, and find that the circumstances around the particular issue will decide how to approach it. My approaches fall into one of the following categories:

a) Reject bad data

The data modeler in me says to reject bad data. It doesn’t fit, so don’t accept it. A control table has been established for a reason – if the code or entry doesn’t match anything in it, it should be rejected. Rejected records can be reported in a myriad of ways, but the hazard of this approach is that these rejects can be easily ignored – that is, until someone decides the numbers “don’t match”. Then there is much consternation that the ETL process is “dropping” records “arbitrarily”, which is not at all true of course. But this approach does force correction in your source system, and your data warehouse contains only business-defined legitimate data.

b) Load bad data anyway

The business analyst in me says to load the bad data anyway – the data is there, so they should see it. This is problematic for a Kimball-style dimensional table, for essentially some fudging must go on. Inferred dimensional records can be created or a broad and unholy category of “unknown” must be added. Both approaches can mask the problem, the latter much more so. Bad data can reside in the data warehouse for months or even years before it is addressed, hidden away under a miscellaneous category or placeholder surrogate keys.

c) Cleanse the data

This is only possible if specific business rules or fuzzy logic can be applied to the data in question, which I find is not usually the case. If it was easy to fix, they would do it at source themselves.

d) Flag the data

Flagging the data is another approach, but also has its pitfalls – the largest being that the business users will simply ignore the flag. Surrogate key placeholders will still be required in the dimensional table. This is really just a minor variation of choice b. But it can also be useful for identifying data that is not rejected but still outside the business process norms (such as a sale price below cost or a hired employee above or below a certain age).

Identifying the source of the problem is another issue – was the data entered incorrectly or was a business process not followed that led to data outside the limits of acceptance? The difference between the two is huge and can only be determined by the business side, often only through considerable time and investment on their part. The effort they spend on this will depend on how large a problem they perceive the data in question to be.

What is of paramount importance to me is to recognize and respect your business users’ ownership of the data. This is doubly important as an outside consultant. Your users know their data and can often tell a problem instinctually. Don’t tell them a report was built to spec if they complain that data is wrong. Even if it was built to spec, the specs could be wrong – and often are. Find the source of the problem. It can be anything from an incorrect data source or calculation to a faulty assumption or misunderstanding.

Sometimes feats of creativity are required to deal with specific problems. Once I had to deal with an inventory system that went offline on weekends for certain locations, and the only source of reliable inventory data turned out to be back-dated GL entries. We had to insert adjustments into our weekend inventory snapshots after the fact based on these accounting journal entries because the official inventory count was not correct. Only your business users will know the best way to handle situations like this. The heart of the issue is that the data warehouse needs to reflect the reality of the business, and if it doesn’t you have a data quality problem.