Change data capture

Overview

Change data capture (CDC) is a technique that detects every insert, update and delete in a source database and delivers each one as an event to other systems. The most robust form reads the database's transaction log, which already records every change in order. Instead of asking "give me the whole table again", a consumer receives only "what changed since last time".

CDC matters because most useful data lives in operational databases, while analysis, search, caches and machine learning run elsewhere. CDC keeps those copies fresh with low delay and low load on the source. For tabular foundation models, it provides a clean way to refresh the rows a model sees, to detect drift, and to keep training and scoring data aligned with the system of record.

What it is

CDC is not a single product. It is a pattern with several implementations. The shared goal is to turn a changing table into a stream of change events.

A change event usually contains:

• the table and primary key of the row,
• the type of operation (insert, update, delete),
• the values after the change, and often the values before it,
• a position in the log or a timestamp, so order can be kept,
• sometimes transaction identifiers, so related changes can be grouped.

The four common approaches

Log-based CDC is usually preferred when it is available, because it captures everything the database commits, in commit order, without touching the application tables.

Where CDC sits in a system

A typical flow has five parts.

1. A source database such as PostgreSQL, MongoDB, SQL Server or Oracle Database.
2. A capture component that reads the log.
3. A transport such as Apache Kafka, often through Kafka Connect.
4. A processor such as Apache Flink or Spark Streaming.
5. A target: a warehouse, a lakehouse table, a search index, a cache or a feature store.

Where it came from

The idea is old. Early replication systems copied changes between databases for backup and reporting. Database vendors built log-reading replication into their products, mostly for keeping standby servers in sync. Over time the log came to be seen as a general data source, not just a recovery tool.

Several threads came together.

• Replication logs: databases wrote ordered change records for crash recovery and standby copies.
• Data warehousing: teams needed incremental loads, not nightly full reloads.
• Event streaming: log-based message systems made it natural to treat changes as ordered streams.
• Microservices: services needed to publish state changes without distributed transactions.
• Lakehouse tables: formats such as Delta Lake and Apache Iceberg added row-level merge, so streams of changes can be applied to files.

A widely cited idea from this period is that a database is really a log with an index on top. The tables are a view of the log at a point in time. CDC exposes that log to the rest of the organization.

How it works

This section follows one change from a row in a database to a row in a lake table. The everyday analogy is a shop with a till roll.

Step 1: The database writes the log first

When an application commits a transaction, the database writes the change to its transaction log before anything else. This is called write-ahead logging. The log is sequential and ordered.

Analogy: the till prints a paper roll for every sale. The roll is the true record, whatever happens to the shelves.

Step 2: The capture component reads the log

A connector attaches to the log in a supported way. In some databases it uses a replication slot or a log reader interface. It reads new records, decodes them into row changes and remembers its position.

Analogy: an accountant reads the till roll from where they stopped yesterday.

Step 3: An initial snapshot may come first

If the target is empty, the connector first takes a consistent snapshot of the existing rows, then switches to the log from the exact position where the snapshot started. This avoids gaps and duplicates.

Analogy: first count the stock on the shelves, then follow the till roll from the moment of the count.

Step 4: Changes become events

Each row change is encoded as a message. Common encodings are JSON, Avro or Protobuf. With a schema registry, the message structure is stored once and referenced by id, which keeps messages small and compatible.

A simplified update event looks like this.

{
  "source": {"table": "orders", "position": "000123/ABCD"},
  "op": "u",
  "ts": "2025-03-05T10:15:00Z",
  "key": {"order_id": 1003},
  "before": {"order_id": 1003, "amount": 89.90, "returned": 0},
  "after":  {"order_id": 1003, "amount": 89.90, "returned": 1}
}

Step 5: Events are published in order

Events go to a message broker. Each table usually maps to one topic, and the primary key is used as the message key. Messages with the same key go to the same partition, so changes to one row stay in order.

Analogy: letters for the same customer always go in the same mail bag, so they arrive in the sequence they were sent.

Step 6: Consumers apply the changes

A consumer reads the events and applies them to the target. A common pattern is an upsert (insert or update by key) and a delete. A lake format with merge support does this efficiently.

