Gowtham Potureddi

Posted on Aug 15

Scenario-Based Data Engineering Interview Questions: Whiteboard Pipeline Design

#python #sql #interview #dataengineering

The scenario based data engineering interview questions are the ones that do not have a single right answer — the interviewer says "design a pipeline that powers our clickstream analytics" and then watches, for forty-five minutes, how you turn that one sentence into an architecture on a whiteboard. There is no unit test to pass and no hidden edge case to catch; there is only you, a marker, and a blank board, and the entire evaluation is how you think out loud under ambiguity. Candidates who are excellent at coding rounds routinely stall here, because the skill is different: it is the skill of asking the right clarifying questions, sizing the problem in numbers, drawing boxes-and-arrows that a stranger can follow, and defending every choice with a trade-off rather than a preference.

This guide is the playbook for that round. It treats the data engineering system design interview as a repeatable process rather than a talent you either have or lack, and it gives you a seven-step framework you can run on any prompt — requirements, data contract, ingest, store, process, serve, and ops/scale/cost — so that a blank whiteboard never feels blank again. Along the way it works three full example scenarios end to end: a clickstream analytics pipeline, a change-data-capture ingestion into a warehouse, and a metrics/reporting pipeline. Each section pairs a teaching block on one part of data pipeline design with a worked whiteboard scenario — the prompt, the requirements you would clarify, the architecture you would sketch, the reasoning trace, the final design, and a concept-by-concept breakdown of why it holds up when the interviewer pushes back.

When you want hands-on reps alongside the reading, drill pipeline architecture on the system design practice library →, rehearse end-to-end builds on the ETL practice library →, and sharpen the real-time axis with the streaming practice library →.

On this page

How to run a whiteboard pipeline-design round — a repeatable framework
Requirements & data-contract clarification
Ingestion & storage design — batch vs streaming, format, partition, lake vs warehouse
Processing, modeling & serving — transforms, idempotency, serving, backfills
Reliability, scale, cost & defending trade-offs
Cheat sheet — whiteboard pipeline-design recipes
Frequently asked questions
Practice on PipeCode

1. How to run a whiteboard pipeline-design round — a repeatable framework

The round measures structured thinking under ambiguity, not the "right" architecture

The one-sentence framing that changes how you prepare: the whiteboard pipeline-design round is a forty-to-sixty-minute open-ended session where the interviewer deliberately gives you an under-specified prompt and scores the process you use to resolve it — how you clarify, how you structure the design, and how you defend trade-offs — far more than whether you land on their preferred stack. There is almost never a single correct answer; there are defensible answers and indefensible ones, and the difference is whether your choices are tied to the requirements you extracted. A candidate who confidently name-drops Kafka, Spark, and Iceberg but never asked "how fresh does the data need to be?" scores below a candidate who asks three sharp questions and then draws a simple, correct batch job.

What the interviewer is actually scoring. Read the rubric in your head before you touch the marker.

Requirement clarification. Do you refuse to design until you know the purpose, the consumers, the volume, and the latency SLA? This is the single highest-signal behaviour.
Structure. Do you have a repeatable framework, or do you jump straight to "so we'd use Kafka"? Structure signals seniority.
Trade-off reasoning. For every box you draw, can you say what you gave up and why the alternative was worse for these requirements?
Communication. Is the whiteboard readable? Do you narrate assumptions out loud so the interviewer can redirect you early?
Depth on demand. When pushed on one component ("how does the dedupe work?"), can you zoom in without losing the whole picture?

The seven-step framework — memorise this order. Every good pipeline design walks left to right through the same seven stages; running them in order is what keeps a blank board from feeling blank.

1. Requirements. Purpose, consumers, volume, velocity, latency SLA, freshness, correctness bar, retention. Turn every fuzzy word into a number.
2. Data contract. Schema, grain (one row = what?), keys, semantics, schema-evolution rules, and who owns the source.
3. Ingest. Batch vs streaming vs CDC; the buffer; delivery semantics; the landing zone.
4. Store. Lake vs warehouse vs lakehouse; file format; partitioning; the bronze/silver/gold layering.
5. Process. Staging → transform → publish; idempotency; deduplication; the compute engine.
6. Serve. The consumer-facing model (star schema, pre-aggregates) and the serving store (warehouse, OLAP, key-value, API).
7. Ops / scale / cost. Reliability (retries, DLQ, data-quality gates), scaling, monitoring/SLOs, and the cost envelope.

How to actually drive the whiteboard. The physical layout is part of the score.

Reserve the top-left for requirements. Write the numbers you extract — volume, latency, freshness — in a corner and keep referring back to them; they justify every later choice.
Draw left-to-right, boxes and arrows only. Sources on the left, consumers on the right, data flowing rightward. Label every arrow with what flows and how often.
Narrate assumptions as you write them. "I'm assuming events are at-least-once, so I'll make the sink idempotent" lets the interviewer correct a wrong assumption before you build a wing of the house on it.
Leave whitespace. You will need room to zoom into one component when asked; do not fill the board on the first pass.

The anti-signals that sink candidates. Avoid these more carefully than you chase the "right" stack.

Designing before clarifying — the number-one failure. Never draw a box before you have numbers.
Buzzword architecture — naming ten technologies with no requirement tying them together.
Ignoring the consumer — a pipeline that lands data nobody can query the way they need.
No failure story — a happy-path design with no retries, no bad-data handling, no idea what happens at 10x.
Defending choices with taste, not trade-offs — "I like Kafka" instead of "the freshness SLA is sub-second, which rules out nightly batch."

Worked example — running the full framework on a first prompt

Detailed explanation. The most useful thing to rehearse is the opening two minutes of the round, because that is where most candidates either establish control or lose it. When you get a one-line prompt, do not start drawing — start a requirements block, ask questions until the fuzzy words become numbers, and only then draw the seven-stage skeleton. Below is the whole motion on the classic opener, "design a pipeline for our product's clickstream analytics," so you can see how a vague sentence becomes a defensible architecture.

Purpose block — write down who consumes the output and the decision it drives (analysts, dashboards, funnel/retention analysis).
Numbers block — event rate, event size, daily volume, retention, and the freshness SLA the dashboards need.
Contract block — the event schema, the grain, the dedupe key, and how schema changes are handled.
Skeleton — draw the seven boxes and fill each with the simplest thing that meets the numbers, upgrading only where a requirement forces it.

Question. Given the one-line prompt "design our clickstream analytics pipeline," what does the first pass of the framework produce before you commit to any technology?

Input.

Clarifying question	Answer you extract	Consequence for the design
Who consumes it and for what?	Analysts + product dashboards (funnels, retention)	Warehouse-shaped serving, not a key-value store
Event rate / size?	~50k events/sec peak, ~1 KB each	~4 TB/day raw — needs partitioning + columnar
How fresh must dashboards be?	Hourly is fine; not sub-second	Micro-batch or hourly batch, not full streaming
Correctness bar?	No double-counted sessions	Idempotent dedupe on `event_id`
Retention?	13 months hot, then archive	Partition by date + lifecycle tiering

Code.

# First-pass whiteboard skeleton (boxes and arrows, left to right)

  [ web/mobile SDK ]
        | click events (~50k/s, ~1KB, at-least-once)
        v
  [ ingest buffer ]  --- absorbs spikes, enables replay
        |
        v
  [ landing / bronze ]  --- raw JSON, partitioned by dt/hour, in object store
        |
        v
  [ transform (hourly batch) ]  --- dedupe on event_id, sessionize, clean
        |
        v
  [ silver / gold tables ]  --- columnar (Parquet), partitioned by event_date
        |
        v
  [ warehouse serving ]  --->  [ dashboards / analysts ]

# Requirements pinned in the top-left corner (referred to for every choice):
#   volume  = ~4 TB/day     freshness = hourly (NOT real-time)
#   grain   = one click event  dedupe = event_id   retention = 13 months

Step-by-step trace.

Refuse to draw until the requirements block has numbers: rate, size, freshness, correctness, retention — five questions, five numbers.
Read the freshness answer ("hourly is fine") — this single answer kills the temptation to build a full streaming stack and points to hourly micro-batch.
Read the consumer answer ("analysts + dashboards") — this points the serving layer at a warehouse/columnar shape, not a low-latency key-value store.
Draw the seven-stage skeleton and put the simplest component in each box that satisfies the numbers, leaving room to upgrade a box only if a follow-up requirement forces it.
Narrate the one load-bearing assumption ("events are at-least-once, so the transform dedupes on event_id") so the interviewer can redirect early.

