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

I find it belittling…the notion that everything must be tested by a tester before it goes to production.  It means we test because of a procedure rather than to provide information that is valuable to somebody.

This morning our customers submitted a large job to one of our software products for processing.  The processed solution was too large for our product’s output.  So the users called support saying they were dead in the water and on the verge of missing a critical deadline.  We had one hour to deliver the fix to production.

The fix, itself, was the easy part.  A parameter needed its value increased.  The developer performed said fix then whipped up a quick programmatic test to ensure the new parameter value would support the users’ large job.  Per our process, the next stop was supposed to be QA.  Given the following information I attempted to bypass QA and release the change straight to production:

• Testers would not be able to generate a large enough job, resembling that in production, in the available time given.
• There was no QA environment mirroring production bits and data at this time.  It would have been impossible to stand one up before the one hour deadline.
• The risk of us breaking production by increasing said parameter was insignificant because production was already non-usable (i.e., it would be nearly impossible for this patch to make production worse than it already was).

Even with the above considerations, some on the team reacted with horror…”What? No Testing?”.  When I mentioned it had been tested by a developer and I was comfortable with said test, the response was still “A tester needs to test it”. 

After convincing the process hawks it was not feasible for a tester to test, our next bottleneck was deployment.  Some on the team insisted the bits go to a QA environment first, even though it would not be tested.  This was to keep the bits in sync across environments.  I agree with keeping the bits in sync, but how about worrying about that once we get our users safely through their crisis!

As I watched the email thread explode with process commentary and waited for the fix to jump through the hoops, I also listened to people who were in touch with the users.  The users were escalating the severity of their crisis and reminding us of its urgency.

I believe those who insist everything must be tested by a tester do us a dis-service by making our job a thoughtless process instead of a sapient service.