Designing ETL data reconciliation routines
Reconciliation routines are automated ways to check the data integrity using predefined business rules. These routines look at the content of the information and record the results. Information is then compared to determine if it matches.
Getting ready
Identify the business rules to compare and validate information as it flows through the business intelligence and data warehouse solution from the source environment to the target environment. Review your data lineage from Chapter 8, Analyzing the Sources, specifically for fact table loads. Along the stream for fact loads are excellent places for data reconciliation scripts.
How to do it...
Proving data integrity to the business users is key. Automating this allows one to include it into the process flow and have it generated upon load automatically:
1. Create the reconciliation control table:
CREATE TABLE "DW_RECONCILE" ( "SCRIPT_ID" NUMBER, "SCRIPT_NAME" VARCHAR2(50), "SCRIPT_PHASE" VARCHAR2(50),...
