Warehouse Database Projects

See what a warehouse database project is

A Fabric warehouse has a schema: tables, views, stored procedures, and functions. A database project keeps that schema as files on your machine, one .sql file per object, so that you can edit it in an editor, validate it before it reaches the warehouse, and deploy it in a controlled way.

For Fabric Data Warehouse, the project is developed in Visual Studio Code. The work has four steps: create the project, define the schema objects, build and validate the project, and publish it to the warehouse. This feature is in preview.

Behind the extension works DacFx, the Data-Tier Application Framework. The SQL Database Projects extension uses the DacFx package to build and publish database projects, compare schemas, script changes, and extract or deploy .dacpac files. A .dacpac file is the compiled model of the warehouse schema.

The SQL database lesson showed a project that Fabric generates for you inside Git. Here you own the project and you run the build and the publish yourself.

Prepare the tools and the access

Four things must be in place before you create a project.

Each piece has a job. The SQL Server (mssql) extension holds the connection to the warehouse. The SQL Database Projects extension creates, builds, and publishes the project. The .NET SDK is what builds and publishes database projects.

The role is the one to remember: Contributor is the lowest workspace role that works. Member and Admin also qualify. The Viewer role sits below Contributor and does not meet the requirement.

Create the project

You can create a database project from scratch or from an existing warehouse.

From scratch, start from the Command Palette with Database Projects: New, or from the Database Projects pane with Create new. The answers are the same in both:

1. Database project type: SQL Server Database.
2. A project name and a local folder.
3. Target platform: Synapse Data Warehouse in Microsoft Fabric.
4. SDK-style project: Yes.

Two of these answers are not preferences. The target platform must be Synapse Data Warehouse in Microsoft Fabric, even though the project type says SQL Server. And only SDK-style projects are supported for Fabric Data Warehouse.

To start from an existing warehouse, you first need a connection profile in the SQL Server extension.

• Server name: in the Fabric portal, open the Settings of the warehouse, select SQL connection string, and copy the string. It is the server name of the warehouse, and it is different from the SQL analytics endpoint.
• Authentication type: Microsoft Entra ID - Universal with MFA support.

Then open the ... options of the Database Projects pane and select Create Project From Database. Choose the warehouse from the connection profiles, then a project name and a folder. Visual Studio Code extracts the project files from the warehouse.

The result is a project with a folder per schema and, inside it, folders such as Tables, Views, and Functions.

Configure the project file

The project file has the extension .sqlproj. Right-click the project and select Edit .sqlproj File. Two things need checking.

First, the SDK. The file should use the latest version of the Microsoft.Build.Sql SDK:

<Sdk Name="Microsoft.Build.Sql" Version="2.3.0" />

Second, the reference to the system objects of a Fabric warehouse. Add a package reference to Microsoft.SqlServer.Dacpacs.FabricDw inside the ItemGroup node:

<PackageReference Include="Microsoft.SqlServer.Dacpacs.FabricDw" Version="170.0.4">
  <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
  <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
</PackageReference>

The first line names the package and its version. The second line, DatabaseVariableLiteralValue with the value master, is metadata on the reference. It looks optional and it is not, as you will see next. Save the file and select Yes when Visual Studio Code asks to reload the project.

The DatabaseVariableLiteralValue line is easy to leave out, and the result is confusing because the build still succeeds.

The failure comes later. Without that metadata, Publish and Script actions can fail as soon as your code references objects in the sys schema, such as sys.views. The error looks like this:

Error SQL72033: This deployment may encounter errors during execution because [<schema>].[<procedure>] depends on [sys].[views].[schema_id] and [sys].[views].[schema_id] does not exist in the target database.

So the pattern to recognize is: build fine, publish fails, error SQL72033, and a sys object that supposedly does not exist. The fix is in the package reference, not in your procedure.

One more habit: the version numbers above are examples. New versions of these NuGet packages are released often, so update the versions in your local project to stay aligned with the latest ones.

Edit objects and build

