Unit tests for an Azure Synapse Analytics dedicated SQL Pool

Reading Time: 4 minutes

In this post I want to cover one way you can perform unit tests for an Azure Synapse Analytics dedicated SQL Pool. Because it can be useful.

By the end of this post, you will know one way you can do unit tests for a dedicated SQL Pool.

To clarify, an Azure Synapse Analytics dedicated SQL Pool is the new name for what use to be known as Azure SQL Datawarehouse. It was renamed during the Azure Synapse Analytics evolution and is now integrated into the Azure Synapse Analytics product.

Since I have returned from having a month off, I have been doing a lot more with Azure Synapse Analytics. Because of various reasons.

Azure Synapse Analytics dedicated SQL Pool example

In reality, I had been doing some things with it beforehand. In fact, anybody who has read my post about using Azure DevOps to deploy to multiple SQL Server database types will have picked up on this.

Due to the fact that one of the destinations is a dedicated SQL Pool as you can see below. However, I had to truncate the name of the stage otherwise it would have looked very cluttered. You can read that post in detail here.

Unit tests

Unit tests are performed before any environments are updated. To catch any potential issues with updates. As you can see in the deployment pipeline below

How is this possible? Well, the database project in the Azure DevOps repository is based on a SQL Server database. First the ‘Build’ stage takes place. During this stage an initial dacpac file is created. Based a SQL Database Project that exists in a repository within Azure Repos.

Once the dacpac is created it is published along with other files as part of what is known as an an artifact within Azure Pipelines.

Afterwards, the ‘Unit Testing’ stage takes place. It deploys database updates to a database that is in a container running SQL Server. Based on the dacpac that was created during the ‘Build’ stage. Once the updates are completed unit tests are run on the database.

If your dedicated SQL Pool can be based on a SQL Server database schema, you can use this method to do unit testing. Afterwards, you can look to transform your SQL Server database schema for a dedicated SQL Pool. So that you can create another dacpac in your pipeline that can deploy updates to a dedicated SQL Pool.

tSQLt unit tests

To clarify, the unit tests are done in this deployment pipeline using tSQLt. Sander Stad done the groundwork for this stage since he is the tSQLt expert. In fact, Sander has created an open source tSQLt test generator. You can read more about that in detail here.

In addition, you can read more about unit testing databases in a previous post I wrote about unit testing database changes.

Azure Synapse Analytics dedicated SQL Pool considerations

Now, there are some things to take into consideration if you are going to deploy to a dedicated SQL Pool based on a SQL Server database. One important thing is the fact that there are T-SQL differences between the SQL Server database and a dedicated SQL Pool.

In addition, if you are going to do a dedicated SQL Pool deployment based on a SQL Server database project there is something else to think about.

Depending on its intended use, you might want to consider optimizing the objects behind the scenes before deploying to dedicated SQL Pools. Just like you can do with other ETL processes.

If you use your imagination you can do all kinds of transformations of your objects within your YAML pipeline.

For example, in the pipeline above I created multiple dacpac files based on one database project in a repository. I did this to be able to deploy to different database types whilst keeping one source of truth. You can read about this in more detail here.

For Azure Synapse Analytics deployments, you can do various things in your pipeline. For example, you can change the files that contain your table schemas behind the scenes. So that you can make them optimal for use.

I did another post that shows what you can do to in the pipeline for a dedicated SQL Pool after unit tests. Which is about how you can transform your SQL Server database schema for a dedicated SQL Pool

To help optimize your deployments you can read the official documentation that covers the Transact-SQL features supported in Azure Synapse SQL.

You can see an example of an Azure DevOps template you can use for dedicated SQL Pool deployments within a GitHub repository that I have made public. Which is called AzureDevOps-AzureSynapseSQLPool.

Azure Synapse Analytics session at Data Toboggan

On a side note, myself and Sander Stad covered deploying to dedicated SQL Pools at Data Toboggan. Which was a free online conference consisting of 12 sessions. All touching elements of Azure Synapse Analytics.

We also covered other things as well. Click here to watch the video from Data Toboggan.

Final words about Unit Tests

I hope this post about how you can perform unit tests for an Azure Synapse Analytics dedicated SQL Pool has given some of you ideas.

Because there is a lot of inventive things you can do in Azure Pipelines for SQL Pools.

If you have any comments or queries about this post feel free to reach out to me.