Incremental loading is a method that enables efficient data updates by only loading new or changed records. This process requires a reliable source table with specific fields for identifying changes.
To enable incremental loading, ensure the following:
Reliable Natural or Surrogate Key: A unique identifier for each record. In essence one or more fields set as Primary Keys or a created Supernatural key set as a Primary Key.
Reliable Incremental Field: A field that tracks changes. This is often a datetime field called something like "ModifiedDateTime" or a "TimeStamp" number that should have a larger value when a record is updated. In some cases you will not get a high level of granularity as the field only contains a date or do not update the record with a higher value when an change is made. Then a subtraction can work to reload these records based on this rule.
How Incremental Loading Works
Enabling Incremental Loading
When the option is enabled and the table is deployed, TimeXtender performs the following:
Initial Full Load: The system fully loads the table.
Setting content of the _I Table: This table records the largest incremental field value from the given load.
Subsequent Loads
Future loads will only transfer records with an incremental value larger than the one stored in the _I table. These records are considered new or updated.
Data Cleansing Process
During data cleansing:
TimeXtender compares the transferred records to the records in the valid table using the primary key.
Update: Existing records in the valid table will be updated if the incremental value is higher.
Insert: New records are inserted.
Handling Deletes (Soft / Hard)
If delete functionality is enabled:
_PK Table Creation: A table is created to maintain a live record of primary key combinations present in the source.
After the _R table is populated, if a record is found in the valid table but not in the _PK or _R tables, it will be flagged as tombstone field equal true when set to Soft Deletes or removed if set to Hard Deletes.
Adapters and _INCR Table
Some adapter data sources may create an _INCR Table instead of an _I table. This table functions similarly to the _I table but tracks incremental values for each source, company, or account individually.
Important Considerations
Table Refresh
The valid table will not be truncated unless:
There is a change in the incremental selection rule. Meaning a different field is chosen as what the rule will be based on.
The primary key changes. Meaning that you change the current primary key settings in the table.
A full reload can be triggered by an execution package if needed.
Automation Setup
Use the Set Up Incremental Load Wizard for automated setup.
Run Add Suggested Constraints before using the wizard to automatically set primary keys in the source.
Unchanged Records after a incremental execution
Records will not be refreshed during an incremental load if they do not have an updated incremental value. This means that transformations, lookups and fields with transformations will only be updated for new or modified records.
You can use the setting Keep Field Values Up-to-Date to address this limitation. Then your lookups will be updated with a value if there is changes to this.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.