Hi TDA User,
We are working on a set of tools that will help you with data quality, whether that is data validation or data cleansing. This can be a large area and I am in need of your input. If you have a moment, please provide details (and more importantly examples) of your specific data validation/cleansing needs.
Here is a list of questions to give you an idea of the type of data I am looking for. You don’t have to answer every question. I am most interested in details of specific validation/scrubbing problems that take you a lot of time and if automated would lighten your work load.
-
Do you write and execute data validation statements on your source data?
a. What types of data validation checks do you perform?
i. Single column checks
-
Looking for null Values -
Checking specific values exist -
Checking specific values do not exist -
Checking values are within a range -
Checking values exist in a reference table -
Finding values not in an a reference table -
Data type validation -
Format/Mask validation (Address, emails, phone numbers, Addressing Names, SSN etc) -
Checking for fuzzy or exact duplicates ii. Multiple column/row checks -
Checking a set of columns add up to another column (total or concatenation with a mask). -
Checking aggregation of a column grouped by a surrogate key reconciles to another set. -
Checking values of a 2<sup>nd</sup> column are consistent for same surrogate key
b. What do you do with validation errors?
-
Do you write & execute data cleansing/scrubbing statements?
a. What type of data scrubbing do you do?
b. What happens to your dirty data?
Please include any SQL or sample data. You can reply to this post or email we directly. dpeabody@quest.com
Hope to hear from you soon.
Thanks, Debbie