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.
To clarify, an Azure Synapse Analytics 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.
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 an Azure Synapse Analytics dedicated SQL Pool. 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.
After the initial dacpac is created, unit tests are performed before any deployments take place. 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. During the ‘Build’ stage a dacpac file is created. In the ‘Unit Testing’ stage the dacpac is deployed to a SQL Server instance running in a container. So that unit tests can be performed on the database.
If for some reason you need to have a dedicated SQL Pool based on a SQL Server database schema, you can use this method to do unit testing.
tSQLt unit tests
For this particular pipeline, the unit tests 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.
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 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 Azure Synapse Analytics. Just like you can do with other ETL processes.
In reality, 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. 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.
So, for Azure Synapse Analytics deployments, you can change things like the files that contain your table schemas behind the scenes to make them optimal for use.
To give you a better idea how what you can do to optimize your deployments, you can read more about what Transact-SQL is supported in Azure Synapse SQL here.
On a side note, Sander Stad and I will be covering deploying to dedicated SQL Pools at Data Toboggan. Which is a free online conference consisting of 12 sessions. All touching elements of Azure Synapse Analytics.
We will also be covering other things as well. So, feel free to come along to get some tips and ask us questions.
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.
If you have any comments or queries about this post feel free to reach out to me.