Incremental Loading and CDC: How a Pipeline Knows What Changed

A nightly job copies orders into the warehouse with a filter everyone has written at least once: take the rows whose updated_at is later than the last run. It works for months. Then finance asks why a refunded order still shows as paid in the report, and why the day’s revenue is off by three orders that exist in the source and not in the warehouse. Nothing failed. No job errored. The example is invented, but the mechanism behind it is not: that filter answers the question “what changed?” in a way that quietly misses some changes.

Every incremental pipeline rests on a change-detection mechanism, and each mechanism has a different idea of what a change is and when it becomes visible. This article is about those mechanisms — how extraction by timestamp, source-provided change tracking, and log-based change data capture actually work, what each one cannot see, how the first full load is joined to the ongoing stream without a gap, and what the choice costs the source system. Product behavior described below was checked against vendor documentation in September 2026; version details are named where they matter.

The question every method has to answer

Copying everything, every time, is the one approach that needs no change detection at all. A full reload is simple and self-correcting, and for a small dimension table it is often the right answer. It stops being the right answer when the table is large, when the source cannot absorb a full scan, or when the destination needs to know that something changed rather than only what the current state is.

Everything else is incremental, and incremental means the pipeline must answer one question at every run: since the position I recorded last time, what has changed, and what is my new position? Three properties of the answer decide what the destination can do with it:

• Completeness. Does the mechanism see every change, including deletes, or only some kinds?
• Granularity. Does it deliver each change as it happened, or only the row’s current state at the moment you asked?
• Position. Is there a position you can record, resume from, and compare — one that the source and the pipeline agree on?

The rest of this article is those three properties applied to each method.

Asking the table: extraction by watermark

The most portable method needs nothing from the database engine. You pick a column that only moves forward — a last-updated timestamp or an increasing key — and you remember how far you got. Microsoft’s Data Factory documentation describes this as a watermark: “a column that has the last updated time stamp or an incrementing key,” where the delta load copies the changed data between an old watermark and a new one. The stored position is an incremental extraction watermark.

-- Bound the run first, so rows written during the extraction
-- do not fall into an interval nobody will read again.
SELECT :new_watermark = MAX(updated_at) FROM orders;

SELECT order_id, status, amount, updated_at
FROM orders
WHERE updated_at > :last_watermark
  AND updated_at <= :new_watermark
ORDER BY updated_at, order_id;

-- Advance the stored watermark only after the destination commits.

Two ordinary mistakes are worth naming before the interesting one. Advancing the watermark before the destination has committed loses every row in the interval if the write fails. Using an unbounded upper end (updated_at > :last_watermark with no ceiling) makes the run’s contents depend on how long the query took, which makes reruns non-comparable.

The gap between when a timestamp is set and when the row becomes visible

The structural problem is that updated_at records when a statement ran, while a reader can only see the row after the transaction commits. Those are different moments, and nothing forces them to be close together.

Time	What happens
01:00:00	A transaction updates order A and sets updated_at = 01:00:00. It stays open.
01:00:30	The extraction runs, sees MAX(updated_at) = 01:00:20 from other rows, copies through 01:00:20, and stores that as the watermark. Order A is invisible: it is uncommitted.
01:00:45	The transaction commits. Order A becomes visible, carrying updated_at = 01:00:00.
Next run	The filter asks for rows after 01:00:20. Order A is older than that. It is never extracted again until something else touches it.

Nothing errors. The row is simply never in any interval. The same gap appears whenever the timestamp comes from a clock the extraction does not share — application servers with drifting clocks, or a batch that stamps rows with the time the batch started.

The mitigations are all approximate. Re-reading an overlapping window on each run — say, from the last watermark minus ten minutes — catches transactions that committed late, provided they committed within that window, and only helps if the destination applies changes idempotently. Taking the upper bound from a commit-ordered source rather than the row’s own column removes the problem where the engine exposes one. Neither turns the method into a complete record: they reduce a known, unbounded hole to a bounded and monitored one.

