Three ways to create a Microsoft Fabric Data Warehouse Database Project

Reading Time: 5 minutes

In this post I want to cover three ways to create a Microsoft Fabric Data Warehouse database project.

Which you can use to perform state-based deployments to Microsoft Fabric Data Warehouses. By performing CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps or some other method.

Which can be useful if you want Data Warehouses in the same workspace to be consistent or as an alternative to Microsoft Fabric Deployment Pipelines.

It is inspired by a short session that I presented at the Data Toboggan conference over the weekend of the same name.

I want to do this post as a follow-up. So that everybody knows the steps involved in all three methods. Especially for the lesser-know method with the dotnet command. Along the way I share plenty of links.

Before I go any further, I want to point out that with a few modifications you can use the below methods to create database projects for SQL Server related applications and services as well. Including SQL Server, Azure SQL Databases and Azure Synapse Analytics SQL Pools.

Note that to work with database projects in Azure Data Studio you need to install the SQL Database Projects extension.

1: Download from Warehouse Editor

I covered how to download a Microsoft Fabric Data Warehouse database project to perform CI/CD using Azure DevOps in a previous post.

However, I do want to add that you can extract the downloaded zip file with PowerShell instead. You can do this with the Expand-Archive cmdlet.

Expand-Archive -LiteralPath "{DATAWAREHOUSE NAME}.zip" -DestinationPath "{FOLDER LOCATION}" -Force

From there, you can open the database project with Azure Data Studio like in the below example.

AzureDataStudio {FOLDER LOCATION}

Once opened, you can view the Data Warehouse database project in Azure Data Studio in the “Database Projects” section.

2: Azure Data Studio

In reality, I covered most of this process in previous posts. However, I go into further detail in this section to make things clearer.

First, you need to create a connection to your Microsoft Fabric Data Warehouse in Azure Data Studio if one does not exist.

You can get the connection string for your Data Warehouse by following the below steps:

1. Go into your Data Warehouse in Microsoft Fabric by clicking on it.
2. Select the settings icon.
3. Copy the SQL Connection String.
4. In Azure Data Studio perform the same steps as you would do to connect to an Azure SQL Database server. But instead paste the SQL connection string in as the server name and the Data Warehouse name as the database.

Afterwards, right click your new connection and select “Create Project From Database”.

You can then fill out the required details in the “Create project from database” window.

One key point I want to highlight about this window is that you do not need to enter the full project path in the as the location. Because it will create a subfolder with the project name in the location specified.

Once completed you can go to the “Database Projects” section to view the database project.

In reality, you can create a database project in Visual Studio Professional or Enterprise by using a similar method.

However, I do not cover this due to the fact that creating database projects within them has been heavily covered online over the years.

For example, this old post from Rick Kreuger covers how to do it with Visual Studio 2010. Obviously, the layout and method have both changed over the years. I just wanted to show an older example.

3. Via dotnet command

Final method to create a Microsoft Fabric Data Warehouse database project is by creating a brand-new database project with the dotnet command.

Which will create allow you to create a new database project with the new ‘Microsoft.Build.Sql‘ .NET SDK for database projects.

If it is your first time creating a database project this way you need to run the below command to install the templates locally.

dotnet new install Microsoft.Build.Sql.Templates

Once the templates are installed, you can issue the below command to create a new database project.

dotnet new sqlproj -tp SqlDwUnified

Afterwards, you can open up the contents of the folder in Azure Data Studio. Either through the command line as shown earlier in this post or manually.

Creating a database project this way has many advantages. Including the fact that it creates a smaller sqlproj file than the older style of database projects.

In addition, this type of project has a default globbing pattern that identifies SQL files. For example, if I have a SQL file in the ‘\dbo\Views‘ subfolder I do not have to add it to the sqlproj file in the root directory. Like with the previous method.

You can find out more about the this default globbing pattern in the ‘Microsoft.Build.Sql‘ functionality page.

In addition, you can run the below command to view more options to create a database project. Including the different target platforms supported.

dotnet new sqlproj /?

One final point I want to add in this section is that SDK-style projects are now the default project types when you create one within Azure Data Studio.

Final words about three ways to create a Microsoft Fabric Data Warehouse Database Project

I hope that by showing three ways to create a Microsoft Fabric Data Warehouse Database Project everybody has a better idea of what their options are now.

Because there are advantages to all three ways. For example, the option to download within Warehouse Editor is fast and convenient. Whereas you can utilize the dotnet command method in an Azure DevOps pipeline if required.

To find out more about using database projects and Azure DevOps together feel free to look at my other posts. For example, the one where I cover how to perform CI/CD for Microsoft Fabric Data Warehouses using Azure DevOps.

If you have any comments or comments about this post, feel free to reach out to me.