ETL+ Table Load Types

ETL+ load types are Load All, Load Replace, Load Upsert, and Load Append.

Configure the load type for each table on the Load type panel of the ETL+ Extract, Transform and Load (ETL) Page .

The load types available are Load All, Load Replace and Load Upsert

Load Types

See also ETL+ Load Replace and ETL+ Load Upsert sections.

Load All

Based on the table’s ETL SQL Statement, reads all data in the source table or file and loads it to the data warehouse table (the destination). In effect, this is a source table/file data replication/mirroring.

Load All is the default setting and is useful when the data volume is small, or there are no ways to perform delta loads.

Load All Process

• Loads the data to a temporary data warehouse table named TableName_Temp. If the temp table already existed before the process started, Load All will delete the temp table first to be sure it is a clean new load.

• When the temporary table load completes:

  • If there are no table transformations, ETL+ renames TableName_Temp → TableName.

  • If there are table transformations, ETL+ deletes the data warehouse TableName, does a bulk insert from TableName_Temp into TableName applying transformations, and deletes the temp table.

Temp Tables (<tablename>_Temp)

A temp table (or staging table) is created every time there’s a table load. This temporary table is named <tablename>_Temp where <tablename> is the name of the target table.

The temp table allows its target table to be available for reporting during most of the data load processes (the target table remains unaffected until the very last step of the data load process). Once the temp table has completed a new load process, it takes little time for the target table to incorporate the new data. And then the temp table is deleted.

You might see temp tables in the data warehouse if their tables had errors in the last load attempt or the last load was canceled. Some sources (like OData) might have two temp tables, for instance: ARTran_Temp and ARTran_Temp2.

For more on using the temp table/staging table see ETL+ Upsert Concepts & Logic.

Replace

Replace is a delta loading option that dramatically speeds up the load after the initial one.

Replace deletes all target table’s rows that meet the filter criteria and re-loads rows that meet that criteria from the source table. For more see the ETL+ Load Replace section.

When to Use

• When there’s a single column identifying the order of the table’s row creation such as a Document Date, or a sequentially increasing Document Number.

• To re-load all records for a recent period that might have records modified and/or deleted. Examples of expressions for reloading recent periods:

  • Last 7 days: DATEADD(DAY, - 7, GETDATE())

  • One calendar month ago: DATEADD(MONTH, - 1, GETDATE())

  • Last day of of the prior calendar month: EOMONTH(GETDATE(), - 1)

  • This calendar month: DATEADD(DAY, 1, EOMONTH(GETDATE(), - 1))

  • Last two calendar months: DATEADD(DAY, 1, EOMONTH(GETDATE(), - 2))

  • This calendar year: DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)

  • Last two calendar years: DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) - 1, 0)

• Do NOT use columns that can have their content updated such as LastModifiedDateTime columns. For tables with such columns, use Upsert below instead.

Parameters

• Select a column in the Field dropdown to function as the Replace filter. This column should identify the order of the table’s row creation.

• Adjust the Filter Value if needed. By default, the filter value will be Max(ColunmName). You may also customize the Filter Value with values such as:

  • Constants. Ex.: ‘1/1/2022’

  • MS SQL expressions. Ex.: getdate()-30 to Replace all records for the past 30 days.

Requirements

- Availability of a column to control the delta load subset. Example: a date or a sequentially growing value.
- The source table allows filtered data extraction.
- After the initial load, delta loads need columns and formats compatible with the data warehouse table. Addition of new columns and/or increase in column formats require a Load All load to sync up.
- Rows prior to the max control column value don’t change in the data warehouse. If that can happen, use Forced Load All (below) to capture modified or deleted records on a less frequent schedule.

Replace Process

• If applicable, reads the Replace filter from the data warehouse table. In the screenshot above, this would be the maximum value of Invoice Date.

• Delete all data warehouse rows where Filter Column >= Filter Value.

• Load (append) all source table rows where Filter Column >= Filter Value onto the data warehouse table.

  • The load is done on a temporary table similar to Load All. When the load is completed, it’s appended to the target table.

Upsert

Upsert is a delta loading option that dramatically speeds up the subsequent loads after the initial load. Upsert is a portmanteau – a combination of the words “update” and “insert.”
For more see ETL+ Load Upsert

Initial Load When the Target Table is Empty (has zero rows)

When a target table in the data warehouse is empty (has zero rows) Upsert calls a Load All to load all rows from the source table.

Load Upsert