Output:

Stage	First-pass choice	Justified by
Ingest	Buffered event stream	spike absorption + replay
Store	Partitioned columnar lake	~4 TB/day, 13-month retention
Process	Hourly batch, dedupe on `event_id`	hourly SLA + no double counts
Serve	Warehouse / columnar tables	analyst + dashboard consumers

Rule of thumb. If you can state the freshness SLA and the consumer shape in one sentence each, you already know 70% of the architecture — everything else is picking the simplest component that honours those two numbers.

2. Requirements & data-contract clarification

The first ten minutes decide the score: turn a fuzzy ask into numbers, then lock a data contract

The invariant to burn in: you cannot design a pipeline you have not sized, so the round is won or lost in the clarifying phase — you translate every vague word ("large", "fast", "reliable") into a number (TB/day, p99 latency, freshness minutes, error budget), and you lock a data contract that pins the schema, the grain, the keys, and the schema-evolution rules before you draw a single box. The interviewer plants the ambiguity on purpose; asking to remove it is the behaviour they are testing for.

The clarifying-questions checklist — the seven you always ask. Run these in order; each answer constrains a later stage.

Purpose. What decision or product does this data power? A pipeline for a real-time fraud model and one for a monthly finance report are different machines.
Consumers. Analysts running ad-hoc SQL? A dashboard? A downstream service reading via API? The consumer shape picks the serving layer.
Volume. Events/second and bytes/event → daily and yearly volume. This decides format, partitioning, and engine.
Velocity / freshness. How stale can the data be — seconds, minutes, hours, a day? This is the batch-vs-streaming lever.
Latency SLA. For a serving pipeline, the p99 read latency the consumer needs.
Correctness bar. Exactly-once? At-least-once with idempotent dedupe? Is approximate okay for speed?
Retention & compliance. How long is the data kept, where must it live, and is any of it PII?

Turn words into numbers — the back-of-envelope you do out loud. Interviewers love watching you estimate.

Daily volume = events/sec × seconds/day × bytes/event. 50k/s × 86,400 × 1 KB ≈ 4.3 TB/day raw.
Peak vs average — design ingest for the peak (often 3–5× average), storage for the average.
Growth — ask the growth rate; a design that survives 10× is a strong signal, one that melts at 2× is a weak one.
Read vs write ratio — a write-heavy firehose and a read-heavy serving store want opposite optimisations.

The data contract — the artifact that prevents 80% of production incidents. A contract is the agreement between the producer and your pipeline.

Schema + types. Field names, types, nullability, and units (is duration seconds or milliseconds?).
Grain. What does one row mean? "One click event", "one order line", "one daily user snapshot." Everything downstream depends on this.
Keys. The primary/business key (for dedupe and joins) and any partition key.
Semantics. What events mean, when they fire, and edge cases (a "purchase" event on a refund?).
Schema evolution. How new fields, renamed fields, and type changes are handled — additive-only, versioned, or a registry with compatibility checks.
Ownership + SLA. Who owns the source, how you are notified of changes, and the source's own uptime/lateness guarantees.

Common anti-patterns to pre-empt.

Assuming volume instead of asking — you might build a Spark cluster for 10 GB/day, or a single-node script for 10 TB/day.
Ignoring schema evolution — the upstream team adds a field and your rigid pipeline breaks at 2 a.m.
Not pinning the grain — half of all double-counting bugs are a grain misunderstanding.
Treating freshness and latency as the same thing — freshness is "how old is the newest data"; latency is "how fast a query returns." They drive different parts of the design.

Sizing a clickstream firehose — a worked whiteboard example

Detailed explanation. The first thing to do with any volume question is a back-of-envelope estimate narrated out loud, because the interviewer wants to see that you can reason from events/second to an infrastructure shape without a calculator. Take a clickstream: an interviewer says "a popular app", you ask for the numbers, and you convert peak QPS and event size into a daily volume, a yearly storage footprint, and the design constraints those numbers impose (columnar, partitioned, buffered ingest). The point is not precision — it is turning an adjective into an order of magnitude.

Ask for the two inputs — peak events/second and average bytes/event.
Multiply to daily volume — then to monthly and yearly for the storage plan.
Separate peak from steady state — ingest is sized for peak, storage for the average.
Read the order of magnitude — GB/day → a single warehouse load; TB/day → columnar lake + partitioning + a distributed engine.

Question. An app emits click events at 50k/sec peak (10k/sec average), ~1 KB each. Size the daily and yearly volume and state the two design constraints those numbers impose.

Input.

Quantity	Value
Peak event rate	50,000 events/sec
Average event rate	10,000 events/sec
Average event size	~1 KB
Retention (hot)	13 months

Code.

# Back-of-envelope, narrated on the whiteboard

peak ingest    = 50,000 ev/s * 1 KB      = ~50 MB/s   -> size the BUFFER for this
avg  volume    = 10,000 ev/s * 86,400 s  = 864M ev/day
raw  daily     = 864M * 1 KB             = ~864 GB/day (~0.9 TB/day)
raw  yearly    = ~0.9 TB * 365           = ~316 TB/yr raw

# After columnar + compression (~5-8x) the STORED footprint is far smaller:
stored/yr      ~ 316 TB / 6              = ~50 TB/yr compressed columnar

# Two constraints these numbers impose:
#   1) ~0.9 TB/day => object-store data lake, columnar (Parquet), partition by date
#   2) 50 MB/s peak, 5x over average => a buffer that absorbs spikes + enables replay

Step-by-step trace.

Ask for peak rate and event size — the two numbers every volume estimate needs.
Multiply peak rate by size to size the ingest buffer (~50 MB/s), because ingest must survive the peak, not the average.
Multiply average rate by seconds/day to get daily event count, then by size for raw daily bytes (~0.9 TB/day).
Apply a columnar-compression factor to convert raw bytes into a realistic stored yearly footprint (~50 TB/yr).
Read the magnitudes into two hard constraints: a partitioned columnar lake for storage, and a spike-absorbing buffer for ingest.

Output:

Metric	Estimate	Design consequence
Daily raw volume	~0.9 TB/day	columnar lake + date partitioning
Yearly stored	~50 TB/yr (compressed)	tiered retention, lifecycle rules
Peak ingest	~50 MB/s	buffered ingest, spike absorption

Rule of thumb. Always size ingest for the peak and storage for the average; a design that quietly assumes average-rate ingest falls over the first time traffic spikes 5×.

Writing a data contract with schema-evolution rules — a worked whiteboard example

Detailed explanation. The moment your pipeline depends on data you do not produce, a data contract is what stops an upstream change from silently corrupting everything downstream. On the whiteboard, sketch the contract as a small table — field, type, nullability, semantics — and then spend most of your breath on the evolution rules, because that is the part juniors skip and seniors obsess over. The core idea: additive changes are safe, breaking changes must be versioned, and a schema registry with compatibility enforcement turns "the upstream broke us" into "the upstream's incompatible change was rejected at publish time."

Pin the grain and key first — one row = one event, keyed by event_id; everything else hangs off that.
Classify each change — additive (new nullable field) is backward-compatible; renaming or retyping is breaking.
Enforce with a registry — require producers to register schemas; reject incompatible ones (Avro/Protobuf with backward-compat mode).
Version breaking changes — bump schema_version; run old and new in parallel until consumers migrate.

Question. You ingest events from a team you do not control. Design the data contract and the rules that let their schema evolve without breaking your pipeline.

Input.

Contract element	Decision
Grain	one row = one click event
Business key	`event_id` (UUID, dedupe key)
Compatibility mode	backward-compatible (new consumers read old data)
Breaking change policy	version bump + parallel run

Code.

# Data contract v1 (registered in a schema registry, backward-compat enforced)
event_id        STRING   NOT NULL   # UUID, unique per event, dedupe key
user_id         STRING   NOT NULL   # stable user identifier
event_type      STRING   NOT NULL   # enum: view|click|scroll|purchase
event_ts        TIMESTAMP NOT NULL  # event time (UTC), NOT ingest time
properties      MAP<STRING,STRING>  # open-ended, additive-friendly
schema_version  INT      NOT NULL   # 1

