Software Quality Assurance & Testing Stack Exchange is a question and answer site for software quality control experts, automation engineers, and software testers. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Recently I've been testing on an ETL/Data warehouse project and the strategy I took was to work very closely with the business and leverage their knowledge to come up with all the weird and wonderful use cases with which they would have as well as testing your usual data integrity, transformations, lookups, etc.

On top of that I built an automation tool that takes in XML business rules to compare schemas, data, lookups and making sure the data flowed correctly end to end without truncations, calculation errors, etc. Admittedly it's a work in progress and I only spent 2 days on it but it does 90% of the job for me so that the rest of the time, my team and I could test scenarios that the business had and a few of our own and also within the business requirement limits.

However we found a host of new issues in UAT that points to new scenarios that does not conform to the business rules or business processes and hence breaks the code. The scenarios are not simple scenarios or logical scenarios. E.g. A piece of cargo currently resides in the UK and Australia AND New Zealand. Mainly it's a clash of conflicting source data because people somehow entered the same data from different sources so that data is valid on it's own but together does not make sense.

My experience being quite shallow in the world of ETL so I'd like to throw the question out there, so do you

  1. test everything to business requirements and contracts between the sources and deal with these "oddball" scenarios in UAT and BAU?
  2. like web/desktop applications and assume that people using their systems can do absolutely everything and anything and assume the worst and test everything? (Exhaustive testing, which is impossible cause permutations are almost infinite)
  3. Or is there a happy medium?
share|improve this question

It is not possible to do exhaustive testing in the context of data warehousing. I had been into ETL testing. There was a corner case bug in which if there is a lock created on one of the tables in the source database, the migration of data to the destination DB failed. Is it possible for any tester to imagine a scenario where table gets locked automatically during a transaction?

We created a lock using SQL to reproduce the issue in test environment. However, on debugging LIVE logs in real-time, we found that the lock was created due to a long running SQL query which tried to fetch a record from millions of rows of data. The bug caused missing transactions and around $400K loss of money to client. We fixed the issue and brainstormed ways to improve test coverage.

ETL testing is a critical area and since it is DB-related there are many variables other than permutation-combination that impact it. I would recommend you to do exhaustive testing for the basic functionalities of the system without worrying about 100% test coverage. The above issue is a DB design issue which is out of tester's control. If the DB is partitioned properly the SQL query can fetch data quickly even if there are 1000 users using it. We moved the records that could not be migrated successfully to an exception queue as a temporary solution.

The issue you had cited looks more like a product design/architecture issue which should have been caught in the code review/peer review by the DEV. The product should allow users from UK, Australia and New Zealand to concurrently use the system. When they enter the same data there should not be any clash/conflict.

share|improve this answer

There are lots and lots of tests that can be carried out in ETL and data warehouse testing. It helps if you have a set of prioritised requirements as to what you want to try and test.

The usual 3 'functional' checks are:

  1. Have you checked the data coming in is ok? (garbage in garbage out) Often times using the information from data profiling and looking over the business rules will help identify potential issues.

  2. Is the data processing ok? I don't know what your automated tests cover, but you may still need to check boundary cases, division issues, nulls, referential integrity issues?

  3. How is the data output looking? This is when you usually find out if the business rules actually seem to be right or not.. Also it's when you'll be able to check if the data is presented as per the specification.

And chances are, if it's a new system, after clients have actually seen the outputs they're likely to change their minds on certain business rules, so you probably want to be ready for change. :)

You may have other 'non-functional' metrics you may need to test such as performance + load testing, security, etc.

As far as the original question is concerned, I would go as far I could given the budget and project priorities.

share|improve this answer

Updating to reflect exit criteria for testing. You can look at MSDN article - Adventures with Testing BI/DW Application:On a crusade to find the Holy Grail -

Critical areas to focus are

  • Validating the ETL Scenarios for full, delta pull
  • Testing with Production Data for multiple regions, multiple data sets
  • Validating DataMarts, Reports results for the datasets

Hope it helps.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.