Incremental Loads Using Row Version - CaO_Data

A SQL Server rowversion (also called timestamp) column is a good incremental key: it is an 8-byte binary value that the database increments automatically every time the row is inserted or updated. Kodda stores the last value read in its metadata repository as text, so the column must be converted to a string for the incremental value, and converted back to binary at run time so the source query can use the index on the column. The mapping Std.Snowflake.MSSQL.cust.cand_master below reads the cust.candidate table, which has a rowversion column named RowVer.

1. Store the row version as a string

In Mapping Details, set the Load Type to an incremental type (here Incremental Insert/Update) and, under Incremental Load Properties, set Incremental Column to <CustomExpression> with the expression:

convert(varchar(20), convert(varbinary(8), [RowVer]), 1)

This converts the binary row version to its hexadecimal string form (for example 0x00000000000017D4), which is what Kodda keeps as the Next Incremental Value after each run.

2. Compare as binary at run time

Comparing the string form against the column would prevent SQL Server from using the index on RowVer. Use the $incremental_ss_binary(<column>)$ function in the Source SQL instead:

select [cand_id], [cand_name], ..., [year], [RowVer]
from [cust].[candidate]
where $incremental_ss_binary([RowVer])$

When the mapping runs, Kodda replaces the function with a binary comparison using the stored value, for example:

( [RowVer] > convert(varbinary(max), '0x00000000000017D4', 1)
  and [RowVer] <= convert(varbinary(max), '0x0000000000001A2F', 1) )

The lower bound is the Next Incremental Value from the previous run, compared with the Incremental Operator (> or >=).
The upper bound is the maximum row version in the source at the start of the run, so rows changed while the load is in progress are picked up by the next run.
On the first run, when there is no stored value, the condition evaluates to 1 = 1 and the whole table is read.

$incremental_ss_binary()$ is SQL Server specific. For other databases, or for numeric and date incremental columns, use the generic $incremental(<column>)$ function, which compares the values directly.

​1. Store the row version as a string

​2. Compare as binary at run time

1. Store the row version as a string

2. Compare as binary at run time