The first step in the upsert process is to read the target table to find the Column with Last Modified Value with the highest value. That is, the first step is to query the target table for MAX(Column with Last Modified Value).

This high value is analogous to a high watermark. Only those source rows with a watermark >= the high water mark are inserted (added) or updated (changed). The watermark column in source table is the column(s) named in the Column with Last Modified Value.

Update or Insert?

Update: Upsert will update existing rows when the row’s Column with Last Modified Value has changed since the last Upsert. For instance, when a source table’s row’s Column with Last Modified Value was changed since the last ETL load.

Upsert: Load Upsert inserts new rows from the source table when the source’s column nQamed in the Column with Last Modified Value is greater than or equal to the maximum value of the Column with Last Modified Value on the target table.

This function requires the table’s Primary Key and a Column with Last Modified Value.

Watermarks and Hight Watermark

The high watermark is the maximum (e.g.; highest value) of the column named in the Column with Last Modified Value

The column named in the Column with Last Modified Value

Upsert does not delete rows with Column with Last Modified Value < the last value imported into the data warehouse. For instance, on a daily upsert refresh, a deleted record from a week ago will not be deleted from the data warehouse.

Example. All data is loaded in the initial SalesOrderHeader table extraction, and after it only updates rows that have changed since the last load and inserts new ones.

When to Use. When there are existing records that can be updated over time and there is a primary key and a last-modified column flag. Many modern source systems provide these features and this is the most efficient delta refresh process.

Parameters

• Column with Last Modified Value. Usually names a date-time column/field, but might also use other data types.

• Primary Key

Requirements

• There’s a column flagging modified rows (ex.: Last Modified Date and Time).

• The table has a primary key.

• The source table allows filtered and indexed data extraction.

• After the initial load, delta loads need columns and formats compatible with the data warehouse table.

• Addition of new columns and/or increase in column formats require one Load All load to sync up.

• Already loaded records are not deleted. Use Forced Load All (below) to capture deleted records on a less frequent schedule.

Upsert Process

1. Read the max value of the Last Modified Value from the data warehouse table.

2. Load all source table rows where Last Modified Value column >= max value into a temporary table.

3. Delete all rows from the data warehouse table where temporary table primary key = data warehouse table primary key.

4. Load (append) the temporary table into the data warehouse table.

   1. The load is done on a temporary table similar to Load All. When the load is completed, it’s appended to the target table.

Upsert Related Pages

• ETL+ Upsert Concepts & Logic

• Upsert Not Working as Expected

Append

Use this setting when you want to append the source to the data warehouse table.

If you want to add a DateTimeStamp column, either use a field from the source, or apply a transformation in the SQL Statement (if supported), or use the Design page to set up that transformation (getdate()).

Append Process

• Append source table rows into the data warehouse table.

  • The load is done on a temporary table similar to Load All. When the load is completed, it’s appended to the target table.

  • To add a DateTime stamp on each append, and the source table not having such column, create a dummy column and use the ETL+ Design page to assign it GETDATE() value.

Job → Forced Load All

Running a Forced Load All on a Refresh Batch guarantees that its tables will have their data completely re-loaded, ignoring preconfigured delta refreshes.

Usage

One can use this feature manually to guarantee the reload of all data at will, or set up scheduled refreshes in conjunction with delta refreshes. For instance, run Replace and/or Upsert on a frequent schedule (ex.: hourly), and Forced Load All in a less frequent schedule (ex.: nightly).

For instance, run Replace and/or Upsert loading every hour, and Forced Replace All once every night. With this setting, already extracted rows that are modified (Replace misses modified records) or deleted on a day (Replace and Upsert miss deleted records) will be captured overnight.

How to configure this:

1. Set Replace and/or Upsert for the desired table(s) - see ETL+ Extract, Transform and Load (ETL) Page

2. Make a job on the ETL+ Job Page to run the default load (delta) on a frequent schedule.

3. Create a new job on the ETL+ Job Page to run with Forced Load All:

   1. select the table(s) that use Load type options Replace and/or Upsert on the Job Steps panel of the ETL+ Job Page.

   2. Set this job Forced Load All

   3. schedule this job to run on a less frequent schedule.

Related Pages

• T-SQL Date Expressions

• Sequentially Incremented Values Can often be used in place of a datetime field.

• T-SQL String Constants (String Literals) The Replace option can specify literals.

• ETL+ Load Replace

• ETL+ Load Upsert sections.

v2023.07 ✅