What a timestamp cannot tell you at all

• Hard deletes. A deleted row has no timestamp to select. A query-based extraction cannot see it, and the destination keeps the row forever unless something else reconciles the two sides.
• Intermediate states. If an order goes pending → paid → refunded between two runs, the extraction sees only refunded. Any report about how long orders stay in paid is unanswerable from this feed.
• Columns nobody maintains. The method depends entirely on every write path updating that column, including bulk fixes run by hand. One UPDATE issued during an incident without touching updated_at produces a row that is permanently stale downstream.

The same logic applies to files. Data Factory’s documentation notes that filtering by LastModifiedDate requires scanning all files in the source store, so the cost tracks the number of files rather than the number of changes, and it describes time-partitioned folder or file names as the most performant approach for incrementally loading new files. Either way, a deleted file is not a change you will see.

Asking the engine: change tracking the source maintains

Some engines will record changes for you without your having to read their logs. SQL Server’s change tracking is the clearest documented example, and its design choices show the tradeoff. According to Microsoft’s documentation, it records the fact that a row changed and the primary key values of that row, in the same transaction as the change itself; the latest data is then read from the tracked table by joining on those keys. What it deliberately does not keep is history: only “the fact that a row has changed is required, not how many times the row has changed or the values of any intermediate changes.” The documentation points to change data capture instead when intermediate values are needed.

Two consequences follow from it being synchronous and key-only. Because the record is written in the transaction, there is no window in which a committed change is invisible to the tracking — the gap described above does not exist. Because only keys are kept, the values you eventually read are whatever the row holds at read time, which may already be two changes further along. Deletes are visible, which is the significant gain over a timestamp filter. And every DML operation counts: the documentation notes that a row is considered changed even if an update sets a column to the value it already had.

Trigger-based capture, written by hand into an audit table, sits in the same family. It gives you whatever you write into the trigger, at the cost of running your code inside every transaction on the source, in the source’s failure domain.

Reading the log: change data capture

Databases already write every committed change to a durable log for recovery — see write-ahead logging. Log-based CDC reads that log instead of querying tables. Because the log is how the database itself reconstructs state, it contains what no query can reproduce after the fact: every change, including deletes. One distinction matters before you rely on ordering. The log records changes in the physical order they were written, which interleaves concurrent transactions; what a CDC consumer receives is whatever its decoding mode produces from that log, and the common modes deliver each transaction as a unit at commit.

Two documented implementations show what this looks like in practice.

• SQL Server CDC has a capture process read the transaction log and write into change tables that mirror the tracked columns. An insert or delete produces one row; an update produces two, the before image and the after image, distinguished by an operation code. Each row carries a commit log sequence number, which the documentation describes as identifying changes committed in the same transaction and ordering those transactions, plus a sequence value ordering changes within one transaction.
• PostgreSQL logical decoding streams changes through a replication slot, which the documentation describes as replaying changes “in the order they were made on the origin server.” Concurrent transactions “are decoded in commit order,” and transactions “that were rolled back explicitly or implicitly never get decoded” — so with these defaults a consumer is not handed work it should not have seen. That property belongs to the decoding mode rather than to the log: PostgreSQL also supports streaming a large transaction while it is still open, in blocks “demarcated by stream_start_cb and stream_stop_cb callbacks,” after which the transaction “can be committed using the stream_commit_cb callback (or possibly aborted using the stream_abort_cb callback).” A consumer in that mode does see uncommitted work and must hold it until the commit callback arrives.

MySQL’s binary log plays the same role for that engine, and connectors such as Debezium read these engine-specific streams and publish the changes as events.

How much of the old row you get is a setting

A delete event that tells you only “the row with key 42 is gone” is enough to delete a row downstream. It is not enough to know what that row contained, which matters if the destination stores history or needs the old values to reverse an aggregate. What the log records about the pre-change row is configurable, and the default is usually the minimum.