# Evolution rules:
#   ADD a nullable field          -> OK (backward compatible),   version stays or +1 minor
#   ADD to `properties` map       -> OK, no schema change needed (open map)
#   RENAME or RETYPE a field       -> BREAKING -> new schema_version, dual-write both
#   REMOVE a required field        -> BREAKING -> deprecate first, then version bump
# Registry REJECTS any producer publish that violates backward compatibility.

Step-by-step trace.

Fix the grain (one click event) and the business key (event_id) so dedupe and joins have a stable anchor.
Model open-ended attributes as a properties map so that most new fields are additive and need no schema change at all.
Declare backward-compatibility as the registry mode: consumers written for v1 must keep reading data produced under later minor versions.
Route genuinely breaking changes (rename, retype, drop-required) through a schema_version bump with a parallel dual-write window until every consumer migrates.
Let the registry mechanically reject an incompatible producer publish, converting a 2 a.m. incident into a build-time error for the upstream team.

Output:

Change type	Compatible?	Handling
Add nullable field	yes	additive, no version bump needed
Add map entry	yes	open `properties` map, no schema change
Rename / retype field	no	version bump + parallel run
Drop required field	no	deprecate → version bump

Rule of thumb. Make additive changes free and breaking changes expensive: an open properties map plus a registry in backward-compatible mode absorbs most evolution without a single downstream edit.

Whiteboard scenario on requirements clarification

The interviewer says: "Design the analytics data pipeline for a brand-new mobile app. That's all I'll tell you — go." There are no numbers, no named consumers, and no stack. Before drawing anything, produce the requirement set and data contract that make a design possible, and choose the initial architecture shape they imply.

Solution Using a clarify-first requirement pass into a data contract

Approaches an interviewer might see (and how they score).

A. Immediately draw "Kafka → Spark → Snowflake → Looker" and start explaining each box.
B. Ask nothing; assume it's huge and design a full streaming lakehouse to be safe.
C. Ask nothing; assume it's tiny and design a single nightly Python script.
D. Run the seven clarifying questions, size the volume, pin a data contract, then draw the simplest architecture that fits.

Code.

Elimination:
A  design-before-clarify -> buzzword stack with no requirement behind it   [reject: top anti-signal]
B  assume huge -> over-engineered, expensive, unjustified                  [reject: no numbers]
C  assume tiny -> falls over the moment volume is non-trivial              [reject: no numbers]
D  clarify -> size -> contract -> simplest fitting design                  [ACCEPT]

Step-by-step trace.

The prompt is deliberately empty; the only winning first move is to fill the requirements block, not the architecture.
A is the classic failure — a stack with no requirement tying any box to a number; the interviewer planted the ambiguity to catch exactly this.
B and C both skip clarification and guess the scale; one over-builds, one under-builds, and both are indefensible because neither is tied to a measured requirement.
D asks purpose → consumers → volume → freshness → latency → correctness → retention, converts the answers to numbers, and pins a data contract (grain, key, evolution rules).
Only after the numbers exist does D draw the architecture, choosing the simplest component per stage that satisfies the measured freshness and consumer shape.

Output:

Move	Verdict
Draw a stack first	✗ designs before clarifying
Assume scale (either way)	✗ unmeasured, indefensible
Clarify → size → contract → design	✓ requirement-driven

Why this works — concept by concept:

Clarify before you draw — refusing to design until the fuzzy words are numbers is the single behaviour the round is built to measure; it is worth more than any technology choice.
Size in numbers — a back-of-envelope volume estimate turns "big data" into "0.9 TB/day", which mechanically selects the storage and engine.
Data contract first — pinning grain, key, and evolution rules up front prevents the double-counting and 2-a.m.-break failures that dominate real pipelines.
Simplest fitting design — choosing the least complex component that honours the measured requirements signals seniority; over-building is as much a red flag as under-building.
Cost — the effort here is ten minutes of questions, and it prevents the largest class of downstream rework; every later stage inherits its correctness from this one.

Design
Topic — design
Pipeline and system design problems

Practice →

ETL Topic — data-validation Schema and data-contract validation problems

Practice →

3. Ingestion & storage design — batch vs streaming, format, partition, lake vs warehouse

One decision dominates ingestion — batch vs streaming — and one dominates storage — format and partitioning

The invariant: the freshness SLA picks batch versus streaming (or the micro-batch middle), and the query pattern picks the storage — columnar and partitioned for analytics, a lake for cheap raw scale, a warehouse for governed SQL, and a lakehouse table format when you want both — while the recurring failure mode across all of it is the small-files problem. Every ingestion-and-storage question is a variation on matching those two decisions to the numbers you extracted in stage one.

Batch vs streaming — decide by the freshness SLA, not by fashion.

Batch. Periodic jobs (hourly/daily) over bounded files. Simplest to build, cheapest, easiest to reason about and backfill. The right answer whenever freshness of minutes-to-hours is acceptable — which is most analytics.
Streaming. Continuous processing of unbounded events with windowing and watermarks. The right answer for sub-minute freshness (fraud, real-time dashboards, alerting), at the cost of more operational complexity and harder debugging.
Micro-batch. Small frequent batches (every 1–5 minutes). The pragmatic middle for "near-real-time" that avoids full streaming complexity — often the best whiteboard answer.
CDC (change data capture). Streaming database changes (inserts/updates/deletes) off the transaction log into your lake/warehouse — the standard way to mirror an OLTP source without hammering it.

File format & layout — the choices that decide scan cost.

Columnar (Parquet/ORC) for analytics: reads only the columns a query needs, compresses far better than row formats. The default for lake tables.
Row (Avro/JSON) for landing and streaming transport where whole records are written/read together.
Partitioning by a low-cardinality column queries filter on (usually event_date) so the engine skips whole partitions.
Compaction merges the many small files that streaming/micro-batch produce into fewer large ones — because thousands of tiny files wreck read performance and metadata overhead.

Storage tier — lake vs warehouse vs lakehouse.

Data lake — object storage (S3/GCS/ADLS) holding raw and refined files. Cheapest, most flexible, decouples storage from compute. Needs a table format (Iceberg/Delta/Hudi) to get ACID and schema evolution.
Warehouse — a managed columnar SQL engine (BigQuery/Snowflake/Redshift). Governed, fast SQL, great for serving analysts; you pay for the managed compute.
Lakehouse — a table format over the lake (Iceberg/Delta) giving warehouse-like ACID, time travel, and schema evolution on cheap object storage; the modern default for large analytics.
The bronze/silver/gold (medallion) layering — bronze = raw as-ingested, silver = cleaned/deduped/conformed, gold = business-level aggregates the consumers query. Draw these three tiers on the board; interviewers love it.

Common anti-patterns.

Streaming when hourly is fine — you paid a large complexity tax for freshness nobody asked for.
JSON as the analytics storage format — every query scans every field; use columnar.
No partitioning — every query is a full-table scan.
The small-files problem — micro-batch writing millions of tiny files with no compaction step.
Reading the OLTP database directly for analytics — hammers production; use CDC into the lake instead.

Choosing format + partitioning for a 2 TB/day event table — a worked whiteboard example

Detailed explanation. Storage design is mostly one decision repeated: pick a columnar format and partition by the column your queries filter on, because together they decide how many bytes every future query scans. On the whiteboard, take the clickstream table, choose Parquet for columnar compression, partition by event_date (low cardinality, always filtered), and add a compaction step so the micro-batch writes do not degrade into millions of small files. State the partition-cardinality rule explicitly: partition by date, never by a high-cardinality key like user_id.

Format — Parquet (columnar) so queries read only needed columns and compress ~5–8×.
Partition key — event_date (365 partitions/yr) that every analytical query filters on.
Sub-partition sparingly — add event_hour only if queries routinely filter by hour and partitions are large.
Compaction — a periodic job merges small files into ~128–512 MB targets.

Question. A 2 TB/day clickstream lands as micro-batch files. Choose the format, partition scheme, and layout that minimise query scan cost without creating a small-files problem.

Input.

Fact	Value
Daily volume	~2 TB/day
Query pattern	filter by date range, group by `event_type`
Write pattern	micro-batch every 5 min (many small files)
Retention	13 months

Code.

-- Lake table: columnar + date-partitioned (Iceberg/Delta/Hive-style)
CREATE TABLE analytics.click_events (
  event_id     STRING,
  user_id      STRING,
  event_type   STRING,
  event_ts     TIMESTAMP,
  event_date   DATE          -- partition column, derived from event_ts
)
USING iceberg
PARTITIONED BY (event_date);

