Interview questions
When incremental pays
1When is an incremental pipeline worse than a nightly full rebuild?
When the fraction of data that changes exceeds roughly 1 ÷ the merge penalty (often a crossover at 1–10%), or when the table is so small that the fixed cost of capture, state and operations exceeds the time saved, as for a 40 GB table that rebuilds in 2 minutes.
Read moreThe break-even formula
2Why does applying changes on a data lake cost 10–100 times more per row than in a database?
Lake files are immutable and large, say 128 MB, so changing one row rewrites its whole file. It is a mismatch between the size of a change and the size of the unit you must rewrite.
Read moreThe application granularity ladder
Detecting changes
3Is reading changes from the database's log (log-based CDC) free for the source database?
No. It is off the critical path of the write transaction, but it increases log volume (often 10–30% with full row images), costs CPU to decode, and forces the source to keep log segments until the consumer has read them.
Read moreThe claim of zero impact is false
4What happens if a CDC consumer stops for a weekend?
The source cannot recycle log segments the consumer has not read, so its log disk fills and can take the primary database down. Monitor the oldest unread position, and cap how much log may be retained.
5What does trigger-based CDC give, and what does it cost?
It gives transactional consistency: the change record commits together with the change. It costs 2–5 times the write latency per statement, more for bulk operations, and adds a second writer inside every transaction.
Read moreTrigger-based CDC
6What does polling for rows with a recent updated_at value miss?
States in between polls (a row changed twice is seen once), hard deletes, writes that do not set the column (scripts, fixes by a DBA), and rows from long transactions that commit after the polling watermark has already moved past them.
Read morePolling is samplingTwo failures worse than aliasing
7Why can a poller never see a hard delete?
A deleted row cannot appear in a query for rows that changed: it is no longer there. You need log capture, soft deletes, or a comparison of key lists.
Read moreThe fidelity ladder
Bootstrapping and representing changes
8How do you take the initial snapshot of a 300-million-row table without locking it?
Read it in chunks by key range while streaming changes at the same time, writing watermark markers into the log around each chunk. A keyed, idempotent sink applies one rule: when a snapshot row and a streamed change disagree, the change at the later log position wins.
Read moreBootstrap: the lock-free way
9What must a change event contain?
The operation, the row after the change, the row before it, its position in the source log (the key for ordering and idempotence), the transaction ID, and two timestamps: when it happened at the source and when it was captured.
Read moreWhat an event must carry
10Why does the before-image of a changed row matter?
It lets an update be expressed as "remove the old row, add the new one": (old, −1), (new, +1). Without it, a consumer can only upsert the latest state; it cannot handle a change of key, or maintain sums or joins.
Read moreSigned multiplicity
11A replica briefly showed a money transfer with funds missing from both accounts. Why?
The transport was partitioned by key, which split one transaction's changes across partitions that are applied independently. The source's transaction atomicity was not preserved.
Read moreOrdering and transactional grouping
12Which schema changes are safe in a CDC pipeline?
Adding a nullable column. Dropping, narrowing or changing the type of a column breaks existing readers and needs a coordinated migration.
Read moreSchema change propagation
Applying changes
13Why does a MERGE of staged changes fail with 'multiple matches'?
Several staged rows match the same target row, so the result would be arbitrary. Deduplicate the staged changes to one row per key first, keeping the one with the highest log position.
14What happens downstream when a primary key value is updated at the source?
The source reports one update, but it is really a delete of the old key plus an insert of the new one. Without the before-image, the row under the old key is left behind as an orphan.
15Why can SUM be maintained incrementally while MAX cannot?
Sums have an inverse: a delete is undone by subtracting. MAX has none, so deleting the current maximum requires rescanning, or keeping every value.
16What does it cost to maintain a join incrementally?
Each change is small, but you must keep both full input tables as indexed state to find what a change joins with. You trade storage for compute.
Read moreSelection, projection, join
Avoiding CDC, and repairing pipelines
17When do you not need change data capture at all?
When data is append-only and partitioned by arrival: process new partitions past a watermark, plus a window for reprocessing late data. Or, on a versioned table format, diff two snapshots.
Read moreWatermark-based incremental batchSnapshot-diff incrementality
18How do you design the cutover from a backfill to the live pipeline?
Overlap the two deliberately, with a keyed, idempotent sink so the overlap is harmless. Run old and new in parallel, compare until there are no differences, then switch by swapping a pointer.
Read moreCutover: where correctness is decidedBlue-green at table level
19Why not have a service write to the database and publish to the message broker in the same request?
That needs atomicity across two systems. A crash between the two writes leaves them permanently disagreeing. Use CDC, or a transactional outbox.
20How do you get exactly-once effects in a CDC consumer?
Commit the consumer's position and the derived state in one atomic step. Writing state first and position second gives duplicates; the other way round loses data. With a keyed, idempotent sink, duplicates are harmless.
Read moreCommit the cursor with the state
21Can you build a type-2 history table from a polling pipeline?
No. Polling misses versions that changed between polls. You need before-images, order per key, and an atomic close-the-old-row-and-insert-the-new step.
Read moreFeeding history tables
22Why does an incremental pipeline still need a periodic full rebuild?
Derived state drifts through unknown bugs and blind spots, and only a full recomputation can prove it has not. Rebuild or reconcile periodically, as anti-entropy.
Read moreDerived state drifts