I'm fairly new to tSQLt and looking for a bit of advice from more experienced folk 🙂
We have a live DW which we regularly release code updates for. SSIS pulls data from source systems and dumps it raw into the DW staging area. ETL is then done via a scheduling tool that calls stored procs to transform the data and insert it in the DW reporting tables.
We'd like to introduce some automated testing and tSQLt seems the way to go. However i am not sure the best/most efficient aproach to get started. We have numerous feeds that go through complex logic from staging to DW, before being viewed by users with various reporting tools.
The approach i am considering is to "setup" the staging data as part of the class test. So to test a given source datafeed end to end, I would create sample staging data, with a record for every possible scenario and then populate the various "expected" tables that this feed should insert into.
There would then only really be a single tSQLt AssertEquals test to determine of the actual result matches the expected for the entire feed.
Is this an approach others have used? Are there better techniques you would reccomend?