Forum Discussion
Renaming columns and adding columns
Hi ebjim
Thanks for using Fabric Community.
Yes you are right. Currently rename a table in lakehouse, and ALTER table functions, adding new columns in a Lakehouse table is not supported in Fabric.
sp_rename is supported for Tables, Stored Procedures , Views and Functions in Warehouse. It is supported only for objects not for columns.Hope this helps. Please let me know if you have any further questions.
You can change the schema of a lakehouse table by using a notebook and advancing the schema if your table is a delta table. The lakehouse sql endpoint won't let you do any schema (or data) modifications.
In my experience, you need to create a new table with the new column schema. (at least if you are planning to use the table in the SQL Analytics Endpoint and the Semantic Model).
I have done it like this:
1. Create a new table with the new schema.
2. Fill the new table with data.
Copy the data you want to keep from the old table (if needed), by using Dataflows Gen 2 or Notebook.
Or just fill the new table with new data from the source system.
(1. and 2. can be done in the same step).
3. Delete the old table
If you want the new table to have the same name as the old table, you need to do step 3 (delete old table) before step 1 and step 2 (create new table).
I have not been able to keep the version history (delta logs) of the original table.
I hope functionality will be added so we can make changes to the schema of an existing lakehouse table. The process now with deleting and creating new table is quite cumbersome, at least in my experience. Especially if using medallion architecture with delta tables in two or three layers.