You can consider this post a Fabricators guide to database projects for Microsoft Fabric Data Warehouses. Since I intend to go into a lot of detail about them in this post to help those that are preparing for the DP-700 exam.
You can start booking to take the beta exam on the same day that this post is published. Those looking to take the DP-700 beta exam can prepare yourselves now with my checklist for the DP-700 beta exam.
In addition, Microsoft recently released a post this new certification for Microsoft Fabric data engineers. Which contains a special offer for those looking to take the beta exam as soon as possible.
My intention is to compliment the database project documentation recently created by Microsoft. So that you gain a better understanding of how database projects work as far Microsoft Fabric Data Warehouses are concerned.
Including how to put your knowledge about database projects to practice by deploying updates.
Because I have a lot of knowledge in this area. Which hopefully shows in the many DevOps related session I presented in the past with Sander Stad. In addition, the video I did for the Azure Synapse and MVP series relating to CI/CD for dedicated SQL Pools.
By the end of this post, you will know more about database projects. for Microsoft Fabric Data Warehouses. Along the way I share plenty of links.
Personal view about database projects
Database projects have been around for many years. Originally, they were introduced a way to perform state-based migrations for SQL Server databases. By extracting the schema of a SQL server database into a special folder structure.
Within that folder structure is a SQL Project file that has a “.sqlproj” file extension. Which is contains various settings relating to the project.
One of which is “DSP” which stands for Database Schema Provider. Which relates to the target platform the database project is for. For example, the below DSP configuration is for a database project with a target platform of SQL Server 2022.
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>It is worth mentioning that a lot of modern applications provide user-friendly names that you can select the target platform from.
From there, you can create a dacpac file to deploy to one or more SQL Server databases. Which will be specific for that target platform.
Evolution of database projects
Over time, the database projects has evolved. Offering support for more target platforms and allowing more ways to create database projects and the dacpac files.
Including the below target platforms:
- Azure SQL Database/Managed instances
- Azure Synapse Analytics SQL Pools (both dedicated and serverless)
- Microsoft Fabric Mirrored SQL Database (currently in preview)
- Microsoft Fabric Data Warehouses
I will add here that technically you can also create a database project for tables in a Microsoft Fabric Lakehouse as well. Which you can create a dacpac from to deploy to a Microsoft Fabric Data Warehouse.
Another key point I want to highlight is that there are two project formats. One is the original style based on MSBuild and the other is the SDK-style based on .NET Core. All I will say for now is that the SDK-style one is more efficient.
You can create database projects with various applications. Including Azure Data Studio with the SQL Database Projects extension installed and SqlPackage.
In addition, you can now automate the creation and deployment of dacpac files. With services such as Azure DevOps and GitHub.
Implementing database projects for Microsoft Fabric Data Warehouses
In terms of the DP-700 exam, the study guide currently states that you need knowledge on implementing database projects. Since that can be open to interpretation I share a lot of details here.
First of all, there are many ways to create a database project for Microsoft Fabric Data Warehouses. I shared some methods in previous posts. Including one that covers that covered three ways to Microsoft Fabric Data Warehouse database project.
One key point I want to make here is that to extract the schema of Data Warehouse you need the connection string for it. You can get this value by clicking on the ‘Settings’ icon in the Warehouse Editor.
Creating a database project for Microsoft Fabric Data Warehouses
Anyway, there are various ways to create a database project for a Microsoft Fabric Data Warehouse including:
- Either extract performed or new database project created with a version of Visual Studio.
- Either extract performed or new database project created with Azure Data Studio with the SQL Database Projects extension installed.
- New project created by command line with the Microsoft.Build.Sql .NET SDK for database projects.
- New project created by command line with the MSBuild.Sdk.SqlProj (community driven offering).
- Download from Warehouse Editor.
- Configuring Microsoft Fabric Git integration in a workspace containing a Data Warehouse.
When you create a database project with ways 1-4 mentioned above you typically get the following folder structure. Which aligns with the more traditional database project format.
However, most current versions of applications work with SDK-style projects. Which means that even though it looks like a traditional project it is more efficient under the hood.
Whereas if you work with methods 5 or 6 you get a similar structure. However, you also get some additional files as well.
Note that the above folder structure was taken from a workspace that has Git integration configured.
When you create a Microsoft Fabric Data Warehouse in a workspace configured by Microsoft Fabric Git integration it creates a database project in a subfolder. Which is why is in a subfolder with a “.Warehouse” extension.
Below is a very brief description of the additional files.
- .platform – is an automatically generated system file that contains information about the Data Warehouse. Such as its display name and logical id.
- exportScriptVersion.txt -Appears to be version number for type of database project.
- xmla.json – Contains metadata and configuration details about the default semantic model that gets created with the Data Warehouse.
Deploying contents of database project to a Microsoft Fabric Data Warehouse
Once your database project is implemented you can look to deploy updates to it in a Microsoft Fabric Data Warehouse. In order to do that you need the Data Warehouse connection string as mentioned earlier in this post.
Once you get the connection string you can look to deploy it in a variety of ways. In this post we focus on deploying to with the updates in a dacpac file.
First step is to create the dacpac file. Which you do with a variety of methods including the below:
- Publish the contents of the database project into a dacpac file locally in a supported application. For example, Azure Data Studio or Visual Studio.
- Building from a command line, either locally or remotely.
- By specifying the build command in a .NET Core task within Azure DevOps.
- By specifying a GitHub Action within a GitHub workflow.
It is worth noting that you can also extract the schema directly from an existing Data Warehouse directly into a dacpac file. Instead of creating via a database project. You can do this with various applications and also the extract parameters in SqlPackage.
Options 1 and 2 above tend to be performed manually. However, option 2 can also be performed as part of an automated process. Options 3 and 4 relate to automating to perform CI/CD. More on that shortly.
After creating the dapcac file you can look to deploy it. You can do this in various ways including:
- Deploying the dacpac file locally in a supported application. For example, SQL Server Management Studio or Visual Studio.
- Publish parameters within the SqlPackage command, either locally or remotely.
- With the Azure SQL Database deployment task within Azure DevOps.
- With the sql-action GitHub Action within a GitHub workflow.
Performing CI/CD with a Microsoft Fabric Data Warehouse database project
As you can see, the last two options in the previous list also relate to performing CI/CD with an application lifecycle management offering. This is because for a lot of enterprises the ideal option is to perform CI/CD based on the database project.
Typically, where a database project is worked on locally in a folder initialized as a Git repository. Once the updated database project has been saved it is then synchronized with a repository stored in Azure DevOps. Which can then be utilized by an Azure Pipeline to perform CI/CD. Like in the below diagram.
I published some blog posts relating to performing CI/CD for Microsoft Fabric Data Warehouses. Including the two posts below. Which both contain links to repositories that you can download and work with as templates.
- CI/CD for Microsoft Fabric Data Warehouses using YAML Pipelines
- CI/CD for Microsoft Fabric Data Warehouses using GitHub Actions
Final words
I do hope you Fabricators out there find this guide to database projects for Microsoft Fabric Data Warehouses insightful. Especially those of you who are studying for the DP-700 exam.
Good luck to all of you who are looking to take the exam. If you have any comments or queries relating to this post, feel free to reach out to me.
Be First to Comment