tSQLt for Data Warehousing, test your Stored Procedure
8 mins read

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.

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:

Screenshot from application » 2

Bon appétit!
Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *