Skip to content
  • There are no suggestions because the search field is empty.

Configure an incremental load

If you run a full load every time, the processing time will increase continuously, as each day’s load will include more data than the previous one.

A best practice is to use incremental loads, which only process the delta—the changes since the last load.

The benefits of incremental loads include:

  • Faster performance, as they process less data.

  • More consistent execution time over the long term.

  • Preservation of historical data, since many source systems periodically purge old records.

 

We can configure the Incremental Load behavior for the following Model Objects:

  • Stage
  • PIT
  • Business PIT
  • Mart Fact

 

To configure incremental loading, you must map the BG_IncrementalFilter Default Term to:

  • a Source Term that contains the value used for the increment, for example:
  • a function composed of several Source Terms, for example:

The relevant Model Object should not have the Implementation Type property set to Virtual.

See Implementation Type (Model Object) for more details.

 


The BG_IncrementalFilter is created by default with an unsupported Data Type.

To configure incremental load behavior, please change it to the correct Data Type according to the mapped Source(s) Term(s).

All Source terms can be used with data types such as datetime, int...

Example:


Depending on the Model Object Type, the incremental load implementation is different:

  • Stage: As a Stage loads data from the Source system, we prioritize to access this data only once to avoid overloading the Source System 
  • Other Model Object Types: For example, a Mart Fact can contain a large volume of data, so we prioritize minimizing the number of columns.

Stage Model Object Type

If an incremental load behavior was set for a Stage, it contains the increment value used.

So the max value of the Source(s) Term(s) mapped to the Default Term.

Example of values:

This increment is stored after each load in a new table, XXX_INC. For example, the table below shows where two loads occurred:

During deployment, this table is empty. It is populated during the load process.

Reset or Override of the Increment

Once the incremental load is configured, all increments are derived from the logs of previous loads.

However, there are specific scenarios where you want to override this behavior manually:

  • to re-populate an incrementally loaded table (e.g., after a truncate or a re-deployment)
  • to start the incremental load at a specific point (e.g., when enabling incremental load on an already populated table)
  • to ignore all data before a particular value during the initial load (e.g., to apply a specific value of cut-off)
  • to correct issues with a previous load where data was manually modified or removed  in an incrementally loaded table

To configure a manual override for a specific loader, update the column BG_IncrementalFilter in the table XXX_Increment.

You have two options, depending on your objective:

  • To re-populate an incrementally loaded table, truncate the XXX_Increment table.

  • To start from a specific increment, insert a new row with the desired increment value.

Other Model Object Types

If an incremental load behavior was set for a PIT, a Business PIT, or a Mart Fact, it does not contain the increment value used.

This increment is stored after each load in a new table, XXX_INC. For example, the table below shows where two loads occurred:

During the deployment, this table already contains one row per Dataflow and Dataflow Set for the Model Object:

It is populated during the load.

The view XXX_IncrementSource is used to determine which rows should be loaded based on the increment.

Reset or Override of the Increment

Once the incremental load is configured, all increments are derived from the logs of previous loads.

However, there are specific scenarios where you want to override this behavior manually:

  • to re-populate an incrementally loaded table (e.g., after a truncate or a re-deployment)
  • to start the incremental load at a specific point (e.g., when enabling incremental load on an already populated table)
  • to ignore all data before a particular value during the initial load (e.g., to apply a specific value of cut-off)
  • to correct issues with a previous load where data was manually modified or removed  in an incrementally loaded table

To configure a manual override for a specific loader, update the column BG_IncrementalFilter in the table XXX_Increment.

You have two options, depending on your objective:

  • To re-populate an incrementally loaded table, delete the affected rows.

  • To start the incremental load at a specific increment, insert a new row with the desired increment value.

Do not delete the rows created during deployment that contain a NULL value for the increment. These rows are required by the generated code to manage the initial load.