# What is change data capture?

Copying only the rows that were added, changed or deleted in a source database, read from its change log, instead of copying the whole table again.

- Page: https://tecyfy.com/concepts/change-data-capture
- Subject: Data engineering in the AI era (https://tecyfy.com/concepts)
- Level: intermediate
- Also called: CDC

The simplest way to copy a table is to copy all of it, every time. That works until the table holds years of orders and only a few thousand changed since yesterday. Then most of the copy is wasted work, and the copy is hours out of date by the time it lands.

Change data capture, or CDC, copies only what changed: each insert, update and delete, in the order it happened.

## It reads the log the database already keeps

Every serious database writes each change to a log before it changes the table, so it can recover after a crash. PostgreSQL calls this the write-ahead log; MySQL keeps a binlog. CDC reads that log instead of the tables.

PostgreSQL turns its log into readable changes through **logical decoding**, which presents every change to a table in a form you can read without knowing the database's internals. A **replication slot** is one stream of those changes, replayed to a reader in the order they were made. Debezium, an open-source CDC tool, reads the MySQL binlog and the PostgreSQL replication stream and, in its usual setup on Kafka Connect, sends each table's changes to its own topic.

Reading the log has a gain over querying the tables: it sees deletes, which a query for "rows changed since yesterday" never finds, because the deleted row is no longer there to find.

(An interactive demo goes here on the page: https://tecyfy.com/concepts/change-data-capture)

## What a change looks like

Each change says what happened to which row. Delta Lake's change data feed, for example, marks every change as an insert, a delete, or an update recorded twice: the row before the change and the row after it, each with the version of the table it belongs to.

The copy can then keep only the latest state of each row, or keep the history. With history tracking, the old version of a row is kept and the new one added, with columns marking when each was true. That lets you ask what an order looked like last Tuesday. A full reload from scratch throws that history away.

## The trap: a slot nobody reads

A replication slot keeps every change until its reader has taken it. That is what makes CDC safe after an outage, and also its main danger. A slot whose pipeline was deleted, or stopped weeks ago, keeps holding log files, and the database's disk fills. PostgreSQL's own documentation warns that in extreme cases this can make the database shut down to protect itself.

So the setup for a Databricks PostgreSQL connector asks you to drop a slot by hand when its pipeline is removed, and to cap how much log a slot may hold.

## Where it shows up

On Databricks, the Lakeflow Connect database connectors ingest from MySQL, PostgreSQL and SQL Server using CDC. The same idea runs further down a lakehouse: a table's change data feed lets the next step read only what changed.

It matters for AI because deletes matter. When a document is withdrawn, an assistant should stop quoting it. A Databricks AI Search index that syncs from a table needs that table's change feed, so a deleted row leaves the index too. An [embedding pipeline](https://tecyfy.com/concepts/embedding-pipeline) without CDC either reprocesses everything or quietly keeps answering from text that no longer exists.

## Related

- [Data Pipeline](https://tecyfy.com/concepts/data-pipeline): A set of steps that moves data from where it is made to where it is used, cleaning and reshaping it on the way, run on a schedule or as data arrives.
- [Lakehouse](https://tecyfy.com/concepts/lakehouse): One store for all of an organisation's data: cheap files in cloud storage, as in a data lake, with the tables, transactions and fast queries of a data warehouse on top.
- [Embedding Pipeline](https://tecyfy.com/concepts/embedding-pipeline): The pipeline that turns source documents into searchable vectors: it reads new and changed content, chunks it, embeds each chunk and keeps the vector index in step with the source.

## Sources

- [Logical Decoding Concepts](https://www.postgresql.org/docs/current/logicaldecoding-explanation.html)
- [Debezium Architecture](https://debezium.io/documentation/reference/stable/architecture.html)
- [Standard connectors in Lakeflow Connect](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/)
- [Configure PostgreSQL for ingestion into Databricks](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/postgresql-source-setup)
- [Enable history tracking (SCD type 2)](https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/scd)
- [Use Delta Lake change data feed](https://docs.databricks.com/aws/en/delta/delta-change-data-feed)
- [Databricks AI Search](https://docs.databricks.com/aws/en/vector-search/vector-search)