Incremental Load is faster and cheaper than Full Load — that's the whole pitch. But the speed comes from trusting a single filter condition to correctly identify "everything that changed since last time," and that trust breaks in a specific, common way that most pipelines don't catch until data quietly goes missing.

THE THREE APPROACHES, IN ONE SENTENCE EACH

Full Load copies the entire source table every run, regardless of what changed — simple, safe, expensive at scale. Incremental Load filters the source using a watermark (usually an UPDATED_AT timestamp) to pull only new or changed rows since the last run — faster, but entirely dependent on that timestamp being reliable. CDC (Change Data Capture) reads the database's own change log directly — inserts, updates, AND deletes — giving near real-time sync without relying on the source table having a trustworthy timestamp column at all.

WHERE INCREMENTAL LOAD QUIETLY BREAKS

SELECT *
FROM source_table
WHERE updated_at > '2026-05-01 10:00:00'

This is the standard incremental pattern — pull everything changed after the last successful run's watermark. It works perfectly as long as every single system that writes to source_table reliably updates updated_at on every change. The moment one upstream process — a bulk correction script, a manual database fix, an ETL job from a different team — writes to the table without touching that timestamp column, those changes become permanently invisible to this filter. Not delayed. Invisible. The next run's watermark moves forward, and that untimestamped change never gets picked up, ever, unless someone runs a Full Load to catch it.

THE SECOND GAP: DELETES

Incremental Load based on updated_at has no way to detect a row that was deleted from the source. A deleted row doesn't show up with a new timestamp — it just stops existing, and a WHERE updated_at > watermark filter has nothing to select. The target table keeps that row forever unless something else explicitly reconciles deletes.

WHY CDC EXISTS

-- Conceptual: CDC reads the transaction log directly
CHANGE_TYPE | ID | NAME         | CHANGE_TIME
INSERT      | 4  | John         | 02-May 09:30
UPDATE      | 2  | Priya Sharma | 02-May 09:31
DELETE      | 3  | Kiran        | 02-May 09:32

CDC doesn't depend on the source table having a correctly maintained timestamp column at all — it reads the database engine's own change log, which captures every insert, update, and delete regardless of whether the application layer remembered to stamp a timestamp. This is why CDC captures deletes natively, where timestamp-based incremental load structurally cannot.

HOW TO CHECK WHICH RISK YOU'RE CARRYING

If your pipeline uses timestamp-based incremental load, this query tells you whether the assumption is holding:

SELECT COUNT(*) AS untimestamped_recent_changes
FROM source_table
WHERE updated_at IS NULL 
   OR updated_at < DATEADD(day, -1, created_at)

Rows with a NULL updated_at, or an updated_at that's suspiciously older than created_at, are signals that something is writing to this table without maintaining the column your entire incremental strategy depends on.

THE LESSON

Incremental Load isn't a strictly worse or better choice than Full Load or CDC — it's a specific trade: speed, in exchange for total trust in one timestamp column being maintained by every process that ever touches the table. That trust is rarely tested until an audit finds data that should have synced and didn't. Before committing to timestamp-based incremental load on a table, it's worth confirming nothing writes to it outside the path that maintains the watermark column.