Delta Load Combinations -- Upsert, Replace, Append, Load All
ETL+ delta load options are Load Upsert, Load Replace, and Load Append. Each load option has limitations but can be combined for better coverage. π§ Work in progress
In the context of ETL, a delta load (also known as a incremental load) refers to loading/refreshing only the changes or updates that have occurred since the last load.
Delta loading options are designed for situations when a Load All takes too much time or makes heavy demands on the system.
The Load panel on the ETL+ Extract, Transform and Load (ETL) Page offers four load options:
Load All, Load Replace, Load Upsert, and Load Append.
![image-20240603-202209.png](../__attachments/2324824067/image-20240603-202209.png?inst-v=376358cb-4d6d-4354-8803-0b566f5f404f)
Load options on the ETL+ Extract, Transform and Load (ETL) Page
Delta Load Combination Scenarios
Load Upsert run daily with a Load All run on weekends.
![](../__attachments/2324824067/image-20220228-232848.png?inst-v=376358cb-4d6d-4354-8803-0b566f5f404f)
Upsert Example: Column with Last Modified Value = LastModifiedDateTime
Load Replace run daily with a Load All run on weekends.
![image-20231125-004433.png](../__attachments/2324824067/image-20231125-004433.png?inst-v=376358cb-4d6d-4354-8803-0b566f5f404f)
Example 1: Load Replace with Target Value or Constant field configured to process records from the last 60 days.
Load Types Contrasted
Advantages & Uses | Limitations | ||
---|---|---|---|
Fast, can be run many times a day. Updates existing records or inserts new records. | Does not delete records from target/data warehouse table | ||
Retains a history of changes. Good for keeping a history of records from the source table. | Can save several versions of source records on the target data warehouse table. Does not use a unique key to identify the target records to replace. (See example 1 below). | ||
Good for keeping a history of records from the source table. | May create duplicates on the target data warehouse table depending on the filter values. | ||
Makes a %100 copy of the source table. | Doesnβt keep any history/older records on the target table. |
How It Works | |
---|---|
Updates existing records on the target table and inserts new records. ![]() Example 2: Column with Last Modified Value = | |
Replaces certain records β typically recently updated records. ![]() Example 3: Load panel configured for Load Replace | |
Appends (adds) all the records on the source table to the existing records on the target table. | |
Deletes the target table and loads all records from the source table to the target. | |
Tables configured with Load Upsert are temporarily converted to Load Replace by the Force Replace when Upsert, default for others option from the Load type list, Job Properties panel, Job page. | |
Force Load All | All Tables are temporarily converted to Load All by the Force Load All option from the Load type list, Job Properties panel, Job page. |
Target table β a table on the data warehouse. The table loaded by the ETL.
Load Type Options
![image-20240603-192404.png](../__attachments/2324824067/image-20240603-192404.png?inst-v=376358cb-4d6d-4354-8803-0b566f5f404f)
Load type option list found on the Job Properties panel of the Job page