-- Compaction job (runs hourly): merge tiny micro-batch files -> ~256 MB files
CALL system.rewrite_data_files(
  table => 'analytics.click_events',
  options => map('target-file-size-bytes','268435456')  -- 256 MB
);

-- A typical query now prunes to a date range and scans only two columns:
SELECT event_type, COUNT(*) AS n
FROM analytics.click_events
WHERE event_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-07'
GROUP BY event_type;

Step-by-step trace.

Choose Parquet/Iceberg columnar storage so the event_type count reads two columns instead of the full ~1 KB row.
Partition by event_date because every analytical query filters by date; the 7-day query prunes ~358 of 365 partitions before scanning.
Reject partitioning by user_id — millions of partitions would explode metadata and create the small-partitions version of the small-files problem.
Add an hourly compaction job that rewrites the 5-minute micro-batch files into ~256 MB files, keeping read throughput and metadata sane.
Confirm the query plan: partition pruning by date + column pruning by projection means the 7-day aggregate scans a tiny fraction of the 2 TB/day table.

Output:

Design	Bytes scanned (7-day group-by)
Row JSON, no partitions	~14 TB (full scan)
Parquet, no partitions	~2–3 TB (column pruning only)
Parquet + date partition + compaction	~tens of GB

Rule of thumb. Columnar format plus partition-by-the-filtered-date plus a compaction step is the default lake layout; partition by a high-cardinality key and you trade a scan problem for a metadata explosion.

Designing CDC ingestion from OLTP into a warehouse — a worked whiteboard example

Detailed explanation. When an interviewer says "we have a Postgres orders database and analysts need it in the warehouse, fresh," the wrong answer is a nightly SELECT * that scans the production database, and the right answer is change data capture: read the database's write-ahead log, stream inserts/updates/deletes into the lake, and merge them into a warehouse table so it mirrors the source without touching production for reads. The subtlety the interviewer probes is how you apply updates and deletes idempotently — a CDC stream is a log of changes, and you must upsert by primary key and honour tombstones for deletes.

Capture from the log, not the table — a log-based CDC connector reads the WAL, so it does not query or lock production.
Land raw change events in bronze — each with an op type (I/U/D), primary key, and a commit/log sequence number (LSN).
Merge into a current-state table — upsert on primary key, ordered by LSN, applying deletes as tombstones.
Optionally keep history — an SCD-2 table if analysts need "what did this row look like last Tuesday?"

Question. Mirror a Postgres orders table into the warehouse with minutes-fresh data, without running analytical load against production. Design the CDC ingestion and the merge.

Input.

Requirement	Decision
Source	Postgres `orders` (OLTP)
Freshness	minutes, not real-time
Production impact	none — no analytical reads on OLTP
Correctness	updates/deletes applied exactly, in order

Code.

# Architecture (boxes and arrows)
 [ Postgres WAL ] --(log-based CDC connector)--> [ change stream: I/U/D + pk + LSN ]
        |                                                     |
        v                                                     v
 [ bronze: raw change events ]  --(micro-batch merge, ordered by LSN)-->  [ warehouse: orders_current ]

-- Merge the latest change per key (ordered by log sequence number) into current state
MERGE INTO warehouse.orders_current t
USING (
  SELECT * EXCEPT(rn) FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY lsn DESC) AS rn
    FROM bronze.orders_changes
    WHERE ingested_at > (SELECT last_watermark FROM meta.cdc_state)
  ) WHERE rn = 1            -- keep only the newest change per order_id
) s
ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE                       -- honour tombstones
WHEN MATCHED AND s.op IN ('U','I') THEN UPDATE SET *          -- apply latest state
WHEN NOT MATCHED AND s.op IN ('U','I') THEN INSERT *;         -- new rows

Step-by-step trace.

A log-based CDC connector reads the Postgres WAL, so analytics never issues a query against the production database — zero read load on OLTP.
Each change lands in bronze as a row with an op type (I/U/D), the primary key, and an LSN that gives a total order to changes.
The micro-batch merge deduplicates to the newest change per order_id using ROW_NUMBER() ... ORDER BY lsn DESC, so out-of-order or duplicate CDC events resolve to the true latest state.
WHEN MATCHED AND op='D' applies deletes as row removals (tombstones), while inserts/updates upsert the current row — the merge is idempotent, so re-running it is safe.
A watermark on ingested_at advances so each run only processes new changes, keeping the warehouse minutes-fresh.

Output:

Concern	Result
Production read load	none (reads the WAL, not the table)
Updates & deletes	applied exactly via keyed MERGE + tombstones
Out-of-order changes	resolved by LSN ordering
Freshness	minutes (micro-batch merge)

Rule of thumb. Never scan an OLTP table for analytics — capture its log, land the changes, and MERGE by primary key ordered by LSN; that mirrors the source idempotently without touching production.

Whiteboard scenario on ingestion + storage

The interviewer says: "We have an operational Postgres database of orders. Analysts want near-real-time dashboards on it, and data scientists want the full history for modelling. Design the ingestion and storage so production is never impacted and both consumers are served." Freshness target is a few minutes; history must be queryable for years.

Solution Using log-based CDC into a partitioned lakehouse with current + history tables

Approaches an interviewer might see.

A. Nightly SELECT * from Postgres into the warehouse.
B. Point the dashboards directly at read replicas of Postgres.
C. Log-based CDC → bronze change log → MERGE into a partitioned orders_current (dashboards) + append an SCD-2 orders_history (modelling).
D. Dual-write from the app to Postgres and the warehouse simultaneously.

Code.

Elimination:
A  nightly SELECT * -> not near-real-time + heavy scan on production      [reject: freshness + prod impact]
B  dashboards on OLTP replicas -> analytics load + no cheap history        [reject: prod impact + no history]
D  app dual-write -> app now owns warehouse consistency; brittle           [reject: coupling + no replay]
C  CDC -> bronze log -> MERGE current + SCD-2 history                       [ACCEPT]

Step-by-step trace.

Constraint keywords: "near-real-time" (minutes) + "never impact production" + "full history for modelling" → CDC, not batch scans or direct replica queries.
A fails both freshness (nightly) and production impact (a full-table scan on OLTP) — eliminate.
B pushes analytical dashboard load onto database replicas and offers no cheap, columnar history for the data scientists — eliminate.
D couples the application to warehouse availability and gives no replay/backfill path if the warehouse write fails — eliminate.
C reads the WAL (no prod impact), lands changes in bronze, MERGEs to a partitioned orders_current for minutes-fresh dashboards, and appends an SCD-2 orders_history on cheap lakehouse storage for years of modelling data.

Output:

Need	Mechanism
No production impact	log-based CDC off the WAL
Minutes-fresh dashboards	MERGE into `orders_current`
Years of history for modelling	append SCD-2 `orders_history` in the lakehouse
Cheap, queryable storage	columnar + date-partitioned lake tables

Why this works — concept by concept:

CDC over batch scans — capturing the transaction log mirrors the source continuously without ever issuing an analytical query against production, satisfying both freshness and the no-impact constraint at once.
Current + history split — a MERGEd current-state table serves dashboards while an append-only SCD-2 table serves modelling, because those two consumers want opposite shapes.
Idempotent keyed MERGE — applying the newest change per primary key (ordered by LSN, deletes as tombstones) makes re-runs safe and out-of-order events harmless.
Lakehouse storage — columnar, partitioned, ACID table formats give cheap object-store economics with warehouse-like queryability for the multi-year history.
Cost — CDC moves only the changes (kilobytes) instead of re-scanning the whole table nightly, so it is both fresher and dramatically cheaper than a full reload.

Design
Course — ETL system design
ETL system design for data engineering interviews

Practice →

Streaming Topic — streaming Streaming ingestion and CDC problems

Practice →

4. Processing, modeling & serving — transforms, idempotency, serving, backfills

The middle of the pipeline is judged on idempotency and on modeling the data for the consumer

The invariant: the transformation layer is scored on whether re-running it is safe (idempotency) and on whether the output is shaped for the consumer (a dimensional model or pre-aggregate served from the right store), and every senior design has an explicit answer for backfills — reprocessing history without breaking the daily job. The recurring theme is "make every step replayable, and model for how the data is read, not how it arrived."

Transformation design — staging → transform → publish.

Staging — land raw input untouched (bronze) so you can always reprocess from source.
Transform — clean, dedupe, conform types, join reference data, apply business logic (silver).
Publish — write the consumer-facing tables (gold) atomically, so readers never see a half-written result.
ELT vs ETL engine — transform in the warehouse with SQL (dbt/Dataform) when the data is already there and the logic is set-based; use Spark/Flink when you need heavy distributed compute, complex code, or streaming.

