So I write a number of integration tests. Currently I am working to provide a way to compare all tables involved in an ETL (source to target). I have done this in the past with a single test for each table pair (or sets of tables if more than one table on the transactional side is flattened into a single target table). I always try to force people on my team to write this to assert the test conditions. Too often people lazily write the test against each table, compare manually, and throw the SQL away as it has no long term value. They start from scratch with every test run. If you write your integration test like a unit test, it has immediate reusablity, as well as the ability to be included in a continuous integration build.
I currently have a simple ETL to test, which spans 150 tables. I have three types of ETL tests. First are the extract and load tests: row counts and meta data date checks. Thirdly I have the column by column transformation tests. I do not have time to write the 150 tests to test all of the tables I need to test. As such, I am exploring using dynamic SQL to iterate through a list of server/database/table pairs to compare the raw row counts. Once I get that working, I can try to extend it to column comparisons (for those columns where there is no data transformation). Lastly I will add transformation logic to test transformed columns. This is the same asserting test pattern I referred to above, with an expected, actual, and evaluation logic. That logic looks like this
CASE WHEN (@EXECTED = @ACTUAL) THEN 'PASS' ELSE 'FAIL' END AS 'TestName'
This post is actually about how to replace that CASE statement with the simplified IIF built in function from SQL Server 2012. (I will post the solution to the dynamic SQL test once I have it figured out).
In SQL Server 2012 there is a new built in function that allows the CASE logic above to be slightly simplified. It reduces the keywords required for the comparison from five to one. There are fewer typos and thus fewer iterations arrive at a passing (or failing) test.
IIF(@EXECTED = @ACTUAL, 'PASS', 'FAIL') AS 'TestName'
Best thing for me about this is that I can always remember where to put the AS 'TestName'. Half the time I write AS before concluding the CASE with and END statement. I am glad to be ride of that recurring fat fingering.
I currently have a simple ETL to test, which spans 150 tables. I have three types of ETL tests. First are the extract and load tests: row counts and meta data date checks. Thirdly I have the column by column transformation tests. I do not have time to write the 150 tests to test all of the tables I need to test. As such, I am exploring using dynamic SQL to iterate through a list of server/database/table pairs to compare the raw row counts. Once I get that working, I can try to extend it to column comparisons (for those columns where there is no data transformation). Lastly I will add transformation logic to test transformed columns. This is the same asserting test pattern I referred to above, with an expected, actual, and evaluation logic. That logic looks like this
CASE WHEN (@EXECTED = @ACTUAL) THEN 'PASS' ELSE 'FAIL' END AS 'TestName'
This post is actually about how to replace that CASE statement with the simplified IIF built in function from SQL Server 2012. (I will post the solution to the dynamic SQL test once I have it figured out).
In SQL Server 2012 there is a new built in function that allows the CASE logic above to be slightly simplified. It reduces the keywords required for the comparison from five to one. There are fewer typos and thus fewer iterations arrive at a passing (or failing) test.
IIF(@EXECTED = @ACTUAL, 'PASS', 'FAIL') AS 'TestName'
Best thing for me about this is that I can always remember where to put the AS 'TestName'. Half the time I write AS before concluding the CASE with and END statement. I am glad to be ride of that recurring fat fingering.
It's interesting that MS took the IIF syntax from Visual Basic (http://msdn.microsoft.com/en-us/library/27ydhh0d(v=vs.71).aspx). I always disliked the T-SSQL CASE syntax.
ReplyDelete