Injecting TDD into your ETL with the SQL Server Stack

Thoughts on “Injecting TDD into your ETL with the SQL Server Stack”

1. Kent Jiang, China GDC, Lead Technical Consultant, OS CHN January 12, 2012 at 10:16 am

   Hi Andrew,

   Thanks for the post and good to know that you are using TDD methodology in BI project. We are also using agile approach in our data warehousing/ETL project with Informatica and we have a dedicate QA team.
   I agree that tester can prepare test case prior to do development, but in our case both tester and developer have to follow a same mapping design document.
   • With TDD in ETL , did you guys also do source-target data comparison? Can it be implemented by test case package in SSIS?
   • Can TDD framework be a automation-testing for function ?

   Thanks,
   Kent
2. Duane Schafer Post author January 12, 2012 at 10:21 am

   Kent,

   Thanks for the reply. It is good to know someone out there reads these things. To answer your questions:

   1) Question: With TDD in ETL , did you guys also do source-target data comparison? Can it be implemented by test case package in SSIS?
   Answer: Yes, we did store source and target comparisons. That is what the report shows in #3 of the examples. In #1 of the examples it shows how the data is recorded into an audit table. This example shows stored procedure calls within another stored procedure. These calls can easily be moved to a task in your ETL tool as long as the variables can be passed. The trick will be to use another ETL task to capture the information that you want to record. The framework makes comparisons by the test case number. You will want one for your source and one for your destination. The Audit Check at the end runs an algorithm that compares source to destination by test case number. Test case number is the key to the framework. If source and destination match for a metric or record count, the report will show a green check mark. If it does not match, the report will show a red X.

   2) Question: Can TDD framework be a automation-testing for function?
   Answer: If I understand your question, TDD is for unit testing and regression testing – running all of your unit tests again after a change to see all tests still pass. TDD should not replace other quality control and assurance functions within the organization. Other types of testing that may need to be done for a solution are stress/load, business process, and/or compliance testing. A great example of when a regression test of your TDD unit tests is really valuable is when the client asks for one last change before the production rollout. Without this final change, the solution will not completely solve the business problem so it has to be included. Unit testing is performed on the part of the ETL that has changed. But without doing an impact analysis, you are not completely sure that another part of the system has been impacted. By running a complete regression test, you will know with confidence your change did not impact other areas. The key to TDD is creating the tests that match your requirements.

This site uses Akismet to reduce spam. Learn how your comment data is processed.