ETL+ Load Upsert

Upsert is a efficient delta loading option that dramatically speeds up loading data. Upsert is a combination of the words “update” and “insert.” Load Upsert updates existing records in the target (data warehouse) table if they match a certain condition and inserts new records if they do not exist.

The filter condition that selects the records for Load Upsert is the value of the Column with Last Modified Value compared to the highest value of that column in the target table.

Upsert is a portmanteau – a combination of the words “update” and “insert.”
Upsert is a efficient delta loading option that dramatically speeds up the subsequent loads after a initial initial load with Load All.

**Load Upsert** option in **Load** panel.
The **Key+** checkboxes on the **Design** page designates the unique key.

Select Load on the Load panel to open the Design page.

**Design** page. Here `Source_key` is designated as the **Key+** key.

Load Upsert Process – Step-by-Step

First time? If the target table is empty/has zero records
THEN run Load All Log netceed_us_Debug.zip and end the process here.
Evaluate (Find Highwater mark) <target table>.MAX(<Column with Last Modified Value>. Scan the target table in the data warehouse for the highest/MAX value of Column with Last Modified Value in the target table.
Call this value the Last Highwater mark and save this value for the following steps.
Extract from source. Extract all rows/records from the source table where
source table.Column with Last Modified Value >= Last Highwater mark.
Save the extracted rows/records into a temporary staging table.
Delete Selected Rows in Target. Delete all rows in the target table where staging table.Unique Key = target table.Unique Key.
Load from Staging Table. Load (append) all rows in the staging table into the target table.

NOTES

Target table refers to a table in the data warehouse. The available target tables are listed in the ETL+ Target Objects Panel.
Source table refers to a table listed in the ETL+ Source Objects Panel.

In this section

ETL+ Load Upsert Example 1 — Example of Upsert in action. A simple example that shows how Upsert works with a few rows and columns of data.

ETL+ Load Upsert Example 2 — Simple Upsert example. No deleted source records, no changes to key.

ETL+ Upsert Concepts & Logic — Upsert refers to the process of inserting new data or updating existing data in a target (data warehouse) table based on matching criteria. The term "Upsert" is a combination of "Update" and "Insert". The limitation of Upsert is that it does not Delete old records.

Related Pages

ETL+ Design Page

Search: Upsert,