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.
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
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 😃