Idempotency & exactly-once — the property that makes re-runs safe. This is the single most-probed processing concept.

Dedupe key — carry a business key (event_id) and drop duplicates, because most streams are at-least-once.
Upsert / MERGE — apply results by key so re-processing overwrites rather than appends.
Partition overwrite — write a whole partition atomically (INSERT OVERWRITE/WRITE_TRUNCATE on dt=2026-08-14) so a re-run replaces exactly that day.
Watermarks + allowed lateness — in streaming, bound how long a window stays open so late events update the right window instead of duplicating it.
Exactly-once as end-to-end idempotency — you rarely get true exactly-once transport; you achieve it with at-least-once delivery plus idempotent, keyed writes.

Modeling for the consumer.

Star schema — a central fact table (events/orders) surrounded by dimension tables (user, product, date); the default for analyst-facing warehouses.
Slowly changing dimensions (SCD) — type 1 overwrites, type 2 keeps history with effective-date ranges; pick by "do consumers need the past value?"
Pre-aggregates / materialized views — precompute the heavy rollups the dashboard reads repeatedly.
Serving-store choice — a warehouse for analyst SQL, an OLAP store (Druid/ClickHouse) for sub-second slice-and-dice dashboards, a key-value store for point lookups, an API layer for services.

Backfills & reprocessing — the senior signal.

Make history replayable — because you kept bronze, you can recompute any past partition from raw.
Partition-scoped, idempotent — a backfill overwrites specific partitions and produces the same result as the original run.
Decouple from the daily job — run backfills as a separate, throttled job so they do not starve or corrupt the live pipeline.
Version the logic — if the transform changed, note which version produced which partitions.

Common anti-patterns.

Append-only transforms — a re-run double-counts because nothing is keyed or overwritten.
Transforming for arrival, not for reads — a table nobody can query the way the dashboard needs.
No backfill story — "we'd just re-run it" with no idempotency, guaranteeing duplicates.
A single monolithic job — ingestion, transform, and serving welded together so one failure takes down everything and backfills are impossible.

Idempotent upsert for a metrics table — a worked whiteboard example

Detailed explanation. The most common processing follow-up is "what happens if this job runs twice?" — and the answer must be "nothing changes," which is idempotency. On the whiteboard, take a daily metrics rollup and show two equivalent ways to make it re-run-safe: a keyed MERGE that upserts one row per (date, metric), or an atomic partition overwrite that replaces the whole day. Both guarantee that a retry, a backfill, or a late-arriving correction converges to the same table instead of appending duplicates.

Key the output — one row per (metric_date, metric_name); that key is what makes upsert deterministic.
MERGE to upsert — matched keys update, unmatched insert; re-running recomputes the same rows.
Or overwrite the partition — INSERT OVERWRITE ... PARTITION(metric_date=...) atomically replaces the day.
Never plain INSERT — appending on re-run is the canonical double-counting bug.

Question. A daily job computes revenue and active users per day. Make it safe to re-run (retries, backfills) without double-counting.

Input.

Fact	Value
Output grain	one row per `(metric_date, metric_name)`
Re-run triggers	retries, backfills, late-data corrections
Correctness bar	re-run must not change totals
Source	partitioned `click_events`

Code.

-- Option A: idempotent MERGE (upsert one row per date+metric)
MERGE INTO gold.daily_metrics t
USING (
  SELECT DATE(event_ts) AS metric_date,
         'revenue'       AS metric_name,
         SUM(revenue)    AS metric_value
  FROM analytics.click_events
  WHERE event_date = DATE '2026-08-14'
  GROUP BY 1, 2
) s
ON  t.metric_date = s.metric_date AND t.metric_name = s.metric_name
WHEN MATCHED     THEN UPDATE SET metric_value = s.metric_value
WHEN NOT MATCHED THEN INSERT (metric_date, metric_name, metric_value)
                     VALUES (s.metric_date, s.metric_name, s.metric_value);

-- Option B: atomic partition overwrite (replace the whole day)
INSERT OVERWRITE gold.daily_metrics PARTITION (metric_date = DATE '2026-08-14')
SELECT 'revenue' AS metric_name, SUM(revenue) AS metric_value
FROM analytics.click_events
WHERE event_date = DATE '2026-08-14';

Step-by-step trace.

Define the output grain as (metric_date, metric_name) so every run targets a deterministic set of keys.
Option A's MERGE matches on that key: a second run recomputes the same value and UPDATEs in place rather than inserting a duplicate row.
Option B rewrites the entire metric_date=2026-08-14 partition atomically, so a re-run replaces the day's rows wholesale — same end state.
A late correction (a fixed source record) re-runs the job for that date only; because both options are keyed/partition-scoped, the correction lands without touching other days.
Either way, running the job N times yields the identical table it would after one run — the definition of idempotency.

Output:

Runs	Plain INSERT	MERGE / partition overwrite
1×	correct	correct
3× (retries)	3× double-counted	identical to 1×
backfill one day	duplicates	replaces just that day

Rule of thumb. Never INSERT results into an analytics table — MERGE by the output key or INSERT OVERWRITE the partition, so retries and backfills converge instead of duplicating.

Designing a serving layer for a real-time metrics dashboard — a worked whiteboard example

Detailed explanation. "Where does the dashboard read from?" is the serving question, and the trap is serving low-latency dashboards straight off a raw warehouse table that scans terabytes per refresh. The design move is to separate how you store history from how you serve reads: keep the detailed history in the lake/warehouse, but serve the dashboard from a pre-aggregated, purpose-built store sized for the read pattern (sub-second slice-and-dice → an OLAP store or a materialized pre-aggregate). Match the serving store to the p99 latency and the query shape, not to where the data happens to live.

Pre-aggregate the heavy rollups — the dashboard reads a small metrics table, not the raw firehose.
Pick the store by read pattern — sub-second multi-dimensional slice → OLAP (Druid/ClickHouse); simple point lookups → key-value; analyst ad-hoc SQL → warehouse.
Keep detail queryable elsewhere — drill-downs hit the warehouse; the hot dashboard hits the pre-aggregate.
Refresh incrementally — update the serving store as new partitions land, not by full recompute.

Question. A dashboard needs sub-second slice-and-dice (by country, device, event_type) over the last 30 days, refreshed every few minutes. Design the serving layer.

Input.

Requirement	Value
Read latency	sub-second p99
Query shape	group/filter by country, device, event_type
Freshness	few minutes
History for drill-down	full detail, but rarely queried

Code.

# Serving-layer architecture (boxes and arrows)
 [ gold pre-aggregates ]  --incremental load-->  [ OLAP store: pre-cut cube ]
        ^                                                   |
        | (recompute changed partitions)                    v  sub-second slice/dice
 [ warehouse / lake detail ]  <--drill-down queries--  [ dashboard ]

-- Pre-aggregate the exact dimensions the dashboard slices by (small, fast to serve)
CREATE MATERIALIZED VIEW gold.metrics_cube AS
SELECT event_date, country, device, event_type,
       COUNT(*)            AS events,
       COUNT(DISTINCT user_id) AS users,
       SUM(revenue)        AS revenue
FROM analytics.click_events
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY event_date, country, device, event_type;
-- The OLAP store ingests this cube incrementally; dashboards query the cube, not raw events.

Step-by-step trace.

Separate concerns: the lake/warehouse owns detailed history; a purpose-built serving store owns the hot dashboard reads.
Pre-aggregate to exactly the dimensions the dashboard slices by (country, device, event_type), collapsing terabytes of raw events into a small cube.
Load that cube into an OLAP store built for sub-second multi-dimensional filtering, so a p99 read hits pre-cut aggregates rather than scanning raw data.
Refresh incrementally as each new partition lands, holding the few-minutes freshness without a full recompute.
Route rare drill-downs to the warehouse detail, so the hot path stays fast while full granularity remains available on demand.

Output:

Path	Store	Latency
Hot dashboard slice/dice	OLAP pre-aggregate cube	sub-second
Rare drill-down to detail	warehouse / lake	seconds
History storage	partitioned lakehouse	(cold, cheap)

Rule of thumb. Serve the dashboard from a pre-aggregate sized for its read pattern, not from the raw table — decouple "where history lives" from "how reads are served."

