Change data capture (CDC) represents changes made to source records so a downstream system can process them without repeatedly reloading the full dataset. A change feed may include inserts, updates, and deletes, often with metadata such as an operation type, source position, and event time.

Full load versus incremental processing

  • A full load reads a complete source snapshot. It is straightforward but can become expensive or disruptive as data grows.
  • An incremental load reads changes since a checkpoint or watermark. It reduces repeated work but requires careful boundary and late-change handling.
  • A timestamp watermark is not a true change log unless the source guarantees it captures every relevant update and delete.

A dependable CDC flow

  • Capture a source position or bounded time range and preserve it with the run metadata.
  • Land changes with their key, operation, source ordering value, and arrival context.
  • Validate schema, key presence, allowed operation types, and ordering assumptions.
  • Apply changes idempotently to a current-state target, and handle deletes explicitly.
  • Advance the checkpoint only after the target update and required checks succeed.
  • Retain enough context to replay a bounded range and investigate rejected events.

Applying changes in a warehouse

A Snowflake design may stage ordered changes and apply them with a MERGE statement. The source must be reduced to the intended single change per key for a given apply step, and deletes need an explicit target policy. Streams and tasks can support some Snowflake-native change workflows, but they do not remove the need to define correctness and recovery.

sql
MERGE INTO customer_current AS target
USING latest_customer_changes AS source
  ON target.customer_id = source.customer_id
WHEN MATCHED AND source.operation = 'DELETE' THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET name = source.name, updated_at = source.changed_at
WHEN NOT MATCHED AND source.operation <> 'DELETE' THEN
  INSERT (customer_id, name, updated_at)
  VALUES (source.customer_id, source.name, source.changed_at);

Interview questions

  • How would you distinguish a full snapshot from a CDC feed?
  • How do you make replay safe after a partial failure?
  • How should deletes be represented downstream?
  • What source metadata is needed to preserve ordering and traceability?