Skip to main content
Tutorial

Incremental Loading on tables in a Business Unit (BU)

  • December 17, 2024
  • 0 replies
  • 14 views

Thomas Lind
Community Manager
Forum|alt.badge.img+5

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.

Prerequisites

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:
    1. Initial Full Load: The system fully loads the table.
    2. 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:
    1. TimeXtender compares the transferred records to the records in the valid table using the primary key.
    2. Update: Existing records in the valid table will be updated if the incremental value is higher.
    3. Insert: New records are inserted.

Handling Deletes (Soft / Hard)

  • If delete functionality is enabled:
    1. _PK Table Creation: A table is created to maintain a live record of primary key combinations present in the source.
    2. 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.

Set up Incremental load in a Data Warehouse

Incremental load method for Data Warehouses

Did this topic help you find an answer to your question?

0 replies

Be the first to reply!

Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings