DDL support in Notebooks

[FabricGeek]

What level of support is there for DDL commands when using the Notebooks with SQL endpoint in a Lakehouse?

I am finding that some commands are supported and others aren't. As an example, I could add a new column to a table using the Notebook but couldn't change its datatype. I am using SQL Spark in the Notebook.

[v-cboorla-msft]

Hi @FabricGeek 

Glad that your query got resolved.

Please continue using Fabric Community for any help regarding your queries.

Thanks.

[FabricGeek]

Some more info on the above:
- I can create a column in an existing Delta Lake Table - via Notebook

- I can drop the above Column using the ALTER TABLE statement - via Notebook

But when I do the following on a newly created Column or an existing Column, it fails:

ALTER TABLE <MYTABLE> ALTER COLUMN TEST INT

I can use a DF Gen2 to achieve the above but want t understand the extend of DDL support via Notebooks. Thanks for your support. 

[v-cboorla-msft · Accepted solution]

Hi @FabricGeek 

Thanks for using Microsoft Fabric Community.

Apologies for the inconvenience that you are facing here.

Yes, you are correct that DDL support in Notebooks with the SQL endpoint for Microsoft Fabric Lakehouse is currently limited. Some commands work, while others like modifying existing column data types are not yet supported.

Supported : Adding a new column to a Delta Lake table using ALTER TABLE ADD COLUMN syntax within the Notebook. Dropping a column using ALTER TABLE DROP COLUMN syntax within the Notebook.
For further details, you may refer to the following information, which could be helpful - GuyInACube video.

Not Supported : Modifying an existing column's data type using ALTER TABLE ALTER COLUMN syntax within the Notebook (This is the specific case you mentioned with ALTER TABLE <MYTABLE> ALTER COLUMN TEST INT).

Understanding the limitations : The limited DDL support in Notebooks with the SQL endpoint is likely due to the underlying architecture. The SQL endpoint translates T-SQL queries to Spark operations, and some DDL operations might not have direct translations.

For more details, please refer : Limitations.

I hope this informations helps. Please do let us know if you have any further questions.

Thanks.

[FabricGeek]

Thanks for the prompt response.
I had assumed that the SQL EndPoint is using Synapse/DW under the covers and hence, Notebooks provide full SQL DDL support. This is evidently not the case. This helps, as I ma now clearer on when and why I should be using the Fabric DW as opposed to the Lakehouse and Endpoint.
Do you know if there are any plans to include full support for SQL in Notebooks used against the SQL endpoint?

[v-cboorla-msft]

Hi @FabricGeek 

At this time, we don't have any information available regarding this. I will keep you posted regarding the updates.

Thanks.