Promoting warehouse from Test to Prod

[KimTutein]

Hi Community

For now I have a limited usecase for warehouse (we will primary use lakehouse). I bascily just want to create a warehouse with XX schemas and databaseroles which will grant write access to different schemas (the users will make there tables which will not be udner soruce control). This is meant to some of our departments that are used to have write access to a single schema in the warehouse (we do not wish to give every single department their own warehouse).

It seems to me that deployment pipelines and source control is not the way to go as SQL security is not part of that (Source control with Warehouse (preview) - Microsoft Fabric | Microsoft Learn). It is suggested using sql database project with a post deployment script to make the grants.

If anyone has tried setting up a CI/CD flow with a Fabric Warehosue and post deployment scripts please share your experience.

Regards

Kim Tutein

[NandanHegde · Accepted solution]

You can use DACPAC functionality to do the entire deployment:

Sample references:

https://www.kevinrchant.com/2024/08/01/ci-cd-for-microsoft-fabric-data-warehouses-using-github-actio...

https://stackoverflow.com/questions/60347552/azure-devops-how-to-execute-pre-and-post-deployment-sql...
https://www.kevinrchant.com/2023/10/25/ci-cd-for-microsoft-fabric-data-warehouses-using-yaml-pipelin...

[KimTutein]

Hi @NandanHegde 

Thank you very much for your response and reference I will look into the links you provided.

In our old setup in Synapse Dedicated pool we use Dacpac deployment via. a visual studio database project. The little testing I have done with sql projects has been with Azure Data Studio (I have some problems getting the extention to wok in VS Code). As written in documentation for instance adding a new field to a table will delete the contens of a table - which I find a bit disappointing. As we have decided to put most of our data in a Lakehouse and only make a schema in a warehouse available for some appartments I might just populate theese schemas and grants via. a script task using normal pipelines and a control table in our lakehouse.