ETL+ Load Upsert

Owned by Cortlandt _
Last updated: yesterday at 6:27 am

Efficient updating of large tables with the upsert delta load type.

update existing records if they match a certain condition (typically based on a primary key) and insert new records if they do not exist.

Requires the source table has a unique key.

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

  • Select Load image-20240430-184241.png to open the Design page.

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

Load Upsert Process – Step-by-Step

  1. First time? If the target table is empty/has zero records
    THEN run Load All and end the process here.

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

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

  4. Delete Selected Rows in Target. Delete all rows in the target table where staging table.Unique Key = target table.Unique Key.

  5. Load from Staging Table. Load (append) all rows in the staging table into the target table.

NOTES

 

In this section

 

 

Search: Upsert,