System Design: [CDC pipeline name]

Reference design for a change data capture (CDC) pipeline that turns the committed changes of an operational database into an ordered stream of change events, and delivers them to a search index, a cache and a data warehouse. It covers log-based capture, an initial snapshot interleaved with the live log, ordering per key, schema evolution, the transactional outbox pattern, and replay and backfill. Concepts draw on the DBLog watermark approach (Andreakis and Papapanagiotou, arXiv 2010.12597, 2020) and on the concepts documented by Debezium 3.7; the text and the worked design are original. It follows the structure of the System Design Document template. Replace every [bracketed] value and every row marked Example.

Document control

Approvals

Revision history

Contents

1. Overview
2. Requirements
3. Capacity estimation
4. API design
5. Data model
6. High-level design
7. Detailed design
8. Scalability and performance
9. Reliability, security and operations
10. Trade-offs and alternatives
11. Delivery plan and risks
12. Appendices

1. Overview

1.1 Problem statement

[Example: The order service writes to PostgreSQL and then, in application code, updates the search index, deletes cache entries and appends to a nightly export for the warehouse. These dual writes are not atomic: when the service crashes between the database commit and the index update, search shows stale orders for days. The nightly export misses rows changed during the export and the warehouse lags by a full day.]

1.2 Proposed solution

[Example: A CDC connector reads the database's write-ahead log through a logical replication slot and publishes one change event per committed row change to a partitioned log, keyed by the table's primary key. Existing rows are captured by a chunked snapshot that runs alongside the live log and is merged with it using low and high watermark markers, so no table locks are needed and the snapshot can pause and resume. Schemas are registered with a compatibility check. Independent consumers update the search index with version-checked upserts, invalidate the cache, and append raw changes to the warehouse, which merges them into tables. Other teams receive domain events through an outbox table captured by the same connector.]

1.3 Decisions requested from reviewers

1.4 45-minute interview path

2. Requirements

2.1 Core functional requirements

2.2 Non-functional requirements

2.3 Below the line (out of scope)

• [Example: Multi-master or bidirectional replication between databases]
• [Example: Cross-key or cross-table transactional delivery to consumers; consumers see per-key order only]
• [Example: Capturing changes made outside the database log (bulk file loads that bypass it)]
• [Example: Real-time joins across tables; done by stream jobs (system-design/design-stream-processing-engine)]
• [Item deferred, with the release that will take it]

3. Capacity estimation

3.1 Assumptions

3.2 Throughput and partitions

At a target of 5 MB/s per partition, 8 partitions carry the peak; doubling for growth gives 16 partitions per high-volume table topic. Consequence: partition count is set once with headroom, because adding partitions later changes the key-to-partition mapping and breaks per-key ordering across the change. Consumers can scale to 16 instances per topic.

3.3 Log storage

Consequence: 7-day replay costs about 22 TB of log storage; longer history comes from compacted topics (latest value per key) plus the warehouse raw change table, not from longer log retention.

3.4 Source log retention while the connector is down

The replication slot keeps WAL until the connector confirms it:

Consequence: a connector outage of two hours at peak fills more than 200 GB of the primary's disk. Set max_slot_wal_keep_size (PostgreSQL 13 and later; default -1, unlimited) to a cap the disk can absorb, alert well before it, and keep a runbook for re-snapshot when the cap invalidates the slot.

3.5 Snapshot duration

with $k = 4$ parallel table workers. Consequence: a full snapshot is an overnight job, so it must be resumable from the last completed chunk, run alongside live streaming, and throttle itself on primary CPU and replica lag.

3.6 Summary

4. API design

4.1 Change event envelope

Every event has a key (the primary key) and a value envelope. Field names below are this design's own; Debezium documents a similar envelope with before, after, source and op.

{
  "key": {"order_id": 918273},
  "value": {
    "op": "u",
    "before": {"order_id": 918273, "status": "PAID", "total_minor": 125000, "updated_at": "2026-10-22T09:12:03Z"},
    "after":  {"order_id": 918273, "status": "SHIPPED", "total_minor": 125000, "updated_at": "2026-10-22T10:40:51Z"},
    "source": {"db": "orders", "table": "orders", "lsn": "2F/7A3C1B80", "tx_id": 88123401, "commit_ts": "2026-10-22T10:40:51.213Z", "snapshot": false},
    "schema_id": 42
  }
}

4.2 Control operations

Snapshot requests can also arrive as rows in a signal table inside the source database, which lets the request travel through the log in order with the data (a mechanism Debezium documents for incremental snapshots).

4.3 Consumer contract

• Events for one key arrive in commit order; events for different keys have no order guarantee.
• Delivery is at least once: consumers must be idempotent, using source.lsn (or the outbox event ID) as a version or deduplication key.
• A snapshot r event for a key may arrive after c or u events for the same key only if it reflects a newer or equal state (guaranteed by 7.2).
• Tombstones mean the key no longer exists.

5. Data model

5.1 Entities

The pipeline's own state is small: offsets, snapshot progress and schemas. The change events themselves live in the log and the stores downstream.

5.2 Storage choices

5.3 Access patterns

6. High-level design

7. Detailed design

7.1 Deep dive: how to capture changes

