In this article
Before you start
This page is one step of the data engineer interview learning path.
Why interviewers ask this
A pipeline that works on a good day and double-counts after a crash gives a company wrong numbers in its reports. A senior round exists to find out whether you design for the bad day. This page does not claim that any named company runs the round in a fixed way; it covers what a design question of this kind rewards.
A weak answer lists tools: a message queue, a processing engine, a warehouse. A strong answer starts from requirements, says where data can be lost or duplicated, and shows how each layer recovers. A Staff-level answer also names what it will not promise, such as complete data at a given minute, and says how the design degrades. Interviewers listen for the trade-off sentence, not the tool names.
Senior DE system design from first principles
- Medallion architecture
- A medallion architecture is a data design pattern that organises lakehouse data (a lakehouse is a data store that keeps files in cheap object storage and adds table features on top) into layers called bronze (raw), silver (validated) and gold (enriched), so that the quality of the data improves at each layer and each layer has one clear job.
- Idempotent write
- An idempotent write is a write that can be performed many times and leaves the destination in the same state as performing it once, usually because each row has a key and a repeat replaces the earlier copy instead of adding a second one.
- Watermark
- A watermark is a threshold that tells a streaming engine how long to wait for late rows, measured against the newest event time seen so far, and that lets the engine drop old state once that wait has passed.
In plain words
Picture a warehouse receiving dock. Every delivery is first written in a ledger. The parcels are put on raw shelves, then checked and cleaned, then packed into display-ready boxes. If the power fails mid-shift, the ledger shows which deliveries were finished. The analogy breaks in one place. A warehouse does not receive yesterday's parcel today, but a pipeline does, and the late arrival must still be placed correctly.
In one precise sentence, a senior pipeline design is a set of layers, each recoverable by replay, that turns a never-complete stream of events into tables whose correctness, latency and cost were chosen on purpose.
The Dataflow paper (Akidau et al., 2015) puts the starting point bluntly: "We as a field must stop trying to groom unbounded datasets into finite pools of information that eventually become complete."
Step 1: Fix the requirements and name the trade-off
Requirements come first because every later choice trades one thing for another. Correctness, latency and cost are the axes every design trades off, the same three axes the Dataflow paper names for streaming pipelines.
Input: a vague prompt such as "design the pipeline for our app events". Output: a short list of numbers and promises you state aloud. Ask who reads the output and how stale it may be, how many events arrive and how large they are, whether a wrong number or a late number is worse, and how long raw data must be kept. Then say your assumptions as assumptions. If readers accept hourly freshness, a scheduled batch is simpler and cheaper than streaming.
Because unbounded data never becomes complete, state what "done for the day" means: for example, a table that is final after a stated wait. You now have the spec that every later step is judged against.
Step 2: Exactly-once comes from a replayable source and an idempotent sink
Exactly-once delivery is the mode in which the effect of each message lands once and only once (exactly-once output), and a pipeline reaches it by pairing a replayable source with an idempotent sink. A sink is the table or system a pipeline writes its output to. An offset is the position number of a message in a log. A replayable source is an input, such as a log with stored offsets, that can be read again from a chosen position after a failure.
The Confluent documentation (accessed October 2026) names three modes. At-most-once delivery can lose messages. At-least-once delivery never loses messages but can duplicate them. Exactly-once delivery neither loses nor duplicates messages.
The recipe follows. Read from a replayable source, record the offsets you have processed in a checkpoint, and write with an idempotent write. A checkpoint is a saved record of which offsets a streaming query has committed, plus its state. A replayable source and an idempotent sink give exactly-once output. The Databricks streaming tutorial (accessed October 2026) says exactly-once comes from committing the offsets and the output together, so a batch whose commit is missing is replayed and one whose commit is present is skipped.
Text description of the diagram
- At-most-once delivery can lose messages.
- At-least-once delivery never loses messages but can duplicate them.
- Exactly-once delivery neither loses nor duplicates messages.
Step 3: Layer the data into bronze, silver and gold
Bronze, silver and gold layers raise data quality at each hop, which is the medallion pattern in the Databricks documentation (accessed October 2026). The same page calls it a data design pattern used to organise data logically, so it is not a product feature and it does not require exactly three tables.
Input: the raw events your replayable source delivers. Output: three kinds of table, each with one job. Bronze keeps the data as received, so you can always reprocess it. Silver validates, deduplicates and conforms it, for instance by applying your data model. Silver writes are made idempotent with a merge statement on the event key, or by overwriting the whole date partition. Gold holds enriched tables shaped for the readers you named in step 1, such as dashboards or features for a model.
Why layers help in an interview: a bug in silver logic is fixed by replaying bronze, not by asking the source to resend. Every layer is also a place to state a different retention and cost choice. For the SQL that reshapes silver and gold, see SQL interview questions.
Step 4: Handle late data with event time and a watermark
Event time is the clock time at which an event happened in the world, and processing time is the clock time at which the pipeline sees it. The two differ whenever a phone loses its connection or a network is slow, so windows and daily totals should be built on event time.
A watermark bounds how long the engine waits for late rows, per the Databricks tutorial. Spark has supported watermarking since version 2.1, and it also lets the engine clean up old state.
Input: events arriving out of order. Output: windows that close after a stated wait. Be precise about the promise. The Spark 3.5.6 Structured Streaming guide says the rule is firm in one direction only: data later than the threshold may or may not get aggregated. Data inside the threshold is processed. State how late is too late, and where later rows go, for example to a correction table or a later batch.
Step-by-step description
- Fix correctness, latency and cost.
- Replay plus an idempotent sink.
- Bronze, silver and gold.
- Event time and a watermark.
- Correctness, latency and cost are the axes every design trades off.
- A replayable source and an idempotent sink give exactly-once output.
- Bronze, silver and gold layers raise data quality at each hop.
- A watermark bounds how long the engine waits for late rows.
Knowing it is half the job. Saying it is the other half.
Interview Clozo is an AI interview copilot for real interview rounds: it hears the question and puts a strong answer on your screen, and you say it in your own words. See the features or how it works.
Start Free Trial →Worked example
This example uses made-up volumes, so every input is an illustrative assumption. A pipeline receives 50,000,000 events a day of about 400 bytes each, keeps bronze for 30 days, and sees 0.5% of events duplicated by producer retries.1 That is 20 GB of raw data a day and 600 GB in bronze, and 250,000 duplicates a day that silver must remove, leaving 49,750,000 rows.1
Now the crash. Ten events are processed in two batches of five. The job crashes after writing the second batch but before committing its offsets, so the batch is replayed. After the replay an append sink holds 15 rows and a keyed sink holds 10 rows.1 The append sink wrote five rows twice; the keyed write replaced them.
Text description of the diagram
- After the replay an append sink holds 15 rows.
- After the replay a keyed sink holds 10 rows.
Last, a watermark of 10 minutes on illustrative event times, in arrival order: 100, 102, 105, 111, 98, 115, 103 and 120. A strict toy rule rejects an event older than the newest event time minus 10. Events 98 and 103 arrive too late, so 6 of the 8 events are accepted.1 Spark advances the watermark once per micro-batch, not per event, so this per-event rule is a simplification.
| Quantity (illustrative inputs) | Value |
|---|---|
| Raw volume per day | 20 GB |
| Bronze volume with 30 days kept | 600 GB |
| Duplicate events per day at 0.5% | 250,000 |
| Silver rows per day after deduplication | 49,750,000 |
| Rows after replay, append sink | 15 |
| Rows after replay, keyed sink | 10 |
| Events accepted by a 10-minute watermark | 6 of 8 |
Trade-offs and edge cases
Exactly-once is narrower than it sounds. Apache Kafka is a distributed log in which producers write messages and brokers store them. Kafka's idempotent producer is given a producer identifier by the broker and sends a sequence number with every message, and the broker deduplicates retries, per Confluent (accessed October 2026). It still retries; the broker removes the repeats. The Confluent blog post says that prior to 0.11.x Kafka supported at-least-once delivery, and that setting enable.idempotence=true gives exactly-once per partition. Idempotence gives exactly-once per partition; transactions extend it across partitions for read-process-write inside Kafka. Neither covers an external sink, so the warehouse write must still be idempotent.
End to end, Spark's guide says replayable sources and idempotent sinks together give exactly-once under any failure. If your sink cannot be keyed, you lose that property.
Other cases to name:
- Late data never ends. A longer watermark holds more state and delays results; a shorter one drops more rows. This is the correctness, latency and cost trade-off again.
- Layers cost storage. Keeping bronze makes replay possible but costs space; say how long you keep it and why.
- Skewed keys. One very busy key can make one partition far slower than the rest, so say you would salt the hot key or aggregate in two stages.
- Backfills. Reprocess from bronze through the same idempotent steps so reruns are safe.
- Schema changes. New or renamed fields arrive without warning, so handle them in bronze-to-silver with a schema check instead of letting them break gold.
- Data quality. Check row counts and null rates at silver, and alert when freshness misses the step-1 target.
The PySpark tutorial shows the engine side, the data pipeline explainer covers the basics and the Spark interview questions go deeper; the older batch world is in the Apache Hadoop interview questions, and cluster settings are in the Talend Spark configuration questions.
How to answer it in an interview
Design a pipeline that brings app events into tables the analysts can query.
I would start by asking for the freshness target, the volume and who reads the output, because correctness, latency and cost pull against each other. Events go into a replayable log. A bronze layer keeps the raw data, silver validates and deduplicates, and gold serves dashboards. A job can crash mid-write, so I make the sink idempotent: replaying a batch overwrites the same keys instead of adding duplicates. For late events I use event time with a watermark and say how late is too late. Then I would cover backfills and monitoring.
The spoken structure: ask for requirements, give the layers, say how a crash is survived, say how late data is handled, then the trade-off you chose.
Common mistakes
- "Exactly-once means each message is physically sent once." (wrong) Retries still happen. (right) "The producer may retry, and the broker or sink removes the repeats."
- "Kafka exactly-once covers my whole pipeline." (wrong) Kafka's exactly-once support stops at Kafka; transactions cover Kafka-to-Kafka, not your warehouse. (right) "End to end needs a replayable source and an idempotent sink as well."
- "A watermark drops everything that is late." (wrong) The firm rule runs one way only. (right) "Data inside the threshold is processed; later data may or may not be."
- "Medallion means exactly three fixed tables." (wrong) It is a design pattern, not a product feature. (right) "Layers with a clear job each, as many as the problem needs."
- "Streaming lets us wait until the data is complete." (wrong) Unbounded data never is. (right) "I state a wait and the correction path for later rows."
Follow-up questions
What is the medallion architecture?
The medallion architecture is a data design pattern that organises lakehouse data into bronze (raw), silver (validated) and gold (enriched) layers, with quality improving at each layer, per the Databricks documentation.
What is the difference between at-least-once and exactly-once delivery?
At-least-once never loses messages but can deliver some more than once. Exactly-once delivers each message once and only once, which in practice means replay plus an idempotent write.
What happens if the job crashes halfway through a write?
On restart the engine replays the batch whose commit is missing. With a keyed, idempotent sink the replay replaces the same rows; with an append sink it duplicates them, as the worked example shows.
How do you handle late-arriving data in a streaming pipeline?
Build windows on event time, set a watermark for how long to wait, and send rows that arrive after it to a correction path. State the threshold and why you chose it.
How would you backfill a pipeline?
Reprocess from the bronze layer through the same idempotent steps, so a rerun overwrites rather than duplicates. Run it beside the live job and compare totals before switching. For the interview framing, see the data engineer interview guide.
- Start from requirements: correctness, latency and cost trade off against each other.
- Exactly-once needs a replayable source and an idempotent sink, not a single setting.
- Bronze, silver and gold give each layer one job and make replay possible.
- A watermark bounds the wait for late rows; the firm rule runs one way only.
- Say what you do not promise, and how the design degrades.
Sources
- What is the medallion lakehouse architecture? — Databricks documentation (accessed 2026-10-26), 11 Sep 2026
- Kafka delivery semantics — Confluent documentation (accessed 2026-10-26), 26 Oct 2026
- Exactly-once Semantics is Possible: Here's How Apache Kafka Does it — Confluent blog (accessed 2026-10-26), 30 Jun 2017
- Structured Streaming Programming Guide, Spark 3.5.6 — Apache Spark documentation (accessed 2026-10-26), 26 Oct 2026
- The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost — Proceedings of the VLDB Endowment 8(12) (Akidau et al.), 1 Aug 2015
- Structured Streaming - Apache Spark Tutorial — Databricks (accessed 2026-10-26), 26 Oct 2026
