What are the ways to avoid dirty data?

Answer 1

Dirty data is incorrect or incomplete input and can originate in many ways. However, we should also consider the method through which data is determined to be, in fact, dirty. For example, a record may have the incorrect address for a customer but that isn't to say that when the record was created the address was incorrect, it may have changed. Timing and maintenance also play a role.

Generally speaking, data is moved through a three step process known as E.T.L. (Extract, Transform & Load). Extraction is the process of sourcing the data; data entry or from a device or location. Transformation is the process used to validate and coerce the data into predetermined formats. Loading is the process by which the formated data is moved to it's destination.

Most dirty data originates from processes involving data entry and is a result of human err, poorly defined or understood process requirements and/or inadequate validation/error handling methods.

The first step is to completely and accurately define requirements. What data is being collected, where does it come from and how often (Extraction)? How will the data be validated and errors reported (Transformation)? How will it be stored (Loading)?

Only after these questions have been answered can the E.T.L. process can be designed. Once designed, the process requirements are disseminated to the extraction source.

Within user based extraction processes there are many common methods to reduce dirty data. Some of these are; spell checks, data type validations, required fields and value limited input controls such as check, combo and list boxes. When designing the extraction method (or front end for user based systems) the general rule of thumb is the less data entry the better. Other common methods include tool tips (floating help boxes), top to bottom, left to right, tab ordered fields (for heads down data entry) and visual/audible cues for data validation exceptions.