Choice: D. Reason: the log is already the database's source of truth for committed changes, so reading it removes the dual-write gap without adding work to every transaction. Requirements on the source: logical replication enabled, a dedicated slot and publication, primary keys on every captured table, and a replica identity that includes the before image where consumers need it.

7.2 Deep dive: initial snapshot interleaved with the log

The log only holds recent changes, so existing rows need a snapshot, and the snapshot must not overwrite a newer change that the log already delivered.

Choice: C for both initial load and repairs. Reason: it keeps log latency within NFR-01 during a 14-hour snapshot, survives restarts by chunk, and the same mechanism serves targeted backfills (7.6).

Why it is safe: any key that changed inside the window is delivered by the log event, which is at least as new as the selected row; any key that did not change inside the window has the same value in the chunk as in the database at the high watermark. The selection must read committed data at least as new as the low watermark write, so run it on the primary (or on a replica only after confirming it has replayed past the low watermark).

7.3 Deep dive: ordering and idempotent delivery

Choice: B, with transaction metadata on every event for consumers that need to group. Reason: all three consumers apply changes per key, and per-key order with 16 partitions meets throughput.

Delivery is at least once: after a connector crash it resumes from the last committed offset and republishes some events. Consumers stay correct by version-checking:

• Search: index with external version = numeric LSN; an older or equal version is rejected, so duplicates and reordering after replay are harmless. Snapshot r events carry the LSN of their chunk's high watermark as the version, so they never overwrite a newer logged change.
• Cache: always delete (never set) on change; a duplicate delete is harmless.
• Warehouse: append raw changes; the merge picks the row with the highest (lsn, event order within transaction) per key and ignores duplicates.

Handle primary-key changes as a delete of the old key plus an insert of the new key, so they stay ordered in their own partitions.

7.4 Deep dive: schema evolution

Choice: B with backward-compatible mode for table topics and C for outbox contracts. Reason: table schemas change often and mostly additively; cross-team contracts deserve explicit versions. Rules:

• Adding a nullable column or a column with a default is allowed.
• Renaming or dropping a column needs a two-step migration: add the new column, backfill, switch consumers, then drop the old one.
• Changing a column type is a breaking change: new column or new topic.
• DDL runs through review that checks the registry first; the connector stops on an incompatible change rather than publishing it.

7.5 Outbox pattern for domain events

Table changes describe storage, not business meaning, and expose internal columns to other teams. For cross-team events the service writes an outbox row in the same transaction as the business change; the connector captures the outbox table and routes rows to topics by aggregate type, keyed by aggregate ID, so events for one aggregate keep their order.

The outbox removes the dual write without a distributed transaction. It does not make consumers exactly-once: they still deduplicate on event_id. See Event Sourcing and CQRS (architecture/event-sourcing-cqrs) when the events themselves become the system of record.

7.6 Replay and backfill

7.7 How real systems do it

How real systems do it (paraphrased, not copied). Andreakis and Papapanagiotou describe Netflix's DBLog in "DBLog: A Watermark Based Change-Data-Capture Framework" (arXiv 2010.12597, October 2020). The framework reads the transaction log and, to capture full table state, selects rows in primary-key chunks while log processing continues. A dedicated single-row watermark table is updated before and after each chunk select; when the reader sees the matching events in the log, it drops chunk rows whose keys changed between the two markers and emits the remaining rows at the closing marker, so a selected row never overrides a newer logged change. Chunk progress is stored externally so selects can pause and resume, and they can target all tables, one table or specific keys. The paper reports production use across many Netflix services. Debezium 3.7 documents a comparable incremental snapshot driven by a signal table. This template reuses the idea, not the text.

8. Scalability and performance

Freshness budget (NFR-01, Example): commit to WAL flush under 10 ms, logical decoding and publish under 1 s, log replication and acknowledgement under 500 ms, leaving more than 3 s of headroom for consumer polling.

9. Reliability, security and operations

9.1 Failure-mode table

9.2 Security and privacy

9.3 Observability

10. Trade-offs and alternatives

11. Delivery plan and risks

11.1 Milestones

11.2 Rollout and rollback

[Example: Run CDC consumers alongside the existing dual writes, compare outputs, then disable dual writes per consumer behind a flag. Rollback re-enables dual writes; the CDC pipeline keeps running so it can resume.]

11.3 Risks

12. Appendices

12.1 Open questions

12.2 Glossary

12.3 Related templates

• Message Queue System Design (system-design/design-message-queue): the partitioned log that carries change events.
• Distributed Cache System Design (system-design/design-distributed-cache): consumer that invalidates on change.
• Stream Processing Engine System Design (system-design/design-stream-processing-engine): joins and aggregations over change streams.
• Event Sourcing and CQRS (architecture/event-sourcing-cqrs): when events become the system of record.
• Data Pipeline Specification (ai-data/data-pipeline-spec): contracts and quality checks for the warehouse load.

12.4 References

Standard and licence note

Based on the publicly described DBLog technique and on concepts in the Debezium 3.7 and PostgreSQL 18 documentation cited in 12.4; no standard governs CDC pipelines. This is a reference design to adapt, with illustrative numbers marked Example. Original text written for this collection; sources were used as inspiration only and no text, figures or diagrams were copied. Check the current documentation of your database and connector, because replication settings and connector options change between releases. Template licence: MIT.