Data Warehouse Testing

UDA
N/A

Data Warehouse Testing

I work in a Data Warehouse as a Quality Control Analyst. Development uses Abinitio as the ETL tool and we use SQL for writing our test scripts. I am trying to find out how other shops test their existing Data Warehouse maintenance projects. We use the functional and technical specifications to create our Test Strategy, Test Plans and Test Cases. We use a subset of production data for our test bed. We test all of the transformation rules stated in the specifications (some have very complex logic), creating ‘seeded’ test data to meet the criteria of the transformation rule if the data does not already exist. My Questions: How are other shops meeting the need to test all the transformation rules? Are other shops seeding data or only using what exists in production? If all transformation rules are not being tested is there a risk factor associated with the untested rules? In your environment does the Developer run the ETL graphs for loading test target tables or does your Testers run the graphs?
Thanks.
7 REPLIES

Re: Data Warehouse Testing

I'm also in Data Warehousing QA and would be interested in seeing this discussion develop.

At my organization our test bed is a duplicate of the production DW, refreshed at the start of each release. However, it is not synced to source and so does slowly drift from the source data over the course of the testing period. We try to test all transformations but sometimes run into trouble when functional specs are not kept up to date.

Re: Data Warehouse Testing

Answer 1:
For all the transformations depending on the complexity of the logic involved the testing should be done.
Answer 2:Regarding data used:
Data can be from various environments like development,testing,QA and Production.
The important thing here is that the completee logic of the transformation should be checked.
For Eg.If the filter tranformation is to be tested and if no data(or NULL) we can use the seeded data.
Answer 3:
Yes all the transformations should be checked compulsorily.
For Eg.If a Router Transformation is not tested(and if found to be wrong) the dataflow may be incorrect leading to populate the Warehouse improperly
Answer 4:Regarding Execution of graphs/Maps
The execution of graphs/maps depends on the environment.
For Unit Testing (when done by a DWH Tester)its better always the tester to run it as it involves minute tnigs to be checked in.
For System Testing the graphs cane be run by the developer itself either instantaneously or on scheduled basis

Re: Data Warehouse Testing

Hi,

Has anyone developed any nifty automated unit testing frameworks (i.e. agile, tdd, etc...) for teradata? and are happy to share?

Cheers,

Bob

Re: Data Warehouse Testing

We ran into issues at client engagements so we created an automated test tool named QuerySurge.  You can find it at 

http://www.rttsweb.com/services/products/querysurge

Regards,

Bill

Re: Data Warehouse Testing

Hi,

Can anybody provide me a sample UAT test plan for Data Warehousing  project.

Re: Data Warehouse Testing

I recently published a book titled, "Testing the Data Warehouse". It's available at Amazon.com and through other similar sources. While planning for and writing the book, I collected many reference materials and would be glad to share them; let me know if interested. The book and other documents answer many of the questions posed in this blog post. www.testingdatawarehouse.com, wyaddow@gmail.com

Re: Data Warehouse Testing

Hi,

I have been involved in manual ETL testing for past 3 years using Sql ,data extraction and data comparison .

Could someone pls let me know the usage of teradata tool in ETL testing.