I have a For Each activity in azure data factory that runs two copy activities. Each of these copy activities have a Synapse Dedicated pool table as a sink (different table for each copy activity). I'm using an Upsert as the copy method. I want for the data to be copied into their designated dedicated tables at the same time so as to not get inconsistent data.
How can I do this? Would it need a pre-copy script in ADF that checks the status of each activity or a temp table the data is dumped to prior to being pushed to the actual dedicated table?
ADF and Synapse Copy activities are not transaction bound and load in batches. For both tables to be consistent in loading, you would need to copy the data into staging tables and then trigger a stored procedure activity that would load the data into destination tables bound within transcation