Forum Discussion
SQL ALTER command
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
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.
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.