Microsoft Fabric Warehouse development – creating database project

Nowadays, it has become standard to develop database solutions for Microsoft Fabric Warehouse, SQL Server or Azure SQL using Visual Studio Code. This tool with its extensions, when combined with version control systems like Git, provides not only a stable but also an efficient working environment. In this article, I will demonstrate how to set it up and create a basic database project. However, this is only the first article in an entire series dedicated to this topic so stay tuned.

Let’s start with a short introduction to depict what we’re dealing with. The sql database project can be installed as a extension into Visual Studio Code. By leveraging this tool, we gain access to a wide range of useful features embedded in it, such as:

- Syntax highlighting and IntelliSense,
- Code debugging,
- The ability to perform standard Build and Rebuild operations to validate the code and generate an output file,
- Easy deployment to the target server,
- Automatic generation of deployment files for incremental and full deployments,
- Integration with version control systems,
- Support for environment variables as well as pre- and post-deployment scripts,
- The ability to compare schemas between projects, databases, etc. (I’ve written about this functionality here before).

These and many other advantages make Visual Studio Code the best environment for creating and maintaining our Microsoft Fabric Warehouse related projects. The purpose of today’s article is to introduce the world of database projects, and within its scope, we’ll demonstrate how to create such a project from scratch.

Visual Studio Code can downloaded for free from here. Installation is pretty simple so I will skip that part but you should also install .NET SDKs for Visual Studio that should be installed from here. After all those installations let open Visual Studio Code. When you open it you should install extension named SQL Database Project like it is visible on the below screenshot:

It will also install the SQL Server (MSSQL) extension, which provides additional features, such as the ability to connect to and query a Fabric Warehouse. Once it is installed, we can navigate to the extension page and create a new project by clicking ‘Create New.’ There is also an option to import an existing database into VS Code by selecting ‘Create Project from Database.’:

Subsequently, Visual Studio Code will prompt us to provide a project name, select the directory where the project files will be stored, and determine whether the project should conform to the SDK-style format, ensuring alignment with standard development practices and preferences.

After a brief wait of just a few seconds, our project is fully set up and ready for use, allowing us to explore its detailed structure directly within the Visual Studio Code interface as well as observe the corresponding file organization on the disk.

As I mentioned earlier, the SQL Database Project can be utilized to develop SQL solutions for various platforms, including SQL Server, Synapse Dedicated SQL Pools, and Azure SQL Database. Therefore, before we begin adding our objects, we must first specify the target platform for deployment. This can be accomplished by right-clicking on the project and selecting ‘Change Target Platform’ from the context menu. From there, we can choose the ‘Synapse Data Warehouse in Microsoft Fabric’ option. This setting informs Visual Studio Code about the specific syntax against which the code will be validated, among other configurations.

At this point, within the project, we can manually create any object within the appropriate folder structure. To accomplish this, simply right-click on the project, select ‘Add’ from the context menu, and then choose the specific type of object you wish to create.

There are many ways how objects can be grouped under project for example object type/schema or schema/object. I recommend choosing the latter, as this way the folder structure created within the project will group objects first by schema and then by object type. For testing purposes I added Customer table in shared schema with some sample columns as it is visible below:

CREATE TABLE [shared].[Customer]
(
  [Id] INT NOT NULL,
  [FirstName] NVARCHAR(50) NOT NULL,
  [LastName] NVARCHAR(50) NOT NULL,
  [Email] NVARCHAR(100) NOT NULL,
  [PhoneNumber] NVARCHAR(15) NULL,
  [Address] NVARCHAR(255) NULL,
  [City] NVARCHAR(50) NULL,
  [State] NVARCHAR(50) NULL,
  [ZipCode] NVARCHAR(10) NULL,
  [CreatedDate] DATETIME2(0) NOT NULL DEFAULT GETDATE()
)

Having objects at our disposal, we can, of course, edit and adapt them to our needs at any time. For example, we can try changing the data type of a column, and as you can see, this can be done via the code. Additionally, syntax errors are flagged immediately, and the lines we’ve modified are highlighted with color.

“Please remember that creating objects in a database project is defined using statements like CREATE TABLE, CREATE STORED PROCEDURE, and so on. When deploying to the target server, the extension will adapt this syntax into the appropriate ALTER statements to propagate the changes. Quite a lot of functionality for a simple table definition, right? And this is just the beginning of the wide range of possibilities that this type of project offers us.

