Skip to main content
Skip table of contents

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.

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


In this section

Related Pages

Search: Upsert,

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.