In PostgreSQL this is replica identity. The documentation states that “a published table must have a replica identity configured in order to be able to replicate UPDATE and DELETE operations”; by default that is the primary key, and a table with no suitable key can be set to FULL, which makes the entire row the key. Tables with replica identity NOTHING, or DEFAULT without a primary key, cannot support those operations in a publication — attempting them “will result in an error on the publisher,” while inserts proceed regardless. Debezium’s PostgreSQL connector documents the downstream effect: with the default setting, update and delete events carry the previous values of the primary key columns only; with FULL, they carry the previous values of all columns.

MySQL exposes the same decision as binlog_row_image, documented with three values: full logs all columns in both the before and after image; minimal logs only the columns needed to identify the row plus those actually assigned; noblob is full minus unchanged BLOB and TEXT columns. The default is full.

Both settings trade log volume and write cost against downstream capability, and both are the source team’s setting, not the pipeline team’s. Confirm it before promising anything that depends on before-values.

The methods side by side

	Sees deletes	Keeps intermediate states	Needs from the source	Main failure mode
Full reload	Yes, by replacement	No	Capacity for a full scan	Cost and duration grow with the table
Timestamp or key watermark	No	No	A column every write path maintains	Rows committed after the watermark moved past their timestamp are never picked up
Engine change tracking (keys only)	Yes	No	A feature enabled per table; retention	Falling behind the retention window
Engine CDC change tables	Yes	Yes	A capture job, log retention, storage	Requesting an interval outside the validity window
Log-based CDC connector	Yes	Yes	Log access, a slot or position, privileges	Connector downtime past log retention; unbounded log growth
Application events (outbox)	Yes, if published	Yes, as published	Source team owns and evolves the events	The published event drifts from what the database actually recorded

The bottom row is the only one where a human decided what a change means. The others infer it, which is why they are available without the source team’s cooperation and why their edge cases are the source team’s internals.

The first load, and where it joins the stream

A change stream starts empty. The destination needs the rows that already existed, and the log will not supply them: Debezium’s documentation puts it plainly for PostgreSQL, where WAL segments are purged periodically, so the connector “does not have the complete history of all changes that have been made to the database.” Hence a snapshot, followed by the stream.

The hard part is the seam. The snapshot must correspond to one position in the log, and streaming must begin at exactly that position. Read the table and separately note “the latest log position” and you have created either a gap (changes committed between the two moments are in neither) or an uncontrolled overlap (changes that are in both, applied in an order nobody defined). This is the snapshot and stream handoff, and the two documented ways of getting it right differ in what they cost the source.

A consistent snapshot, then the stream

Debezium’s default PostgreSQL snapshot opens a transaction, reads the current log position within it, scans the tables to emit read events, commits, and records the offset. The transaction’s consistent view and the recorded position describe the same instant, so streaming can begin there with nothing missing and nothing duplicated. The costs are that the scan runs as one long operation — holding a transaction open on the source for its duration — and that, per the documentation, an interrupted snapshot restarts from the beginning rather than resuming.

Interleaving the snapshot with the stream

The alternative is an incremental snapshot: select the table in chunks while the stream keeps running, and resolve the overlap deliberately. Netflix’s DBLog paper describes the mechanism it was built on. For each chunk, log processing pauses briefly; a low watermark is written by updating a dedicated watermark table; the chunk is selected into memory; a high watermark is written; log processing resumes. When the low watermark event appears in the log stream, the framework starts removing from the in-memory chunk any rows whose primary keys changed between the watermarks; when the high watermark event appears, whatever remains of the chunk is appended to the output. The log event always wins, so, in the paper’s terms, an older version of a row is never delivered after a newer one.

