ETLintermediate
Incremental Loads & Change Data Capture
Explain how incremental load strategies and change data capture (CDC) avoid full table reloads while keeping a warehouse current and consistent.
Truncating and reloading a fact table works until it does not. Incremental loading is what every production warehouse actually does.
A watermark load is like only reading mail postmarked after your last visit to the mailbox — reliable as long as every letter is postmarked correctly, but you'd never notice a letter that was simply shredded (a delete). Change Data Capture is like having a security camera on the mailbox itself, recording every single delivery and removal as it happens, regardless of postmarks.
Key Concepts
1
The watermark pattern is the simplest.
remember the maximum LAST_MODIFIED_DATE from the last run
next run: SELECT ... WHERE LAST_MODIFIED_DATE > :watermark
store the new maximum2
Three ways it goes wrong, and they are all silent.
3
The column is not reliable. A source that updates rows without touching LAST_MODIFIED_DATE — a bulk fix,
a trigger that was disabled — produces changes the load never sees.
LAST_MODIFIED_DATE
4
Deletes are invisible. A deleted row has no modified date. It simply stops appearing, and the warehouse
keeps it forever. Only a full reconciliation finds these.
5
The boundary. Using > on a timestamp with rows committed during the extract means those rows are missed.
Using >= means some are processed twice. Neither is wrong if the load is idempotent, which is the reason
to make it so.
>>=
6
Change data capture reads the database log instead.
the source's redo or transaction log
-> every insert, update and delete, in order
-> applied to the warehouse7
It catches deletes, catches changes that bypassed the application, and puts almost no load on the source —
because it reads the log rather than querying tables.
8
The cost is setup: it is a database-level configuration, it needs privileges, and it is a component to
operate.
9
Trigger-based capture sits between them: triggers on the source tables write to a change table. It
catches everything and adds work to every write on the source, which is the trade.
10
Making the load idempotent matters more than the strategy. A run that can be repeated safely turns
"did it finish?" into "run it again", which is what you want at 3 a.m.
11
That usually means a merge keyed on the business key rather than a blind insert.
12
And reconcile periodically. Whatever the incremental strategy, a monthly count-and-sum comparison
against the source finds the drift that incremental loading accumulates. Without it, the warehouse is
slightly wrong for a long time before anyone notices.
13
What the interviewer is probing.1. "Why not just reload everything each night?" Probing: the practical driver. Stalls: "You
could." Moves up: volume and window — a full reload stops fitting, and it also destroys type 2
history unless carefully handled.
14
2. "How do you identify changed rows?" Probing: the techniques. Stalls: "A date column."
Moves up: a reliable last-updated column, a database change capture feed, or a hash comparison —
each with different trust and cost.
15
3. "What is the risk with a last-updated column?" Probing: the honest caution. Stalls:
"None." Moves up: it is only as good as the source application maintaining it — deletes and bulk
updates often bypass it entirely.
16
4. "How are deletes handled?" Probing: the gap people forget. Stalls: "They are not." *Moves
up:* a capture feed, a soft-delete flag, or a periodic key reconciliation — otherwise deleted rows
live forever in the warehouse.