Designing a metrics/reporting pipeline end to end — a worked whiteboard example

Detailed explanation. A frequent full-scope prompt is "design the pipeline behind our KPI/reporting dashboard," which forces you to connect all seven stages into one coherent flow. The winning shape is the medallion pipeline: ingest events to bronze, transform/dedupe to silver, model into a star schema and pre-aggregate to gold, and serve gold to the dashboard — with every step idempotent and backfillable. The interviewer is checking whether you can keep the whole picture coherent while still going deep on idempotency and modeling when asked.

Bronze — raw events landed, partitioned by date, immutable.
Silver — deduped on event_id, typed, sessionized, conformed.
Gold — a star schema (fact + dimensions) plus pre-aggregates for the KPIs.
Serve — the dashboard reads gold pre-aggregates; drill-downs hit the star.

Question. Design the end-to-end pipeline for a daily KPI dashboard (revenue, DAU, conversion) over the clickstream, with correct history and safe re-runs.

Input.

Requirement	Value
Output	revenue, DAU, conversion per day + by segment
Freshness	daily (hourly nice-to-have)
Correctness	idempotent, backfillable
Consumers	BI dashboard + analysts

Code.

# End-to-end medallion pipeline
 [ events ] -> [ bronze: raw, dt-partitioned ]
            -> [ silver: dedupe(event_id), sessionize, type-cast ]
            -> [ gold: fact_events + dim_user + dim_date  (star) ]
            -> [ gold: daily_kpis pre-aggregate ]  -> [ BI dashboard ]
                                                    -> [ analysts (drill to star) ]
# Every stage: partition-scoped, idempotent (MERGE / INSERT OVERWRITE), replayable from bronze.

-- Gold KPI pre-aggregate, idempotently rebuilt per day
INSERT OVERWRITE gold.daily_kpis PARTITION (metric_date = DATE '2026-08-14')
SELECT
  COUNT(DISTINCT CASE WHEN event_type='view'     THEN user_id END) AS dau,
  SUM(CASE WHEN event_type='purchase' THEN revenue END)           AS revenue,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN event_type='purchase' THEN user_id END),
    COUNT(DISTINCT CASE WHEN event_type='view'     THEN user_id END)
  )                                                                AS conversion
FROM silver.click_events
WHERE event_date = DATE '2026-08-14';

Step-by-step trace.

Land raw events in bronze, partitioned by date and never mutated, so any day can be recomputed from source.
Build silver by deduping on event_id and sessionizing — the one clean, conformed copy every gold table derives from.
Model gold as a star (fact + user/date dimensions) for flexible analyst queries, and add a daily_kpis pre-aggregate for the dashboard's fixed metrics.
Rebuild each gold partition with INSERT OVERWRITE, so retries and backfills replace exactly one day and never double-count.
Serve the dashboard from daily_kpis and let analysts drill from the star, keeping the hot path small and the detail available.

Output:

Stage	Table	Property
Ingest	`bronze.click_events`	raw, immutable, dt-partitioned
Transform	`silver.click_events`	deduped, typed, sessionized
Model	`gold.fact_events` + dims	star schema
Serve	`gold.daily_kpis`	idempotent pre-aggregate

Rule of thumb. Draw the medallion (bronze → silver → gold) and make every arrow idempotent and replayable from bronze — that single picture answers "history", "re-runs", and "modeling" in one stroke.

Whiteboard scenario on processing + serving

The interviewer says: "Events flow in fine. Design the processing and serving so that (a) re-running any day is safe, (b) analysts get a flexible model, and (c) a KPI dashboard returns in under a second — and be ready to backfill six months when we change a metric definition." Freshness is hourly; correctness must survive re-runs.

Solution Using an idempotent medallion transform into a star schema plus a pre-aggregate serving layer

Approaches an interviewer might see.

A. One big job that appends computed KPIs straight to the dashboard table.
B. Serve the dashboard by querying raw events live each refresh.
C. Medallion transforms (bronze→silver→gold), idempotent per-partition writes, a star schema for analysts, and a pre-aggregate the dashboard reads; backfills re-run partition-scoped from bronze.
D. Precompute everything once and never reprocess.

Code.

Elimination:
A  append-only single job -> re-runs double-count, no model, no backfill   [reject: not idempotent]
B  dashboard on raw events -> terabyte scans, not sub-second               [reject: latency]
D  compute-once, no reprocess -> can't backfill a changed metric           [reject: no backfill]
C  medallion + idempotent + star + pre-aggregate + partition backfill      [ACCEPT]

Step-by-step trace.

Constraints: "re-running any day is safe" → idempotency; "flexible model" → star schema; "sub-second dashboard" → pre-aggregate; "backfill six months" → partition-scoped replay from bronze.
A appends KPIs directly, so a retry double-counts and there is no model or backfill path — eliminate on idempotency.
B serves the dashboard off raw events, scanning terabytes per refresh — it cannot hit sub-second — eliminate on latency.
D never reprocesses, so a changed metric definition cannot be backfilled across six months — eliminate.
C keeps immutable bronze, builds deduped silver, models a gold star plus a KPI pre-aggregate, writes every partition with INSERT OVERWRITE/MERGE, and backfills by re-running specific partitions from bronze — satisfying all three requirements at once.

Output:

Requirement	Mechanism
Safe re-runs	partition-scoped idempotent writes
Flexible analyst model	gold star schema
Sub-second dashboard	gold pre-aggregate
Six-month backfill	partition replay from immutable bronze

Why this works — concept by concept:

Idempotent partition writes — MERGE/INSERT OVERWRITE per partition make retries and backfills converge to one correct state, which is the entire "safe re-run" requirement.
Medallion layering — immutable bronze plus a single clean silver is what makes a six-month backfill possible: you recompute gold from raw without re-ingesting.
Star schema for flexibility — a fact-plus-dimensions model lets analysts slice arbitrarily, satisfying "flexible model" without bespoke tables per question.
Pre-aggregate for latency — serving the dashboard from a small pre-cut table is the only way to hit sub-second over a terabyte-scale history.
Cost — computing each layer once and serving from a compact aggregate minimises repeated scans; backfills touch only the affected partitions, so reprocessing six months costs six months of compute, not a full-history rescan every run.

Design
Topic — design
Data modeling and serving-layer design problems

Practice →

Analytics Topic — real-time-analytics Real-time metrics and aggregation problems

Practice →

5. Reliability, scale, cost & defending trade-offs

The last third of the round is failure modes, 10x scale, cost, and how well you argue

The invariant: once the happy path is drawn, the interviewer attacks it — "what happens when this fails?", "what breaks at 10×?", "why not Kafka?", "how much does this cost?" — and the score comes from having an explicit failure story (retries, dead-letter, data-quality gates, SLOs), a scaling story (partitioning, autoscaling, skew), a cost story (scan-less design, tiering), and the discipline to defend every choice with a trade-off tied to the requirements rather than a preference. The design is not finished when data flows; it is finished when it survives interrogation.

Reliability — the failure story every design needs.

Retries with backoff — most failures are transient; bounded exponential retries absorb them automatically.
Dead-letter queue (DLQ) — after N failed attempts, route the poison record aside so it cannot block the pipeline; inspect and replay later.
Data-quality gates — validate row counts, null rates, schema, and business invariants before publishing; fail closed rather than serve bad data.
SLAs / SLOs + alerting — define freshness and completeness objectives and alert when they are missed, not when someone notices at 9 a.m.
Lineage & idempotency — know what fed each table, and make every step re-runnable so recovery is "replay", not "reconstruct".

Scale — the 10x story.

Partitioning / sharding — split work by key so it parallelises; the fundamental lever for both storage and compute scale.
Autoscaling — scale workers to the backlog (streaming) or to the data size (batch) instead of a fixed cluster.
Skew & hotspotting — a few hot keys (a celebrity user, a mega-tenant) overload one partition; salt keys or handle hot keys separately.
Backpressure — when a downstream stage can't keep up, the buffer absorbs it (that is why you put a buffer in front of the processor).
Decoupling — a buffer between stages lets each scale independently and prevents a slow consumer from stalling the producer.

Cost — the cheapest correct design.

Scan less — partitioning, clustering, columnar formats, and pre-aggregates all reduce bytes scanned, which is usually the bill.
Tier storage — hot/warm/cold classes with lifecycle rules; keep only recent data on expensive fast storage.
Right-size compute — spot/preemptible workers for fault-tolerant batch; autoscale to zero when idle.
Avoid reprocessing — incremental (only-new-data) processing beats full recompute; CDC beats full reloads.

