Dataflow and Delta Tables - strange behaviour

[st_0999]

Hi, 

I'm got strange behaviour with one of the Delta tables I created yesterday, but I'm not sure if this is a bug, or whether it is expected. 

I'll try to explain what I saw:

I have a dataflow that picks up data from a table in an Excel file, does a few transformations and then sends it to a Delta Table, usnig the Replace method (not the Append method)

Let's say the Excel file had 10 records (1 to 10). Then yesterday, when I removed records 1-3 from the Excel file, and ran the Data flow again, I was expected to see only records 4 to 10.

But I still saw all the records 1-10. 

So does the replace method, not overwrite the whole table again, and only on a record by record basis, based on some kind of primary key match (so that whatever was already in the table, stays there - so in this case it ovewrote / replaced records 4 to 10, but left 1-3 in the table). Or was this a bug? (I ran the dataflow 3 times...thinking it might be some kind of system lag, but I still saw records 1-3.)

How can I make sure that the Delta table reflects exactly the updated Table in the Excel file?

Knowing this has quite a big implication on what I'm designing.

Thanks again for all the help!

[st_0999]

So, in Fabric, does the "Replace" method when setting a Data Destination in a Gen 2 Dataflow replace the whole table, or just the records that have changed?

It happened again. In a Query, I added a step to filter out the 0's. 

But after a few refreshes, the Delta Table still held those 0's! 

I had to delete the Delta Table completely from the Lakehouse to get this working properly. 

Is this expected behaviour, or a bug?

[miguel · Accepted solution]

Replace = basically deletes all the records in the table and replaces the content in the table with new one

Append = Simply adds new records to an existing table

A method such as "UPDATE AND INSERT" would probably update existing records if it finds any and will try to also insert new ones if they dont already exist. This method doesn't exist today, but you can create a new idea on aka.ms/FabricIdeas for it.

[st_0999]

Thanks for explaining this. I appreciate Fabric is still in Beta and Preview, but I often still get buggy behaviour with this, where Replace doesn't always replace the whole table afresh. Cheers,

[DennesTorres]

Hi,

I'm not sure exactly what you mean about "Replace method".

The sink activity has the option to make trucate or append in relation to the existing table.

In relation to the operations record by record, there is the option to enable update, insert and upsert.

But I'm not much sure which option you mean when you mention replace. Could you show a screenshot?

By the way, if the sink activity is set to truncate the table before the operations, it will always reflect the excel file
Kind Regards,

Dennes