Overview

SQL Server Change Tracking (CT) is a lightweight feature that records changes applied to tables in a Microsoft SQL Server database.

It was introduced in SQL Server 2008 and is available in all editions, including Express.

CT captures inserts, updates, and deletes as soon as a transaction is committed. Since CT is synchronous, Etlworks can retrieve change data without reading the transaction log or waiting for background processes. This makes CT simpler than Change Data Capture (CDC) in environments where full log-based capture is not required.

CT is supported only for Microsoft SQL Server sources.

If you are looking for other change replication methods, see Change Replication Techniques.

How CT Works in Etlworks

CT in Etlworks is implemented as a variation of high watermark (HWM) change replication.

Etlworks automatically generates and runs SQL queries that return only the data that changed since the last run. It also supports a full initial load.

Initial Load (Full Load)

On the first run, Etlworks generates a query that selects all rows from the source table:

select db.schema.table.*, 
      'c' debezium_cdc_op, 
      0 debezium_cdc_timestamp,
      0 hwm_change_version
from db.schema.table

During the initial load:

• debezium_cdc_op is always c (insert)

• debezium_cdc_timestamp is set to 0

• hwm_change_version is set to 0

Incremental Loads

For every run after the initial load, Etlworks retrieves only changed rows using SQL Server system functions CHANGETABLE and CHANGE_TRACKING_CURRENT_VERSION.

Example:

SELECT ct.pk, db.schema.table.*,
    case CT.SYS_CHANGE_OPERATION 
      when 'I' then 'c' 
      when 'U' then 'u' 
      when 'D' then 'd'
    end as debezium_cdc_op,
    CT.SYS_CHANGE_VERSION as debezium_cdc_timestamp,
    CT.SYS_CHANGE_VERSION as hwm_change_version
FROM CHANGETABLE (CHANGES db.schema.table, {HWM}) as CT 
LEFT JOIN db.schema.table ON ct.pk = db.schema.table.pk
ORDER BY SYS_CHANGE_VERSION

About the generated metadata columns

• debezium_cdc_op

  Used by Etlworks MERGE logic to determine insert, update, or delete.

  Not written to the destination table.

• debezium_cdc_timestamp

  Represents the CT change version.

  Used by MERGE logic. Not written to the destination table.

• hwm_change_version

  Used as the high watermark for subsequent incremental runs.

  Automatically added to the destination table during table creation.

  If the destination table already exists, you must add this column manually and populate it with:

  • the current highest change version, or

  • zero if you want to force a reload.

Prerequisites 

Step 1. Enable CT at the database level

Step 2. Enable CT for each table

Example:

ALTER TABLE dbo.test_table_with_ct
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

The source table must have a PRIMARY KEY.

CT does not work without a primary key.

Configuring a Flow in Etlworks

Step 1. Create a flow

Use any flow where the source is Database. Examples:

• Database to Database

• Database to Snowflake

• Database to any supported destination

Step 2. Select source and destination

Set FROM and TO tables.

If you need to load multiple tables, you can use wildcards.

Step 3. Configure change replication

Go to MAPPING → Change replication tab and select change replication Type.

Choose one of:

1. Change Tracking with calculated HWM (recommended)

2. Change Tracking

See the section Types of Change Tracking below for details.

Step 4. Configure Parameters

Select Parameters tab.

Configuration depends on the destination:

If the destination is a cloud data warehouse

(Example: Snowflake, BigQuery, Redshift)

• Set Action = CDC MERGE

• Enable Predict Lookup Fields

If the destination is another relational database

• Set Action = Record with MERGE

• Enable Predict Lookup Fields

• If deletes can be ignored and you want better performance, enable:

  Do not execute DELETE when Action is Record with MERGE

Step 5. Enable temporary connections for source

Under Parameters, enable:

Create new database connection for source

This prevents locking issues in long-running CT flows.

Overriding the Change Tracking SQL

Etlworks automatically generates SQL for:

• Initial load

• Incremental loads

You can override both queries by entering custom SQL into Source query.

Separate initial and incremental SQL with a semicolon (;).

Required tokens

Your SQL must include the following tokens:

• {table}

  Name of the source table. Etlworks replaces it at runtime.

• {keys}

  Comma-separated list of primary key columns.

• {HIGH_WATERMARK}

  Last saved change version.

• {join_cond}

  The join condition between CHANGETABLE and the source table.

• {debezium_cdc_op}

  Operation type. Use CT.SYS_CHANGE_OPERATION for incremental runs.

• {debezium_cdc_timestamp}

  CT.SYS_CHANGE_VERSION

Example override

-- initial
select {table}.*,
       'c' as debezium_cdc_op, 
       0 as debezium_cdc_timestamp,
       0 as hwm_change_version
from {table};

-- consecutive
SELECT {keys},
       {table}.*,
       case CT.SYS_CHANGE_OPERATION
         when 'I' then 'c'
         when 'U' then 'u'
         when 'D' then 'd'
       end as debezium_cdc_op,
       CT.SYS_CHANGE_VERSION as debezium_cdc_timestamp,
       CT.SYS_CHANGE_VERSION as hwm_change_version
FROM CHANGETABLE (CHANGES {table}, {HIGH_WATERMARK}) as CT
LEFT JOIN {table}
ON {join_cond}
ORDER BY SYS_CHANGE_VERSION

Types of Change Tracking in Etlworks

There are two CT modes. The key difference is where the high watermark is stored.

1. Change Tracking with calculated HWM (recommended)

Etlworks:

1. Reads max(hwm_change_version) from the destination table

2. Uses that version to query the source table

Advantages:

• No internal state is stored

• No risk of desynchronization

• Works best when the destination is a relational database

Use this mode in most scenarios.

2. Change Tracking

Etlworks:

1. Saves the highest CT version in its internal storage

2. Uses that value for the next incremental load

Use this mode only when:

• The destination table is not relational

• Running select max(hwm_change_version) on the destination table is slow

Summary

SQL Server Change Tracking offers a simple and efficient way to capture inserts, updates, and deletes.

Etlworks uses CT together with high watermark logic to provide reliable, incremental replication for SQL Server sources.

For best performance and reliability, use Change Tracking with calculated HWM unless your destination cannot support it.