The paper’s stated advantages are the operational ones: selects can be paused and resumed at chunk granularity, log processing does not stall, and because no table locks are used the impact on the source is minimal. Debezium documents an equivalent facility, comparing primary keys of buffered snapshot events against streamed events inside a snapshot window and discarding the superseded ones. The practical benefit beyond the first load is that a snapshot becomes repeatable: a table can be re-seeded after a downstream loss without stopping the pipeline, which is the same operation as a targeted backfill.

Deletes, and what “deleted” should mean downstream

Deletes deserve their own decision because they are where the capture method and the destination model have to agree.

• If the method cannot see them — query-based extraction — the only remedies are a soft-delete flag the source maintains, or a periodic comparison of full key sets.
• If it can, decide what the destination does: physically remove the row, mark it deleted and keep it, or keep the delete as one more event in an append-only history. An analytics destination usually wants the last of these; a replica meant to mirror the source wants the first.
• Watch the key-change case. Debezium’s documentation describes a primary key update as three events — a delete with the old key, a tombstone, then a create with the new key. A destination that treats deletes as business events will record a deletion that never happened in business terms.
• Separate erasure from deletion. A row removed to satisfy a deletion request has to disappear from every copy, including the change history. A pipeline that converts deletes into “keep everything, mark deleted” has made that request harder to satisfy, and should have done so knowingly.

Order, transactions, and what the destination sees mid-flight

A change stream in commit order is not the same as a destination that is always consistent. Three distinctions decide how wrong things can look.

Per-key order is the one you cannot lose. If two updates to the same order arrive out of order, the destination ends up with the older value — a last writer wins outcome decided by arrival rather than by the source’s commit order. Parallelism is the usual cause: partitioning a change topic by anything other than the key, or applying batches concurrently without key affinity. Order across unrelated keys rarely matters; order within a key always does.

Transaction boundaries do not survive by default. One source transaction that moves stock and writes a shipment produces changes to two tables. Unless the destination applies them together, there is a window in which it holds half the transaction — and if the two tables land in different destination batches, that window can be minutes. Engines expose enough to do better: SQL Server’s change tables carry the commit LSN that groups changes from one transaction, and PostgreSQL’s logical decoding brackets each transaction’s changes between begin and commit. Using that grouping is a destination-side choice, and for many analytical uses the honest answer is to accept the window and make sure readers know that a cross-table query can catch a transaction in the middle.

Re-delivery is normal, not exceptional. PostgreSQL’s documentation is explicit that a slot’s position is persisted only at checkpoints, so after a crash the slot “might return to an earlier LSN, which will then cause recent changes to be sent again,” and that “logical decoding clients are responsible for avoiding ill effects from handling the same message more than once.” That is a statement about the general case: the position a consumer has acknowledged and the work it has actually committed can diverge at a failure, so duplicates arrive. The destination has to be idempotent — see checkpoint and replay for how position and commit are sequenced.

Applying changes without corrupting the target

Two properties make application safe: applying the same change twice changes nothing, and applying an older change after a newer one changes nothing either. A comparable version carried with each event gives you both.

Building that version takes a little care, because a commit position alone is not one. SQL Server’s commit LSN is shared by every change in the same transaction, so if one row changes twice inside a transaction both changes carry the same LSN — which is why the documentation pairs it with a sequence value ordering changes within one transaction. The version to compare is the pair: transaction position first, then the sequence within it. Below, src_version stands for that pair encoded so it sorts correctly, and comparisons across different databases, partitions, or a re-seeded capture need their own rule rather than a raw position.

The second decision is what the event actually contains, because a merge that sets every column only works if each event carries the whole row as it stands after the change. That is not guaranteed. MySQL’s binlog_row_image defaults to full, which “logs all columns in both the before image and the after image,” but minimal “logs only those columns in the before image that are required to identify the row to be changed” and in the after image only “where a value was specified by the SQL statement, or generated by auto-increment”; noblob omits large columns “that are not required to identify rows, or that have not changed.” PostgreSQL has a narrower case that needs no configuration at all: in the logical replication protocol a column can arrive as a marker that “identifies unchanged TOASTed value (the actual value is not sent)” — so a large value left untouched by the update is absent from the event.

