What are some real world strategies for testing ETL and data warehouse applications

Question

Does anyone have any advice to share on how to successfully test BIG ETL or data warehouse applications?

I have my own ideas of-course, but there only appears to be one main article on line that covers the basics:

• Data completeness. Ensures that all expected data is loaded.

• Data transformation. Ensures that all data is transformed correctly according to business rules and/or design specifications.

• Data quality. Ensures that the ETL application correctly rejects, substitutes default values, corrects or ignores and reports invalid data.

• Performance and scalability. Ensures that data loads and queries perform within expected time frames and that the technical architecture is scalable.
• Integration testing. Ensures that the ETL process functions well with other upstream and downstream processes.
• User-acceptance testing. Ensures the solution meets users' current expectations and anticipates their future expectations.
• Regression testing. Ensures existing functionality remains intact each time a new release of code is completed.

Answer 1

I agree there's not much out there - I took on my first (and so far, last) data warehouse testing project a couple of years ago and found it pretty hard to find good advice. I haven't responded so far, as I think my experience having done just one project is fairly slight so I was waiting to see if you got more useful responses.

Some good resources:

Karen N Johnson has useful articles about BI testing: part 1, part 2, and here's one specifically about testing SCDs. I've found what she has to say about BI testing really valuable.

There was a BI Testing group on the Software Testing Club.

I remember the big challenges for us being around deciding where the highest risks were, given limited time and resources for testing, as well as needing to suddenly get very familiar with data warehousing without having any prior experience in the test team. We ended up working very closely with the developers, and doing a lot of exploratory testing to scope out what areas were causing the biggest headaches.

Strategies we used that I'd use again:

1. Creating data mapping spreadsheets to understand how data from our data feeds ended up in the final data warehouse was an useful exercise for us, even though we didn't create them for everything. It helped us to build up a much better understanding of how the data flowed through the system, that we could ask the designers and developers to review.
2. Focusing in: Using known datasets for field-level and row-level tests for key business scenarios we'd identified
3. Defocusing: as integration testing was our main focus, we also wanted to widen our search again to catch any scenarios that we hadn't considered, so as well as identifying specific tests, we also grabbed a lot of actual data generated by the test source systems and reviewed that to see what other nasties turned up. (Quite a few, as it happened - this was very productive for us and caught a lot of issues that would have broken the production batch run).
4. Don't forget flow tests - extend your scenarios over time, for instance don't just have single orders that get processed through, have an order that's raised, updated, amended, cancelled, re-opened, processed. Messy, ugly, real stuff.

Answer 2

There is a lot of overlap between ETL and DB testing, where both need similar types of test data to stress-test operations with what will represent reality later. For more details see this article.

Answer 3

1. Starts the project with run the jobs manually.
2. Check all the source data is loaded into target or not.
3. Write SQL queries to compare source and target data.
4. Review the column mapping document.
5. Check surrogate key is generating unique value or not.
6. Check surrogate key value is not null.
7. Check primary key value is not null.
8. Check the count of source and target tables.
9. Is the business logic applied in the source table is showing correct updated value in target table or not.
10. Check is there any duplicates in target table.
11. Check in target no columns is having null values.

These are the basic points to do as a ETL tester. If there is any other than that give me other contents.

Answer 4

This is a huge question. I don't think it can be fully answered without further details. I'll try anyway though, for the more common cases.

If this is an in-house app, that needs only to run on one environment, then you should start by adding detail to the things you listed. My favourite things are the ones that alwaya find some bug somewhere in the data layers:

• Special characters
• Different alphabets (varchar vs nvarchar)
• Right-to-left languages
• long strings
• database collation issues

If this is going to be installed on more than one site, you get to test all the different projected environment combinations.

• Operating system type/version
• Database type/version
• Underlying storage

Then there's the usual stuff we never remember that needs to be spec-ed and tested, like Disaster Recovery procedures, Backup/Restore, Outage handling (network crash for example) and so on.

In two words: Negative testing.