Under the folder of a schema, for example dbo, each object is a .sql file with its T-SQL definition. You add, delete, or change those files to define tables, views, stored procedures, and functions. To modify an existing object, open its file and update the definition.

Then right-click the project and select Build. The build validates all new and updated objects and includes them in the generated dacpac file:

{project folder location}/bin/debug/{project name}.dacpac

If the build fails, the output of the build shows the errors to fix.

Two details save time:

• Changes are local until you publish. Changes made in the project are not reflected in Microsoft Fabric until you Build and Publish the project.
• The terminal matters. Currently, the default terminal of Visual Studio Code must be PowerShell for the build in the Database Projects extension to succeed. You set it in the Command Palette with Terminal: Select Default Profile.

Publish to the warehouse

Publishing is the step that changes the warehouse. It creates a script that resolves the difference between the database project and the actual warehouse, and it executes that script to make the warehouse match the project.

Right-click the project and select Publish. Choose Publish to an existing SQL server, then the connection profile of the warehouse and the name of the target warehouse. For the publish profile, choose Don't use profile the first time. The last choice is the action.

The first time you deploy changes, generate the script and review the T-SQL before you publish.

At the end, Visual Studio Code asks whether you would like to save the settings in a profile. Choose Yes. A publish profile is a .publish.xml file that stores the options you used, so the next time you load the profile and do not answer the questions again.

A project is not limited to one profile. You might have different profile options for different warehouses, or for the different environments of your development process, such as dev, test, acceptance, and production. The next section shows why: the safe options for production are not the convenient options for development.

Set the options that protect data

Two publish settings decide whether a deployment may remove things. They are the part of this lesson most likely to cost you data.

Read them as two different questions. BlockOnPossibleDataLoss asks: may this deployment lose data? With True, it stops. With False, the deployment is allowed even if data can be lost, so False is only for controlled environments such as dev or test.

DropObjectsNotInSource asks: what happens to objects that exist in the warehouse and not in the project? With True, they are dropped. In production that can delete important objects and data, so double-check before you enable it there.

An example makes the difference concrete. The production warehouse holds a table dbo.AdHocFix that someone created by hand. It is not in the project.

In SqlPackage, the command-line tool that publishes dacpac files, BlockOnPossibleDataLoss defaults to True and DropObjectsNotInSource defaults to False. The defaults are the safe side, and a profile that changes them should be a decision, not an accident.

Run scripts before and after the deployment

A publish compares schemas. Some work is not schema: copying rows away before a table disappears, or filling a lookup table. For that work a project has two script slots.

The order decides which slot you need. Data that the deployment is about to remove must be saved in PreDeploy, while the table still exists. Data that needs the new schema must be written in PostDeploy, when the new tables and columns are there.

Both scripts run on every publish, so both should be idempotent. An idempotent script can run multiple times without causing logical problems or failures, and it can be deployed to several databases without knowing their state in advance.

In practice: a script that inserts five lookup rows is not idempotent, because the second run fails or duplicates them. A script that first checks which rows exist, or that deletes and reloads the five rows, gives the same result on every run.

Three cautions complete the picture:

• A PreDeploy script should not introduce schema changes that conflict with the deployment.
• A PostDeploy script should avoid heavy operations on large tables in production.
• The build does not check these scripts. Pre-deployment and post-deployment scripts are not validated against the database model at build time. An error in a script surfaces at deployment time, and a script failure halts the deployment.

The same two slots exist for a warehouse item inside Fabric, also in preview. There, a pre-deployment or post-deployment script is one of the shared queries under the Queries folder of the warehouse. You designate it in the Fabric portal, with Set deployment scripts in Object Explorer or in the CI/CD section of the warehouse Settings.

Three rules come with that designation.

• One of each. A warehouse supports at most one pre-deployment script and one post-deployment script, each in a single file. The same shared query cannot be both.
• It is stored in the project file. On commit, the designation is written to the .sqlproj file as a PreDeploy or PostDeploy item. If the .sqlproj contains multiple pre-deployment or post-deployment entries, the Git update fails.
• It runs on every deployment. On an update from Git, the pre-deployment script runs first, then the schema changes are applied, then the post-deployment script runs. Deployment pipelines also run the designated scripts in each target stage.

