How to rightly configure an Azure data factory data flow with a delta file as sink?

Question

I am trying to sink data to a delta file. I want to use a insert and update method using the unique Id column: (sink settings)

Whenever the update method is allowed a AlterRow operation will appear. I have the following settings for the alterRow:

I want a row to be updated whenever the id value is the same (set to true()). I want a row to be inserted whenever the id value is not yet in the delta file (set to false().

Is this correct?

Answer 1

If you want inserting when the id doesn't exist and updating when it does, you could just select upsert as your update method.

An upsert is a combination of an insert and an update.

The way you've currently got it configured means that it will always update (as it's set to true) and will never insert (as it's set to false).

Answer 2

I want a row to be updated whenever the id value is the same (set to true()). I want a row to be inserted whenever the id value is not yet in the delta file (set to false().

To achieve this, you can use the upsert option directly with alter row transformation as below

then you need to set update method as upsert in sink with the key column based on which it will update the destination.