SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Applying tSQLt on existing datawarehouse


Applying tSQLt on existing datawarehouse

Author
Message
Guy-928956
Guy-928956
SSC-Enthusiastic
SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)SSC-Enthusiastic (165 reputation)

Group: General Forum Members
Points: 165 Visits: 101
Hi,

I'm fairly new to tSQLt and looking for a bit of advice from more experienced folk Smile

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?

Thanks

Guy
pietlinden
pietlinden
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34161 Visits: 15500
It appears that Steve Jones (the guy that runs this show... I mean website) wrote several articles about tSQLt. This is the first one. Try reading some of that and see if that helps.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351048 Visits: 20201
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search