I have done data and systems validation many times before, but the standard way is to shut a couple of people in a dark room and get them to manually check everything… for a few months!
A lot of things that can be done manually can also be automated.
If you want to do some data validation consider the following options:
- Select a sample of records (say a dozen) from every field in a database where the field value is equal to the maximum field width. With this information you have a simple report that you can quickly eye-ball to see if any information has been truncated in a given field, this can often happen when fields have been transformed or migrated from other systems
- Check the number of null values for every field. Are any fields completely null? Does the field need to stay in the database?
- Profile every descriptive field in a database. Output a report that could either be used as the basis for a new data dictionary, or it could be used to check against the values in the existing data dictionary. Again, using code we can run a second step which automatically checks the profile report against the data dictionary to identify gaps. An entire database can be checked this way. With every value you could also get an aggregate query included which counts the number of occurrences for each value.
- Validation rules for data. Check Email address formats, which email addresses won’t send? Can any be cleaned? Check Phone numbers – mobile, international, local. Will they work in the automated dialler of your telephony centre? Is there a high percentage that can be fixed without losing the integrity of the existing data.
- Do you want to create a single customer view and you have various systems and would like to populate values based on a date of capture priority, or a system priority. This can be generated using a combination of SQL and VBA.
These are just a few of the things that I have coded, executed and presented in this area. Again, it is only limited to the checks that you want to do. It can all be wrapped up in a nice database with a front-end form so that you can re-run checks at different intervals.
If you would like to develop this capability yourself you will find some helpful code under the Code Snippets menu.
If you would like to discuss the type of functionality that can be built into this type of tool then please contact me.
Alternatively, to view the Strong Password Tool Click Here