Pragmatic Works trainer Austin Libal walks through one of the useful newer features available in Azure Data Factory: the ability to perform an upsert in a Copy Data Activity when loading data into an Azure SQL Database table. This feature helps data professionals handle inserts and updates in a single process, making ETL workflows more efficient and easier to manage.
In this video, Austin explains that an upsert is a combination of two actions: updating existing records and inserting new records. Instead of building separate logic for each action, Azure Data Factory can now compare incoming source data to an existing SQL table and decide whether each row should be updated or added.
Austin begins by breaking down the meaning of upsert in simple terms. When data is copied from a source into a destination table, Azure Data Factory checks whether a matching record already exists based on a chosen key column. If the record exists, the pipeline updates it. If it does not exist, the pipeline inserts it as a new row.
This is especially useful when working with datasets that frequently change over time. Instead of deleting and reloading data or building more complex transformation logic, users can streamline the process through the Copy Data Activity.
To demonstrate the feature, Austin uses an Azure SQL Database table named dbo.upsert. The table contains product records with product IDs and list prices for bicycles pulled from the Adventure Works Microsoft sample database.
In the example, the existing table contains product IDs ranging from 680 to 714. Austin then prepares a source file in a data lake that includes:
This setup makes it easy to test both sides of the upsert process in one pipeline execution.
Austin shows that the Copy Data Activity has already been configured to read from a source file in the data lake and write to the Azure SQL Database destination. In the sink settings, the write behavior is set to Upsert.
One important requirement is selecting the correct key column. In this demo, the key column is ProductID. This tells Azure Data Factory how to determine whether a row from the source matches a row in the destination table.
The key column drives the logic as follows:
Austin also mentions that the destination dataset has been parameterized, which adds flexibility to the pipeline design. While he does not go deep into parameterization in this video, he notes that Pragmatic Works training covers this topic in more detail for those who want to build scalable cloud ETL solutions.
After configuring the Copy Data Activity, Austin runs the pipeline in debug mode. The sample dataset is small, so the pipeline completes quickly. He then returns to the Azure SQL Database table to verify the results.
The outcome confirms that the upsert worked as expected:
He also points out a visible difference in the values. Previously existing records were updated to whole numbers, while newly inserted records still contained decimal values in the list price column. This made it easy to verify that both update and insert operations had happened successfully during the same run.
Austin presents this upsert capability as a valuable enhancement for Azure Data Factory users, especially for those working in ETL, data engineering, and cloud analytics. By combining inserts and updates into one activity, the feature reduces development complexity and improves efficiency when maintaining SQL tables from incoming source data.
This functionality is particularly useful for:
In this walkthrough, Austin Libal gives a practical demonstration of how Azure Data Factory can perform upserts directly through the Copy Data Activity. By setting the sink write behavior to upsert and choosing the proper key column, users can efficiently update existing rows and insert new records in Azure SQL Database during the same process.
This feature adds meaningful flexibility to modern ETL workflows and highlights another way Azure Data Factory supports efficient cloud data integration. Pragmatic Works offers additional training on Azure Data Factory and Azure Synapse Analytics for professionals who want to expand their skills and build stronger data engineering solutions.
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.