Forum Discussion
Warehouse Schema - Tables CI / CD
Hello.
I'm implementing a CI / CD solution with Azure DevOps Pipeline to update Fabric in a classic DEV -> TEST -> PREPROD -> PROD environment, where in each environment there is a dedicated workspace.
- in feature, dev and test i use git integration
- in preprod and prod i use fabric deployment pipeline
How deal with warehouses?
- If i have a new table, i get it from feature to prod?
- If i have a new column in a table, how get it from feature to prod?
The starting point is a source database from get a sql server ( from get structure, data ) ... but this is just to give a little context, the real question is how pass changes from feature to prod?
Warehouses should be supported both by git integration and fabric deployment pipeline but, seems that, if Fabric detects a new column in an existing table, it drop and recreate the table instead of performing an in-place schema update leading in a data loss.
Regards,
Giulio
Hi giulio-diluca, deployment pipelines are made for deploying code and do maintain the state of database structures or data. To deploy database structures to a Warehouse or SQL database, you should put your database structure into a database project using VSCode or Visual Studio and maintain the database project in your repositorie.
Then using DevOps you can build your project into a .dacpac and deploy it to your Warehouse in every environment. Any change in database structure is now automatically deployed.
But, you need to look into using pre-/post-deployment scripts that you can use for structure changes that could cause data loss. Depending on your database project settings, if you want to add a column to a table and the column is added in the middle of the table, the dacpac deployment will fail with a message that it cannot continue due to possible data loss or it will drop and recreate your table resulting in data loss.
To solve this, you need to use a pre-script to rename or clone your table, then the deployment will recreate your table and the post-script needs to copy the data from the old table into the new table.
In DevOps you can choose to use the integrated SqlAzureDacpacDeployment task or look into SqlPackage CLI for more flexibility.
Hope this helps you on your way. Probably someone else will post more information on how to best maintain pre-/post-script versioning or anything other that I might have mist in this reply.
4 Replies
Hi giulio-diluca,
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to nielsvdc for prompt and helpful response.
Not all schema changes cause data loss: Correct. Safe operations include adding a new column (at the end of a table), creating new tables, or adding indexes/constraints. Data loss only comes into play with destructive changes such as dropping a column, changing a column’s data type incompatibly, or reordering columns (which DACPAC interprets as a rebuild).
Scope of data loss: If a destructive change is applied, only the affected table is rebuilt and its data lost. The warehouse itself is not dropped or recreated other tables remain untouched.
Fabric built‑in deployment vs. DACPAC: Fabric Git integration and Deployment Pipelines can move schema forward, but they don’t give you fine‑grained control over how changes are applied. In some cases, Fabric will drop/recreate objects rather than apply incremental ALTERs. DACPAC deployments, on the other hand, explicitly compare source vs. target schema, apply incremental changes where possible, and block or warn on destructive changes. They also allow you to add pre‑ and post‑deployment scripts: to handle complex migrations safely. That extra control is the main difference.
DACPAC vs. BACPAC: A DACPAC contains schema only. If you want schema plus data, you use a BACPAC. DACPAC is the right tool for CI/CD schema evolution; BACPAC is for exporting/importing both schema and data (for example, moving a database snapshot between environments).Data-Tier Applications (DAC) Overview - SQL Server | Microsoft Learn
Pre-Deployment and Post-Deployment Scripts - SQL Server | Microsoft Learn
CI/CD with Warehouses in Microsoft Fabric | Microsoft Fabric Blog | Microsoft Fabric
Thanks & Regards,
Prasanna Kumar
Hi Anonymous
thanks for the response, really appreciated
Hi giulio-diluca, deployment pipelines are made for deploying code and do maintain the state of database structures or data. To deploy database structures to a Warehouse or SQL database, you should put your database structure into a database project using VSCode or Visual Studio and maintain the database project in your repositorie.
Then using DevOps you can build your project into a .dacpac and deploy it to your Warehouse in every environment. Any change in database structure is now automatically deployed.
But, you need to look into using pre-/post-deployment scripts that you can use for structure changes that could cause data loss. Depending on your database project settings, if you want to add a column to a table and the column is added in the middle of the table, the dacpac deployment will fail with a message that it cannot continue due to possible data loss or it will drop and recreate your table resulting in data loss.
To solve this, you need to use a pre-script to rename or clone your table, then the deployment will recreate your table and the post-script needs to copy the data from the old table into the new table.
In DevOps you can choose to use the integrated SqlAzureDacpacDeployment task or look into SqlPackage CLI for more flexibility.
Hope this helps you on your way. Probably someone else will post more information on how to best maintain pre-/post-script versioning or anything other that I might have mist in this reply.
hI nielsvdc
I'm not a data expert so probably some questions that i'll do might seems obvious
1) not all operations made in a database structure cause loss data right?
2) if an operation that cause loss data is performed in a table in warehouse, only data in this table are loss, the warehouse object is not re-created right?3) using fabric built-in deployment systems ( like git integration, fabric deployment pipeline ) we're able to manage database structure even if some data could be loss based on operations. how they differ from creating a .dacpac considering that seems work the same?
4) .dacpac, with specific option, seems that is able to extract also data from a source database, so i can point a warehouse url from where i can get these data?
Regards,
Giulio
