There are basic things you can do with any set of data:
- Validate values (String length tolerance, data type, formatting masks, required field presence, etc.)
- Range correctness (Does this seemingly correct data fall within expected ranges of values)
- Preliminary processing (If I attempt to analyze this data, can I perform the basics without running into errors)
- Preliminary reporting (run a report against a data set and ensure that it passes a sanity test)
- Defining null vs. empty vs. zero vs. False for any given column of data
- Identifying data that is out of place (numeric values dramatically different than other values in a data set, string values that look like they might be misspelled, etc.)
- Eliminating or correcting obviously errant data
Understanding data to identify errors is a whole different ball game, and it is very important.
For instance, you can have a rule that says a serial number must be present in a given data set and that serial number must be alphanumeric with a maximum string length of 255 and a minimum string length of 5.
Looking at the data, you may find one particular serial number value reads
"PLEASE ENTER SERIAL" It's perfectly valid, but wrong.
That's kind of an obvious one, but say you're processing stock data and you had a price range for 1000 stocks that was under a dollar. A lot of people would not know that a stock price so low is invalid on certain exchanges and perfectly valid on others. You need knowledge about your data to understand if what you are seeing is problematic or not.
In the real world, you don't always have the luxury of understanding your data intimately.
The way I avoid problems is by leveraging the people around me. For small data sets, I can ask someone to review the data in it's entirety. For large ones, pulling a set of random samples and asking someone to do a sanity check on the data is more appropriate.
Further, questioning the source of the data and how well that data source can be trusted is imperative. I often have multiple conflicting sources of data and we create rules to determine the "source of truth". Sometimes one data set has great data in a given aspect, but other data sets are stronger in other areas.
Manually entered data is usually what I'm most skeptical about, but in some cases it is stronger than anything that can be acquired through automation.