Page Comparison

...

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.

...

.

...

Anchor
loadupsert loadupsert

Upsert

...

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

...