Dataflow Gen2 update method

[Noeleke1301]

I've just created a dataflow gen2 and want to load the data directly into my lakehouse.

It asks me to select an 'update method', but how does this work exactly?

For example, what if I get the same data with just a different DateTimeReceived? How does it know which row to update? Should I do anything with the column mapping? Shouldn't I set a unique key somewhere?

Thanks.

[Anonymous · Accepted solution]

Hi  @Noeleke1301 ,

The Replace and append functions do not allow you to update your data and directly insert the modified data.

For replace, it replaces all the data directly after the refresh, and for append, it appends all the content of the next refresh directly to the previously saved data.

Fore more information, you can refer to :

Create your first Microsoft Fabric dataflow - Microsoft Fabric | Microsoft Learn

If you need to implement data partition refresh, you can try to understand incremental refresh::

Using incremental refresh with dataflows - Power Query | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[WLY168]

I believe this is using some kind of Delta Parquet difference log in the background. I conducted an experiment defining a table with no primary keys and subjected it to a barrage of updates and inserts. The GEN2 Dataflow takes care of every case as long as the Timestamp column specified is correctly updated at the same time. Given "Replace" option chosen

• If you insert to the source the sink reflects the change
• If you update the source the sink reflects the change 
• If you delete from the source table, the corresponding delete does NOT happen in the sink (provided no other row has it's timestamp updated. As soon as you update the timestamp of any other row, the delete is reflected in the sink)

While all of this is very powerful, the documentation is poor. The explanation regarding "buckets" might be better if an example is provided or we can more clearly understand where the "buckets" of change are built from.

https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-gen2-incremental-refresh#how-incremen...