Change data capture (CDC) is a way of copying data that reads each change out of a database the moment it is written, and sends it onward. With it, a warehouse or another system is only seconds behind the source database, instead of a night behind.
How does CDC see each change? Every serious database keeps a log. Before it changes any data, it first writes the change into this log, so that it can recover after a crash. PostgreSQL calls it the write-ahead log, and MySQL calls it the binary log. CDC software reads this log, not the tables themselves. Each entry becomes one change event: a row was added, changed or deleted, with the values before and after, and the time.
CDC starts with one full copy of the tables, called a snapshot. After that, it sends only the changes, in the order they happened. It can send them to event streaming, from where many systems can read them. Or it can apply them straight to a data warehouse.
Reading the log has two advantages over asking the database, on a schedule, for rows that have changed. First, it adds little load, because the database writes the log anyway. Second, it sees deleted rows. A scheduled query cannot find a row that no longer exists, so the warehouse would keep it for ever. The page on batch and stream processing compares the two ways of copying.
Here is a typical case. A gold loan company in Thrissur has 900 branches, and its loans sit in a relational database. At 11:02:05 a.m., a customer repays a ₹60,000 loan at one branch, and the branch releases her gold chain. The database marks the loan as closed. At 11:02:06, the CDC software reads that change from the log. By 11:02:09, the warehouse shows the loan as closed too. The risk team's report of how much pledged gold each branch holds is now correct within seconds. Before CDC, the warehouse was copied once a night. So all day, it showed gold that the branches had already returned to their owners.
CDC works with document databases as well. Many of them publish a stream of changes of their own.
How is CDC different from an ETL and ELT tool? An ELT tool usually copies data from many kinds of source, including apps, on a schedule. CDC copies from databases only, but continuously. Many ELT tools now use CDC for their database sources.