Azure Data Factory: Upsert with the Copy Data Activity

Azure Data Factory (ADF) continues to evolve with powerful features that simplify data integration and transformation. One of the latest additions is the ability to perform an upsert operation using the Copy Data Activity. In this blog, Austin Libal from Pragmatic Works explains how this feature works and why it’s a game-changer for data engineers and analysts.

What is an Upsert?

An upsert combines two operations: update and insert. This means you can update existing records and insert new ones in a single process. For example:

• If a record with a specific primary key exists, ADF updates it.
• If the record does not exist, ADF inserts it into the table.

Why Use Upsert in Azure Data Factory?

This feature is particularly useful for ETL workflows where data changes frequently. Instead of running separate insert and update processes, you can streamline operations with upsert, saving time and reducing complexity.

Step-by-Step Guide to Upsert with Copy Data Activity

1. Prepare Your Table: Austin demonstrates using an Azure SQL Database table named dbo.upsert containing product IDs and list prices from the AdventureWorks sample database.
2. Configure the Copy Data Activity:
   • Set your source dataset (e.g., a file in Azure Data Lake with updated product data).
   • Define the sink dataset pointing to your Azure SQL Database table.
   • Choose Upsert as the write behavior.
3. Specify the Key Column: Select the primary key (e.g., ProductID) to determine whether a row should be updated or inserted.
4. Run the Pipeline: Execute the activity in debug mode to validate the process.

What Happens During Execution?

When the pipeline runs:

• ADF checks if the source row matches an existing row in the sink based on the key column.
• If a match exists, it updates the record (e.g., adjusting list prices).
• If no match exists, it inserts a new record (e.g., adding new product IDs).

Results from Austin’s Demo

After running the upsert, Austin verified that:

• New product IDs were added to the table.
• Existing records had updated list prices, changing from decimals to whole numbers.

Benefits of Using Upsert in ADF

• Reduces complexity by combining insert and update operations.
• Improves efficiency for ETL processes in cloud environments.
• Ideal for dynamic datasets where changes occur frequently.

Learn More with Pragmatic Works

This tutorial is just one example of the practical training offered by Pragmatic Works. If you want to master Azure Data Factory, Synapse Analytics, and other Azure tools, explore our On-Demand Learning Platform. Gain access to comprehensive courses, boot camps, and expert tips to accelerate your data career.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Data Factory and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.