Set every column from an event like that and you overwrite untouched columns with nulls. The failure is silent — the job succeeds and the column empties — so check what the source emits before writing the merge.

Two apparent fixes do not work, and both are worth naming because they look sufficient. Falling back to the stored value with COALESCE(new, stored) cannot distinguish a column the event omitted from a column the source genuinely set to null: an update that clears an amount of 100 leaves 100 in place. And collapsing the batch to each key’s latest event, which the example below does, loses earlier changes when the events are partial — starting from (open, 100), a patch {status: paid} followed by {amount: 80} yields (open, 80) if only the last one is applied, whether or not it is merged with the stored row. The paid is simply gone.

So partial events need a different apply protocol rather than a patched merge. Two workable routes: change the source configuration so each event carries a complete after-image, which is the cheaper answer where it is available; or keep the events partial and build the complete state before merging — mark, per column, whether the event carried a value at all (distinct from carrying null), then apply the batch’s events for a key in version order, accumulating into the stored row, and merge the single resulting state. Written that way, collapsing to one row per key happens after accumulation, not instead of it.

The example that follows assumes the first route: changes_batch holds complete after-images, one row per change, with a comparable version. That assumption is the example’s input contract, and it is what makes the last-change-wins collapse safe.

The third decision is what a delete leaves behind. If a delete removes the row, it removes the version with it — and a late-arriving older update for that key then finds no row to compare against and inserts it again. The deleted order comes back. So a delete marks the row rather than removing it, keeping the key and the version and clearing the payload. Consumers read through a view that filters the marked rows out.

