Dataflows to Lakehouse does not overwrite table, but only the records existing in the new table

[optimizer]

Dataflows to Lakehouse (replace) does not overwrite table (first delete all records, then append new ones) , but only the records existing in the new table will be updated. Is this a bug or expected?

[Anonymous]

Hi @optimizer ,

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

When I tried to publish the dataflow gen2 with replace, it worked as expected - haven't faced any issue.

Hope this is helpful. Please let me know incase of further queries.

[optimizer]

Well it doesn't replace all the data, because some of it still exists. Apparently sometimes it does, but in my case it didn't.

[frithjof_v]

Are the unwanted rows visible in the SQL Analytics Endpoint, Lakehouse Explorer and Notebook?

Or are the unwanted rows only visible in some of these user interfaces?

I had an experience where Notebook was showing me some old rows which should have been deleted by Dataflow Gen2 replace.

However the SQL Analytics Endpoint was showing me the correct rows only.

[miguel]

the scenario that you're bringing up might be unrelated to the discussion of this thread. I'd recommend opening up a new topic / thread and share some repro steps to see the behavior or reaching out to our customer support team by opening a support ticket so an engineer can take a closer look at your specific scenario.

[Anonymous]

Hi @optimizer ,

Apologies for the issue you are facing, if replace is not working as expected - can you try recreating new data flow and test it? Hopefully it should work properly.

It's difficult to tell what could be the reason for this performance.

If the issue still persists, please reach out to our support team so they can do a more thorough investigation on why this it is happening: Link 

After creating a Support ticket please provide the ticket number as it would help us to track for more information.

Hope this helps. Please let us know if you have any other queries.

[Anonymous]

Hi @optimizer ,

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 .

[Anonymous]

Hi @optimizer ,

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 .

[govindarajan_d]

No. When you have replace option, you can think of it like a truncate and load of table.

With Delta lake, versions of data are available, so there won't be any true deletion of data happens until you use VACUUM command. Even though multiple versions of data will be available, when we query only the latest version of data will be used. So, when you use replace only the new data records as is when you had loaded should come.