MERGE INTO lake.orders AS t
USING staged_changes AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET t.amount = s.amount, t.returned = s.returned
WHEN NOT MATCHED AND s.op <> 'd' THEN
  INSERT (order_id, amount, returned) VALUES (s.order_id, s.amount, s.returned);

Step 7: Offsets record progress

The connector records the log position it has delivered. The consumer records the message offset it has processed. If either restarts, it resumes from the stored position. This gives at-least-once delivery, and with idempotent writes the result behaves as exactly-once.

Step 8: Schema changes are handled

When a column is added to the source, the log carries a schema change, or the connector detects a new structure. The pipeline must decide how to pass it on. See schema evolution and schema drift detection.

Key concepts and terms

Soft deletes versus hard deletes

Some applications never delete rows. They set a flag such as is_deleted. Log-based CDC sees that as an update. Others delete rows for real. Then the event must be turned into a delete, or a tombstone, in the target. Query-based CDC cannot see hard deletes at all, which is its most serious limitation.

Ordering guarantees

Ordering is guaranteed within a partition, not across partitions. If two tables are related, their events may arrive in a different order than they were committed. Consumers that need cross-table consistency should use transaction markers or rebuild the join with care.

A worked example

This example builds a small incremental pipeline in Python that mimics log-based CDC in memory. It is not a production tool. It shows the logic of applying change events.

The events

events = [
    {"pos": 1, "op": "c", "key": 1, "after": {"id": 1, "city": "Leeds", "tier": "basic"}},
    {"pos": 2, "op": "c", "key": 2, "after": {"id": 2, "city": "Bonn",  "tier": "basic"}},
    {"pos": 3, "op": "u", "key": 1, "after": {"id": 1, "city": "Leeds", "tier": "gold"}},
    {"pos": 4, "op": "d", "key": 2, "after": None},
    {"pos": 5, "op": "c", "key": 3, "after": {"id": 3, "city": None,    "tier": "basic"}},
]

The apply function

def apply(events, state=None, last_pos=0):
    state = {} if state is None else state
    for e in sorted(events, key=lambda x: x["pos"]):
        if e["pos"] <= last_pos:
            continue            # already applied, makes the function idempotent
        if e["op"] == "d":
            state.pop(e["key"], None)
        else:
            state[e["key"]] = e["after"]
        last_pos = e["pos"]
    return state, last_pos

state, pos = apply(events)
print(pos)       # 5
print(state)

What happens step by step

1. Events 1 and 2 create two rows.
2. Event 3 updates row 1 from basic to gold.
3. Event 4 deletes row 2.
4. Event 5 creates row 3 with a missing city.
5. The final state holds rows 1 and 3. The position is 5.

Replaying safely

If the job crashes after event 3 and restarts with last_pos=3, then events 1 to 3 are skipped and only 4 and 5 are applied. The result is the same as an uninterrupted run. This is the idea behind idempotent consumers.

Producing a training snapshot as of a time

Because every event has a position or time, you can rebuild the table as of any earlier point.

def snapshot_as_of(events, pos_limit):
    state, _ = apply([e for e in events if e["pos"] <= pos_limit])
    return state

print(snapshot_as_of(events, 3))   # rows 1 and 2, with row 1 as gold

This ability, to reconstruct a past state exactly, is very helpful for building honest training and test sets, since a model should only see what was known at the time.

A connector configuration sketch

Below is a generic, simplified configuration. The names and hosts are placeholders and the keys vary by tool.

name: orders-capture
source:
  type: relational-log
  host: db.example.internal
  port: 5432
  database: appdb
  user: capture_user
  tables: ["public.orders", "public.customers"]
  snapshot: initial
  heartbeat_seconds: 10
sink:
  type: topic
  brokers: ["broker.example.internal:9092"]
  topic_prefix: appdb
  key: primary_key
  format: avro

A numeric look at lag

Suppose the commit time is 10:00:00.000 and the row appears in the lake table at 10:00:02.500.

• Capture delay: 0.2 seconds to read and decode.
• Broker delay: 0.3 seconds.
• Micro-batch wait: 2.0 seconds for the next write.
• Total lag: 2.5 seconds.

