Forum Discussion
Helper IWarehouse Schema - Tables CI / CD
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 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
giulio-dilucaHi Anonymous
thanks for the response, really appreciated