Replicate It Without Breaking It

A hard 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

hard

Seniority

L6

Problem

What streaming pipeline keeps a Delta lake continuously replicated from an OLTP database taking about 200K writes a minute at peak, so the analyst querying it never touches production? Two of its 50 tables, orders and order line items, carry 80% of those writes.

The source changed. The lake needs to know immediately.

Business requirements

• Analysts query the lake constantly and need source changes visible within minutes; today they wait for the nightly snapshot.
• When the same row changes multiple times, an old change arriving late can't make the warehouse go backwards on numbers analysts have already seen.
• GDPR requires that a hard delete in the source physically remove the row from the lake within a day.
• Streaming merges can produce many small files and queries get slower over time; the lake's read performance can't degrade as a side effect of writes.

Worked solution and explanation

This looks like "copy the database into the lake," but it is really four promises that one naive consumer breaks together: fresh within minutes, never going backwards, hard deletes that are physically gone, and reads that stay fast. The default design is a CDC consumer that upserts every change by row id. It looks fine in the demo. Then a late older change overwrites a newer state, a deleted customer's row survives the GDPR audit, and a month of tiny files makes every dashboard crawl.

Walk the requirements

Step 1: Capture from the log, not the tables

Log-based CDC on oltp_database reads the write-ahead log, so analysts never touch production. Changes land on cdc_stream (Kafka), which buffers bursts so the merge can fall behind briefly without losing anything.

Step 2: Merge in LSN order, in real time

ordered_merge is the streaming stage that keeps the lake current within minutes. It compares each change's LSN to the row's last-applied LSN before writing. That one comparison is what stops the lake regressing on numbers analysts already used.

Step 3: Make deletes physical and prove it daily

A source DELETE must remove the row from the lake, not flip a flag. delete_propagator runs inside the 24 hour window and confirms every source-deleted id is really gone. A soft-delete flag is the version that fails the first time someone asks for proof.

Step 4: Compact so writes don't tax reads

Continuous merges leave many small files. compaction_job rewrites them into larger ones per partition, off-peak, and the table format's transaction log keeps readers consistent while it does. The orchestrator schedules both maintenance jobs so neither depends on someone remembering.

Upsert on row id

The last change to arrive wins, so a replayed or late older event overwrites a newer state. Deletes look like one more row, and DELETE events are easy to lose or turn into flags.

Upsert gated on LSN

The write happens only when the incoming LSN is higher than the stored one. Late and replayed events are harmless, and DELETE events are handled as physical removals with a daily confirmation.

Common follow-up questions

• An analyst saw a row show a wrong value briefly after a connector restart. What in this design allows that, and what corrects it? (Tests whether you see the design is eventually consistent inside the streaming budget: replay is safe because the LSN check converges the row once the newer change is applied.)
• `compaction_job` falls behind because the partition layout creates too many small partitions. What do you change? (Tests partition strategy: coarsen partitions for low-volume tables or compact more often to keep file counts in range.)