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.

Happy Data Warehouse Testing!

[TestMethod]
        public void VerifyCDCUpdate_FactOrder()
        {//get some data from the source
            var fields = DataSource.ExecuteReader(@"
SELECT     TOP (1) OrderID, OrderName
FROM         Database.dbo.tblOrder");
            var OrderID = (int)fields[0];
            var originalValue = (string)fields[1];

            //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";

                DataSource.ExecuteNonQuery(
                    @"UPDATE Database.dbo.tblOrder SET OrderName = @NewValue WHERE OrderID = @OrderID",
                    new SqlParameter("@NewValue", newValue),
                    new SqlParameter("@OrderID", OrderID));

                var startTime = DateTime.Now;
                var valueInDW = originalValue;
                while (DateTime.Now.Subtract(startTime).Minutes < 10)
                {
                    // Verify the value in the source database is still what we set it to, otherwise the test is invalid
                    var updatedValueInSource = DataSource.ExecuteScalar<string>(@"SELECT OrderName FROM Database.dbo.tblOrder WHERE OrderID = @OrderID",
                            new SqlParameter("@OrderID", OrderID));

                    if (updatedValueInSource != newValue)
                        Assert.Inconclusive("The value {0} was expected in the source, but {1} was found.  Cannot complete test", newValue, updatedValueInSource);

                    //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));
            }
        }