▣ dataarchitect.studio

Pattern

Change Data Capture Ingestion

Replicate operational data by streaming the database's own change log, instead of repeatedly querying tables for what's new.

Intent

Get every insert, update, and delete out of an operational database — reliably, in order, with low latency and near-zero load on the source — by reading the transaction log the database already writes, rather than querying tables with WHERE updated_at > ?.

Context

Analytical systems need fresh copies of operational tables. Query-based extracts miss hard deletes, hammer the source, lose intermediate states, and depend on trustworthy timestamp columns. The database’s write-ahead log has none of these problems — it is the ground truth of what changed.

Structure

OLTP database transaction log CDC reader e.g. Debezium change stream ordered, replayable MERGE into lakehouse table the log is ground truth: every change, in order, including deletes

A log reader emits each change as an ordered event into a durable stream; a sink job applies them to the analytical copy with an idempotent MERGE. Replaying the stream rebuilds the target — idempotency is what makes the pattern safe to operate.

Trade-offs

Gains: deletes and every intermediate state captured; minutes-level freshness; negligible source load; one mechanism for both streaming and batch consumers.

Costs: operational machinery (connectors, offsets, schema-change handling, snapshot bootstrapping) that must be monitored; database-specific quirks; and initial-load complexity. The capture is the easy half — the other ten steps of making the data trustworthy still apply.

When not to use it

Small tables, daily-batch freshness requirements, soft-delete-only sources, or sources you can’t get log access to: a scheduled full or incremental extract is simpler and honestly sufficient. CDC earns its machinery when deletes matter, freshness matters, or source load matters.

Common questions

What is change data capture in simple terms?

Instead of repeatedly querying a database for what changed, CDC reads the transaction log the database already writes. Every insert, update, and delete comes out complete, in order, including deletes — with almost no load on the source system.

What is log-based CDC?

The standard modern implementation: a reader (such as Debezium) tails the database's write-ahead log and emits each change as an event into a durable stream, which a sink job then merges into the analytical copy. It contrasts with query-based CDC, which polls tables and misses deletes.

When is CDC overkill?

Small tables, daily-batch freshness needs, soft-delete-only sources, or databases you can't get log access to. A scheduled incremental extract is simpler to run and honestly sufficient there. CDC earns its operational machinery when deletes, freshness, or source load genuinely matter.