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 databaseA 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\warehouseYou 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 dataExample:Sprint 1 : Student table contains Name, Age, GradeYou create test data for this table, and unit tests based on thatSprint 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
How do you unit test \ use TDD methods for ETL's and reporting projects?
unit testing;sql;tdd