Our Data Warehouse uses Change Data Capture (CDC) to keep its tables current. After collaborating with one of the programmers we came up with a pretty cool automated test template that has allowed our non-programmer testers to successfully write their own automated tests for CDC.

We stuck to the same pattern as the tests I describe in my Automating Data Warehouse Tests post. Testers can copy/paste tests and only need to update the SQL statements, parameters, and variables. An example of our test is below. If you can’t read C#, just read the comments (they begin with //).

BTW – if you want a test like this but are not sure how to write, just ask your programmers to write it for you. Programmers love to help with this kind of thing. In fact, they will probably improve upon my test.

//make sure the above data is currently in the Data Warehouse var DWMatch = new DataSource("SELECT OrderID, OrderName FROM FactOrder WHERE OrderID = @OrderID and OrderName = @OrderName", new SqlParameter("@OrderID", OrderID), new SqlParameter("@OrderName", originalValue));

//fail test is data does not match. This is still part of the test setup. DataSourceAssert.IsNotEmpty(DWMatch, "The value in the datawarehouse should match the original query");

try { // Set a field in the source database to something else var newValue = "CDCTest";

//start checking the target to see if it has updated. Wait up to 10 minutes (CDC runs every five minutes). This is the main check for this test. This is really what we care about. valueInDW = DataSource.ExecuteScalar<string>(@"SELECT OrderName FROM FactOrder WHERE OrderID = @OrderID", new SqlParameter("@OrderID", OrderID));

if (valueInDW == newValue) break; Thread.Sleep(TimeSpan.FromSeconds(30)); } if (valueInDW != newValue) Assert.Fail("The value {0} was expected in DW, but {1} was found after waiting for 10 minutes", newValue, valueInDW); } finally { // Set the value in the source database back to the original // This will happen even if the test failes DataSource.ExecuteNonQuery( @"UPDATE Database.dbo.tblOrder SET OrderName = @OriginalValue WHERE OrderID = @OrderID", new SqlParameter("@OriginalValue", originalValue), new SqlParameter("@OrderID", OrderID)); } }

Who am I?

My typical day: get up, maybe hit the gym, drop my kids off at daycare, listen to a podcast or public radio, do not drink coffee (I kicked it), test software or help others test it, break for lunch and a Euro-board game, try to improve the way we test, walk the dog and kids, enjoy a meal with Melissa, an IPA, and a movie/TV show, look forward to a weekend of hanging out with my daughter Josie, son Haakon, and perhaps a woodworking or woodturning project.