Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ch9862
Yak Posting Veteran
76 Posts |
 Posted - 2007-02-16 : 16:11:39
|
| A bit strange (?) question. I'm helping finish up some DTS packages. Customer asked for some form of data validation to be included. So I have a SP that logs status of each table DTS packages are loading - in case of success it inserts rowcount and table name into log, otherwise logs an error. But they asked for something more sophisticated; it seems that they had an error in ETL routine on another project, which went undetected for a long time. Could someone tell me what kind of data validation I could do? Any ideas appreciated... |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-02-16 : 16:18:16
|
| Validation is a very standard part of ETL.Lots of possibilities, they are going to have to tell you what they want to validate, things likeNumeric and date values within specific ranges.Strings not blank, or all spaces if they are required.Strings all upper, all lower, match a given pattern (like email addresses being valid format).No duplicates.Values in one column being larger, smaller etc. than values in another.Relationships between rows and/or tables correct (eg. no orders for customers that don't exist).Nulls allowed or not.Also, making data consistent, for example the source might have values like IL, Illinois, ILLINOIS, and ILL. in a given column, and you should import them all as IL. |
 |
|
|
ch9862
Yak Posting Veteran
76 Posts |
Posted - 2007-02-19 : 23:38:52
|
| Sorry for late reply.Thanks for the suggestions. It's true that I could count NULL values to make sure some columns were actually processed. However - the customer was talking about mechanism for detecting incorrect values, many of which are calculated. So how am I to check if (for example) I used records with correct dates for some calculation? Thanks for any ideas... |
|
|
|
|
|
|
|
|
Data validation