-- Current-state table: apply only if this change is newer than what is stored.
-- A delete marks the row; it never removes it, so the version survives.
MERGE INTO orders_current AS t
USING (
  -- One row per key from this batch: the change with the highest version.
  -- Safe only because each row is a COMPLETE after-image. With partial
  -- (changed-columns-only) events, accumulate them in version order first.
  SELECT order_id, status, amount, op, src_version
  FROM (
    SELECT c.*,
           ROW_NUMBER() OVER (PARTITION BY order_id
                              ORDER BY src_version DESC) AS rn
    FROM changes_batch AS c
  ) AS ranked
  WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.src_version > t.src_version AND s.op = 'D'
  THEN UPDATE SET is_deleted = TRUE, status = NULL, amount = NULL,
                  src_version = s.src_version
WHEN MATCHED AND s.src_version > t.src_version
  THEN UPDATE SET status = s.status, amount = s.amount,
                  is_deleted = FALSE, src_version = s.src_version
-- A delete for a key we have never seen is still recorded, so a later
-- older update cannot insert it as if it were live.
WHEN NOT MATCHED
  THEN INSERT (order_id, status, amount, is_deleted, src_version)
       VALUES (s.order_id,
               CASE WHEN s.op = 'D' THEN NULL ELSE s.status END,
               CASE WHEN s.op = 'D' THEN NULL ELSE s.amount END,
               s.op = 'D', s.src_version);

The pattern is ordinary upsert and merge with three additions: collapse each key to its latest complete after-image within the batch before merging, so a batch containing several changes to one row cannot apply them in an arbitrary order — with partial events, accumulate in version order first, as above; compare versions, so a replayed older change is a no-op rather than a regression; and keep deleted keys as marked rows — a delete tombstone — so that ordering guarantee still holds for keys that no longer exist. Those marks need a retention period, and the period that matters is the one that covers how far back you may replay or restore; dropping them sooner reopens the same gap. Keeping the change events themselves in an append-only table alongside the current-state table costs storage and answers the questions the current state cannot — how long orders sat in paid, what the amount was before the correction.

Schema changes need a decision made in advance rather than during an incident. SQL Server’s documentation describes its own answer: a capture instance keeps a fixed column shape, new columns not selected at enable time are ignored, a dropped tracked column yields nulls, a type change is propagated, and a second capture instance can be created to serve the new shape while the first still feeds existing consumers. Whatever the mechanism, someone must decide whether an added source column reaches the destination automatically or through a change someone reviews.

What it costs the source

“Log-based CDC has no impact on the source” is close enough to true for query load and wrong for everything else. The source pays in retention, in storage, and in a new dependency on a consumer it does not control.

Mechanism	What the source carries	What happens if the consumer falls behind
PostgreSQL replication slot	Slots “persist across crashes and know nothing about the state of their consumer(s)” and “prevent removal of required resources even when there is no connection using them”	WAL accumulates and disk fills; a slot dropped to relieve it costs the consumer its position
MySQL binary log	Files expire on a timer — binlog_expire_logs_seconds defaults to 2,592,000 seconds (30 days) in MySQL 8.4	A connector offline past expiry cannot resume and needs a new snapshot
SQL Server CDC	Change tables plus a capture job; per the documentation the log truncation point does not advance until marked changes are captured, even under the simple recovery model	Cleanup moves the validity window forward; requests outside it fail or silently miss data

SQL Server’s documentation is unusually explicit about the window: change data is available only within a validity interval, and “the extraction interval for a request must be fully covered by the current change data capture validity interval for the capture instance.” The default retention is three days, with a cleanup job that runs daily. It also names the latency inherent to the design — because the capture process extracts from the log, a change is not available until that process has read the relevant log entries, with the default job processing up to 1,000 transactions per cycle and waiting five seconds between cycles.

Read those numbers as the shape of an obligation rather than as trivia. Every log-based mechanism has a retention window, and a pipeline that is down longer than that window does not resume — it re-snapshots. The monitoring that matters is therefore consumer lag against the retention window, and the plan that matters is what you do when a connector has been stopped over a long weekend. Data freshness targets should be stated with that in mind.

None of these mechanisms verify themselves

Every method above can lose data without producing an error: a watermark that skips a late commit, a connector restarted with a stale position, a snapshot that finished while its table was being written, a merge that silently dropped rows whose key was null. Success in the job log means the code ran, not that the destination matches the source.

The only defense is an independent comparison: reconciliation against the source at an agreed cutoff, cheaply as counts and key-range summaries — a chunked checksum — and occasionally in full. Decide the cadence when you build the pipeline, because the value of the check is that it runs before anyone has made a decision on wrong numbers.

Choosing

Work from the requirement to the mechanism, not the other way around.

If this is true	Then
The table is small and the source can take a full scan	Reload it fully and spend the effort elsewhere
You need deletes, and the source will not add a flag	Query-based extraction is out
You need intermediate states or the old values of a row	Log-based CDC or CDC change tables, with before-images enabled
You cannot get log access, an agent, or elevated privileges	Engine change tracking if available; otherwise a watermark plus reconciliation, with the hole documented
The source team can publish events and wants to own the contract	Application events, with the pipeline consuming a promised interface rather than internals
Latency must be seconds, not hours	Log-based, and budget for the operational duties that come with it

References

Product behavior was checked against the following documentation in September 2026, at the versions named. Defaults and features change; verify against the version you run.

• Microsoft Learn, Incrementally copy data from a source data store to a destination data store (Azure Data Factory)
• Microsoft Learn, About change tracking and What is change data capture (CDC)? (SQL Server)
• PostgreSQL 18 documentation, Logical Replication: Publication, Logical Decoding Concepts, and Logical Decoding Output Plugins, and Logical Replication Message Formats
• MySQL 8.4 Reference Manual, Binary Logging Options and Variables
• Debezium 3.6 documentation, Debezium connector for PostgreSQL
• Andreas Andreakis and Ioannis Papapanagiotou, DBLog: A Watermark Based Change-Data-Capture Framework, Netflix, 2020