Applied Pipeline Design: How To Architect Any Pipeline

A design round is an interview where you plan a system. You get a short prompt.

You must design a pipeline out loud.

The hard part is not the tools. It is knowing what to say first, and what to say next.

This chapter gives you eight steps in a fixed order. You say them in the same order every time. Here is the prompt we use in this chapter:

This chapter teaches no new tool. Each step uses a chapter you already finished. Each step gives a short recap, and then you use it.

What you will learn

1. The eight steps, in order.
2. How to ask the questions that shape a design.
3. How to walk the shop prompt through the steps, from end to end.
4. How the same steps work on a different prompt.
5. The mistakes that cost the most marks, and how to say your answer out loud.

The method has eight steps. Each step answers one question. You do not skip a step, and you do not name a tool before step 2.

This table lists the steps. The last column names the chapter that taught the idea:

Steps 1 and 3 give you numbers. Steps 4 to 7 use those numbers. This is why the order matters.

Step 1 is to ask questions. You ask before you draw anything. Four questions decide most of the design.

This table shows the four questions. It also shows why you ask each one, and the answer in our shop prompt. The answers are made up, as an interviewer would give them:

Say the answers back in one sentence. For example: "Five minutes behind, 200 orders a day, with updates and deletes, for a dashboard."

Now you and the interviewer agree on the problem.

Ask about limits too: the size of the team, the tools you already have, and the deadline.

A team of one person cannot run ten services.

Before you build, ask one more question: does anyone need a pipeline at all? A pipeline is code that you must run and fix for years. Sometimes a simpler answer is enough.

This table shows four situations and the better answer for each:

The shop prompt asks for a warehouse and a five-minute limit. So a pipeline is the right answer here.

A connector can still do step 2 for you. You still decide steps 3 to 8. Say why out loud, then keep the pipeline small.

Step 2: capture the source

The warehouse needs new orders, changed orders and deleted orders. A watermark query on updated_at cannot see a deleted row. So you choose CDC.

Debezium reads the Postgres log. It writes one event for each change to Kafka. Each event has a pos, which is its place in the log.

You start with a snapshot, which is a full copy of the table. Then you stream the changes that come after it.

Say what you give up. The database owner must set wal_level to logical and create a replication slot. Your team must run Debezium and Kafka.

Step 3: choose the speed

Split the five minutes into parts. This plan is the latency budget:

So you run a micro-batch every minute. Each run reads the new events from Kafka and writes them.

It saves the last pos it read, so the next run starts there. A failed run is easy to run again.

Streaming also fits five minutes. You choose it only when you need seconds, or when you must act on each event.

Step 4: store and model

Keep three layers. Raw keeps the events as they came.

Cleaned has one row for each order, with the right types. Serving has the tables people query.

Write Parquet files. Use a table format such as Delta or Iceberg, because it supports MERGE. At 200 orders a day the table is small, so you do not need a partition yet.

Add one on order_date when it grows.

At 200 orders a day, most runs see no new order. A run with no events writes nothing. A daily job compacts the small files into bigger ones.

The model has a fact table, fact_orders, with one row for each order. A dim_customer table holds the customer facts.

Use SCD2 on it. SCD2 means slowly changing dimension, type 2: each change adds a new row. So an old order keeps the customer facts it had that day.

Step 5: transform and load

Each run keeps the newest event for each order. Then it writes with MERGE on order_id. warehouse.orders is the cleaned table. A retry gives the same result.

This query is one run. It uses the three MERGE rules from the CDC chapter.

The pos guard is the check e.pos > t.last_pos. It skips an event that the table already has:

MERGE INTO warehouse.orders AS t
USING latest AS e
  ON t.order_id = e.order_id
WHEN MATCHED AND e.op = 'd' AND e.pos > t.last_pos THEN
  DELETE
WHEN MATCHED AND e.pos > t.last_pos THEN
  UPDATE SET status = e.status, amount = e.amount, last_pos = e.pos
WHEN NOT MATCHED AND e.op <> 'd' THEN
  INSERT (order_id, customer_id, status, amount, last_pos)
  VALUES (e.order_id, e.customer_id, e.status, e.amount, e.pos);

Step 6: check and watch

Check the data before readers see it. Write each run to a staging table, run the checks, then publish.

This is write-audit-publish.

A block check stops the publish when it fails. A warn check only sends an alert.

Watch two numbers. CDC lag is how far Debezium is behind Postgres. Run age is the time since the last good run ended.

Do not watch the age of the newest order. At 200 orders a day, often no order comes for many minutes.

The delay is CDC lag plus run age. Alert when the delay passes 3 minutes. That leaves 2 minutes before the promise breaks.

A data contract with the source team says which changes are allowed. A new column can pass. A renamed column is blocked.

Step 7: fail and recover

A backfill is a run over old data, to fix it or fill it. Say what you do in each of these three cases:

Every choice has a price. Say what each choice gives you and what it costs. The cost has two parts: machines and people.

This table shows the main choices in the shop design:

At 200 orders a day, the data is small. The cost is in the parts that run all day, not in the rows.

End with one sentence: "I chose a one-minute micro-batch because it meets five minutes. It costs more parts than a nightly job. If the need becomes seconds, I would look at streaming again."

Here is the path an order takes: Postgres, then Debezium, then Kafka, then a micro-batch every minute. The batch writes to the raw, cleaned and serving layers.

Follow one order through the five-minute budget. The order is saved at 10:02. This table shows the longest wait at each part:

This table is the whole design, one line for each step:

The same eight steps work on a different problem. Here is a new prompt:

The answers below are made up. The table puts them next to the shop answers, so you can compare:

The steps stayed the same. The answers changed, because the numbers changed. This is the point of the method.

These five mistakes cost the most marks. The table shows each mistake and what it causes:

In the round, you speak the steps. Say the step name first, so the listener can follow. Put a number in each step.

This is the shop answer, one point for each step. It takes about one minute to say:

1. Ask: I need a freshness number, the volume, whether rows change or get deleted, and who reads it. I will assume five minutes, 200 orders a day, updates and deletes, and a dashboard.
2. Capture: I use CDC with Debezium and Kafka, because a watermark query misses deletes. I start with a snapshot, then stream.
3. Speed: my budget is CDC lag 1, wait 1, run 1, and 2 spare minutes. So I run a micro-batch every minute.
4. Store: raw, cleaned and serving layers, in Parquet, in a table format that supports MERGE. The data is small, so no partition yet.
5. Load: MERGE on order_id with a pos guard, so a retry gives the same result. A delete event removes the row.
6. Check: write, audit, publish. I alert when the delay passes 3 minutes.
7. Recover: I re-run a failed run, restart from the saved pos, or rebuild from raw.
8. Cost: more parts than a nightly job, but it meets five minutes. If the need becomes seconds, I would look at streaming again.

Then ask: "Does this match what you need?" This gives the interviewer a chance to correct you early.

Build each scenario on the pipeline canvas with the chapter closed. Say the eight steps out loud as you build. The review tells you which requirement your design missed.