Data Synchronization Part 3: Large Table Sync

Data synchronization for large tables should process only the rows that changed since the last successful run. Scanning the whole source table every time works for small tables. On a large table, it turns the database into a very expensive fan heater.

This third synchronization method uses a high-water mark: store the highest primary key before the load, read only rows above the previous value, insert them into the target, then save the new value. It is simple, fast, and much kinder to the database than asking the same question 10 million times because we enjoy suffering.

Use this method when new rows are appended

This approach is designed for append-style synchronization. New records are inserted into the source table, and each record receives a larger primary key than the previous one. That makes the primary key a practical marker for progress.

If records are updated or deleted after insertion, this method is not enough on its own. You will need an update timestamp, a change flag, change data capture, triggers, or another audit mechanism. Do not pretend deletes did not happen. They have a way of appearing in month-end reports wearing a false moustache.

Store the synchronization state in a small control table

The control table stores the previous and current primary-key boundary for each synchronized table. In this example, PK_BEFORE is the last processed key, and PK_AFTER is the new highest key captured before the load starts.

CREATE TABLE dbo.PK_STATISTICS
(
TABLE_NAME varchar(50) NULL,
  PK_BEFORE int NOT NULL,
  PK_AFTER int NOT NULL
);

Keep this table small and boring. Boring control tables are excellent. Exciting control tables usually mean someone has been debugging at 2:13 a.m. with cold tea and regret.

Capture the start value before reading the source rows

The first step reads the current synchronization state. Advanced ETL Processor Enterprise stores the returned values in package variables so later transformations can use them.

The old article used placeholder values such as 1234567890. The name is not special. It is just a variable marker that gets replaced before execution. Use a meaningful name if you like future-you. Future-you is already dealing with enough.

Read only the rows inside the new primary-key range

The data reader should not scan the entire source table. It should read only rows where the primary key is greater than the previous marker and less than or equal to the current marker.

SELECT PRIMARY_KEY,
  DATA,
  UPDATE_FLAG
FROM dbo.TBL_SOURCE
WHERE PRIMARY_KEY > {PK_BEFORE}
AND PRIMARY_KEY <= {PK_AFTER}
ORDER BY PRIMARY_KEY;

This is the important part. You are reducing the work before the data reaches the transformation. Filtering late is like closing the stable door after the horse has opened a consultancy.

Pass the rows through the transformation and insert them

The transformation in this example does not need to be complicated. It passes the source values to the writer. In a real project, this is where you would clean data, map fields, validate required values, or convert formats.

The writer inserts the new records into the target table. If the target already contains a row with the same primary key, stop and investigate. Duplicate keys in synchronization are like duplicate relatives at Christmas: one of them is probably not meant to be there.

Update the control table after the writer finishes

Before the writer starts, capture the maximum source primary key and store it as PK_AFTER. After the writer finishes successfully, move PK_AFTER into PK_BEFORE. That records the new checkpoint.

The checkpoint should move only after the insert finishes successfully. If you update it too early and the load fails, rows can be skipped. That is not synchronization. That is hide-and-seek with invoices.

Set the source flag only for the processed range

If the source table uses an UPDATE_FLAG, update only the rows in the processed range. Avoid broad updates such as every row with UPDATE_FLAG = 'N' unless you are completely sure no other process is inserting rows at the same time.

Microsoft documents transaction handling in Transact-SQL transactions. For high-volume jobs, also review your isolation level so the reader gets a consistent range without blocking the business all morning.

Large-table synchronization checklist

• Use this method only when new rows are appended.
• Store the previous and current primary-key values in a control table.
• Capture the new high-water mark before reading source rows.
• Filter the reader by the captured primary-key range.
• Insert the rows into the target table.
• Update the checkpoint only after the writer succeeds.
• Mark source rows as processed only inside the loaded range.
• Log the row count for every run.

How this compares with parts 1 and 2

Part 1 shows the basic idea: copy new records and mark them as processed. Part 2 improves the process by avoiding per-row existence checks. This part goes further by reducing the source rows before they enter the package.

If your table has a few thousand rows, the earlier methods may be perfectly fine. If your table has millions of rows, use a high-water mark. If your source data changes in complicated ways, use a proper audit column or database feature. SQL Server change tracking is documented by Microsoft in change tracking.

Data warehouse performance explains why reducing the number of rows read matters. The short version is simple: the fastest row is the one the database never has to read.

FAQ

What is high-water mark synchronization?

High-water mark synchronization stores the last processed key or timestamp and reads only newer rows on the next run. It is a common way to reduce load on large source tables.

Can this method handle updated records?

Not by primary key alone. If existing records can change, add an update timestamp, a change flag, change tracking, or another audit method.

What happens if the package fails halfway through?

The checkpoint should not be advanced until the writer finishes successfully. If the process fails, the next run should use the previous checkpoint and retry the same range.

Do I need Advanced ETL Processor for this?

No, not for a one-off small table. You can write SQL manually. Use Advanced ETL Processor Enterprise when the synchronization must run repeatedly, use variables, handle transformations, and be scheduled without scripting.

The practical answer: for large append-only tables, store the last processed key, read only the next range, update the checkpoint after success, and keep the process boring. Boring synchronization is good synchronization. It rarely calls you during dinner.