In Azure Synapse, Does UPSERT include DELETES as well?

Question

Hi all,

Can some one here help me to understand if UPSERT configured in Azure Synapse Pipelines includes DELETES as well. Normally, UPSERT means Update+Insert based on the key column provided, but recently I have observed that there are two rows from Source has been successfully inserted into my Staging area and then one of the two rows has been deleted from Source and the pipeline ran again. This deleted row also disappeared from my Staging area. So, it means that UPSERT performs DELETES as well?

Thanks in advance.

Best Regards

Answer 1

as per ref: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-alter-row

The UPSERT operation in Azure Synapse and most other data systems typically refers to an operation that either inserts new rows based on a specified key or updates existing ones. This operation does not include a delete operation by default. When a row is deleted from the source table, it is not deleted from the target table as part of a UPSERT operation.

However, in Azure Data Factory, which Azure Synapse uses, there is a transformation called "Alter Row" that allows you to specify insert, update, and delete policies on rows based on certain conditions. This means you can configure a pipeline to handle inserts, updates, and deletes based on the data and conditions you specify. If rows are deleted in your target table when they are deleted from the source, this is most likely due to an explicit delete condition configured in the Alter Row transformation 1.

Answer 2

Hi TheNerd ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, it seems you want to know if 'UPSERT' includes delete operation as well or not since you have observed that the record deleted from source is missing from sink as well after pipeline execution. Please let me know if that is not the correct understanding.

Could you please share your pipeline configuration. Are you using upsert option in Copy activity or dataflow in azure synapse to load the data from source to sink?

Upsert is combination of Insert (new records) and update (existing records) . It doesn't include delete operation. So, in case you are using copy activity, then it is strange to know that without any intervention, the record has been deleted from your staging table. Kindly make sure no one manually deleted the data from sink .

You can implement 'delete policy' using alter row transformation in mapping dataflow. Here are the examples of the implementation on how to do the same: Data Flow - "Delete If" setting in Alter Row

ADF: How to delete missing source rows from your target database using data flows

Hope it helps. Kindly accept the answer if it's helpful. Thankyou.