Lakehouse Add or Remove columns from table

[BryanCarmichael]

Not sure if this is the right forum or not - but here is the issue.

We are loading data to a Lakehouse using gen 2 data flows (for now they are just pointing at exisitn gen 1 dataflows then doing the lakehouse insert - we will recify this later on).

Over time it is typical for columns to be added, removed and / or updated in a dataflow  - with a datamart these changes are reflected automatically in the schema - however with a laehouse when adding a new column to the dtaflow i can see no way to bring that into the lakehouse.

What do i need to do here - only options i can see are
1: import it as a new table but that seems to be very clunky as you would need to update queoroes / stored procedures on your sql end point to cater for this
2: Delete exisitng table in lakehouse and then add a new one with the same name.

Am i missing something ?

[funtomas]

There is 3rd option which worked for me:

3.Rename original table. For exmplate rename "Table" to "Table1"

Then go to your Dataflow and setup destination of your Dataflow again. (Of course use create new table called "Table".)

[BryanCarmichael]

So where we ended up with this is moving to using a warehouse for almost everything - and creating our own tables in it.

This allows you to alter them and create primary keys (not enforced) .
Lakehouse is good for super unstructured data but if you have structured data then a warehouse is a much better option.

[asittrivedi]

I have used a Python notebook to add a column to an existing table and that works just fine. One can use spark dataframe or pyspark.pandas dataframe to get the desired outcome. 

[coolie]

could you share how to do this in python?

[duttasoumya]

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

[marcuspaivio]

I was wondering the same. 🙂