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 Azure SQL Datawarehouse. It was renamed during the Azure Synapse Analytics evolution.
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 are performed before any deployments happen. 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 is created based a SQL Database Project in the repository.
Once the dacpac is created it is published along with other files as part of an artifact.
Afterwards, the ‘Unit Testing’ stage takes place. First, the dacpac that was created in the Build stage gets deployed. It performs updates a database that is in a container running SQL Server. After the updates have finished, unit tests are then 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.
tSQLt unit tests
To clarify, unit tests are done in this particular deployment pipeline are done 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, 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.