CI/CD for Microsoft Fabric Data Warehouses using YAML Pipelines

Reading Time: 6 minutes

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

To clarify, YAML pipelines are the pipelines you work with in Azure DevOps that use YAML syntax. In other words, code. Unlike the GUI-based classic pipelines that I covered in my previous post about CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps.

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

There are various ways you can setup YAML pipelines to do the deployment. For example, you can use one YAML file to call another using a stage template. However, in this post I show one YAML file for the pipeline.

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.

CI/CD for Microsoft Fabric Data Warehouses using YAML Pipelines template

I have made a template for the contents of this post publicly available in GitHub. Which you are free to download and work with. It is called AzureDevOps-FabricDWDBProject.

I do have one request however. If you do find the repository useful please click on the star in the right-hand corner of the repository GitHub.

Note: Sometime after publishing this post I published another one that covers three ways to create a Microsoft Fabric Data Warehouse Database Project. One of which I show in the following section.

Creating a Database Project based on a Microsoft Fabric Data Warehouse

In reality, this section is the same as it was in my CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps. So, if you already know this part you can go straight to the next section.

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 either installing 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.

Other prerequisites

To make sure this is different from my last post I used a Microsoft-hosted agent.

To ensure that the build stage works I show how to create (build) dacpac files with a .NET build task instead of a Visual Studio Build task.

In addition, I am using the official instructions to update SqlPackage on the Pipeline agent. In order for the Azure SQL Database Deployment task to work properly.

Since it worked fine before due to the fact that I had also installed the last version of SqlPackage through the MSI installer as well.

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 YAML Pipelines

For this example, the build (Continuous Integration or CI) and the deployment (Continuous Deployment or CD) are in two separate stages in the same pipeline (YAML file).

To perform the CI part, I first install the .NET version of SqlPackage on the agent.

      # First install latest version of SqlPackage
      - task: PowerShell@2
        inputs:
          targetType: 'inline'
          script: 'dotnet tool update -g microsoft.sqlpackage'

Afterwards, I use a .NET task to build (create) the dacpac based on the contents of the database project and publish it as an artifact.

      # Then build the project using a .NET task
      - task: DotNetCoreCLI@2
        displayName: 'Build the dacpac using dotnet'
        inputs:
          command: 'build'
          projects: 'FabricDWProject.sqlproj'
          arguments: '--configuration $(BuildConfiguration)'
      
      #Then publish it as part of artifact
      - task: PublishBuildArtifacts@1
        displayName: 'Publishes dacpac as an artifact'
        # Publishes the dacpac as part of an artifact within Azure DevOps
        inputs:
          PathtoPublish: 'bin/$(BuildConfiguration)'
          ArtifactName: $(SQLPoolartifactname)
          publishLocation: 'Container'

Once the artifact is published, I then create a new stage. and specify that an environment called ‘Production’ is used. I then specify what is knows as a deployment job. Which allows me to specify an environment to use as below.

  jobs:
    - deployment: 'FabricDW'
      displayName: 'Fabric Datawarehouse'
      environment: Production

From there, I specify a run once strategy within the job and specify deploy to specify that I want a deployment done as below.

strategy:
  runOnce:
    deploy:
      steps:

Because I want to work with the Azure SQL Database Deployment task, I must install the MSI version SqlPackage. Due to the fact that this task is location specific. I decided to do this using the official instructions to update SqlPackage on the Pipeline agent as below.

- task: PowerShell@2
  displayName: 'upgrade sqlpackage'
  inputs:
    targetType: 'inline'
    script: |
      # use evergreen or specific dacfx msi link below
      wget -O DacFramework.msi "https://aka.ms/dacfx-msi"
      msiexec.exe /i "DacFramework.msi" /qn

From there, I downloaded the artifact that contained the dacpac file onto the agent and then specified it as part of the deployment.

- task: DownloadBuildArtifacts@0
  displayName: 'Download Artifacts'
  inputs:
    buildType: 'current'
    downloadType: 'specific'
    artifactName:  '$(SQLPoolartifactname)'
    downloadPath: '$(System.ArtifactsDirectory)'

- task: SqlAzureDacpacDeployment@1
  displayName: 'Install DACPAC on Fabric Data warehouse'
  inputs:
    azureSubscription: $(AzureSubscription)
    AuthenticationType: 'aadAuthenticationPassword'
    aadSqlUsername: '$(aadSqlUsername)'
    aadSqlPassword: '$(aadSqlpw)'
    ServerName: $(DestSQLConnString)
    DatabaseName: '$(DestinationDW)'
    deployType: 'DacpacTask'
    DeploymentAction: 'Publish'
    DacpacFile: '$(System.ArtifactsDirectory)\$(SQLPoolartifactname)\$(Targetfile)'

During running the pipeline, I am asked to approve the deployment to Production.

This is due to the fact that I have a Production environment configured and an approval setup in ‘Approvals and checks’.

Once approved the pipeline completed as below.

Current issue when performing CI/CD for Microsoft Fabric Data Warehouses

One key point I want to highlight is that at this moment in time attempting to update an existing table in the Data warehouse will fail 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 YAML pipelines

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 YAML pipelines within Azure DevOps.

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