Upsert in data flow

Question

Hi

I created a data flow where in the Sink I use an upsert.
Everything works fine.

I am counting the inserts and updates by using a variable

@if(empty(activity('df_fac_upf_ipy_ordr_di').output.runStatus.metrics),0,string(activity('df_fac_upf_ipy_ordr_di').output.runStatus.metrics.snkfacipyordr.rowsWritten))

But in this way it is just counting the inserts and updates in one value.
Is there a possibility to split out the number of inserts and updates? So that I have to counts: rows-inserted and rows-updated.

Regards
Ron

Answer 1

Hi @HimanshuSinha

must I write the numbers to a file. Aren't they visible in the data flow outputs without writing to a file?

regards
Ron

Answer 2

Hello @Poel van der, RE (Ron) ,
Thanks for the ask and using the Microsoft Q&A platform .

Add a new branch directly after the Alter Row. Add an Aggregate transformation to that New Branch and count the number of rows that are isUpsert() or isInsert(). https://learn.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions#isupsert

The Aggregate transformation would have no group-by field and would have a column for insert and upsert with an expression like this: sumIf(isUpsert(),1) and sumIf(isInsert(),1).

Then after the Aggregate, add a Sink that writes the sum columns to a file.

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members