Integrating legacy historical data in TX history table

Hi team, 

In many projects, I need to integrate legacy historical data with current updates — essentially allowing TimeXtender to continue tracking Type 2 Slowly Changing Dimension (SCD) changes as if it had always managed the full history. Currently, this isn’t natively supported. To make it work, I have to either misuse incremental/history features, write custom scripts, or use a mix of both. 

In SQL, this problem isn’t that complex. But in TimeXtender, the logic becomes tricky when you're sourcing data from a pre-existing SCD-2 structure.

Logically, you’d want to:

• Load everything initially as Type 1,

• Then filter for IsCurrent = 1,

• And apply your Type 2 fields accordingly.

But this doesn't work cleanly in TimeXtender, since the natural/primary key definitions are different for full-history and current-only records. 

Scenario 1: Import Static History Once + Load Only IsCurrent Going Forward

Initial Run:

• Use Valid_From as part of the primary key.

• Map Valid_From and Valid_To from the source to SCD_From and SCD_To in TimeXtender.

• This seeds the full historical record set.

After Initial Run:

• Switch to using only the BusinessKey as the primary key.

• Add a data selection rule like IsCurrent = 1, or point to a separate current-only source table.

• Now TimeXtender tracks changes using standard Type 1 and Type 2 logic on new data.

This approach is straightforward and works well for historical seeding followed by real-time tracking.

Scenario 2: Sync Ongoing SCD-2 Source (Still Changing Slowly)

Initial Load:

• Use BusinessKey + Valid_From as the primary key.

Subsequent Loads:

• Use only BusinessKey as the primary key.

• Then apply logic based on the Valid_From date (within BusinessKey):

Case A – Valid_From <= last known SCD_From:

• Update Type 1 fields if values have changed.

• Insert a new Type 2 row if a Type 2 field has changed.

Case B – Valid_From > last known SCD_From:

• Option 1: Order rows by Valid_From and map to SCD_From/SCD_To.

• Option 2: Take the most recent record only, and apply Type 1/2 change logic as needed.

This scenario is more complex and requires careful handling of edge cases like overlapping dates or reissued statuses.

Use Cases

• When the source system has incomplete SCD-2 tracking.

• Migrating from a legacy DWH that has historical data TimeXtender needs to continue.

• Blending multiple systems, where each handles history differently.

• Replacing a legacy system with built-up history by a new system that doesn’t track history.