Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 39 ยท Projects

Project: Database Sync with Change Events

Turn every row change in a database into a Kafka event, keep a compacted topic as a materialized view and rebuild a lost copy by replaying it, with runnable stand-ins.

Most companies keep the same facts in several places: a main database, a search index, a cache, an analytics store. Keeping them in step with scheduled copy jobs is slow and fragile. Change data capture (CDC) solves it differently. Every insert, update and delete in the source database becomes an event on a Kafka topic, and every other system builds its own copy by reading that topic.

In this project you follow one small customers table from the first row to a rebuilt copy. You will design the change events, see why the primary key is the record key, see how a compacted topic becomes a table (a materialized view), and replay the topic to rebuild a store that was lost.

Real versus stand-in. The connector configuration below is real Kafka Connect usage with the Debezium PostgreSQL connector, shown read-only because the site has no database or cluster. The TryIt blocks are hand-written JavaScript stand-ins: a Map plays the database, an array plays one topic partition, and a small function plays the log cleaner. They show the logic of CDC, not the real connector.

Change data capture

Database Every committed change is in its transaction log.
Connector Reads that log and writes events.
Compacted topic Keeps the newest event per row.
Consumers Search, cache and analytics build copies.
1 / 2

Step 1: why not write to both?

The tempting design is for the application to save a customer in the database and then also send an event to Kafka. Two separate writes cannot be atomic. If the process dies between them, or the second one fails, the database says one thing and the topic says another, and nobody notices. This is the problem the outbox pattern in Event-Driven Patterns addresses from the application side.

CDC attacks it from the other side. The application writes only to its database. The database already records every committed change in its own transaction log (the write-ahead log in PostgreSQL, the binary log in MySQL) so that it can recover and replicate. A CDC connector reads that log and turns each change into an event. Nothing can be committed without being captured, and nothing is captured that was not committed.

Step 2: the shape of a change event

A good change event says what happened and carries enough data for any consumer. The widely used shape, which Debezium follows, is an envelope with an operation code and the row before and after the change:

JSON
{
  "op": "u",
  "before": { "id": 1, "name": "Ana", "city": "Lisbon" },
  "after":  { "id": 1, "name": "Ana", "city": "Porto" },
  "ts_ms": 1767225600000
}

The operation is c (create), u (update), d (delete) or r (read, used for the initial snapshot of rows that already existed). For a create, before is null; for a delete, after is null.

The record key is the row's primary key, for example {"id": 1}. This one choice gives you the two properties you need:

The stand-in below plays the database. Each write updates a Map and appends the matching event to the topic. A delete appends the delete event and then a tombstone, a record with the same key and a null value, which tells compaction that the row is gone.

JS Step 2: a database that emits change events

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Seven writes produced eight records, because the delete wrote two. The topic contains the whole history of the table, including row 3, which no longer exists.

Step 3: the real connector

On a real cluster you do not write the code above. You register a source connector with Kafka Connect (see Kafka Connect). A sketch for PostgreSQL with the Debezium connector, sent to Connect's REST API:

JSON
{
  "name": "customers-cdc",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "db.internal",
    "database.port": "5432",
    "database.user": "cdc_reader",
    "database.password": "${file:/etc/secrets/cdc.properties:password}",
    "database.dbname": "shop",
    "topic.prefix": "shop",
    "table.include.list": "public.customers",
    "plugin.name": "pgoutput",
    "snapshot.mode": "initial"
  }
}

The connector first takes a snapshot of the existing rows (events with op set to r), then switches to streaming new changes from the log. Events for public.customers land on a topic named from the prefix, schema and table, here shop.public.customers. Give the connector a database user with only the privileges it needs, and keep the password in a secrets provider as shown (see Security and Quotas). The exact option names differ between connector versions and databases, so check the documentation of the version you run.

On that topic you set cleanup.policy=compact, so Kafka keeps at least the newest event for every key and the topic does not grow forever.

Step 4: a compacted topic is a table

