Databases as Sources
PedalCity's first pipeline ran a big SELECT * on the app's database at 08:30. Riders trying to unlock bikes waited ten seconds, and some gave up. The app's database is the most valuable source you have, and also the most fragile one to read from. You have to copy from it without hurting the product it serves.
The rule
Copy from a production database without slowing the app that owns it.
Four techniques, from simplest to most complete:
- Read a replica. Many databases keep a read-only copy, called a replica, that follows the main one a moment behind. Point your pipeline there and the app never feels your queries.
- Snapshot small tables. Copy tables such as
stationsin full. They are cheap, and a full copy also catches deletions. - Extract incrementally. For large tables, read only rows whose
updated_atfalls in your window, as in the ingestion lesson. - Capture changes from the log. Every database writes each insert, update and delete to an internal log before applying it. Change data capture (CDC) reads that log and turns it into a stream of change events. It adds almost no load and sees every change, including deletions.
Deletions are the hidden problem with timestamp queries. When a support agent deletes a fraudulent ride, the row simply disappears:
DELETE FROM rides WHERE ride_id = 90412;No row is left with a new updated_at, so an incremental query never learns that ride 90412 is gone, and your warehouse keeps counting it. A full snapshot or CDC catches it. Some teams instead ask the app to soft delete, which means setting deleted_at rather than removing the row.
The shortcut
For each table ask: Is it small? Does it delete? How fresh must my copy be? Small → snapshot. Large with no deletes and daily freshness → incremental on a replica. Large with deletes, or needs minutes-fresh data → CDC.
stations (120 rows)
Small, so snapshot it nightly. Deletions are caught automatically.
Drills
Answer fast — trust the rule.
Where should a heavy nightly extract query run?
An incremental query filters on updated_at. A ride row is hard-deleted. What does the warehouse see?
What does change data capture read?
Which table is best copied as a full snapshot each night?
Remember
- Copy from production without slowing the app: prefer replicas and small, targeted reads.
- Timestamp-based incremental loads cannot see hard deletes.
- Snapshot small tables, go incremental for big ones, and use CDC when deletes or freshness matter.