Defending trade-offs — how to answer the pushback.

Tie every choice to a requirement. "We chose hourly batch because the freshness SLA is one hour; streaming would add operational complexity for freshness nobody asked for."
Name what you gave up. Every decision has a cost; saying it out loud ("this trades some freshness for much lower cost and complexity") signals maturity.
Answer "why not X?" with the constraint, not taste. "Kafka would be right if we needed sub-second, multi-consumer replay; here a managed queue meets the SLA with less to operate."
Have a 10x answer ready. Know which component saturates first and how you would scale it (usually: add partitions / autoscale / add a buffer).

Common anti-patterns.

Happy-path-only designs — no retries, no bad-data handling, no failure story.
"It'll just scale" — no idea which component breaks first at 10×.
Defending with preference — "I like Spark" instead of a requirement-tied reason.
Ignoring cost entirely — a technically correct design that is wildly expensive is still a fail.

Adding reliability to a flaky pipeline — a worked whiteboard example

Detailed explanation. The most common reliability follow-up is "this stage fails sometimes and sometimes gets bad data — make it robust." The answer is a small, standard kit: retries with backoff for transient failures, a dead-letter queue for poison records, and a data-quality gate that refuses to publish a bad batch. On the whiteboard, wrap the fragile stage in these three guards and add an SLO alert, so a failure becomes an automatic retry, a bad record becomes a quarantined DLQ entry, and a bad batch becomes a blocked publish plus an alert — never a corrupted downstream table.

Retry + backoff — absorb transient network/service errors without human involvement.
Dead-letter queue — quarantine records that fail repeatedly instead of blocking or dropping them.
Data-quality gate — assert row counts, null rates, and invariants before the publish step; fail closed.
SLO + alert — page on missed freshness/completeness, not on a customer complaint.

Question. A transform stage intermittently fails on transient errors and occasionally receives malformed records. Make it reliable and observable without adding heavy infrastructure.

Input.

Symptom	Fix lever
Transient stage failures	retries + exponential backoff
A few malformed records abort the batch	dead-letter queue + `maxBadRecords`
Bad batch reaches the dashboard	data-quality gate before publish
Silent failures	SLO alert on freshness/completeness

Code.

# Guarded transform stage: retry -> DQ gate -> publish, poison rows -> DLQ
@retry(max_attempts=3, backoff="exponential")   # absorb transient failures
def run_stage(partition_date):
    df = read_silver(partition_date)

    good, bad = validate(df)                     # split malformed rows out
    write_dead_letter(bad)                       # quarantine, don't abort

    # Data-quality gate: fail CLOSED if the batch looks wrong
    assert good.count() > expected_min_rows(partition_date), "row-count drop"
    assert good.null_rate("user_id") < 0.01,               "null spike in user_id"

    write_partition_overwrite("gold.metrics", partition_date, good)  # idempotent publish

# Orchestrator: alert when the SLO (data fresh by 02:00) is missed
on_sla_miss("gold.metrics", deadline="02:00", action=page_oncall)

Step-by-step trace.

Wrap the stage in bounded exponential retries so a transient error triggers up to three automatic attempts instead of failing the run.
Split malformed records out and write them to a dead-letter queue, so a few poison rows are quarantined for later replay rather than aborting the whole batch.
Run a data-quality gate before publishing — assert the row count did not collapse and user_id nulls did not spike; if either fails, the publish is blocked (fail closed).
Publish with an idempotent partition overwrite, so even a retried run leaves exactly one correct copy of the day.
Attach an SLO alert on freshness, so a stall pages on-call at 02:00 instead of surfacing as a stale dashboard the next morning.

Output:

Before	After
Transient error fails the run	3 automatic retries with backoff
One bad row aborts the batch	bad rows quarantined in DLQ
Bad batch reaches dashboard	DQ gate blocks the publish
Silent stall	SLO alert pages on-call

Rule of thumb. Wrap every fragile stage in the same kit — retry, dead-letter, data-quality gate, SLO alert — so failures become automatic recoveries and bad data is quarantined, never served.

Scaling a pipeline 10x and defending the cost — a worked whiteboard example

Detailed explanation. "It works today; what breaks when traffic grows 10×?" is the scaling question, and a strong answer names the component that saturates first and the lever that fixes it, then defends the cost trade-off. On the whiteboard, walk the pipeline left to right at 10× load: the ingest buffer must absorb the higher peak, the processor must parallelize by partition and autoscale, hot keys threaten skew, and storage/scan cost grows — so you tier storage and process incrementally to keep the bill sublinear. The interviewer is testing whether you can reason about bottlenecks and money, not just draw boxes.

Find the first bottleneck — usually the single-threaded or fixed-capacity stage.
Parallelize by partition — more partitions = more concurrent workers; the core scale lever.
Autoscale + spot — scale workers to the backlog and use preemptible compute for fault-tolerant batch.
Handle skew — salt or isolate hot keys so one partition doesn't become the bottleneck.
Keep cost sublinear — incremental processing + tiered storage so 10× data isn't 10× cost.

Question. The clickstream pipeline runs comfortably at 5k events/sec. Traffic will grow to 50k/sec. What breaks first, how do you scale each stage, and how do you keep cost from growing 10×?

Input.

Stage	At 5k/s	Risk at 50k/s
Ingest buffer	1 partition	throughput ceiling
Transform	fixed workers	falls behind (backlog)
Storage	date-partitioned	scan cost grows with volume
Serving	queries raw	dashboard slows

Code.

# 10x scaling plan, stage by stage (defend each trade-off out loud)

INGEST   : 1 -> N buffer partitions  (parallel consumers)      [trade: more partitions to manage]
TRANSFORM: fixed cluster -> autoscale by backlog + spot nodes   [trade: some latency variance for cost]
SKEW     : salt hot keys (mega-tenant) into sub-partitions      [trade: a merge step at read time]
STORAGE  : hot(30d) SSD/warehouse + cold(>30d) object archive   [trade: slower cold reads for lower $]
SERVING  : pre-aggregate cube instead of raw scans              [trade: fixed set of dims, not arbitrary]

# Cost stays SUBLINEAR because:
#   - incremental processing: only new partitions, not full recompute
#   - tiered storage: 10x data but most of it on cheap cold storage
#   - autoscale-to-zero + spot: pay for throughput, not a fixed 10x cluster

Step-by-step trace.

Walk left to right and find the first ceiling: a single-partition buffer and a fixed-worker transform both saturate first as the rate climbs.
Scale ingest by adding buffer partitions and parallel consumers, and scale the transform by autoscaling workers to the backlog — throughput now tracks load.
Watch for skew: a mega-tenant's key overloads one partition, so salt that hot key into sub-partitions and merge at read time.
Attack cost, not just throughput: tier storage (hot 30 days, cold archive beyond), so 10× data mostly lands on cheap cold storage instead of 10× expensive fast storage.
Keep compute cost sublinear with incremental processing (only new partitions) and spot/preemptible workers, and defend each trade-off explicitly — e.g. "cold reads are slower, which is fine because history is rarely queried."

Output:

Stage	Scale lever	Trade-off defended
Ingest	partition the buffer	more partitions to operate
Transform	autoscale + spot	latency variance for lower cost
Skew	salt hot keys	a read-time merge
Storage	hot/cold tiering	slower cold reads
Serving	pre-aggregate	fixed dimensions

Rule of thumb. Scale by partitioning and autoscaling, keep cost sublinear with tiering and incremental processing, and defend every trade-off with the requirement it serves — "slower cold reads are fine because we rarely read old data."

Whiteboard scenario on hardening and defending the design

The interviewer says: "Good — now stress-test your clickstream pipeline. It's going viral: 10× traffic next month. Walk me through what fails first, how you make it reliable, what it costs, and defend why you didn't just use Kafka and Flink for everything." Freshness SLA is still hourly; budget is a real constraint.

Solution Using partition-scaled ingest, guarded idempotent transforms, tiered storage, and requirement-tied trade-off defence

Approaches an interviewer might see.

A. "We'll rewrite everything on Kafka + Flink to be safe."
B. "It's on the cloud, it'll autoscale — no changes needed."
C. Partition the buffer, autoscale the idempotent transform, guard with retries/DLQ/DQ gates, tier storage hot/cold, pre-aggregate serving — and defend hourly-batch-over-streaming with the freshness SLA.
D. Add more fixed servers to every stage.

