As it is officially braai (barbeque, BBQ) season in South Africa, I decided to compare the elements involved in testing ETL tools with that of a braai. After all, you would not simply light a fire and throw your carefully selected steak onto the flames.
There are several steps you take in the process to ensure that your steak is cooked to your satisfaction. As a data warehouse is not a specific type of technology and there are many types of ETL tools available, proper testing is essential to ensure that your ETL tool is servicing your data warehouse correctly.
So, in the below, we compare the testing of an ETL tool to the testing of your braai skills.
1. When should testing happen and how often?
o ETL: ETL Testing should take place during development of a new system and when any changes occur to that system (source system changes (upgrades) or manual customizations to the reporting solution).
o Braai: Once you have successfully built your braai, and the lit the fire, is important to regularly test the heat of the braai to ensure that you cook your steaks at an optimal temperature. Make the necessary changes to the braai, stoke the coals, add additional fire wood, and change the location of the Weber if necessary.
o ETL: Occasional testing beyond implementation that can usually be accomplished through managed reporting (if you get an error report from the system this is an indication that something is wrong).
o Braai: Occasional testing is necessary now that you have adjusted your fire. High flames are an indication that it is nowhere near ready; take caution around the open flames.
2. Who is responsible for testing?
o ETL: During implementation – the implementation consultant/developer.
o Braai: During initiation of the braai / fire – the braai master (developer who is usually pedantic about the fire they have lit) is responsible for testing.
o ETL: During Customizations to the report tool – the implementation consultant/developer performs these changes.
o Braai: On occasion the braai master may send a braai assistant (consultant) to check or adjust the braai.
o ETL: During daily automated runs – System administrator (SQL Administrator).
o Braai: Advanced braai masters (developers) have automated tools such as a braai thermometer that will sound an alarm when the optimal temperature is reached reducing manual intervention.
3. What are the benefits of testing ETL?
o ETL: Faults are eliminated early – before the reports get to Business Users.
o Braai: Faults are eliminated early – having a braai with sub-standard charcoal or braai wood will affect the end result.
o ETL: The system ends up being more stable and reliable
o Braai: Your guests at your braai can trust that they will have a phenomenally cooked steak.
o ETL: Users can trust the system (proof can be shown that it balances).
4. Types of ETL Tests
o Constraint Testing (Developer checks the following constraints (NOT NULL, UNIQUE, Primary Key, Foreign Key, Default, & NULL)
o Source to Target Count and Data Validation
o Duplicate Checking
o Incremental and/or Historical load processes
o Regression Testing (Does a fix on one field break any other fields)
5. Phases of ETL testing:
o Requirements/Design Review
o Test Planning (What tests to perform)
o Test Designing (Build the tests)
o Test Environment setup
o Test Execution
o Test Reporting (results of the tests)
Most braai masters will tell you that there is a very specific method that each one of them uses to create the best braai. The same is relevant to ETL developers and consultants when it comes to testing ETL Tools on the data warehouse.
Our approach is to use data warehouse automation tools such as TimeXtender, as they reduce the dependency on senior BI technical skills as well as reducing development time by an estimated 80%.
Image credit: rapgenius
[Originally appeared on the Karabina/IS Partners company blog 18 September 2014]