Alter Table in Fabric

[SachinNandanwar]

I am trying to drop a column through SQL endpoints using the ALTER table command but it errors out with a message : "Specific statement is not suported"

Please note I am trying to alter a table in a datawarehouse and not in a lakehouse.

[Anonymous]

Hi @SachinNandanwar,

Can you please share some more detailed information about these operations? They should help us clarify your scenario and test to troubleshoot.

For example:

Are you directly executed these on the data warehouse Editor or use external tools to connect to the data warehouse?
Any reference on the table from other workflows or features? Did this table add to the semantic model?

Regards,

Xiaoxin Sheng

[SachinNandanwar]

Hi @Anonymous ,

I am not using any external tools.We have a redudant column on a table which needs to be dropped.Executing  ALTER TABLE...DROP COLUMN is erroring out.

Now this table is being referenced in the semantic model and we are not sure if dropping the table and recreating it with the necessary columns would break the existing model.

[Anonymous]

Hi @SachinNandanwar,

OK, I test these operations on my side, and they also be blocked due to the limits. For your scenario, perhaps you can try to remove the not need columns at the 'data ingestion' steps.

Regards,

Xiaoxin Sheng

[SachinNandanwar]

For the time being we would send default values to the column. I came to know that ADD column statement works with the ALTER TABLE but not the DROP column .

Any tentative timelines when this feature would be available ?

[Anonymous]

HI @SachinNandanwar,

I check on the official document and blog but not found they mention this. I'd like to suggest you focus on the follow link that listed the fabric data warehouse feature release plan:

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

Regards,

Xiaoxin Sheng

[SachinNandanwar]

Hi @Anonymous ,

I dont see anything about ALTER Column in the link you shared.

[frithjof_v]

@SachinNandanwar please vote for this idea to support DROP COLUMN in Fabric Data Warehouse:

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=5cf6f63e-9d5b-ef11-b4ad-6045bdbf73f2

[SachinNandanwar]

Done.

[frithjof_v]

I don't think it's possible. I'm not sure what is the workaround. Perhaps create a new table without that column, and copy the data into the new table.

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#limitations

https://www.serverlesssql.com/the-reality-of-alter-table-in-fabric-warehouses-2/

[SachinNandanwar]

This is a bit shocking that such a basic SQL command isnt supported.

The semantic model is partially developed that involves this particular table. Do you think there would be any checks in fabric that would prevent dropping of the table  if the table is already referred somewhere else ?

[frithjof_v]

I don't think there will be anything preventing you from dropping the table, even if the table is referred somewhere else. So that would probably cause breaking of the semantic model.

I'm not sure what happens if you drop and then recreate the table. Will the semantic model work then? I don't know.

I hope someone else knows the answer and can tell.