cdc-starter

Change data capture from PostgreSQL to ClickHouse, with Debezium, Redpanda and dbt, on a laptop, in one command. It is the smallest version of the pattern I use, with the parts that usually go missing put in from the start: deletes, ordering by log position, lag measured against a heartbeat, and a check that the warehouse really matches the source.

make demo

That starts the stack and registers the connector. It writes a few inserts, updates and a delete to PostgreSQL, waits until ClickHouse shows exactly the same orders, then builds and tests the dbt models and runs the checks. Run it again and it still passes.

You need Docker with Compose v2, make and curl. The containers are capped at about five gigabytes of memory in total.

How data moves

flowchart LR
  PG[(PostgreSQL<br/>app.orders)] -->|WAL, pgoutput| DBZ[Debezium<br/>Kafka Connect]
  DBZ -->|cdc.app.orders| RP[(Redpanda)]
  RP -->|Kafka engine| RAW[(ClickHouse raw.orders<br/>ReplacingMergeTree)]
  RAW -->|FINAL, deletes removed| STG[dbt staging.stg_orders]
  STG --> MART[dbt marts.orders_by_status]
  HB[app.cdc_heartbeat] -.->|every few seconds| DBZ

Loading

1. Source. app.orders in PostgreSQL with logical replication on. A trigger moves updated_at on every change. The publication lists only the tables meant for capture, and Debezium connects as its own role with replication and read access only.
2. Capture. Debezium reads the write-ahead log through pgoutput and publishes flattened rows to Redpanda. Each event carries the operation, the log position (LSN) and a delete flag.
3. Landing. A ClickHouse Kafka engine table reads each message as one raw JSON string. A materialized view types it and writes raw.orders, a ReplacingMergeTree versioned by the LSN, so the latest change in the source wins rather than the latest to arrive.
4. Models. dbt reads the current state through one macro, FINAL plus the delete filter, into staging.stg_orders, then builds marts.orders_by_status. Tests cover keys, accepted values and a check that no deleted order ever reaches staging.
5. Checks. make check reconciles the source and the warehouse, measures lag against the heartbeat, and asks Kafka Connect whether the connector and its task are running.

The checks, and what they catch

make check

Check	Catches
Connector and task RUNNING, from the Kafka Connect API	A failed or paused connector, even when the containers are up
Row counts agree between source and warehouse	Lost or extra rows
Every order has the same state in both	Changes that have not arrived yet, even when the counts happen to match
Lag against the heartbeat, under a limit	A connector that has stopped keeping up, even when no business table is changing

Try it. Pause the connector, change the source, and run the checks:

set -a; . ./.env; set +a
curl -X PUT "http://127.0.0.1:${CONNECT_HOST_PORT}/connectors/cdc-starter/pause"
docker compose exec -T postgres psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" \
  -c "UPDATE app.orders SET status = 'cancelled' WHERE order_id = 2;"
make check      # fails: connector paused, order states differ
curl -X PUT "http://127.0.0.1:${CONNECT_HOST_PORT}/connectors/cdc-starter/resume"
make check      # passes again once the change arrives: nothing was lost

With the connector paused and nothing written, the counts still agree. Only the connector check and the heartbeat lag notice. That is why the checks are layered.

Design choices

• An explicit publication and a narrow role. Debezium is told not to create its own publication, so only the listed tables are captured. Its role has replication, read access and the one update the heartbeat needs.
• Order by log position, not arrival. _version is the PostgreSQL LSN, so a late or redelivered event cannot overwrite a newer one.
• Deletes are rows. The connector rewrites a delete as a row with __deleted = 'true', and ReplacingMergeTree(_version, _is_deleted) hides it under FINAL. Without this, a delete either vanishes or comes back as an empty row.
• Raw JSON first. The Kafka engine reads a string and the view types it, so a malformed message is skipped instead of stalling the consumer.
• The current-state read lives in one macro. Forgetting FINAL gives duplicates, and forgetting the delete filter brings back deleted rows. Neither raises an error, so no model writes them by hand.
• A heartbeat for lag. Debezium updates app.cdc_heartbeat on a timer, which measures lag even when the business tables are quiet and keeps the replication slot moving.
• The slot survives restarts. slot.drop.on.stop is false, so a restart resumes from the recorded position. max_slot_wal_keep_size stops a forgotten slot from filling the disk, and make down removes everything, the slot included.

Adapt it to your own tables

1. Add the table in postgres/init.sh with a primary key, add it to cdc_pub, and grant the CDC role SELECT on it.
2. Add it to table.include.list in connect/postgres-source.json.
3. In clickhouse/init.sql, copy the orders block: a Kafka engine table for cdc.<schema>.<table>, a landing table ordered by your key, and a materialized view that extracts your columns.
4. Declare it as a dbt source, read it through current_state, and add tests for its grain.
5. Extend scripts/check.sh to reconcile it.

Start again from scratch with make down then make demo, because the init scripts run only on empty volumes.

Commands

Command	What it does
make demo	Everything: start, register, change data, build and test dbt, check
make up	Start the stack and register the connector
make dbt	Build and test the dbt models
make check	Reconcile, measure lag, check connector health
make stop	Stop the containers and keep the data
make down	Remove the containers and all data

Ports bind to 127.0.0.1 only and are set in .env: PostgreSQL 45432, Redpanda 49092, Kafka Connect 48083, ClickHouse HTTP 48123. Use letters, digits and dashes in the .env passwords; the connector config is filled in with sed.

Where it comes from

It is the core of two larger public projects of mine:

• wb-cdc-analytics, with Airflow, Prometheus and alert runbooks;
• kiva-microfinance-loan-cdc-analytics, with Dagster and a reconciliation monitor.

The CDC pipeline checklist and the step-by-step guide explain each choice.

Licence

MIT. See LICENSE.