<ItemGroup>
  <PreDeploy Include="pre-deployment.sql" />
  <PostDeploy Include="post-deployment.sql" />
</ItemGroup>

Version the warehouse with Git integration

Visual Studio Code is one way to keep a warehouse as code. Fabric Git integration is another, and for warehouses it is also in preview.

When you connect a workspace with a warehouse to Git, the warehouse definition is committed as a database project. That project becomes the authoritative representation of the warehouse schema in source control, and in the repository the schema appears as individual .sql files.

When the workspace is synchronized from Git, Fabric does not recreate the warehouse. It uses DacFx-based incremental schema deployment, which applies only the relevant schema differences instead of updating the entire warehouse definition. It is the same idea as a publish from Visual Studio Code: compare, then apply the difference.

One file is left out on purpose. XMLA.json is excluded from commits and updates, so the default semantic model metadata is not stored in Git, and it is ignored when the workspace is synchronized from Git.

Know the limits of warehouse Git integration

Git integration for warehouses has limits that change how you design a release. The first group concerns what source control carries.

A post-deployment script for security runs on every deployment, so it tests before it creates:

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N'DataReaders')
BEGIN
    CREATE ROLE DataReaders;
END;
GO
GRANT SELECT ON SCHEMA::dbo TO DataReaders;
GO

The role is created only when it is missing, and then it receives SELECT on the dbo schema. Some earlier guidance says that scripts added to the project through Git are not preserved, so check which source control experience a workspace has.

The third row surprises people. If you changed three views and one table in a warehouse, you cannot commit only the views. The warehouse is one item, and the commit takes all four changes.

The second group concerns operations that fail or are not supported.

The first row is a design problem, not a setting. When two or more warehouse items reference each other, Fabric detects the circular reference during branch-out or a sync from Git to the workspace, and the operation fails. The fix is to avoid cyclic dependencies between items.

The collation case is the dangerous one because it can look fine. These operations might succeed without errors and still result in metadata errors: in a deployment pipeline, for example, the deployment might succeed, but the dataset collation is not updated to match the collation of the target warehouse.

Choose a route for development and deployment

With all the pieces on the table, the remaining question is which route fits a team. Fabric Data Warehouse offers five modes, and they can be combined.

Read a scenario for its requirements and match each one to a mode.

• One person, no history, no pre-production environments: Fabric web without Git. Without Git, changes are live and immediately reflected, and nothing tracks them.
• History, review, and isolated work in the browser: Fabric web with Git. It adds commits, sync from Git, reverting to previous commits, and branching out to feature workspaces.
• A local project with build, schema compare, and publish options: the IDE route, with a database project in Visual Studio Code.
• Promotion from dev to test to prod, tracked inside Fabric: Fabric deployment pipelines.
• Build and deployment automated in your own DevOps system: external CI/CD.

The modes cover different needs, so a team can use two together: one to develop and version, and one to promote.

Key takeaways

• Create it for the right target: the target platform is Synapse Data Warehouse in Microsoft Fabric, and only SDK-style projects are supported.
• Contributor is enough: the warehouse must be in a workspace where you have Contributor or higher permissions.
• Generate Script first: it shows the T-SQL and makes no changes to the target warehouse.
• SQL72033 points to the package reference: add DatabaseVariableLiteralValue with the value master.
• Two settings decide what a publish may remove: BlockOnPossibleDataLoss stays True in production, and DropObjectsNotInSource drops whatever is not in the project, so it belongs in dev and test.
• Scripts must be idempotent: PreDeploy runs before the schema deployment and PostDeploy after it.
• Git integration has limits: permissions need a post-deployment script, commits are at the warehouse item level, and warehouses that reference each other make branch-out fail.
• Pick the route by requirement: Git in the Fabric web for versioning and isolated work, deployment pipelines for tracked stage promotion, external CI/CD for automation outside Fabric.