TL;DR: Change Data Capture streams inserts, updates, and deletes from a source database to the warehouse so it stays current without full reloads. Prefer log-based CDC (read the database transaction log, e.g. via Debezium) over query-based polling on
updated_at, because log-based captures deletes and hard-to-detect changes with low source load. Apply changes with idempotent MERGEs (often into SCD2 history), and handle ordering and exactly-once with keys and watermarks.
How to approach it. Frame the goal: a fresh analytical copy of transactional data without re-extracting everything. Contrast the three approaches (full reload, query-based, log-based) by their tradeoffs, recommend log-based, then cover how you apply the changes idempotently. The signal is knowing exactly why polling updated_at is insufficient.
A strong answer. The approaches. Full reload re-extracts the whole table each run. Simple, but it does not scale (expensive, slow, heavy source load) and gives stale, coarse snapshots. Fine only for small dimension tables. Query-based (incremental) CDC polls for rows where updated_at > last_run. Better, but it has real holes: it misses hard deletes (a deleted row leaves nothing to find), depends on a reliable updated_at on every table, can miss intermediate changes between polls, and adds query load to the source. Workable for append-mostly tables, fragile generally. Log-based CDC (preferred) reads the database transaction log (MySQL binlog, Postgres WAL) via a tool like Debezium, which emits an ordered stream of every insert, update, and delete. It captures deletes and every change, runs near-real-time, loads the source lightly (it reads the log, not the tables), and needs no application-maintained timestamps. This is the production standard.
| Approach | Catches deletes | Source load | Latency | Use when |
|---|---|---|---|---|
| Full reload | Yes (implicitly) | High | Hours | Small dimensions only |
| Query-based | No | Medium | Minutes | Append-mostly tables |
| Log-based | Yes | Low | Seconds | Default for production |
Applying changes to the warehouse. The change stream lands (often via Kafka) and you merge it into the target. Apply changes with MERGE keyed on the primary key so re-processing the same change is a no-op; never blind-append, which creates duplicates. Changes must be applied in event order per key (a later update must win), so order by the log sequence number or event time and dedupe to the latest change per key per batch. When analytics needs history, the merge writes SCD2 versions (close the old row, open the new) rather than overwriting. Decide soft-delete (flag) versus hard-delete in the warehouse; CDC gives you the delete events to act on. Bootstrap with a one-time full snapshot, then switch to the ongoing log stream, stitched at a consistent log position. Finally, handle schema evolution (new or dropped columns) gracefully in the pipeline.
The defensible framing: use log-based CDC to capture all changes including deletes with low source impact, stream them, and apply with ordered, idempotent merges (often SCD2), bootstrapped from an initial snapshot.
Key takeaways.
- Log-based CDC beats
updated_atpolling on every axis that matters: it catches deletes, needs no app timestamps, and barely touches the source. - Apply changes with a primary-key MERGE so reprocessing is a no-op; blind-append always produces duplicates.
- Order and dedupe by log offset or event time per key, so a late-arriving older change cannot overwrite a newer one.
- Bootstrap from a full snapshot stitched at a known log position, then stream; use SCD2 when analytics needs point-in-time history.
What interviewers probe next.
- "Why is polling
updated_atinsufficient?" It misses hard deletes, depends on a reliable timestamp everywhere, can skip intermediate states, and loads the source; log-based avoids all four. - "How do you guarantee no duplicates or lost changes?" Idempotent key-based MERGE, ordering by log offset or event time, exactly-once-effect via checkpointed offsets, and an initial snapshot stitched at a known log position.
- "Current state vs history?" MERGE-overwrite for a current-state mirror; SCD2 when analytics needs point-in-time history.
- "Out-of-order or late changes?" Order and dedupe per key by the log sequence number so the latest change wins regardless of arrival order.
Common mistakes.
- Full nightly reloads that do not scale and give stale data.
- Query-based CDC that silently misses deletes and intermediate changes.
- Blind-appending change events instead of idempotent key-based merges (duplicates).
- Applying changes out of order, so an older update overwrites a newer one.