Validating data approaches

[saifnawaz]

Hi,
Can anyone direct me to which are are commonly used approaches for validating data in a target table after I populate the table through an ETL tool or any other means.
Thx

 

[Elsebet]

What do you wish to validate the data against? The source system? Staging?

If it's the source system, perhaps there are existing reports based off that source system you could compare. I know for a project where I recreated mainframe-based reports in an Oracle Reports/DW platform I did a lot of that. It's basically working with a user to compare your new figures against theirs. Also, we sometimes compared values in staging tables to what went into target tables by simple queries, and likewise compared row counts of source files to row counts of staging tables by queries.

Perhaps you mean something more like auditing? If so, you can create a general auditing table which can store historical row counts, dollar amounts, or whatever facts you feel are compelling. Then you can create a simple report with the tool of your choice to show how your most recent load compares historically. You could set warning thresholds, perhaps if your row count figures differ from 5-10% month-to-month this would trigger a warning in an e-mail/report.

That's all I can think of unless you have more detail on what you are trying to accomplish?

 

[johnherman]

Things which need to balance include record counts, money, and other measures such as item or inventory counts, weights, hours, or other summable fields. JenPrichard is correct in that the balancing needs to be done from source system to staging to warehouse to datamart to report or cube. All must be in balance.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright