ADF mapping data flow only inserting, never updating

Question

I have an ADF data flow that will only insert. It never updates rows. Below is a screenshot of the flow, and the Alter Row task that sets the insert/Update policies.

data flow

alter row task

There is a source table and a destination table. There is a source table for new data. A lookup is done against the key of the destination table. Two columns are then generated, a hash of the source data & hash of the destination data. In the alter row task, the policy's are as follows:

• Insert: if the lookup found no matching id.
• Update: if lookup found a matching id and the checksums do not match (i.e. user exists but data is different between the source and existing record).
• Otherwise it should do nothing.

The Sink allows insert and updates:

Even so, on first run it inserts all records but on second run it inserts all the records again, even if they exist.

I think I am misunderstanding the process and so appreciate any expertise or advise.

Answer 1

I tried simple dataflow structure for above problem. Where source1 is my AzureSynapse table and Sink1 is Snowflake table.

for Alter row, I added keycolumn name : if you have more than one column, you can provide comma separated list as well.

And added same column as The Snowflake table have a unique constraint defined on column ID. With this, I am able to update as well as insert data in my final table.

Hope this helps you as well.

Answer 2

Thank you Joel Cochran for your valuable inputs, repro’d the scenario, and posting it as an answer to help other community members.

If you are using the upsert method in the sink, add alter row transformation with upsert if and write the expression for the upsert condition.

If you are using insert and update as your update method in the sink then in alter row transformation use both inserts if and update if conditions to insert and update data accordingly into the sink based on alter row conditions.