Data warehouse (DW) testing is a far cry from functional testing. As testers, we need to let the team know if the DW dimension, fact, and bridge tables are getting the right data from all the source databases, storing it in such a way as to allow users to build reports, and keeping it current.
We are using automated tests as one of our test approaches. Contrary to popular belief, we found DW testing to be an excellent place to build and reuse automated tests. We came up with a simple automated test template that is allowing testers who have never done test automation to build up a pretty sweet library of automated tests.
Here's how it works:
Our automation engineer built us a test with two main parameters we pass in; a SQL statement for source data and a SQL statement for target data. The test compares the two record sets, including all data values and asserts they are equal. As the programmers build the various DW dimension, fact, and bridge tables, the testers copy and paste said automated test, and swap out the SQL with their SQL for the current test subject.
One of our most important tests is to rebuild the programmer's SQL ourselves, which is a manual process. If the tester's datasets don't match the DW, there may be a problem. This is where most of the defects are found. As we manually build our SQL, we plug it into an automated test that actually compares every record and column data value on millions of rows, and tells us where the differences are. The byproduct is an automated regression test.
Sure, rebuilding much of what the programmers did is challenging. We've had to learn case statements, cross DB joins, data type casting, and keep our heads straight while working through queries that include 30 or more joins. But normally it's fun and easy to get help from Google or our programmers.
We've been getting one or more automated tests completed for each dimension, fact, or bridge table; sometimes as early as in the development environment. When the DW is deployed to various environments, we normally just execute our automated tests and interpret the results. It's quite beautiful.
I’ll blog about some of the other tests we execute. There appears to be a shortage of pragmatic DW test ideas out there.