Forum Discussion
Advocate IVDeployment Pipeline fails deploying WH if Schema with same name exists in a different WH
Greetings !
We have seem to have encountered an issue where we have 2 warehouses in a workspace and both contain a schema DW_COPY
WH_1
-DM_Reporting
-DW_Copy
WH_2
-DM_Financial_Reporting
-DW_Copy
When moving WH_1 and WH_2 to a new environment the deployment fails as it seems to lose the scope the schema is under.
Relevant portion of the error message is:
Backend Error: Import failure: DmsImportDatabaseException. Error occured during import database for the Datawarehouse '2c5751a4-d019-404e-a4f7-12ff5277aabc@d4bd9585-0b70-4134-be5f-3a72cfe29abb'.
File: schemas/[DW_Copy].sql,
Error: There is already an object named 'DW_Copy' in the database.
*guids obfuscated
Is this a known limitation? Odd that this would be allowed in our initial dev workspace, and not be revealed until time to promote via deployment pipeline.
As well, should mention that this occurs whether we deploy WH_1 & WH_2 simutaneously, or selecting them individually to deploy one aftr the other.
Tamanchu & v-abhinavmu
After connecting with Microsoft support it was determined the issue is not actually related to schemas in 2 different warehouses, but special character schema names created with SET QUOTED_IDENTIFIER ON switch.
Effectively:SET QUOTED_IDENTIFIER ON
This allows for distinct Schema names to be created within a warehouse, including ones with the same name.
CREATE SCHEMA [TEST]
CREATE SCHEMA "[TEST]"
While this is expected behaviour and valid, the issue surfaces with deployment pipelines.
this is due to how CICD for the warehouse stores these without being aware of special characters:
TEST.sql -> CREATE SCHEMA TEST
[TEST].sql -> CREATE SCHEMA [TEST]
This is the actual source of the issue. I have opened an idea to add better support for the special characters: Make CI/CD & Deployment Pipelines aware of specia... - Microsoft Fabric Community
As a workaround we chose a different schema name, and rebuilt the tables/stored procedures within.
5 Replies
anon97242Tamanchu & v-abhinavmu
After connecting with Microsoft support it was determined the issue is not actually related to schemas in 2 different warehouses, but special character schema names created with SET QUOTED_IDENTIFIER ON switch.
Effectively:SET QUOTED_IDENTIFIER ON
This allows for distinct Schema names to be created within a warehouse, including ones with the same name.
CREATE SCHEMA [TEST]
CREATE SCHEMA "[TEST]"
While this is expected behaviour and valid, the issue surfaces with deployment pipelines.
this is due to how CICD for the warehouse stores these without being aware of special characters:
TEST.sql -> CREATE SCHEMA TEST
[TEST].sql -> CREATE SCHEMA [TEST]
This is the actual source of the issue. I have opened an idea to add better support for the special characters: Make CI/CD & Deployment Pipelines aware of specia... - Microsoft Fabric Community
As a workaround we chose a different schema name, and rebuilt the tables/stored procedures within.- Tamanchu
Super User
Hi anon97242,
This really looks like a deployment pipeline / import scoping issue rather than a SQL Warehouse design issue.
Having the same schema name in two different Warehouses should be valid, because the schema is scoped to its own Warehouse/database. The error message is interesting because it references schemas/[DW_Copy].sql and then says that an object named DW_Copy already exists in the database, which suggests that during deployment/import the schema script may not be fully scoped to the correct Warehouse context.
The fact that it happens even when deploying WH_1 and WH_2 individually makes it even more likely that this is not just an ordering issue in the deployment pipeline.
As a workaround, I would test with a temporary unique schema name per Warehouse, just to confirm the root cause. But if the same setup works in the source workspace and only fails during promotion, I would definitely raise this with Microsoft Support and include the pipeline deployment error, warehouse IDs, and the schemas/[DW_Copy].sql reference.
This feels like a product-side ALM bug or limitation in Warehouse deployment pipelines.
Hope this helps narrow it down. If this helped, feel free to mark it as a solution so others can find it too.
- anon97242
Yes, thank you, did not know if this belonged in warehouse, or data engineering, or Fabric platform. I will open an issue with Microsoft Support.
v-abhinavmuCommunity Support
Hi anon97242,
Could you please confirm if the issue has been resolved after raising a support case? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.
william1234Helper II
This islikeley a deployment limitation bug. DW_Copy can exist in separate warehouses, but the deployment process seems to lose that scope and tries to create the same schema twice in one context.
A workaround is to rename one of the schemas. Otherwise, this looks like something to raise with Microsoft support