If the business needs data within one minute, this is fine. If it needs sub-second, the batch step must change.

A reconciliation check

Even a healthy pipeline should be checked from time to time. A simple method compares counts and a checksum over a window of keys.

-- On the source
SELECT COUNT(*) AS n, SUM(MOD(order_id, 1000003)) AS key_sum
FROM   orders
WHERE  order_date >= DATE '2025-03-01';

-- On the target
SELECT COUNT(*) AS n, SUM(order_id % 1000003) AS key_sum
FROM   lake.orders
WHERE  order_date >= DATE '2025-03-01';

If both numbers match, the window is very likely identical. If they differ, narrow the window by day, then by key range, until the missing or extra rows are found. This is the same idea as a binary search.

Handling a partial update event

Some sources emit only the changed columns. The consumer must merge them with the stored row.

def merge_partial(current, changes):
    row = dict(current or {})
    for col, val in changes.items():
        row[col] = val          # a real NULL is kept as None
    return row

current = {"id": 7, "city": "Bonn", "tier": "basic"}
print(merge_partial(current, {"tier": "gold"}))

The key point: a column that is absent from the event means "unchanged", while a column present with a null value means "set to null". Mixing these up creates false missing values.

Choosing a lag budget

Work backwards from the need.

Many teams over-engineer freshness. A lag target that fits the real use is cheaper and easier to keep reliable.

Strengths and limitations

Strengths

• Low load on the source: reading the log does not run queries on the tables.
• Completeness: inserts, updates and deletes are all seen.
• Order: events keep commit order per key.
• Freshness: changes arrive in seconds, not hours.
• Efficiency: only changed rows move, so cost scales with change volume, not table size.
• Replayability: the stream can be reread to rebuild a target.
• History: the stream can feed audit trails and point-in-time rebuilds.

Limitations

• Setup effort: needs log access, permissions and configuration at the database level.
• Log retention: if the consumer falls behind and the log is purged, a new snapshot is needed.
• Source impact if stuck: a slow consumer can force the database to keep logs, filling disk.
• Schema change handling: a new or renamed column can break a pipeline.
• Large transactions: a huge batch update becomes a flood of events.
• Cross-table consistency: related events may arrive out of sync.
• Operational complexity: more parts to monitor than a nightly export.
• Data volume: high-churn tables produce high event volume.

How it compares with alternatives

CDC versus data synchronization

Data synchronization is the wider goal of keeping copies consistent. CDC is one tool for it, alongside batch replication and reconciliation. A mature setup often combines them: CDC for freshness, plus a periodic reconciliation job that compares counts and checksums to catch silent errors.

CDC versus streaming ETL

CDC produces the raw change stream. A streaming engine such as Apache Flink or Spark Streaming can then clean, join and aggregate it. Orchestration tools such as Apache Airflow schedule the batch parts of the same system.

Role in tabular foundation models

Tabular foundation models learn from tables: rows, typed columns, missing cells and often no useful column names. CDC is not a model technique. Its role is to deliver those tables, and their changes, reliably. No specific product integration is claimed here. The points below describe how the pattern fits the common needs of such models.

Row and column structure

A CDC event carries a full row identified by a key, plus its column values. That fits the row-and-column view that tabular models use. The stream can be materialized into a current-state table, or into a history table with one row per change, depending on whether the model needs the latest values or the sequence of values.

Mixed types

Events carry numbers, strings, timestamps and sometimes nested values. Encodings such as Avro keep types explicit, which reduces the guesswork when a table is rebuilt. A common trap is that decimals, dates with time zones and large integers get turned into plain floats or strings along the way. Check types at the target before they reach a model.

Missing values

A column that was never set, a column set to NULL, and a column absent from a partial event are three different situations. Update events from some sources only include changed columns. The consumer must merge them with the previous row. Mistakes here create false missing values that a model may treat as meaningful. See imputation networks for filling real gaps, and keep false gaps from arising in the first place.

No column names

Pipelines often rename columns, for example when a column is dropped and re-added. Models that do not rely on column names cope better, but stable positions and consistent types still matter. A data catalog and schema registry record what each column means over time, and give humans the context that anonymous columns lack.

