How to do Data Warehouse Testing, Step by Step Guide

Data Warehouse testing requires strong SQl skill and good understanding of DW concepts(you can learn DW concepts from this blog).I am going to explain a list of methods which I have successfully implemented.

Data Warehouse QA Skil Set:- The person must have very strong SQL Skill and good understanding of Data Warehouse concepts, he should have good knowledge on test case preparation

What QA Need :- The ETL Mapping document(I have explained about it here) will be the core document for QA to prepare test cases, the document will have business rules for each tables/extracts and how it should get loaded into Warehouse target tables.

1) ETL Mapping document

2) Data Model document(to understand the relationship between tables)

3) Access to Source/Target Database

4) Access to reporting toll(if you are testing via reporting tool)

Preparition :

           The QA person should start creating test cases and SQL scripts to pull data out of source/target systems, this task can get started as soon as the data model is ready and no need to wait until the ETL development complete.

Different type of sources :

           Source data for target table can come from files or tables from different database.

What if files :- If your source is a file then , it has to be loaded as a raw data into table QA database for testing, this method will help the testing process to have one common method to compare data between two different tables rather than file and table. 

A task should be raised in the begging of the project to have developer load the raw source file into a table for QA to access

What if Table : Create a script on source table with business rule(if any) and compare against target table

Top down testing Approach : I recommend the following testing approach to begin with

1) Create a row count,sum(measures) script on source and tables to validate

2) Create a script to validate the surrogate key in fact table is available in dimension tables
3) Table partition check script
4) Primary key/Index checking script
5) Row/column level validation can be performed using excel sheet manually or using automate tool