15

ETL projects are projects created using an ETL (Extract - Transform - Load) tool such as SSIS, PowerCenter,etc

These typically involve reading data from an external source, loading it to a staging database, performing certain transformations and loading it to a final database

A simple example would be to use SSIS to read excel files provided by schoolteachers using SSIS and load them into a database. Then write stored procedures or more SSIS packages to calculate the grades of each student and load that data into a data mart\warehouse

You then build stored procedures on top of the mart to generate output which is used by reporting tools (SSRS\Excel\etc) to generate visualizations.

I'm trying to understand how to perform TDD and proper unit testing in this scenario. Tests for ETL's are mostly about ensuring the data loaded in the staging tables matches is the right subset of the data from the source. So implementing a test for it leads to implementing a mini version of the ETL. The output of the report SP's depends on the data in the tables themselves, so one cannot have a stable set of output data without a maintenance nightmare even if you do create a database containing scrubbed test data

Example:

Sprint 1 : Student table contains Name, Age, Grade

You create test data for this table, and unit tests based on that

Sprint 2: A gender field is added to the table.

Now, if you refresh the data in the student field to populate the gender attribute, the test cases are invalidated since the data changed. And if you dont, you cant create test cases that require the gender column

Improve this question
10
  • It's not TDD if the tool you're testing already exists. Just write ordinary tests.
    – Robert Harvey
    Apr 7, 2015 at 17:10
  • @RobertHarvey In effect the ETL tool is no different from the .Net Framework when writing C# code isnt it? So, the tool exists in the same way the .Net framework exists, yet you can do TDD in C#
    – user87166
    Apr 7, 2015 at 17:35
  • I wouldn't test Framework methods either. I assume they already work. If you're testing a configuration for an ETL tool, you don't need to re-create the logic in the ETL tool to do that; just use the tool.
    – Robert Harvey
    Apr 7, 2015 at 17:35
  • 1
    Then write the tests, using expectations that you expect to get from the ETL, the proposed data, and the proposed configuration. Make them conceptual tests, if you like, but the functionality already exists. Pure "test first" development is for things that don't exist yet. Whatever you do, Don't reinvent the ETL tool!
    – Robert Harvey
    Apr 7, 2015 at 19:03
  • 2
    "since the age in the base data changed" - what is so hard about providing stable test data as input? If there are time depend calculations involved, mock the reference timer out.
    – Doc Brown
    Apr 7, 2015 at 19:47

2 Answers 2

Reset to default
3

What I have done in the past is use Acceptance Test Driven Development. ETL code is often distributed across different stages/languages and technologies AND tightly coupled. Most ETL process are dependent on the sequence of transformations in the pipeline.

The risk in using unit test only in ETL is that it won't cover the integrations. The sequencing of transformations is an equal part to the actual transformations in many ETLs. If I am spending resources on creating an automated test suite I would make sure it covered the sequencing as well.

I would focus on TDD for each unique transformation sequence or at least include these tests in a larger test suite. If there are too many combinations you may have to pick and choose which sequences to test. The idea is to validate the ETL pipeline for the data set(s) it will be used on. As well as making sure you have test coverage on all your code.

Improve this answer
0

ETL can be done with TDD and tests pretty similarly to most projects, i.e.

write a test that fails (red) fix the failure (green) make the code performent & maintainable (refactor)

So for ETL that might be:

  • write a script to load 1 record
  • fail (no data source defined)
  • define source [green]
  • no refactor need
  • write a test to load 1 record with only 1 field filled in
  • fail (no code written for that field)
  • define code details for that field
  • refactor
  • define failing tests that look for attributes having valid values [red]
  • etc
Improve this answer
1
  • The first 8 steps have nothing to do with TDD, as they don't leave any tests behind. The rest is not clear.
    – Bulat
    Mar 11, 2017 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.