Renaming columns and adding columns

[ebjim]

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!

[v-nikhilan-msft · Accepted solution]

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.

[frithjof_v]

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]

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]

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]

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]

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]

Thank you both for the feedback!

[v-nikhilan-msft]

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

[AndyDDC · Accepted solution]

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.

[murray-matthew]

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

[mjc-fabric]

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 · Accepted solution]

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.