Code.

Elimination:
A  rewrite on Kafka+Flink -> huge complexity for a 1-hour SLA; unjustified   [reject: over-engineering]
B  "it'll autoscale" -> no failure story, no cost story, hand-wave           [reject: no analysis]
D  more fixed servers -> cost grows ~10x linearly, still no failure story    [reject: cost + reliability]
C  partition + autoscale + guards + tiering + pre-agg + defended trade-offs  [ACCEPT]

Step-by-step trace.

Constraints held constant: freshness SLA is still hourly and budget is real — so full streaming is not required and cost must stay sublinear.
A jumps to Kafka + Flink, adding large operational complexity for sub-second freshness nobody asked for — the textbook over-engineering trap — eliminate.
B waves at autoscaling with no failure story, no bottleneck analysis, and no cost reasoning — eliminate for lack of rigor.
D scales by adding fixed servers everywhere, so cost grows ~10× linearly and there is still no reliability story — eliminate on cost and reliability.
C names the first bottlenecks (buffer partition, fixed transform), scales them by partitioning and autoscaling, wraps the transform in retries/DLQ/DQ gates, tiers storage hot/cold, pre-aggregates the serving layer, and defends hourly batch over Kafka/Flink with the one-hour freshness SLA — every choice tied to a requirement.

Output:

Challenge	Answer
What breaks first	single-partition buffer + fixed transform
Reliability	retries + DLQ + DQ gate + SLO alert
Scale	partitioned buffer + autoscaled idempotent transform
Cost	hot/cold tiering + incremental processing (sublinear)
"Why not Kafka/Flink?"	hourly SLA doesn't need sub-second; less to operate

Why this works — concept by concept:

Requirement-tied defence — rejecting Kafka/Flink because the freshness SLA is one hour is exactly the trade-off reasoning the round scores; the constraint, not taste, drives the choice.
Partition-first scaling — adding buffer partitions and autoscaling the transform makes throughput track load, and it is the standard, defensible answer to "what breaks at 10×?".
Reliability kit — retries, dead-letter, and data-quality gates convert failures into automatic recoveries and quarantines, giving the design a concrete failure story instead of a happy path.
Sublinear cost — tiered storage plus incremental processing keeps 10× data from becoming 10× spend, which is what makes the design defensible on budget.
Cost — the whole plan reuses the existing architecture with scaling levers rather than a rewrite, so the engineering cost is a few components, and the run cost grows sublinearly with traffic — the cheapest correct way to absorb 10×.

Design
Course — ETL system design
Scaling, reliability and trade-off design drills

Practice →

ETL
Topic — etl
Reliability, retries and backfill problems

Practice →

Cheat sheet — whiteboard pipeline-design recipes

The seven-step framework (run it in order, every time).

Step	Question you answer	Output on the board
1. Requirements	purpose, consumers, volume, freshness, latency, correctness, retention	numbers in the top-left corner
2. Data contract	schema, grain, keys, semantics, evolution, ownership	a small contract table
3. Ingest	batch / streaming / micro-batch / CDC + buffer	left-side boxes + arrows
4. Store	lake / warehouse / lakehouse, format, partition, medallion	bronze/silver/gold slabs
5. Process	staging → transform → publish, idempotency, dedupe	middle transform box
6. Serve	dimensional model, pre-aggregate, serving store	right-side consumer boxes
7. Ops/scale/cost	retries, DLQ, DQ gates, scaling, cost, SLOs	annotations + failure story

Clarifying-questions checklist (ask before you draw).

Purpose and the decision it drives; who the consumers are and how they read.
Volume: events/sec × bytes/event → daily/yearly; peak vs average; growth rate.
Freshness (how stale is okay) and latency (how fast a query returns) — they are different.
Correctness bar: exactly-once vs at-least-once + idempotent dedupe; is approximate okay?
Retention, data residency, and PII/compliance.

Batch vs streaming decision line. Freshness of minutes-to-hours → batch (simplest, cheapest). Sub-minute freshness needed → streaming (windows + watermarks). Near-real-time without full complexity → micro-batch. Mirroring a database → CDC.

Storage / format / partition lookup.

Need	Choice
Analytics scans	columnar (Parquet/ORC)
Landing / transport	row (Avro/JSON)
Skip data at query time	partition by filtered date column
Many small files (streaming)	add a compaction job
Cheap raw scale + ACID	lakehouse (Iceberg/Delta)
Governed analyst SQL	warehouse
Point lookups / sub-second slice	key-value / OLAP store

Trade-off defence one-liners (say the requirement, not the preference).

"Hourly batch, because the freshness SLA is one hour — streaming adds ops complexity for freshness nobody asked for."
"CDC, not nightly SELECT *, so production takes zero analytical load and the warehouse stays minutes-fresh."
"Pre-aggregate serving, because the dashboard needs sub-second reads over a terabyte-scale history."
"Idempotent partition overwrite, so retries and backfills converge instead of double-counting."
"Kafka/Flink would be right for sub-second multi-consumer replay; here the SLA is met with far less to operate."

Whiteboard-round anti-patterns. Designing before clarifying → buzzword stack → ignoring the consumer → happy-path with no failure story → defending choices with taste instead of trade-offs → assuming volume instead of asking.

Frequently asked questions

What are scenario-based data engineering interview questions?

They are open-ended prompts — "design a pipeline for X" — where the interviewer gives you a deliberately under-specified scenario and evaluates how you clarify it, structure a design, and defend trade-offs, rather than whether you produce one exact answer. The scenario based data engineering interview questions in the whiteboard round are testing process and judgment: clarifying questions, back-of-envelope sizing, boxes-and-arrows architecture, and requirement-tied reasoning. They are the data-engineering equivalent of a software system-design round, focused on data pipelines instead of request/response services.

How is the whiteboard pipeline-design round different from coding rounds?

Coding rounds have a correct output and hidden tests; the whiteboard round has no single right answer and no compiler — it scores your thinking out loud. You are judged on clarifying the ambiguous prompt, sizing the problem, drawing a readable architecture, and defending each choice against pushback. A candidate who asks sharp questions and draws a simple, correct batch job usually outscores one who name-drops ten technologies with nothing tying them to a requirement.

What framework should I use to design a data pipeline on a whiteboard?

Run seven steps in order: requirements → data contract → ingest → store → process → serve → ops/scale/cost. Start every data pipeline design by turning fuzzy words into numbers (volume, freshness, latency), pin a data contract (schema, grain, keys, evolution), then draw left-to-right boxes putting the simplest component in each stage that honours the numbers. Reserve the top-left of the board for the requirements so you can point back to them when defending choices.

How much detail is expected — do I write code?

You mostly draw boxes-and-arrows and talk; you are not expected to write production code. You should be able to zoom in on demand — sketch a schema, a partition scheme, or the logic of an idempotent MERGE — when the interviewer probes a component, but the whole board is architecture and reasoning, not a coding exercise. Depth-on-demand (going deep on one piece without losing the whole picture) is itself a strong signal.

How do I answer "batch or streaming?" in a system design interview?

Decide by the freshness SLA, not by fashion: if the data can be minutes-to-hours stale, choose batch (or micro-batch) because it is simpler, cheaper, and easier to backfill; only choose streaming when a genuine sub-minute requirement exists. State the trade-off out loud — "streaming buys sub-second freshness at the cost of operational complexity and harder debugging, and nobody asked for sub-second here." In a data engineering system design interview, defending hourly batch with the SLA is a stronger answer than reaching for streaming reflexively.

How do I defend my trade-offs when the interviewer pushes back?

Tie every choice to a requirement and name what you gave up: "I chose CDC over nightly reloads because the freshness target is minutes and production can't take analytical load; the trade-off is a merge step to apply updates in order." Answer "why not X?" with the constraint under which X would win, then show that constraint isn't present here. The interviewer is not trying to prove you wrong — they are checking whether your reasoning is requirement-driven rather than preference-driven.

Practice on PipeCode

Turn the whiteboard framework into muscle memory

Reading about pipeline design builds recognition; drills build the reflex the round actually tests — clarifying the ambiguous prompt, sizing it in numbers, drawing the seven stages, and defending each trade-off under a clock. Pipecode.ai is Leetcode for Data Engineering — scenario-first practice on SQL, ETL, and pipeline design tuned to the exact trade-offs a whiteboard pipeline-design interview rewards.

Practice system design problems →
Practice ETL problems →