Incremental Loading and CDC

ByJu Yeon Eum

In an illustrative scenario, 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. 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. The choice depends on 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.

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?

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 for an append-only table an increasing key — and you remember how far you got. The two are not interchangeable: a key assigned at insert never changes when the row is updated, so a key watermark sees new rows only; capturing updates needs a value that moves on every write, and even then the commit-visibility problem below applies. 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.

The first query returns the upper boundary. The calling job retains that value and passes it as :new_watermark to the extraction query, alongside the previously committed :last_watermark. It stores the new boundary as the completed position only after the destination commits.

-- Bound the run first, so rows written during the extraction
-- do not fall into an interval nobody will read again.
SELECT MAX(updated_at) AS new_watermark 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.

TimeWhat happens
01:00:00A transaction updates order A and sets updated_at = 01:00:00. It stays open.
01:00:30The 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:45The transaction commits. Order A becomes visible, carrying updated_at = 01:00:00.
Next runThe 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. A different gap remains, between the pieces of a read: validating the version you stored, enumerating changed keys, and joining to the current rows are separate operations, and between them cleanup can remove versions and concurrent updates can move rows on. Microsoft’s guidance is to check that the stored version is still at or after the minimum valid version — reinitializing if it is not — and to obtain the current version, enumerate the changes, and read the rows inside one snapshot-isolation transaction, so that the version you store describes what you read. That transaction is the documented method; any other read procedure has to add its own validation and boundary handling. 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 calls,” 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 deletesKeeps intermediate statesNeeds from the sourceMain failure mode
Full reloadYes, by replacementNoCapacity for a full scanCost and duration grow with the table
Timestamp watermark, or key watermark on an append-only tableNoNoA column every write path maintains; an insert-time key sees new rows only, never updatesRows committed after the watermark moved past their timestamp are never picked up
Engine change tracking (keys only)YesNoA feature enabled per table; retentionFalling behind the retention window
Engine CDC change tablesYesYesA capture job, log retention, storageRequesting an interval outside the validity window
Log-based CDC connectorYesYesLog access, a slot or position, privilegesConnector downtime past log retention; unbounded log growth
Application events (outbox)Yes, if publishedYes, as publishedSource team owns and evolves the eventsThe 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 without stopping the pipeline, which is the same operation as a targeted backfill — with one limit. A snapshot reads what exists now, so it restores current state and nothing else. A row deleted at the source after the destination lost its delete event is not in the snapshot, and upserting the snapshot into the existing table does not remove it; the intermediate states lost with the events are not recovered at all. Re-seeding into an existing destination therefore needs a rule for keys the snapshot did not return — a comparison against the snapshot’s key set, coordinated with the stream boundary — or a fresh destination generation loaded from snapshot plus stream, verified, and switched to. Which of those a connector’s re-snapshot does is worth checking rather than assuming.

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. Where a deletion obligation applies to a person’s data — which depends on the jurisdiction, on whether the request is valid, and on whether an exemption or a retention duty covers the data — the row has to go from every copy in that scope, including the change history, and backups that cannot be edited need controls of their own: restricted access, a defined expiry, and re-deletion after any restore. A pipeline that converts deletes into “keep everything, mark deleted” has made that 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 — or, more precisely, the one you must be able to reconstruct. If two updates to the same order arrive out of order and the destination applies them as they come, it ends up with the older value — a last writer wins outcome decided by arrival rather than by the source’s commit order. Whether out-of-order arrival is survivable depends on what the events carry. A complete after-image with a comparable version can arrive late and be discarded by the version check, so arrival order need not match source order as long as the source order is recoverable from the events. A partial change, an increment, or a command cannot be discarded, because its effect is not contained in the newer event; for those every change has to be obtained, and each applied only after the changes it depends on — which the transport can provide by keeping per-key order, or the destination can provide by reordering on arrival, so long as it can detect and recover a change that never comes. Parallelism is the usual cause of disorder: 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 has to be either preserved in transit or reconstructed before applying.

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 — for events that carry the complete row. Events that carry only changed columns need more, as the rest of this section explains, because an older change may still hold something the newer one did not.

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.