Pre-training on synthetic or real tables

Pre-training for tabular foundation models commonly uses generated tables; see synthetic data generation and TabPFN. CDC does not take part in that stage. It comes after: when the pre-trained model is applied to an organization's own tables, CDC keeps those tables current.

In-context learning

With in-context learning, a labeled sample of rows is given to the model together with the rows to predict. The quality of that sample matters a lot. A CDC-fed table can be sampled at any moment, and because it has the same keys as the source, the context can be refreshed as rows change. If a customer record is corrected, the context row can be updated before the next prediction.

Continual learning

Continual learning means adapting to new data over time without forgetting what was learned. A change stream is the natural input: it says exactly which rows are new, changed or removed. It also exposes shifts in the data. Counting events per day, tracking the share of nulls per column and watching value ranges give early warning of drift, as covered in schema drift detection.

Imputation

When values are imputed, the original and the imputed values should both be stored, with a flag. A CDC pipeline can carry imputed columns as separate fields or write them to a sibling table. Re-imputation after late-arriving updates can be triggered by the same events.

Anomaly detection

Change streams are a rich signal for anomaly detection. Unusual patterns in the change events themselves, such as a burst of deletes, a field updated many times in a minute, or an odd sequence of status changes, can reveal errors or misuse. Scoring can run in the stream processor, and flagged rows can be written back for review.

Sector and semantic classification

Records with free-text descriptions can be classified by sector or meaning as they arrive. A streaming job reads the new row event, calls the model, and writes the label and confidence to another table or event. Keeping the model version in the output allows reprocessing when the model is upgraded.

Governance

Because CDC copies data out of the source quickly, it needs controls. Mask or tokenize sensitive fields before they leave, see data tokenization. Track where each stream goes with data lineage. Agree on formats and rules with data contract validation. For deeper background on how such models read tables, see the model research and architecture pages.

Practical guidance and best practices

Design

• Start with the use case. Decide what freshness and completeness are required before choosing a tool.
• Choose keys carefully. Every captured table needs a stable primary key. Without one, updates and deletes cannot be applied.
• Capture only what you need. Selecting tables and columns reduces volume and risk.
• Include before images when needed. They help with auditing and with computing diffs.
• Plan the initial snapshot. It can be large, and it must connect cleanly to the log position.

Delivery

• Make consumers idempotent. Use upserts keyed by primary key, and ignore events older than the stored position.
• Partition by key. This keeps per-row order.
• Use a schema registry. It makes format changes safe and visible.
• Keep a dead-letter path. Bad events should be set aside with an error note, not silently dropped.
• Handle deletes explicitly. Decide between hard delete, soft delete and history in the target.

Operations

• Monitor lag. Alert when the delay exceeds the agreed limit.
• Monitor the source log retention. A stuck consumer can fill the source disk.
• Reconcile regularly. Compare row counts and checksums between source and target.
• Test failure. Stop and restart the connector, the broker and the consumer, and check there are no gaps or doubles.
• Version the configuration. Treat connector settings like code.

Data quality for modeling

• Keep event time and processing time as separate fields.
• Build training sets as of a fixed position, so labels and features line up in time.
• Avoid leakage by joining only data that was available at the prediction time.
• Record which stream positions went into each training run.

Security

• Use a dedicated capture account with the least privileges.
• Encrypt connections and store secrets outside code.
• Mask personal fields early.
• Limit who can read raw topics.

Common mistakes

1. Using timestamp polling and expecting deletes. Deleted rows never show up, so targets drift.
2. Skipping the snapshot-to-log handoff. Gaps or duplicates appear if the start position is wrong.
3. Ignoring log retention. A long outage can leave the connector unable to resume.
4. Non-idempotent consumers. Reprocessing after a restart creates duplicate rows.
5. No primary key. Updates cannot be matched to rows.
6. Assuming global order. Order holds per key and partition, not across tables.
7. Treating partial update events as full rows. Missing columns become false NULLs.
8. Letting schema changes break the job silently. Add alerts and compatibility rules.
9. Never reconciling. Small silent losses add up. Compare counts and checksums.
10. Copying sensitive data unmasked. Add masking before data fans out.
11. Building training sets from the latest state only. This leaks future information into past rows.
12. Forgetting time zones. Convert and store timestamps consistently, preferably in UTC.

