If you run a full load at each load, the time required to process is monotonically increasing because today’s load will always have more data than yesterday’s.
A best practice is to use incremental loads, which only load the delta.
The benefits are:
- Run faster since they touch less data
- Usually steady over time
- Can preserve historical data. Many source systems purge old data periodically
We can configure the Incremental Load behavior for the following Model Objects:
- Stage
- PIT
- Business PIT
- Mart Fact
To configure it, you must map the BG_IncrementalFilter Default Term to:
- A Source Term that contains the increment to consider, for example:
- A function composed of several Source Terms, for example:
The concerned Model Object should not set the Implementation Type Property to Virtual.
See Implementation Type (Model Object) for more details.
The BG_IncrementalFilter is a by default with an unsupported Data Type.
To set an incremental load behavior, please change it to the correct Data Type according to the Source(s) Term(s) mapped.
All Source terms can be used with data Types such as datetime, int...
Example:
Value
If an incremental load behavior was set, 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 at each load in a new table XXX_INC. For example, here is where two loads happened:
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 incremental load at a specific increment (e.g., when you enabled incremental load on an already populated table)
- to ignore everything before a particular value during the initial load (e.g., to apply a specific value of cut-off)
- to correct problems with a previous load where you manually modified or removed data in an incrementally loaded table
To configure this manual override for a specific loader, you can update the column BG_IncrementalFilter in the table XXX_Increment.
So, truncate the table to re-populate an incrementally loaded table or add a new line with a different increment to start the incremental load at a specific increment.