Deploying Dacpac to Existing Data Warehouse

[davidwolfson]

I currently have a release pipeline in Azure Devops that takes a Dacpac file and deploys it to a fabric data warehouse. When I created a new, empty data warehouse, the deployment went smoothly. However, now if I make a schema change and try to redeploy the dacpac to the same data warehouse, I get the following error: 

Error SQL0: Deployment cannot continue because table changes that require 'ALTER TABLE' are not supported on Synapse Data Warehouse in Microsoft Fabric at this time.

Does anyone have a workaround for this? One possibility I thought of is to delete and recreate the data warehouse before the dacpac deployment, but with my setup that won't work. Any ideas would be appreciated.

[Anonymous]

Hi @davidwolfson ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[AndyDDC]

Hi @davidwolfson with dacpacs (as you know) it's not possible to alter a table other than to add a new column.

What I do is I have a process in SQL to write out the new table amended schema eg if I have DimProduct, then I write out to DimProduct_New.  Then I rename the objects (switching them over using sp_rename).  If there are PK/FKs then they will need to be dropped recreated after the table rename.

What I then do is keep the latest schema in a database project in Azure Data Studio in GitHub by doing a schema compare and bringing the latest changes into the project.  So I don't deploy from the project, it's just there to make sure I have a full copy of the schema.

I'm returning to my testing with Deployment Pipelines, but there is still the issue of changing the schema other than adding new columns.

[davidwolfson]

Can you provide some more details on your process in SQL? Also, with the database project, do you compare the "old" schema with the schema that is the result of the SQL process? 

[Anonymous]

HI @davidwolfson,

AFAIK, current some SQL functions (e.g. ALTER TABLE) not released to data warehouse and been listed in the limitations.
If you want to use these feautre on data warehouse, you can pay attention on the new features release or try to submit an idea about these requirements.

What's new and planned for Synapse Data Warehouse in Microsoft Fabric - Microsoft Fabric | Microsoft...

Microsoft Fabric Ideas
If you interested about that document, you could take a look at the following link to know more about these:

T-SQL surface area - Microsoft Fabric | Microsoft Learn
Regards,

Xiaoxin Sheng