Change Data Capture (CDC) is a common way to keep analytical platforms synchronized with operational databases without repeatedly extracting entire tables. CDC interviews test whether you understand transaction logs, ordering, deletes, checkpoints, recovery, and how to make target state correct despite retries and failures.
1. How does log-based CDC work?
Log-based CDC reads a database's transaction log—such as a write-ahead log or binary log—to capture committed inserts, updates, and deletes. Instead of repeatedly querying application tables, a CDC connector reads the change stream and converts log entries into ordered change events.
Typical flow:
- The source database records transactions in its transaction log.
- A CDC connector reads the log from a saved log position or offset.
- The connector emits events containing metadata such as table, operation, key, before/after values where supported, transaction position, and event timestamp.
- Events are published to a durable stream or staged for downstream processing.
- Consumers apply events to a target table and persist their own progress safely.
Application transaction
|
v
Source database transaction log
|
v
CDC connector + saved log position
|
v
Durable event stream / raw landing
|
v
Deduplicate + order + apply changes
|
v
Target table / warehouse / lakehouseAdvantages: Low-latency change capture, reduced repeated table scans, and access to deletes and intermediate changes when the connector and source configuration support them.
Challenges: Log retention, connector lag, schema changes, permissions, source-specific configuration, transaction ordering, and checkpoint recovery.
2. What is the difference between CDC and periodic snapshots?
CDC captures changes as they occur, while periodic snapshots repeatedly read a source table or a defined slice of it at scheduled intervals. Snapshots show the state at a point in time; CDC provides a sequence of changes, subject to the source and connector's guarantees.
| Dimension | Log-based CDC | Periodic snapshots |
|---|---|---|
| What is captured? | Committed change events | Current row state at extraction time |
| Freshness | Potentially seconds or less, depending on setup | Limited by the snapshot schedule and duration |
| Deletes | Can capture explicit delete events | May require comparing snapshots or using tombstones/audit data |
| Source load | Reads transaction logs; still needs monitoring and configuration | Can scan large volumes repeatedly unless incremental filters are effective |
| Complexity | Higher operational complexity and log-retention requirements | Often simpler, but can be expensive or miss transient changes between snapshots |
Choose CDC when low latency, change history, or accurate delete propagation matters. Choose snapshots when the source does not expose logs, the table is small, freshness requirements are relaxed, or periodic state comparison is sufficient.
3. How would you capture deletes from a source database?
A physical delete removes a row from the current table, so a normal query of that table cannot reveal the deleted record. The best capture strategy depends on what the source exposes.
- Transaction-log CDC: Capture delete operations from the database log, including the primary key and available transaction metadata.
- Soft deletes: Use a field such as
is_deletedordeleted_atif the source application preserves deleted rows. - Audit or tombstone tables: Consume an explicit deletion record written by the source application or trigger.
- Snapshot comparison: Compare successive key sets and infer deletes when a previously present key disappears. This can be costly and only identifies deletion between observed snapshots.
Applying a delete: A CDC event should include the key needed to remove or mark the target row, plus ordering metadata so an old delete does not overwrite a newer insert.
if event.operation == "DELETE":
delete target row where primary_key = event.primary_key
else:
upsert target row using event.after_imageThe code is illustrative; production logic must also validate event ordering, handle missing before-images if needed, and apply changes transactionally where supported.
4. How do you handle duplicate or out-of-order CDC events?
Assume events can be redelivered and design the target application to be idempotent. For ordering, use source-defined ordering metadata—not event arrival time alone.
Recommended approach:
- Stable event identity: Use a unique event ID or a combination of source log position, transaction ID, table, and row key where appropriate.
- Per-key ordering: Preserve source ordering for changes to the same key. A stream partitioning strategy based on the primary key can help when the broker's guarantees support it.
- Version-aware writes: Store a source sequence, log position, or comparable version on each target row and apply only newer events.
- Deduplicate: Ignore event identities already processed, or use deterministic upserts that safely tolerate replay.
- Handle transactions: If multiple row changes must appear atomically, preserve transaction boundaries or define how partial visibility is handled.
if event.version <= target.version:
ignore event # duplicate or stale event
elif event.operation == "DELETE":
apply delete/tombstone with event.version
else:
upsert event.after_image
and store event.versionThe comparison must use a version value that is meaningful and comparable for the source. A timestamp may not be sufficient if precision is low or clocks are inconsistent.
5. How would you recover a CDC pipeline after losing its checkpoint?
A checkpoint identifies how far the connector or consumer has safely progressed through the change stream. If it is lost, do not simply start reading from the current log position: that can skip changes that were never applied to the target.
- Assess retained history: Determine whether the source log still contains all changes since the last known good position.
- Recover a trusted position: Restore checkpoint metadata from durable storage, backups, connector state, or transaction/stream offsets if available.
- Replay safely: If a valid earlier position is available, replay from it using idempotent target writes and event deduplication.
- Reconcile target state: Compare target keys, counts, and aggregates with the source or a trusted snapshot to identify gaps.
- Rebootstrap if needed: If required log history has expired and no reliable checkpoint can be recovered, take a new consistent snapshot and resume CDC from a coordinated position.
- Improve durability: Persist connector offsets and consumer checkpoints reliably, monitor lag against log retention, and test recovery procedures.
Interview-ready answer: “I first establish whether the gap can be replayed from retained logs. If yes, I replay from a trusted earlier offset with idempotent application. If not, I rebootstrap from a consistent snapshot, coordinate the snapshot boundary with CDC, and reconcile before declaring the target current.”
6. How would you implement CDC for a large database with minimal source impact?
Use log-based CDC for ongoing changes where supported, and plan the initial snapshot so it does not overwhelm the source database or compete with production workloads.
- Prefer transaction-log capture: It usually avoids repeatedly scanning entire tables for ongoing changes, though log reads still have resource and retention implications.
- Coordinate a consistent snapshot: Establish a snapshot boundary or log position that lets the snapshot and change stream join without gaps or double counting.
- Chunk the snapshot: Read bounded key ranges or partitions with controlled concurrency and resumable progress.
- Use replicas when appropriate: A read replica may reduce primary read load, but confirm its lag and whether the CDC product supports the topology.
- Throttle and schedule: Set limits for connections, query duration, snapshot workers, and bandwidth. Monitor CPU, I/O, lock waits, replication lag, and application latency.
- Plan log retention: Ensure logs are retained long enough for the snapshot and any outage; alert before the connector falls behind the retention window.
- Test with production-like volume: Validate source impact and catch long-running transactions or large tables before full rollout.
7. How do you apply CDC events to maintain an accurate target table?
Treat the target as a state machine driven by source changes. For each event, identify the affected row, determine whether the event is newer than the target state, then apply an insert, update, or delete consistently.
Core requirements:
- Use stable primary keys and define how key changes are represented.
- Preserve source ordering/version metadata and reject stale events.
- Make event application idempotent so replay does not create duplicates.
- Apply related changes transactionally when target semantics require atomicity.
- Represent deletes correctly and retain tombstones or audit history when required.
- Advance the consumer checkpoint only after target writes are durable and the corresponding events are safely applied.
- Monitor lag, rejected events, duplicates, schema errors, and reconciliation drift.
Current-state table vs. history table: A current-state table stores the latest known version of each entity. A history table retains change events or validity intervals so consumers can reconstruct prior states. Choose based on whether the use case needs current reporting, audit, or point-in-time analysis.
for event in ordered_events:
key = event.primary_key
current = target.lookup(key)
if current and event.version <= current.version:
continue
if event.operation == "DELETE":
target.apply_tombstone(key, event.version)
else:
target.upsert(key, event.after_image, event.version)
commit_checkpoint_after_durable_apply()This is conceptual pseudocode. In a real implementation, coordinate target writes and checkpoint storage to handle crashes between them. A durable event log plus idempotent writes is often used to make replay safe.
8. What challenges arise when combining an initial snapshot with streaming changes?
A snapshot captures existing rows while CDC captures concurrent changes. If the snapshot and stream do not share a coordinated boundary, updates can be missed or applied twice, producing an inconsistent target.
Common challenges:
- Gap between snapshot and stream: Changes occurring during the snapshot may be missed if streaming begins too late.
- Duplicate changes: A row may appear in the snapshot and also arrive as a CDC event.
- Ordering conflicts: A snapshot row may overwrite a newer streaming update if writes are not version-aware.
- Long-running snapshots: Large snapshots can take hours, requiring sufficient log retention to preserve changes while the snapshot runs.
- Deletes during snapshot: A row may be read by the snapshot and deleted before the snapshot finishes.
- Restart complexity: Failed snapshot chunks need repeatable boundaries and a way to coordinate with the stream.
Safer implementation pattern:
- Establish a consistent snapshot boundary and associated log position using the source/connector's supported protocol.
- Start retaining or consuming CDC from that boundary while the initial snapshot is taken.
- Load snapshot chunks into staging with keys and snapshot metadata.
- Apply streaming events with source-version ordering so newer changes win over older snapshot rows.
- Reconcile the completed target and only then mark the bootstrap as complete.
Quick Revision Table
| Topic | Core takeaway |
|---|---|
| Log-based CDC | Read committed transaction-log changes from a durable position. |
| CDC vs. snapshots | Changes over time vs. periodic observations of current state. |
| Deletes | Capture log delete events, soft deletes, tombstones, or compare snapshots. |
| Duplicates and ordering | Use stable event identity, source versions, and idempotent writes. |
| Checkpoint recovery | Replay from a trusted offset if logs remain; otherwise rebootstrap and reconcile. |
| Large initial loads | Chunk and throttle snapshots while preserving a consistent CDC boundary. |
| Accurate target state | Apply ordered changes, handle deletes, and commit progress after durable writes. |
| Snapshot + stream | Coordinate boundaries and ensure newer events cannot be overwritten by older snapshot rows. |
Scenario-Based Practice Questions
Try answering these scenarios out loud before checking the suggested approach.
Scenario 1: The CDC connector is down longer than the source log-retention period. What next?
First confirm whether the missing log range can be restored or recovered from another durable source. If the source has purged required changes and no trusted checkpoint is recoverable, establish a new consistent snapshot, resume CDC from a coordinated boundary, and reconcile the target before switching consumers back to it. Add alerts based on connector lag and remaining log-retention headroom.
Scenario 2: An old update arrives after a delete and recreates the row.
Store source ordering/version metadata for each key, including a tombstone version after deletion. Reject events whose version is older than or equal to the latest applied version. Ensure the version ordering is source-defined and remains available long enough to reject delayed events.
Scenario 3: The initial snapshot takes eight hours. How do you avoid losing changes during that time?
Use the connector's consistent-snapshot protocol to establish a corresponding log position, and ensure the source retains enough log history for the entire snapshot and recovery window. Consume or retain changes while snapshot chunks load, apply them with version-aware ordering, throttle snapshot reads, and monitor log growth and connector lag.
Scenario 4: Your CDC target row counts differ from the source. What do you check?
Compare at a consistent source position and account for deletes, filters, and in-flight transactions. Check connector lag, missing offsets, duplicate events, rejected records, schema errors, and merge logic. Compare distinct primary keys and partition-level aggregates, not just total row counts, then replay or rebootstrap if a gap is confirmed.
Chapter Summary
- Log-based CDC reads committed changes from transaction logs and uses durable positions to track progress.
- Snapshots capture observed state; CDC captures changes, and many systems need both.
- Deletes need explicit capture and target semantics, not just ordinary upserts.
- Duplicate and out-of-order events require idempotency and source-version-aware application.
- Checkpoint loss is recoverable by replay only when the required log history remains available.
- Large initial snapshots should be chunked, throttled, and coordinated with streaming changes.
- Target accuracy depends on ordering, durable writes, checkpoint discipline, and reconciliation.
Practice Challenge
Design a CDC pipeline from a high-volume transactional database into a cloud data lake and warehouse. Explain how you would bootstrap an initial snapshot, capture inserts/updates/deletes, manage ordering and retries, recover from expired log history, minimize source impact, and prove the target is consistent.
Suggested Blogger post title: Top 8 CDC Interview Questions for Data Engineers | Practical Examples and Failure Scenarios
Comments
Post a Comment