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
• Satellite
• Link Satellite
• Multi-Active Satellite
• Effectivity Satellite
• Record Tracking Satellite
• Effectivity Link Satellite
• Record Tracking Link Satellite
• Non-Historized Link Satellite
• Business PIT
• Business Satellite
• Business Link Satellite
• Effectivity Business Satellite
• Record Tracking Business Link Satellite
• Effectivity Business Link Satellite
• Non-Historized Business Link Satellite
• Mart Fact
• 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 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 is set for a Stage, the BG_IncrementalFilter Default Term contains the increment value used to select rows for the incremental load. 

So the max value of 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, the BG_IncrementalFilter Default Term does not contain the increment value used to select rows for the incremental load.

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.

Increment Management Types

By default, the incremental load is automatically managed by the loaders. They will query the data sources to find and load new data and then update the increment tables to reflect up to which point data was loaded. However, since all fresh data is always loaded in one transaction, this could overwhelm the database system in an environment with very large datasets.

Therefore, you can choose to control the incremental load yourself, by configuring user managed increment management type. In that case the user is responsible to identify and specify the next increment that should be loaded.

To configure user managed incremental load, select the option User for the property Increment Management Type on the model object:

After deployment, and before each load you have to prepare the increments in the corresponding increment table XXX_Increment for this model object.

For each data flow and data flow set create a new row and specify the lower and upper boundary by which the BG_IncrementalFilter column should be filtered using the columns BG_LowerThreshold and BG_UpperThreshold.

To ensure consistency during loads, the specified value for the lower boundary will be excluded, the value for the upper boundary will be included. To avoid gaps or duplicated data in concurrent runs, the lower boundary therefore should match the upper boundary of the previous run. For both boundaries you can use NULL to not filter for a specific value and rather load all available rows.

The column BG_IncrementLoadStatus will be used to indicate the load status: NULL means the load is configured but didn't happen yet; after the load the loader will update this field to done to indicate a successful load.

You can prepare multiple rows (e.g. for setting up an initial load); they will be processed one row for each load, data flow and data flow set ascending by BG_ID.

We recommend that you automate this using your orchestration framework.