SQL ALTER command

[frithjof_v]

Hi, is the sql ALTER TABLE (ADD/ALTER/DROP COLUMN) commands supported now?

I see ALTER TABLE ADD COLUMN is being used successfully in this GuyInACube video to add columns to a Lakehouse table (and the new column also appears in the SQL endpoint and semantic model in the video). That is great!

I think this didn't use to work before, and I read in this documentation that sql ALTER TABLE ADD/ALTER/DROP COLUMN is not supported in Fabric. Am I missing something, or is that part of the documentation not updated?

Does this mean that these commands are supported in Fabric now? 

Thank you!

[AndyDDC · Accepted solution]

Hi @frithjof_v If you are using a Lakehouse, this is a case of the Fabric runtime version 1.1 vs 1.2 in a workspace.  If you try using ALTER TABLE in a Notebook in a workspace with Fabric 1.1 (spark 3.3) then the ALTER TABLE is inconsistent and doesn't sync properly with the sql endpoint.  If you use Fabric runtime 1.2 (spark 3.4) then ALTER TABLE should work.

It works for me, I can use:

%%sql

ALTER TABLE dimproducts

ADD COLUMN iscurrent int

and it's sync with the sql endpoint and the default semantic model

[frithjof_v]

@AndyDDC @Anonymous 

I just want to provide a little update on the ALTER TABLE topic. It seems to me that adding columns to Lakehouse tables works, and the changes also get updated in the SQL Analytics Endpoint and the Direct Lake Semantic Model.

However I am not able to drop columns or rename columns.

This is a part of the error message I get in the notebook when running a ALTER TABLE drop column or rename column command:

DROP COLUMN is not supported for your Delta table. 
Please enable Column Mapping on your Delta table with mapping mode 'name'.
You can use one of the following commands.

If your table is already on the required protocol version:
ALTER TABLE table_name SET TBLPROPERTIES ('delta.columnMapping.mode' = 'name')

If your table is not on the required protocol version and requires a protocol upgrade:
ALTER TABLE table_name SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')

(...)

If I try to enable the Column Mapping (by running the code provided in the error message) then I am able to rename or drop columns in the Notebook interface, however it doesn't work together with the SQL Analytics Endpoint and Direct Lake Semantic Model.

In the Lakehouse explorer, the menu on the left-hand side is updated similarly to the notebook, however the data view in the Lakehouse explorer doesn't reflect the changes made:

Here, I have renamed the Description column to Beskrivels, and I have dropped the Model and Category columns.

In the menu on the left-hand side, the updates I did in the notebook are reflected, but in the data view it's not.

The table also doesn't refresh in the SQL analytics endpoint and Direct Lake Semantic Model anymore.

Just something I noticed and thought I should share. It seems add columns is working, however drop columns and rename columns don't seem to be possible at the moment.

[AndyDDC · Accepted solution]

Hi @frithjof_v If you are using a Lakehouse, this is a case of the Fabric runtime version 1.1 vs 1.2 in a workspace.  If you try using ALTER TABLE in a Notebook in a workspace with Fabric 1.1 (spark 3.3) then the ALTER TABLE is inconsistent and doesn't sync properly with the sql endpoint.  If you use Fabric runtime 1.2 (spark 3.4) then ALTER TABLE should work.

It works for me, I can use:

%%sql

ALTER TABLE dimproducts

ADD COLUMN iscurrent int

and it's sync with the sql endpoint and the default semantic model

[frithjof_v]

Thank you @AndyDDC 

I agree, I hope MS can confirm this 👍 It would be good to have a separate documentation for SQL limitations within Spark, because this ALTER TABLE ADD COLUMN used to be a limitation also with Spark SQL but there was no separate documentation for the limitations with Spark SQL as far as I know. The documentation is just for the limitations with Warehouse T-SQL as far as I know.

Anyway, this is a nice feature and I am glad it is working now with the newest Spark runtime in Fabric 😃

[Anonymous]

Hi @frithjof_v ,

Thanks for using Fabric Community.
As per my knowledge though  ALTER TABLE ADD COLUMN commands work, these are not recommended to use.

At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.

[Anonymous]

Hi @frithjof_v ,

As per the documentation -

This command is also not executable in SQL Endpoint.

Without official confirmation this is not a recommended way to use.

Hope this is helpful.

[AndyDDC]

Can you please link to that documentation? I believe it needs updating to differentiate between Warehouse (SQL) and Spark.

[Anonymous · Accepted solution]

Hi @frithjof_v , @AndyDDC 

We have an update from internal team -

Note that there are two different SQL dialects in use in Fabric. In a Spark notebook you use Spark SQL. In the SQL Endpont you use T-SQL.

---

Inorder to clear everyone's confusion - we can use Alter commad in Notebook as it uses Spark SQL, but not T-SQL.

These limitations are with T-SQL - T-SQL surface area - Microsoft Fabric | Microsoft Learn

Hope this is helpful. Please let me know incase of further queries.

[Anonymous]

Glad to know that your query got resolved. Please continue using Fabric Community for your further queries.

[AndyDDC]

Confirmation would be good. But that documentation is specifying Warehouse and not Lakehouse. You can't modify a table schema or data using the Lakehouse SQL Endpoint anyway and that's by design.

but issuing ALTER TABLE via Notebook on a Lakehouse table should be supported in Fabric runtime 1.2. Hoping MS can clarify