ETL+ Table Load Types

Owned by Cortlandt _
Oct 06, 2023
7 min read

One can configure the load types for each table on the ETL+ Extract, Transform and Load (ETL) Page .

Load Types

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 (ex.: TableName_Temp)

A temp table is created every time there’s a table load. This temporary table is named TableName_Temp.

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.

Replace

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

In this example the filter criteria is “InvoiceDate >= Max(InvoiceDate)".
Field (dropdown menu) shows “InvoiceDate" from the source table.
>= (right hand side) shows Max(InvoiceDate) from the target table.

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 example, all data is loaded in the initial InvoiceHeader table extraction, and after it only adds/replaces rows where the control column values >= last extracted InvoiceDate.

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

If a table doesn’t have data in the data warehouse yet, the first Upsert load will load all rows. Upsert can be added to a table at any time, before or after an initial load.

Upsert will update existing rows when the Column with Last Modified Value has changed since the last Upsert. For instance, a row was loaded a month ago, and was changed yesterday.

Upsert will insert new rows from the source. This is defined by Column with Last Modified Value is greater than the last value in the data warehouse plus these rows PKs are not found in the target.

This function requires the table’s Primary Key and a 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

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

  • https://dataself.atlassian.net/wiki/spaces/DS/pages/1955725323 Can often be used in place of a datetime field.

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

 

v2022.08