Frequently asked questions

What is change data capture in simple terms?

Change data capture is a way of noticing every change made to a database and passing each change on as a small message. Instead of copying a whole table again and again, you send only the rows that were added, changed or removed. This keeps other systems up to date quickly and cheaply.

What is the difference between log-based CDC and trigger-based CDC?

Log-based CDC reads the database's own transaction log, so it adds almost no work to the application's writes and sees everything that was committed. Trigger-based CDC attaches code to each table that records changes in an extra table, which works without log access but slows down every write and needs upkeep.

Does CDC capture deletes?

Log-based and trigger-based approaches do capture deletes, because the delete is a recorded operation. Query-based approaches that look for newer timestamps do not, since a removed row leaves nothing behind to find. If deletes matter, choose a log-based method or use soft deletes with a flag column.

How does CDC guarantee that no changes are lost?

It relies on the ordered log and stored positions. The capture component remembers how far it has read, and the consumer remembers how far it has applied. After a failure, both resume from those positions. Combined with idempotent writes and regular reconciliation checks, this gives a dependable result.

Is CDC the same as data replication?

They overlap but are not identical. Replication usually means keeping a copy of the same kind of database, often at the physical level. CDC produces a logical stream of row changes that can go to any kind of target, such as a lake, a search index or a cache. CDC is one way to achieve replication and wider data synchronization.

What happens when the source schema changes?

The pipeline needs a rule. Adding a nullable column is usually safe. Renaming or dropping a column may break consumers. Good practice is to use a schema registry with compatibility checks, to alert on drift, and to test changes in a staging environment before they reach production.

How much delay does CDC add?

Typical delays range from under a second to a few seconds, depending on the log reader, the broker and how often the target is written. Batching into files or table commits can raise it to tens of seconds. The delay should be measured as lag, from commit time to target availability, and monitored.

Can CDC be used for machine learning features?

Yes. A change stream can update a feature table or feature store as source rows change, so models score on fresh data. It also lets you rebuild features as of a past time for honest training. Care is needed to avoid leakage and to keep the same logic in training and in serving.

What is the outbox pattern?

In the outbox pattern an application writes a business event into a special table in the same transaction as its normal data change. A CDC process then reads that table and publishes the events. This avoids the problem of updating a database and a message system separately, where one could succeed and the other fail.

How does CDC relate to tabular foundation models?

CDC does not train or run those models. It keeps the tables they read fresh, supports sampling at a known point in time, and exposes drift. You can read more about how such models consume tables in the model research and benchmarks pages.

Glossary

• After image: the row values after a change.
• At-least-once delivery: every event arrives, possibly more than once.
• Backfill: loading historical data into a target.
• Before image: the row values before a change.
• Broker: the system that stores and delivers messages.
• Capture: the act of reading changes from the source.
• Compaction: keeping the latest event for each key.
• Dead-letter queue: a place for events that could not be processed.
• Idempotent: producing the same result when repeated.
• LSN / SCN: log or change numbers that mark positions.
• Lag: delay between source commit and target availability.
• Offset: position of a message within a topic partition.
• Outbox: a table used to publish events from an application.
• Partition: a slice of a topic that preserves order.
• Replication slot: a bookmark that retains log data for a reader.
• Snapshot: a consistent copy of existing rows.
• Tombstone: a message that marks a deleted key.
• Upsert: insert or update by key.
• WAL: write-ahead log.

Summary

Change data capture turns a database's transaction log into a stream of row-level events. It keeps lakes, caches, search indexes and feature tables in sync with low delay and low source load, and it can see deletes that simple polling misses. Its main costs are setup effort, careful handling of schema changes, and the need to monitor lag and log retention.

For tabular foundation models, CDC supplies fresh, keyed, typed rows, supports point-in-time rebuilds for honest evaluation, and surfaces drift and anomalies in the data they consume. Pair it with data synchronization checks, a schema registry, masking and lineage, and read more in the model benchmarks pages.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: