How far would you go in ETL and data warehouse testing?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

As a general principle, testing is about risk/reward, which comes down to cost in terms of money/time/resources (people/machines) in various combinations.

The test coverage curve will be some sort of log curve , where the most benefit in testing occurs in the initial set of tests.

The first 500 tests give 62% coverage, the next 500 tests only give an extra 7% coverage, the next 500 tests give an extra 3% coverage, etc. Obviously, this is a case of diminishing returns, where one could execute an infinite number of tests and not really gain any better confidence in the system correctness. My numbers are arbitrary to illustrate the point.

You have mentioned some testing that I would call Unit testing, but I think you are jumping past testing steps that will give you improved correctness of the database. See this excellent SE answer on types of testing.

This will pick up some problems that you haven't realised exist until UAT. It sounds like there should be more extensive Acceptance Testing (by IT) but it also sounds like the scope of UAT isn't broad enough, if there are errors that are still being found in the UAT. The business analysis hasn't does a good enough job of finding the edge cases or understanding the data conflicts. This really should have been understood in the design phase (depending on the implementation methodology you are using and how much refactoring you plan to do; but that is another discussion).

Answer 4

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.

Answer 5

ETL is commonly associated with Data Warehousing projects but in reality any form of bulk data movement from a source to a target can be considered ETL. Large enterprises often have a need to move application data from one source to another for data integration or data migration purposes. ETL testing is a data centric testing process to validate that the data has been transformed and loaded into the target as expected.