Fact table reconciliation or verify

Question

In a data warehouse project how do I verify that my fact table loaded in a data warehouse DB through SSIS ETL load is correct with my staging table so that later I don't have incorrect reporting?

Answer 1

Good question, people creates different systems for this. So you understand this is one of most complex check/reconciliation process that developers built. I tried to give you three ways to do this. I would recommend first one because its easier and most efficient.

You can -

1. Post Load reports: create reports which will reconcile data after load. Write SQL to compare source data and target data - compare count, compare amount, compare null values, compare daily data etc. If the comparison generates flag/alert - this means some issue in load.
2. Check as you go : You can create some reusable function or mapping which will compare incoming source data and target data - compare count, compare amount, compare null values, compare daily data etc. and store in a table. A script will keep on checking those values and if there is any issue, script will notify support team.
3. Pre process check : Before starting any ETL, you can check source data - like count, null values, daily count etc. to verify how is the data, if there is any file missing etc.