Applying tSQLt on existing datawarehouse

  • Guy-928956

    SSC Veteran

    Points: 243


    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?



  • pietlinden

    SSC Guru

    Points: 62848

    It appears that Steve Jones (the guy that runs this show... I mean website) wrote several articles about tSQLt. This is the first one[/url]. Try reading some of that and see if that helps.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    This is hard, but it's the approach I'd recommend. Take a sample feed, and use that. Then add rows as needed to test cases, improving your coverage over time. Or as you find bugs. Don't try to be perfect the first time.

    I'd do it this way. Lots of tSQLt people in DW situations use row counts as testing, but this only does a partial job of testing.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply