Azure Data Factory — How To Upsert Data During Copy

This article explains how to upsert data using only copy activity in ADF.

Azure Data Factory recently released an extremely helpful feature to upsert data using copy activity. In this article, let’s explore how to utilize this feature in your next data pipeline.

I will copy data from a sample csv spreadsheet that looks like below into my Azure Synapse Data Warehouse.

In ADF, drag copy activity to the blank canvas. In the source dataset, I’ll provide the sample csv file. At the sink dataset, I’ll select the Azure Synapse Data Warehouse and select Auto create table for the first run. This option is to ensure that my copy activity creates the table first and then I can use the upsert feature.

Note: When you use auto create table option, all the columns are created with nvarchar(max) option. Before using the upsert option, change the data type from max to a fixed length such as nvarchar(100) in order for upsert to work.

(ADF Copy Auto Create table)

After the first run, the table is created in the Azure Synapse Data Warehouse.

Scenario 1: Testing…