Unit-testing – How to unit test \ use TDD methods for ETL’s and reporting projects

sqltddunit testing

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

Best Answer

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.

Related Solutions

C# – Deep SQL Relationships to a C# Object Model

Sounds like somebody wants you to write a lot of SQL.

I'm assuming here only reading data since that is all the question specifically mentions.

In the past, I have created an AutoMapper-like component which would map a DataRow to a class by examining the class's property names with reflection, then assign matching DataTable columns of same name and similar type to the class's properties.

Then building on that only a little, you can convert an entire DataTable to a list of objects. I believe AutoMapper will do this already.

The query to populate an entire object tree usually returned multiple DataTables in the DataSet.

-- data table 0
SELECT ... FROM Client WHERE ClientId = @clientId;
-- data table 1
SELECT ... FROM ClientData WHERE ClientId = @clientId;
-- data table 2
SELECT b.*
FROM ClientData a
JOIN ClientJob b ON b.ClientDataId = a.ClientDataId
WHERE a.ClientId = @clientId;
-- etc.

Then building on that and with some up-front configuration, you can convert an entire DataSet into an object tree.

var sqlToObjectConfig = new []
{
    typeof(Client),
    typeof(ClientData),
    typeof(ClientJob)
};

// call your converter
ConvertToObject<Client>(sqlToObjectConfig, dataSet);
// it will use the config to know the type of each DataTable (by index)

So your normal steps would be:

Develop a query to get the data you want
Define a configuration array for the query's conversion to objects
Define a method to execute the query and convert

You could relatively easily query a specific branch like ClientJobs on down.

C# – SQL query or C# .net code for csv files import

SQL servers will offer specific products such as DTS or SSIS for importing data. These can be pretty optimised and run very quickly.

However!! If you have programming skills I highly recomend you write your own importing software.

The reason for this is that although the Sql server may be able to run an import process faster in a best case senario, it is inherently unscalable.

If you write your own importer then you can move all that logic and file reading off to a seperate server and only load your sql box with simple writes. This enables you to scale horizontaly.

SQL databases are one of the major pain points when scaling and the way to minimise this is to keep all business logic off the box. I have seen systems with complicated scheduling logic literaly running out of hours in the day for importing files, running a top spec sql box at 100% 24/7

Additonaly although tools lile DTS/SSIS are fine for DBAs who want to run the odd 'programming style' task. They fall well behind the features of a modern devops stack, such as versioning, debugging, no down time deployments etc etc

Best Answer

Related Solutions

C# – Deep SQL Relationships to a C# Object Model

C# – SQL query or C# .net code for csv files import

Related Topic