Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ebjim
Helper IV
Helper IV

Renaming columns and adding columns

I have digging around for definitive answers but have not much to show for. I have read this

 

https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area#t-sql-surface-area

and

https://blog.fabric.microsoft.com/en-US/blog/announcing-fabric-warehouse-support-for-sp_rename/

 

The testing I have leads me to conclude:

 

1. I cannot add a column to an existing lakehouse table.

2. I cannot rename a column in an existing lakehouse table. 

3. I cannot add a column to an existing warehouse table.

4. I cannot rename a column in an existing warehouse table (sp_rename doesn't seem to go the column level).

 

Any feedback about how correct (or not) I am would be appreciated!

 

2 ACCEPTED SOLUTIONS
v-nikhilan-msft
Community Support
Community Support

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.

vnikhilanmsft_0-1701336454461.png

Hope this helps. Please let me know if you have any further questions.

View solution in original post

AndyDDC
Most Valuable Professional
Most Valuable Professional

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.

 

 

View solution in original post

11 REPLIES 11
frithjof_v
Community Champion
Community Champion

It seems to me that you can use overwriteSchema for renaming columns (and other schema changes):

 

https://community.fabric.microsoft.com/t5/General-Discussion/Dropping-and-recreating-lakehouse-table...

 

That implies doing a full overwrite of the table data as well.

 

 

For adding columns I think you can use this generic code:

 

%%sql

ALTER TABLE tableName 

ADD COLUMN columnName columnType; 

 

The way I understand it, this is just a metadata operation and does not imply a full overwrite of the data.

PradeepOS
Regular Visitor

MSFT Fabric now has a solution to rename delta table columns.

 

Run this code in a Fabric notebook:

 


ALTER TABLE <table_name> SET TBLPROPERTIES (
   'delta.columnMapping.mode' = 'name',
   'delta.minReaderVersion' = '2',
   'delta.minWriterVersion' = '5')
 
and then run the rename query:
 
ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>
AndyDDC
Most Valuable Professional
Most Valuable Professional

Careful with this though as if you set the table to name mapping mode, table changes won't be synced to the Lakehouse SQL Endpoint

frithjof_v
Community Champion
Community Champion

It seems to be possible to add columns in Lakehouse table now by using notebook.

 

I am able to use the following type of command in a Notebook:

 

%%sql

ALTER TABLE tableName

ADD COLUMN columnName dataType

 

And the table will get updated also in SQL Analytics Endpoint and Direct Lake Semantic Model, something which was a problem before.

 

Ref. this thread:

https://community.fabric.microsoft.com/t5/General-Discussion/SQL-ALTER-command/m-p/3748079#M4861

 

However, I get an error if I try to rename or remove (drop) a column.

fabricator1
Advocate II
Advocate II

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.

ebjim
Helper IV
Helper IV

Thank you both for the feedback!

Hi @ebjim 
Glad that your query got resolved. Please continue using Fabric Community for any help regarding your queries.

AndyDDC
Most Valuable Professional
Most Valuable Professional

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.

 

 

Could you please expand on this or provide links to additional resources. Thanks appreciate the insight.

Can you provide more information on how to use a notebook to add a column to a lakehouse table?

 

I am using a Dataflow Gen2 to get data from SQL Server into the lakehouse. Do you know if that is a delta table?

v-nikhilan-msft
Community Support
Community Support

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.

vnikhilanmsft_0-1701336454461.png

Hope this helps. Please let me know if you have any further questions.

Helpful resources

Announcements
Oct Fabric Update Carousel

Fabric Monthly Update - October 2024

Check out the October 2024 Fabric update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.