Data Engineering · Episode 03 · 2:06
Change data capture explained: why your nightly job misses updates
Six changes happened in a day. A nightly snapshot diff saw 3 and missed paid, cancelled and shipped; a change log caught all 6, and replaying it made the target match the source. Triggers stand in here for the log Debezium reads.
What you’ll needPython 3.10+
What you’ll need
- python.org/downloads →
Python 3.10 or newerRuns the code. Check yours with python3 --version. - code.visualstudio.com →
VS Code or CursorEither works; so does any editor you like. - git-scm.com/downloads →
gitGets the code from GitHub. - No packages needed: it uses only the Python standard library.
Set it up
- 1Get the code
git clone https://github.com/DayanEbrar0X/data-anatomy.ai.git cd data-anatomy.ai
- 2Make a virtual environmentIt keeps this project's packages separate from the rest of your computer.
python3 -m venv .venv source .venv/bin/activate
- 3Run it
cd data-engineering/17-change-data-capture python3 src/cdc.py
Project structure17-change-data-capture/
Project structure
data-engineering/17-change-data-capture/Open on GitHub →
- data/
- day.sqlthe day's six writes, in order
- orders.csvthe table before the day starts: orders 1 and 2, both 'new'
- short/
- cdc.pyrunthe code from the video
- shop.pybuilds the source and target databases, loads data/
- src/
- cdc.pymainthe code from the video
- shop.pybuilds the source and target databases, loads data/
- README.mdthe idea, how to run it, a line-by-line walkthrough, things to try
data-engineering/17-change-data-capture/src/cdc.pyOpen on GitHub →
from shop import source, target, DAY
src = source() # orders 1 and 2, both 'new'
SNAP = "SELECT id, status FROM orders"
yday = dict(src.execute(SNAP))
# one trigger per write: our stand-in for the WAL
for op, row in [("INSERT", "NEW.id, NEW.status"),
("UPDATE", "NEW.id, NEW.status"),
("DELETE", "OLD.id, NULL")]:
src.execute(f"""CREATE TRIGGER log_{op}
AFTER {op} ON orders BEGIN
INSERT INTO changes(op, id, status)
VALUES ('{op}', {row}); END""")
src.executescript(DAY) # six writes
# the nightly job: diff two snapshots
night = dict(src.execute(SNAP))
diff = {k: night.get(k) for k in yday | night
if yday.get(k) != night.get(k)}
# CDC: replay the log, in order
log = list(src.execute(First 24 of 43 lines
Transcript
Your nightly job missed 3 updates. CDC catches every one. Change data capture records every change, the moment it's written. A nightly diff is two photos of a parking lot.
You see what's new, never who came and went in between. CDC is the security camera. It records every car, in order. Our orders table takes six writes in a day: an insert, four updates, a delete.
A helper builds the source: orders 1 and 2, both new. Last night's snapshot: one SELECT, saved as a dict. Real CDC tools like Debezium read the database's write-ahead log. Here, triggers stand in.
One trigger per write: insert, update, delete. Each one appends the operation, the id and the new status to a changes table. A delete logs the old id. Then the day runs: six writes.
The nightly job snapshots again, and keeps each order that changed. CDC reads the log instead, in sequence order. The target starts from last night's copy. Inserts and updates become an upsert.
Deletes become deletes. Every change replays in order. Then we print what the diff missed. Let's run it.
Six changes today. The diff missed three: paid, cancelled, shipped. Order 1 went paid, shipped, delivered. The diff saw only delivered.
Order 2 was cancelled, then deleted. No trace of the cancel. CDC replayed all six, and the target matches the source. Nightly diff: three of six.
CDC: six of six. So how many orders were cancelled today? The diff says none. In production, Debezium reads the Postgres or MySQL log and streams every change to Kafka.
Triggers work, but they add a write to every transaction. The gotcha: replay in order per key, or delivered turns back into shipped. A snapshot shows where you ended. The log shows how you got there.
Read the lesson on GitHub →- #changedatacapture
- #debezium
- #dataengineering
- #sql
