Data Warehousing Testing

From last one year I am working on data warehousing testing & I would like share how we test data-warehousing in my organization (Tavant Technologies).

We follow below 2 steps:-

1. Code verification

2. Data verification

Code Verification

We have written our own QA code in oracle which will process source data and create dimension / aggregates/stage or facts data in target tables. Once data is created we do DEV minus QA data and QA minus DEV data using all columns.

As per my knowledge most organization doesn't follow this as maintaining two codes base DEV and QA is not easy and on top of it it’s not easy to get QA resource who can write plsql.

Data Verification

In data verification we verify daily, weekly, and monthly data loads.

Daily Loads:

For daily data loads we perform 2 sigma level verifications. To do this we have written procedure which will generate 2 sigma reports and send a mail to all the team members.

Monthly Loads:

In monthly loads we do trend analysis of data flow from dimension to fact table.

We also generate pivot charts reports which helps verifying business data requirement. (Fox ex – In our case program rating report or commercial viewer ship rating report)

Apart from all this whatever quarries we have to fire that also we have put it in procedure; so you just has to pass input parameter and you will get pass/fail status.

I found whatever process we follow is very innovative and extremely reliable and as we work for TiVo their data is huge and yet we achieved zero bugs in UAT or Production.

In Tavant Technologies all this data warehousing testing practices started by Ravish Hande and now I am going along with him.

Amit Kurchania