To check if everything is okay, I can build my project and review the output of this operation:

I received many errors, such as ‘shared schema does not exist,’ ‘NVARCHAR is an unsupported type,’ and ‘DEFAULT constraint is not supported’:

stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(5,3,5,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(6,3,6,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(7,3,7,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(8,3,8,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(9,3,9,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(10,3,10,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(11,3,11,3): Build error SQL70015: 'NVarChar' is not supported for the targeted platform. [c:\temp\FabricDBProject\FabricWarehouseProject\FabricWarehouseProject.sqlproj]
   stdout: c:\temp\FabricDBProject\FabricWarehouseProject\shared\Customer.sql(12,3,12,3): Build error SQL70015: 'DateTime' is not supported for the targeted platform.

Pretty cool, isn’t it?

I made corrections, added CREATE SCHEMA statement and now everything works as it should.

When I tried to run Build operation one more time, this time it works as it should:

As you can see above there are some artifacts produced by build, the most important one is a DACPAC. A DACPAC, or Data-tier Application Package, is a single file used in Microsoft SQL Server and connected services to deploy and manage database schemas and objects. It’s essentially a packaged unit that contains the database schema (e.g., tables, views, stored procedures) and sometimes associated data or deployment policies.

Think of it as a portable snapshot of a database’s structure. Developers and DBAs use DACPACs to:

- Deploy a database to a new environment (like moving from dev to production).

- Update an existing database by comparing the DACPAC schema to the target database and applying the differences.

- Version control database changes in a structured way.

It’s part of Microsoft’s Data-tier Application (DAC) framework, which simplifies database management in a way that’s similar to how application packages (like .NET assemblies) work for code. The DACPAC doesn’t include the full dataset—just the schema—but it can include some reference data if specified. If we have our simple project in place, let’s try to deploy it to the target Fabric Warehouse. We can simply right-click on the project and select ‘Publish’ to deploy it. VS Code will prompt us to choose whether we want to publish it to an existing SQL Server or perhaps a local development container—in our case, the first option is the correct one.

After that, we can check if we want to use a profile, which is a saved file containing connection details and some optional variables. We don’t have such a thing for now, so we will create a connection profile.

We have to provide a server name or connection string. To get that, log in to Fabric and open the warehouse. On the Home tab, there is a small ‘Settings’ icon that will open a new window, and there, under SQL Endpoint, you will find the connection string you are looking for.

After all of this, we need to provide the database name and specify how we want to authenticate. In our case, we will use an interactive Entra session, which means that VS Code will prompt us to log in when we attempt to deploy it.

Then we have two options: publishing or generating a script. As you might imagine, ‘publish’ will generate a difference script and deploy it directly to the target database, while ‘generate script’ will display the script for us to review, allowing us to decide whether to deploy it or not. I recommend choosing the ‘generate script’ option to fully understand what’s happening behind the scenes. The difference script is a set of operations defined by comparing our database project with the target database, ensuring the target database becomes compatible (have same definition) with the project.

I generated the script so you can review it and see what will be executed on the target database. It might seem a little strange to you because some specific constructions are written in SQLCMD mode, which extends standard SQL functionality. I’ll write about this in one of my future articles. For now, if you want to execute the script, you’ll need to connect to the target database because, by default, the query window with the script is disconnected. Don’t worry—you don’t have to specify all the connection details again; you can simply select a previously saved connection.

When window is connected then turn on SQLCMD mode and click play button and after few seconds your project will be deployed to target database.

To review whether your changes are already deployed, you can go to the SQL Server Object Explorer in your VS Code and view them using the graphical interface or even query the metadata.

If you want to see how difference script works in practice make some changes in table definition or any other changes that you want save it and publish it one more time. New difference script will reflect your changes typically with set ALTER statements. Below you can see how it looks like when I added new column to existing table:

This article, the first in a series, explains how to use Visual Studio Code with the SQL Database Project extension to develop database solutions for Microsoft Fabric Warehouse, SQL Server, or Azure SQL. It covers setup (installing VS Code, .NET SDK, and the extension), creating a project, setting the target platform, and adding objects , highlighting benefits like syntax highlighting, debugging, and version control. More articles will follow, making this crucial for both practical use and DP-700 exam preparation. Stay tuned!