I'm revisiting Azure Data Factory after the introduction of REST Apis as supported sources for dataflows. We manage multiple client M365/Azure environments and using the MS Graph API I'm trying to pull different types of reports and info from their Tenant. My pipeline will start with pulling a list of our Tenant Ids, foreach tenant, run the dataflow that pulls their user list and then sinks that to our Azure SQL DB.
Main parts works as expected, and inserts run just fine, however I want to delete users that no longer exist in the API source from the SQL table or update them if they already exits and of course insert if they are new. I can't seem to figure out how to accomplish this.
Currently works with Inserting only.
Sink Settings when trying to do updates/deletes/inserts.
Alter Row Transformation
I can't for the life of me figure out what expressions to put in here.
Id column is a GUID from MS for that specific user.
My thought was I needed to add a 2nd source to compare against, that 2nd source being the target table for my sink, but my google fu is proving very weak and can't seem to figure out how to use the expression language to reference columns from another source in the AlertRow step.
Am I going about this the wrong way? My sink table is going to have multiple clients in it so I can't drop the table each time I run the data flow, I need it to be to flexible, as there might be times I just want to update a single client's data.