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...