Ensuring Exactly Once Delivery in CDC

Implement checkpointing and offset management to guarantee that every database change is processed exactly once by the downstream consumer.

In this lesson, you will learn how to synchronize database transaction logs with downstream storage to ensure every change is processed exactly once, avoiding duplicates or data loss.

The Challenge of Distributed State

When performing Change Data Capture (CDC), we extract events from a database log and push them into a stream (like Kafka). The "Exactly Once" challenge arises because we have two distributed systems: the source database and the stream processor. If the stream processor crashes after consuming a record but before committing its state, a naive system might re-process that record upon restart, leading to duplicates.

Managing Offsets and Checkpoints

To achieve exactly-once semantics, we must ensure that the "checkpoint" (the record of how much data we have processed) and the "output" (the action of writing the record) happen atomically.

In a CDC pipeline, this is handled through offset management. Instead of relying on the system clock, we track the specific position in the database transaction log—the Log Sequence Number (LSN) or Binary Log Position.

Consider this logic:

Source: Read change event from the database log.
Transform/Sink: Write the record to the target system.
Commit: Store the database LSN inside the target system’s transaction, or use a distributed transaction coordinator to commit both the record and the offset simultaneously.

If your sink is an external database, you can ensure exactly-once delivery by writing the CDC event and the offset into the same atomic transaction:

BEGIN;
  -- Insert the data from the CDC event
  INSERT INTO target_table (user_id, action) VALUES ('123', 'update');
  
  -- Update the offset table to mark this LSN as processed
  UPDATE cdc_offsets SET last_processed_lsn = '0000456' WHERE source_db = 'users_db';
COMMIT;

By storing the LSN alongside the data, your application can check the cdc_offsets table upon restart. If the database reports the LSN has already been processed, the application knows to ignore the duplicate event sent by the CDC connector. This coupling of data and state ensures that even if a failure occurs mid-stream, the system resumes from the exact point of the last successful commit.

Key Takeaway: Exactly-once delivery in CDC is achieved by atomically committing the processed data alongside its corresponding source log offset within the downstream destination.