CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps

Reading Time: 5 minutes

In this post I want to cover CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps. Which can now be done gracefully with the new target platform thanks to a new SqlPackage update.

In this post I cover the entire flow. From creating the database project all the way through to the deployment. Admittedly, I do reference previous posts for some parts to avoid repetition.

To manage expectations, this post covers performing CI/CD for Microsoft Fabric Data Warehouses using classic pipelines. In other words, the Classic Editor for the build (CI) and the Releases feature for the deployment (CD).

If you are interested in working with YAML pipelines in Azure DevOps instead please read my other post. Which covers CI/CD for Microsoft Fabric Data Warehouses using YAML Pipelines.

Plus, at this moment in time the pipeline will fail in one scenario. Which I highlight towards the end of this post.

In addition, this post contains plenty of links.

One key point to note is that this post is based on Azure DevOps. You can view the GitHub alternative in my other post that covers CI/CD for Microsoft Fabric Data Warehouses using GitHub Actions.

If you need help with any jargon used in this post, then I recommend that you read one my other posts. Which is a Microsoft Fabric Git integration jargon guide for Fabricators.

Creating a Database Project based on a Microsoft Fabric Data Warehouse

You can create a Database Project based on a Microsoft Fabric Datawarehouse with a variety of applications. Including Azure Data Studio.

To save some repetition in this post I recommend installing both Azure Data Studio and the SQL Database Projects extension, or updating them both if they are already installed.

Afterwards, go through the steps in my post about sharing a Microsoft Fabric Data Warehouse Database Project with the new target platform in Azure Data Studio.

From there, initialize the repository and synchronize it with one in Azure DevOps. I covered how you can do this in detail in the below sections of a previous post about how to create a dacpac for an Azure Synapse Analytics dedicated SQL Pool using Azure DevOps.

• Create Git repository for SQL Pool in Azure DevOps
• Synchronize Azure Synapse SQL Pool repository to Azure DevOps

In reality, you can follow the above steps because the only difference is that you synchronize a database project for a Microsoft Fabric Data Warehouse to Azure Repos instead.

Prerequisites

For my pipeline demos I used a self-hosted agent that has the latest version of SqlPackage installed. Which was released towards the end of last week.

However, for the benefit of this post I show how you can install SqlPackage first. Which is why I show how to create (build) dacpac files with a .NET build task instead of a Visual Studio Build task.

In addition, I use a Azure Pipeline variable group. Which is why you can see variables used in the pipelines. Which are represented by a dollar sign followed by parenthesis. For example, $(BuildConfiguration).

CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps

I first use the Classic Editor in Azure Pipelines to create and publish the dacpac as below.

One key point I want to highlight here is that you can enable continuous integration in the ‘Triggers’ tab above. Which would mean that this pipeline would start every time the branch named above was updated. This means you are fully implementing the CI part of CI/CD.

Anyway, in the ‘Install SqlPackage’ PowerShell task I run the below line of code to install SqlPackage.

dotnet tool install -g microsoft.sqlpackage

To update SqlPackage on an agent instead you can simply swap the ‘install’ parameter with ‘update’.

In the ‘Build dacpac’ .NET core task I simply state the location of the sqlproj file and then set the configuration in the ‘Arguments’ section as below:

--configuration $(BuildConfiguration)

Since I am able to set the configuration, I can set the ‘Path to Publish’ section of the Publish Artifact task to be ‘bin\$(BuildConfiguration)’.

Once completed, I can see that an artifact has been published that contains the dacpac.

Release

Once I had created an artifact, I could look to use the Releases feature to deploy the contents of the dacpac to a Microsoft Fabric Data Warehouse. As you can see below.

During the initial pipeline setup I configure the ‘Artifacts’ section to get the latest artifact from the build pipeline.

In the ‘Deploy to Fabric DW’ stage I have two tasks configured.

First, the stage downloads the artifact that contains the dacpac. From there, the ‘Deploy Dacpac’ Azure SQL Database deployment task deploys the contents of the dacpac to the Data Warehouse.

I decided to use this particular task since I needed to use Azure Active Directory password authentication.

One good about SqlPackage being updated is that initially I do not need any additional arguments.

Of course, that can change depending on your requirements. For example, to block on possible data loss. So make sure you are acquainted with the SqlPackage publish parameters.

Anyway, after setting up the tasks I saved the pipeline and then created a release. Which worked as you can see below.

To check it was consistent I first removed a table from the destination Data Warehouse and added a comment to one of the ‘Create Table’ statements. Which worked fine.

Afterwards, I added a new column to one of the tables. Which failed with the below error.

Error SQL0: Deployment cannot continue because table changes that require 'ALTER TABLE' are not supported on Synapse Data Warehouse in Microsoft Fabric at this time.

This is due to the fact that the latest version of SqlPackage does not support alter table statements within Microsoft Fabric yet. Which you can read about in the SqlPackage release notes.

Final words about CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps

I hope this post about how to perform CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps helps some of you.

Because I wanted to raise awareness about the fact that the new version of SqlPackage supports the new target platform. In addition, show that it works for Azure DevOps deployments.

Of course, if you have any comments or queries about this post feel free to reach out to me.