That still leaves the batch boundary. Sorting within a batch does nothing for a change that arrives in a later batch than one it precedes: if v2 {amount: 80} lands in this run and v1 {status: paid} in the next, the stored row is already at version 2 when v1 arrives, and the version comparison discards it — (open, 80) again, with the paid lost across batches instead of within one. Partial events therefore need a delivery contract as well as an apply order: every change a key depends on has to arrive and be applied, in version order, before the stored version advances past it. In practice that is a reorder buffer keyed on the source’s per-key sequence, which holds a change until its predecessor has been applied and has a way to detect and recover a predecessor that never comes; a version stored per column rather than per row is the other workable design. A database-wide log position is not that sequence — it is not contiguous per key, so a gap in it says nothing about a missing change.

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,
  -- and only once every earlier change for the key has arrived.
  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 and only after every earlier change for the key has arrived, 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 to true for the steady-state log read and wrong for everything else. The snapshots are queries: the initial load, any incremental snapshot, and any re-seed scan the tables, compete with concurrent writes for I/O, and need their chunk size and throttling set like any other extraction. And the steady state pays in retention, in storage, and in a new dependency on a consumer the source does not control.

MechanismWhat the source carriesWhat happens if the consumer falls behind
PostgreSQL replication slotSlots “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 logFiles expire on a timer — binlog_expire_logs_seconds defaults to 2,592,000 seconds (30 days) in MySQL 8.4A connector offline past expiry cannot resume and needs a new snapshot
SQL Server CDCChange 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 modelCleanup 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 can lose the position a consumer needs, but not always on a timer. SQL Server’s change tables and MySQL’s binary log expire by retention period; a PostgreSQL slot keeps its WAL without limit by default, and the bound that can be set, max_slot_wal_keep_size, is a size rather than a time — so the failure there is a full disk, or a slot invalidated once the limit is set, rather than a window passing. What decides whether a stopped pipeline can resume is whether the position it needs still exists: in the log, in the slot, and in any broker in between. When it does not, resuming is not an option and the table has to be re-seeded — which restores current state, not the events that were lost, as the snapshot section says. The monitoring that matters is therefore consumer lag against whatever bound applies — time, size, or disk — 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 checkpoint written ahead of the output it describes, so that a restart skips what was never written; a snapshot taken without a consistent boundary, so that rows changed during it fall between snapshot and stream; a pre-processing step that filters out rows with a null key before the merge sees them. Not every restart or busy table qualifies — a connector restarted from a position behind its output produces duplicates rather than loss, and a snapshot taken under a proper boundary is safe however busy the table; the loss comes from the missing boundary, not from the concurrent writes. Success in the job log means the code ran, not that the destination matches the source.

Several defenses apply before any comparison: committing the position and the output atomically, key constraints at the destination, and checks on missing or out-of-range values each prevent or detect a class of loss. Beyond them, a reconciliation against the source at an agreed cutoff is the check that does not depend on the pipeline’s own bookkeeping — as counts and key-range summaries, a chunked checksum, where the cost of reading the source allows, and occasionally in full. Be clear about what it proves: a matching current state says the destination is right now, not that every intermediate event arrived, and it costs source reads that have to be budgeted like the extraction itself. 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 trueThen
The table is small and the source can take a full scanReload it fully and spend the effort elsewhere
You need deletes, and the source will not add a flagAn incremental query over current rows cannot see them; use a periodic comparison of complete key sets — read at a consistent point and costed against the source — or a mechanism that records deletes
You need intermediate states or the old values of a rowLog-based CDC or CDC change tables; before-images are the direct way to old values, and a retained baseline plus a complete, ordered history of after-images can reconstruct them for changes since the baseline
You cannot get log access, an agent, or elevated privilegesEngine change tracking if available; otherwise a watermark plus reconciliation, with the hole documented
The source team can publish events and wants to own the contractApplication events, with the pipeline consuming a promised interface rather than internals
Latency must be seconds, not hoursMeasure the end-to-end budget — capture, transport, apply — against the candidates: log-based CDC is the usual route, but its capture job and queue can miss seconds too, while frequent change-tracking polls or application events tied to the source transaction can meet them; whichever is chosen, budget for the operational duties that come with it

References

Discover more from Insightful Data Lab

Subscribe to get the latest posts sent to your email.

Similar Posts

Questions, corrections, or additional insights?

This site uses Akismet to reduce spam. Learn how your comment data is processed.