Two Ways to Catch a Change

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

Should we capture changes from our several production databases by reading each database's write-ahead log, or with triggers? Design the change data capture pipeline that replicates them into the warehouse, and make a concrete recommendation on the capture method for each source.

Two ways to watch the database. Each has a cost.

Business requirements

• Fraud needs database changes visible in the warehouse within minutes, while analytics teams can wait longer.
• The database team has refused to sign off on anything that risks slowing or breaking the live application.
• When a customer account is deleted in the source, the fraud team has to know; a row silently vanishing from the warehouse is wrong.

Worked solution and explanation

The call is made by the constraints

This is a load-and-loss question dressed up as a tooling debate. Anyone can name Debezium. What separates candidates is noticing that the stakeholders already ruled out triggers: the database team won't accept extra write load, fraud needs changes in minutes, and a deleted account must stay visible. Pick triggers and you double every application write, then discover a disabled trigger leaves the warehouse silently wrong.

Trigger-based capture

Every insert, update and delete fires a trigger that writes a second row inside the application's transaction. Peak load gets slower, and a DBA who disables the trigger at 3 AM creates a gap nobody sees. Deletes are only captured if you remember to write the trigger for them.

Log-based capture

A connector reads the write-ahead log or binlog the database already writes. No extra write path and no query load. Deletes arrive as ordinary DELETE events carrying the primary key, in commit order, beside inserts and updates.

Step 1: Capture once from the log

Put one log-based CDC node between source databases and the stream. It is the only thing that touches the sources, so the database team gets one replication slot to review instead of a trigger on every table. If one legacy source exposes no log, escalate that one source rather than retreating to triggers everywhere.

Step 2: Fan out from one change stream

Land the events in a single Kafka-style change stream. Fraud reads it with a Flink job in seconds, and analytics reads the same events with a Spark micro-batch every few minutes. Two independent extracts from the source would double the load and let the two teams disagree about what happened.

Step 3: Make deletes loud on both paths

Each consumer path applies a DELETE event as a soft delete, setting is_deleted and deleted_at on the warehouse row instead of removing it. Fraud can then query for deletions in the last day and see every closed account. A row that vanishes without a trace is the exact failure the requirement names.

Common follow-up questions

• The fraud team now wants each row's state before and after every update. What changes? (Many connectors emit only the new row by default. Full before-images usually need source configuration such as Postgres `REPLICA IDENTITY FULL`.)
• A source column is renamed upstream. How do both consumer paths survive it? (Tests schema evolution: the connector emits the change on the stream, and consumers must tolerate it rather than fail on a hard-coded schema.)