Skip to main content
To homepage To homepage
SUBMITTED

Hash function to identify changes on the source system

Related products:TimeXtender Data Integration
  • July 31, 2024
  • 1 reply
  • 28 views
A
Starter

We have a client case where a huge table - that needs to be loaded incrementally- does not have any changed field. Because of legacy issues, it’s not possible to d introduce a rowversion or Change tracking on SQL Server. The ODX is on a datalake, while the MDW on Azure SQL Server.

The proposed solution tries to identify which rows have been updated in the table, here's a streamlined approach:

  1. Calculate Total Rows: First, get the total number of rows in the table
  2. Initial Range: Start with the full range of IDs, from the minimum to the maximum ID in the target table.
  3. Recursive Division: Divide the range into two halves and calculate a checksum for each half: checksum_agg(checksum(ID * EntryType)). The checksum combines IDs and EntryType values to create a unique fingerprint for that segment.
  4. Compare Checksums:
    1. If the checksums match, no updates in that segment.
    2. If they differ, further divide the segment with the differing checksum into smaller halves and repeat the process.
  5. Threshold for Division: Continue dividing until the segment size is smaller than the square root of the total row count. This helps us quickly narrow down potential updates.
  6. Identify Updates: For small segments (below the threshold), directly compare the rows in the source and target tables to find where row has changed.

We can efficiently find updates without having to compare every single row, by focusing only on segments where changes are likely.

It would be great if such functionality can be implemented in TimeXtender :-)

    1 reply

    rory.smith
    TimeXtender Xpert
    Forum|alt.badge.img+8
    • rory.smith
    • TimeXtender Xpert
    • July 31, 2024

    Hi,

    I am assuming a few things:

    • no changes can be made to the source database at all (i.e. no triggers can be introduced to INSERT to a changed records table on each change).
    • Your solution runs on the source database, so you can apparently add stored procedures but not triggers. Otherwise you are basically doing something similar to the target-based incremental load that older versions of TimeXtender could do and that only saves the transfer from _R table to valid table and some cleansing time.
    • your dataset isn't growing so large that the O(n log_p n) cost of divide-and-conquer won't bite you eventually. You will be going 15 levels deep into recursion for 1B records for instance
    • there are no other fields that can be used to narrow down the extract to reasonable sizes
    • the load of the change-checking is not unduly blocking the source system
    • you are not loading this frequently enough for the computation to be a bottleneck but also not infrequently enough that a full load is still a problem
    • your chosen hash function is large enough that hash collisions aren't a problem
    • setting up replication or log shipping to a database close to your TimeXtender DWH instance is not feasible or is slower
    • the number of customer cases where this is important enough is not going to be > 5

    I don't see how the effort to implement this extreme corner-case is of any use in a broad sense to other customers. There are a huge number of details that would matter and would therefore make it very difficult to generalise this across sources and infrastructure variants. I run across large-ish tables (< 1 TB usually) that cannot be incrementally loaded, but it is mostly possible to exclude chunks of the table to reduce the dataset needing transfer (year or some other discriminating field). 

     

    Terms and ConditionsAccessibility statement