Change data capture from databases
Lesson 3 of 4 in Ingestion Patterns.
There are two ways to get a database's changes into a lake, and the exam has a firm opinion about which one.
The obvious way is to query: SELECT * FROM orders WHERE updated_at > :last_run, on a schedule. It is five minutes of work and it fails in four specific ways, every one of which has been a question.
- It cannot see a delete. A deleted row matches no
WHEREclause. Your copy keeps it forever, and the only symptom is a total that is slightly too high — found months later, in a reconciliation. - It misses updates the watermark does not reflect. If application code updates a row without touching
updated_at— a trigger, a bulk correction, a migration script — the change is invisible to the query and will stay invisible. - It loads the source at the worst moment. The scan competes with production traffic, and the bigger the table gets the more it costs, exactly when the business can least afford it.
- It has a race at the boundary. A transaction that commits during the scan can be read half-applied or skipped entirely, depending on isolation level and timing, and nothing in the result says which.
Change data capture reads the log the database already writes for its own durability. That log records every committed change in commit order, deletes included, and reading it is far cheaper than scanning. The costs move rather than vanish: the source needs configuration, the log must be retained long enough for the reader to keep up, and you now receive a stream of events whose application to a table is a design decision of its own.
| Engine | How DMS reads the changes | Must be enabled on the source | Characteristic failure |
|---|---|---|---|
Oracle | LogMiner or the binary reader over online and archived redo logs, positioned by system change number | supplemental logging, at database level and often per table | full load succeeds, CDC produces nothing or drops updates, because redo records lack the columns needed to identify the row elsewhere |
MySQL / MariaDB | the row-based binary log | binary logging on, and | with statement-based logging the log records the SQL rather than the resulting rows, so it cannot be replayed faithfully against a different engine |
PostgreSQL | a logical replication slot read with the |
| an inactive slot retains write-ahead log segments indefinitely and can fill the source’s disk — a stopped task is not a harmless task here |
SQL Server | the transaction log, read with | MS-CDC or replication configured, and adequate log retention | log truncation by a backup job removes records before DMS has read them |
Amazon RDS (any engine) | the same engine mechanism, with AWS managing the instance | automated backups enabled, and change-log retention set — around 24 hours is usually enough | default retention too short for a task that falls behind over a weekend |
Amazon DynamoDB | not DMS: DynamoDB Streams, or Kinesis Data Streams for DynamoDB | the stream enabled, with a view type of |
|
I have a change feed. How do I turn it into a table?
This is the step people skip, and it is where the pipeline gets hard — see applying change events downstream. Three patterns:
Append and read the latest. Write every change event as a new row with its commit sequence, then define a view that picks the newest per key with a window function. Writes are trivial and idempotent; every read pays for a window over all history, and the table grows without bound.
Merge on the key. Upsert into the target and delete on a delete event. Reads are cheap, the table stays small, and you need a sink that can transact — an open table format or a warehouse — because a merge over plain files means rewriting them.
Keep the history on purpose. Close the previous row with an end timestamp and insert the new one, so the table can answer "what did this look like in March". The cost is that every query must filter on the validity window or it counts the same customer several times.
Order matters in all three: change events must be applied in commit order per key, which is exactly the guarantee a partition key on the primary key buys you if the feed goes through a stream.
The source team says they can just add an updated_at column. Is that enough?
It fixes the second failure and none of the others. Specifically it still cannot see deletes, which is usually the one that matters: a cancelled order, a deleted account, a corrected duplicate. Your copy keeps them, so every count is wrong by the number of deletions since the pipeline started.
The partial remedy that is sometimes acceptable is a soft delete — the application sets deleted_at rather than removing the row, and the row's update is visible to your watermark query. That works, and it is a change to the application's data model agreed with the team that owns it, not something you can retrofit from the pipeline. If you cannot get that agreement, the honest options are CDC or a full snapshot comparison.
Can I use Glue job bookmarks instead of CDC for a JDBC source?
Only for append-only tables, and the reason is precise. A JDBC bookmark tracks bookmark keys — by default the primary key — and compares values between runs, so it captures only newly inserted rows. An UPDATE to an existing row advances no key and is therefore invisible, and a DELETE even more so.
There are two further requirements that make bookmarks fragile here: the keys must be strictly monotonically increasing or decreasing (a revision number whose values repeat is not valid), and the script must call job.commit() on every completing path and pass a transformation_ctx to each frame, or the state never advances and the job silently reprocesses everything.
So the rule is: bookmarks for an immutable event or log table, CDC the moment the source permits updates.
Key terms: Change data capture (CDC), AWS DMS, DMS ongoing replication, Supplemental logging, Zero-ETL integration, DynamoDB Streams