Compaction keeps the latest record per key and drops older ones. Applied to a change topic, what remains is the current state of every row, which is exactly a table. A consumer that reads the topic from the beginning and applies each event (set on create or update, remove on delete or tombstone) ends with the same rows as the source database.

The next stand-in compacts the topic from step 2 and builds the view twice, once from the full history and once from the compacted topic. Both must agree.

JS Step 4: compaction and the materialized view

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Eight records shrank to two, and the view is identical. Row 3 vanished completely: its newest record is a tombstone. A real broker keeps a tombstone for a while (delete.retention.ms, one day by default) so that slow consumers can still see the delete before it is cleaned up, and it only compacts old segments in the background, never the active one. The stand-in compacts instantly to keep the idea clear. Importantly, compaction never changes the order of the records that remain, and never touches the latest record of a key.

A compacted topic must not be used when you need every intermediate change, such as an audit trail of who changed what. Compaction deletes the history. Use a normal retention-based topic for that, or write history to a separate store.

Step 5: replay to rebuild

The payoff of the design is that every consumer owns its own copy and can recreate it at any time. If the search index is corrupted, or you add a new service next year, you do not ask the source database to export data. You start a consumer with a fresh group id (or reset the offsets to the beginning, see Offsets and Commits) and read the compacted topic from offset 0.

Replay makes one rule important: the consumer's writes must be idempotent. Applying an event as "set row 1 to this value" or "delete row 2" gives the same result however often it runs. Appending to a list, or incrementing a counter, would not. Upserts and deletes by primary key are naturally safe, which fits at-least-once delivery (see At-Most-Once, At-Least-Once, Exactly-Once).

JS Step 5: rebuild a lost copy by replaying

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Read the output from the top. The empty index is rebuilt from three records. Re-delivering two of them changes nothing, which is why a consumer crash is harmless here. The per-city view is a second consumer with a different shape, created without touching the source database. Finally a live delete removes row 2 from the index.

Step 6: things to plan for

  • Schema changes. When someone adds a column, events change shape. Use a schema registry and compatible evolution (see Schemas, Avro, Protobuf and JSON).
  • Ordering across tables. Order is guaranteed per row (per key), not across different tables or rows. Do not build a consumer that assumes a customer event arrives before an order event that refers to it; handle a missing parent by retrying.
  • Initial load size. The snapshot of a big table produces many events at once. Watch consumer lag while it runs (see Monitoring, Lag and Troubleshooting).
  • Sensitive data. Events contain whole rows. Mask or drop columns such as passwords and card numbers before they reach Kafka, for example with a single message transform in Connect, and restrict who may read the topic.
  • Sink side. A sink connector (for example to a search engine or another database) can apply the same upsert logic for you, so often no custom consumer is needed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is a table's primary key used as the record key of its change topic?

    Equal keys go to the same partition, which preserves per-row order, and compaction works per key.
  2. 2. What is a tombstone in a change topic?

    The delete event is followed by a record with the same key and a null value. Compaction later removes the key, and consumers delete their copy.
  3. 3. Why must a consumer that rebuilds a view from a replay write idempotently?

    With at-least-once delivery an event can be seen twice. Upserts and deletes by primary key give the same state no matter how often they are applied.
  4. 4. When should you NOT use a compacted topic for change events?

    Compaction discards older events for a key, so the history is lost. Use retention-based storage for audit needs.

Summary

  • CDC reads the database's transaction log, so there is no dual write and no change can be missed.
  • A change event carries the operation, the row before and after; the primary key is the record key.
  • A delete produces a delete event and then a tombstone with a null value.
  • A compacted topic keeps the newest event per key, so it is a materialized view of the table.
  • Any number of consumers can build or rebuild their own copy by replaying from offset 0, as long as they apply events idempotently.
  • Compaction loses history, so keep an audit trail somewhere else if you need one.

Next, combine everything you know in one program with Project: Build a Mini Kafka Broker.