Upserting Data

Upserting refers to UPDATE or INSERT transactions in data stores to streamline data management. This technique automatically updates existing records or inserts new ones into a database, depending on whether a matching record already exists. The data stores could be relational, key-value, or any other store that supports the concept of updating rows or blobs.

Note

This section primarily focuses on the Upsert data concept of the DP-203: Data Engineering on Microsoft Azure exam.

ADF supports upsert operations if the sink is an SQL-based store. The only additional requirement is that the sink activity must be preceded by an Alter Row operation. Figure 5.30 is an example screenshot of an ADF sink with Allow upsert enabled:

Figure 5.30 – Upsert operation in ADF used for updating and inserting data

Once you have saved the preceding setup, ADF will automatically do an upsert if a row already exists in the configured sink.

Now...