Source control with Warehouse (preview)

Статия
07/19/2024

This article explains how Git integration and deployment pipelines work for warehouses in Microsoft Fabric. Learn how to set up a connection to your repository, manage your warehouses, and deploy them across different environments. Source control for Fabric Warehouse is currently a preview feature.

You can use both Git integration and Deployment pipelines for different scenarios:

Use Git and SQL database projects to manage incremental change, team collaboration, commit history in individual database objects.
Use deployment pipelines to promote code changes to different pre-production and production environments.

Git integration

Git integration in Microsoft Fabric enables developers to integrate their development processes, tools, and best practices directly into the Fabric platform. It allows developers who are developing in Fabric to:

Backup and version their work
Revert to previous stages as needed
Collaborate with others or work alone using Git branches
Apply the capabilities of familiar source control tools to manage Fabric items

For more information on the Git integration process, see:

Set up a connection to source control

From the Workspace settings page, you can easily set up a connection to your repo to commit and sync changes.

To set up the connection, see Get started with Git integration. Follow instructions to Connect to a Git repo to either Azure DevOps or GitHub as a Git provider.
Once connected, your items, including warehouses, appear in the Source control panel.
After you successfully connect the warehouse instances to the Git repo, you see the warehouse folder structure in the repo. You can now execute future operations, like creating a pull request.

Database projects for a warehouse in Git

The following image is an example of the file structure of each warehouse item in the repo:

When you commit the warehouse item to the Git repo, the warehouse is converted to a source code format, as a SQL database project. A SQL project is a local representation of SQL objects that comprise the schema for a single database, such as tables, stored procedures, or functions. The folder structure of the database objects is organized by Schema/Object Type. Each object in the warehouse is represented with a .sql file that contains its data definition language (DDL) definition. Warehouse table data and SQL security features are not included in the SQL database project.

Shared queries are also committed to the repo and inherit the name that they are saved as.

Download the SQL database project of a warehouse in Fabric

With the SQL Database Projects extension available inside of Azure Data Studio and Visual Studio Code, you can manage a warehouse schema, and handle Warehouse object changes like other SQL database projects.

To download a local copy of your warehouse's schema, select Download SQL database project in the ribbon.

The local copy of a database project that contains the definition of the warehouse schema. The database project can be used to:

Recreate the warehouse schema in another warehouse.
Further develop the warehouse schema in client tools, like Azure Data Studio or Visual Studio Code.

Publish SQL database project to a new warehouse

To publish the warehouse schema to a new warehouse:

Create a new warehouse in your Fabric workspace.
On the new warehouse launch page, under Build a warehouse, select SQL database project.
Select the .zip file that was downloaded from the existing warehouse.
The warehouse schema is published to the new warehouse.

Deployment pipelines

You can also use deployment pipelines to deploy your warehouse code across different environments, such as development, test, and production. Deployment pipelines don't expose a database project.

Use the following steps to complete your warehouse deployment using the deployment pipeline.

Create a new deployment pipeline or open an existing deployment pipeline. For more information, see Get started with deployment pipelines.
Assign workspaces to different stages according to your deployment goals.
Select, view, and compare items including warehouses between different stages, as shown in the following example.
Select Deploy to deploy your warehouses across the Development, Test, and Production stages.

For more information about the Fabric deployment pipelines process, see Overview of Fabric deployment pipelines.

Limitations in source control

SQL security features must be exported/migrated using a script-based approach. Consider using a post-deployment script in a SQL database project, which you can configure by opening the project with the SQL Database Projects extension available inside of Azure Data Studio.

Limitations in Git integration

Currently, if you use ALTER TABLE to add a constraint or column in the database project, the table will be dropped and recreated when deploying, resulting in data loss. Consider the following workaround to preserve the table definition and data:
- Create a new copy of the table in the warehouse, using CREATE TABLE and INSERT, CREATE TABLE AS SELECT, or Clone table.
- Modify the new table definition with new constraints or columns, as desired, using ALTER TABLE.
- Delete the old table.
- Rename the new table to the name of the old table using sp_rename.
- Modify the definition of the old table in the SQL database project in the exact same way. The SQL database project of the warehouse in source control and the live warehouse should now match.
Currently, do not create a Dataflow Gen2 with an output destination to the warehouse. Committing and updating from Git would be blocked by a new item named DataflowsStagingWarehouse that appears in the repository.
SQL analytics endpoint is not supported with Git integration.

Limitations for deployment pipelines

Currently, if you use ALTER TABLE to add a constraint or column in the database project, the table will be dropped and recreated when deploying, resulting in data loss.
Currently, do not create a Dataflow Gen2 with an output destination to the warehouse. Deployment would be blocked by a new item named DataflowsStagingWarehouse that appears in the deployment pipeline.
The SQL analytics endpoint is not supported in deployment pipelines.

Споделяне чрез

Source control with Warehouse (preview)