tSQLt for Data Warehousing, test your Stored Procedure
In the previous data recipe, Create a Test Class & First Unit Test with tSQLt, we created our very first T-SQL Unit Test to test the database collation. In this data recipe, we will test the execution of a Stored Procedure. Specifically, we will validate what happens when a new User is added to the user dimension.
Recipe Overview
Preparation time: 10 minutes
Ingredients
- tSQLt Database Project
- Visual Studio
- Any dimension load that needs to be tested
Preparation
In my example data warehouse database called Bite Size Data, I have a Dim User object with the following structure:
USE BiteSizeDataDW;
GO
CREATE TABLE [dbo].[Dim_User] (
[UserKey] INT NOT NULL IDENTITY(1, 1),
[UserID] INT NOT NULL,
[Username] VARCHAR (50) NULL,
[Email] VARCHAR (100) NULL,
[Role] VARCHAR (50) NULL,
[CreatedAt] DATETIME NULL,
[AuditCreatedAt] DATETIME NULL,
[AuditModifiedAt] DATETIME NULL,
PRIMARY KEY CLUSTERED ([UserKey] ASC)
);
Next to this dimension table, I also prepared a staging database with 3 objects— a Table in which my application users are synced, a Synonym that references my dimension table in another database on the same server, and my Stored Procedure which we will be testing in this data recipe.
So let’s use the following script to create the Table first:
USE BiteSizeDataStaging;
GO
CREATE TABLE [dbo].[User] (
[UserID] INT NOT NULL,
[Username] VARCHAR (50) NULL,
[PasswordHash] VARCHAR (255) NULL,
[Email] VARCHAR (100) NULL,
[Role] VARCHAR (50) NULL,
[CreatedAt] DATETIME NULL,
PRIMARY KEY CLUSTERED ([UserID] ASC)
);
Now that our User table exists, let’s create the Synonym that points to the data warehouse Dim_User table:
USE BiteSizeDataStaging;
GO
CREATE SYNONYM [dwh].[Dim_User_Syn] FOR [$(DWH_DB)].[dbo].[Dim_User];
as you can see in the code block above I used the following syntax: [$(DWH_DB)]. If you are executing the create statements directly via a query window in SSMS, you need to replace the syntax with the effective database name. Finally, we can create the Stored Procedure that we will be using during the creation of our Unit Test:
USE BiteSizeDataStaging;
GO
CREATE PROCEDURE [dwh].[Load_Dim_User]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dwh].[Dim_User_Syn]
(
UserID,
Username,
Email,
[Role],
CreatedAt,
AuditCreatedAt
)
SELECT
UserID,
Username,
Email,
[Role],
CreatedAt,
GETDATE() AS AuditCreatedAt
FROM [dbo].[User];
END
GO
Now that our staging and data warehouse databases are prepared, we can start preparing for our tSQLt Unit Test.
I have prepared my tSQLt test class, just as an example, you can find the create statement in the upcoming code block. If you are wondering how to do the setup in a Database Project, please take a look at my previous data recipe: Create a Test Class & First Unit Test with tSQLt
USE BiteSizeDataDW_UnitTesting;
GO
CREATE SCHEMA [DimensionUnitTest]
AUTHORIZATION [dbo];
GO
EXECUTE sp_addextendedproperty @name = N'tSQLt.TestClass', @value = 1, @level0type = N'SCHEMA', @level0name = N'DimensionUnitTest';
As you can see in the code block above, I have prepared a tSQLt Unit Testing Schema called DimensionunitTest which will be used to group all my Dimension load-specific unit tests. My advice would be: to group unit tests for configuration, dimension loads, and fact loads in a separate schema to avoid confusion.
Creating the unit test
Before we create the Unit Test, let’s go through the functionality of the Stored Procedure quickly. If you take a look at the previously created Stored Procedure, you will see that the only thing it is doing is inserting the records from the Staging table straight into the Dimension table. it is important to mention that typical dimension loads might be more complex than this example. I simplified the example since we are not focusing on dimension-creation practices but on unit-testing practices.
We are going to build a Stored Procedure in the DimensionUnitTest schema with the name Test_Dimension_LoadDimUser_AddNewUser. Important to notice is that I have been very specific with the name of my Stored Procedure:
- I mentioned Test at the beginning (otherwise tSQLt will not identify this as a valid unit test)
- Even though I’m using a specific schema, I still mention that I’m testing a Dimension
- I explicitly state that I’m testing my LoadDimUser Stored Procedure
- Finally, I mentioned which functionality I’m focussing on in particular, in this case, Add a new user.
If the Stored Procedure would contain logic to update existing users, I would create 2 different Unit Tests:
- 1 Unit Test to validate the result when adding a user
- 1 Unit Test to validate the result when updating a user
While creating Unit Tests, we always start with the Assemble section, in this particular section, we will be preparing the specific testing scenario / setup.
Because we don’t want to impact the existing workloads, we can leverage the power of tSQLt by creating Fake tables. What this does behind the scenes, is copying the existing structure of the table which you want to use, and some additional steps. Don’t worry, after executing the unit test, you won’t even notice that any object was created.
For this example, we are going to create fake tables for the User staging table and the user dimension table.
-- Assemble
EXEC [$(DWH_DB)].tSQLt.FakeTable 'dbo.Dim_User'
EXEC [$(STAGING_DB)].tSQLt.FakeTable 'dbo.User'
To continue, we are going to add dummy data to our take User table. To get this done, we can add an insert statement in our unit test.
INSERT INTO [$(STAGING_DB)].[dbo].[User](UserID, Username, PasswordHash, Email, [Role], CreatedAt)
VALUES (1, 'oliviervs', '123456789azerty', 'olivier@bitesizedata.be', 'admin', '2024-01-01')
To finalize our Assemble phase, we’re creating 2 temporary tables, 1 with the expected result, and 1 where we will capture the actual result.
CREATE TABLE #expected
(
UserID INT,
Username VARCHAR(50),
Email VARCHAR(100),
[Role] VARCHAR(50),
CreatedAt DATETIME
)
CREATE TABLE #actual
(
UserID INT,
Username VARCHAR(50),
Email VARCHAR(100),
[Role] VARCHAR(50),
CreatedAt DATETIME
)
Now that we have done the preparations in the Assemble phase, we can build our Act phase. During the act phase, we execute the Stored Procedure, gather the expected result, and gather the actual result.
-- Act
EXEC [$(STAGING_DB)].dwh.Load_Dim_User
INSERT INTO #expected (UserID, Username, Email, [Role], CreatedAt)
SELECT UserID,
Username,
Email,
[Role],
CreatedAt
FROM [$(STAGING_DB)].[dbo].[User]
INSERT INTO #actual (UserID, Username, Email, [Role], CreatedAt)
SELECT UserID,
Username,
Email,
[Role],
CreatedAt
FROM [$(STAGING_DB)].dwh.Dim_User_Syn
To complete our Unit Test, we are creating the Assert phase. During the phase, we validate and compare the expected output versus the actual output.
-- Assert
EXEC tSQLt.AssertEqualsTable @expected = '#expected', @actual = '#actual';
After we add the Assert phase, we should have the following code:
CREATE PROCEDURE [DimensionUnitTest].[Test_Dimension_LoadDimUser_AddNewUser]
AS
BEGIN
-- Assemble
EXEC [$(DWH_DB)].tSQLt.FakeTable 'dbo.Dim_User'
EXEC [$(STAGING_DB)].tSQLt.FakeTable 'dbo.User'
INSERT INTO [$(STAGING_DB)].[dbo].[User](UserID, Username, PasswordHash, Email, [Role], CreatedAt)
VALUES (1, 'oliviervs', '123456789azerty', 'olivier@bitesizedata.be', 'admin', '2024-01-01')
CREATE TABLE #expected
(
UserID INT,
Username VARCHAR(50),
Email VARCHAR(100),
[Role] VARCHAR(50),
CreatedAt DATETIME
)
CREATE TABLE #actual
(
UserID INT,
Username VARCHAR(50),
Email VARCHAR(100),
[Role] VARCHAR(50),
CreatedAt DATETIME
)
-- Act
EXEC [$(STAGING_DB)].dwh.Load_Dim_User
INSERT INTO #expected (UserID, Username, Email, [Role], CreatedAt)
SELECT UserID,
Username,
Email,
[Role],
CreatedAt
FROM [$(STAGING_DB)].[dbo].[User]
INSERT INTO #actual (UserID, Username, Email, [Role], CreatedAt)
SELECT UserID,
Username,
Email,
[Role],
CreatedAt
FROM [$(STAGING_DB)].dwh.Dim_User_Syn
-- Assert
EXEC tSQLt.AssertEqualsTable @expected = '#expected', @actual = '#actual';
END
Make sure to deploy the code to SQL Server and finally execute the unit test.
DECLARE @TestClassName nvarchar(max) = 'DimensionUnitTest'
EXECUTE [tSQLt].[RunTestClass] @TestClassName
After executing the code above, you should get the following result:
Bon appétit!
Enjoy!