Issue with overwriting delta table in notebook to Atler warehouse table to Add column

[swathisudheer]

I have been trying to alter a table in fabric warehouse to add a column but seems like there is a limitation to it. So I have been trying to implement this notebook by adding a column to the delta tables and save/orvewrite the existing file. I have used the below code to do so:

import os, requests
DATA_ROOT = "/lakehouse/default"
DATA_FOLDER = "Files/Test/dbo"
df_Dummy = spark.read.format('delta').option('header',True).option('inferSchema',True).load(f'{DATA_FOLDER}/Dummy/')

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled",'true')
addingcol = df_Dummy.withColumn('DummyName', lit('Company'))
addingcol.write.format('delta').option('mergeSchema','true').mode('overwrite').save("Files/Test/dbo/Dummy")
addingcol.show()

But it has been throwing the following error:

What am I doing wrong here? Is it even possible to overwrite the exisiting table in delta format to add a column so it reflects in warehouse? The idea is to alter the table in warehouse to add a new column ultimately.

Please do suggest any other way if anybody knows.

[Madhusudan_P]

Hi @swathisudheer ,

As of now 6th Feb 2024 , ALTER TABLE command is not supported for tables in Lakehouse and Warehouse. So when you try to modify the table using Spark code it might result in some issues. 

Please follow any of the two approaches listed below to alter column.

1. Drop the table and then add a column and then recreate the table.
2. Use CTAS statement to add new column. More on https://www.purplefrogsystems.com/2023/10/fabric-data-warehouse-alter-table-workaround/
3. Load data to a dataframe and then alter dataframe by appending/removing column. Then delete the table and recreate the table. More here https://community.fabric.microsoft.com/t5/General-Discussion/Add-or-remove-column-in-Lakehouse-table...

Kr,

Maddy

[v-gchenna-msft]

Hi @swathisudheer ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
Otherwise, will respond back with the more details and we will try to help .

[v-gchenna-msft]

Hello @swathisudheer ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .
In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread .

[swathisudheer]

Hi @v-gchenna-msft ,

The solutions that @Madhusudan_P  suggested involve deletion of the table as a step and I am looking for a solution where I could avoid that. I still have not found a solution for this. Waiting for Microsoft to fix this or get a better work aorund to alter the table to add column.