Add column to dataflow and update Lakehouse

[SustainHacker]

Dear Microsoft Fabric Community,

I'm facing an issue that I believe is not unique to me. My team and I are working on various proof-of-concept projects from a citizen developer perspective. In these projects, we typically load a table into the Lakehouse, build semantics, and define measures in the warehouse over several sprints.

Over time, as we progress through our work, it's common for columns to be added, removed, or updated in a dataflow. When e.g. dealing with a PBI dataflow, these changes are automatically reflected in the schema. However, when it comes to Lakehouse, it seems like we encounter limitations. Whenever we add a new column to the dataflow, we can't find a way to seamlessly bring that into the Lakehouse.

Given these limitations, we're forced to start from scratch with every sprint if we can't update the schema of the Lakehouse destination. This process is not only tedious but also significantly slows down our development speed.

Is this inability to update the Lakehouse schema by design, or is it a recognized limitation that's being tracked for a future update? If it's the latter, can we expect this feature in any upcoming releases? If it's the former, could you provide some best practices or workarounds for handling changes in the schema?

Any insights or advice would be greatly appreciated. If there's any additional information needed to understand our predicament better, please let me know. Thank you!

Best, Stefan

[DataMainer42]

Guy in a Cube did a video about a very similar situation 

Guy in a Cube youtube video 

you can use SQL in a notebook to add a column to a lakehouse table, then update the mapping destination in the dataflow

[igreenhalgh]

Bump! Also would love to see this functionality added. Especially for those so used to working with Power Query - it's so crucial to us to be able to engineer new features right from the dataflow, and incorporate the updates into the lakehouse.

[JustinWesty]

Hi, Any update on this thread @Ehren I have the same issue also Dataflow gen 2 published with 4 columns received some user feedback and a request came in to have 2 more columns update the dataflow and check the destination update schema it only pulls the columns created in the lakehouse the only force of hand I have found is to delete the table/s in the lakehouse first then the destination schema updates feel like there is a bug in the check of the data flow vs the endpoint schema as deleting the table 1st does show a message that the schema has changed which I would expect to see without deleting the table also it should detect column changes of type and additions or removal of columns in the thread posted it seems like the user created the table with all columns but did not apply the mapping she when they went back the user applied the mapping and problems solved.

[miguel]

Hi! as mentioned by jwelch earlier in the topic, this is currently a known limitation. You can go to the Fabric ideas portal to request this capability as a new feature:

https://aka.ms/FabricIdeas 

The product group reviews all ideas and your input will help them prioritize this work.

Thanks for the feebdack!

[jwelch · Accepted solution]

Currently, you cannot modify the Data Destination schema from the Power Query Online editor. Instead, if you want to change the schema of a Data Destination, you need to do it in the editor for the Data Destination itself.
This may change in the future, as we release new functionality. I would recommend posting this in the Ideas community as it will help highlight the need for this: Home (microsoft.com)

[Anonymous]

I have tested the exact same thing and also see no new columns are added to the table in the Lakehouse.

Ehren, what you are linking too doesn't help. I see no way to manually edit the Output Destinations settings. Yes, you can select a new field for an already existing field. But I want the new fields to be added and there is no possibility for that as far as I can see.

[Ehren]

Does the answer suggested in this thread help?

Solved: Re: Few columns are missing from Lakehouse tables ... - Microsoft Fabric Community