Data Engineering System Design Interview: Framework and Example

A data engineering design round looks like a backend one from the outside (an open question, a whiteboard, 45 minutes), but it tests different things. Backend rounds are about serving requests: APIs, load balancers, caches, routing. Data engineering rounds are about moving and shaping data: ingestion, batch vs streaming, data models, storage formats, quality, backfills and cost. URL shorteners and Twitter feeds won't prepare you for "design a pipeline that tracks user activity on an e-commerce site".

There is no single correct answer. Ten senior engineers would draw ten valid pipelines, each trading cost, latency, complexity and failure handling differently. The interviewer is checking whether your reasoning survives changing constraints: what if volume grows 10×, the dashboard must be 10 minutes fresh instead of daily, or a source becomes unreliable?

The six-step framework

Every question (a lakehouse for e-commerce, a recommendation pipeline, fraud detection, a customer-360 platform) can be worked through with the same six steps, in order:

Interviewers use a wide-then-zoom approach: a broad prompt first, then a deep dive into whichever steps they care about (Kafka semantics, Spark internals, the data model, quality checks). Aim to touch all six; it's fine if some stay shallow, as long as the structure is visible.

Step 1: requirements gathering

The most underrated step. Many candidates hear the prompt and immediately draw Airflow, Kafka and Spark boxes. Instead, spend the first five minutes asking questions; it's what separates strong candidates, and the interviewer is waiting for it.

Functional requirements: what the system must do

1. Who is the consumer? A marketing analyst running SQL, an ML team feeding a feature store for recommendations, or a product team watching live metrics? Each implies a different architecture: the analyst can be served by batch; the other two usually need streaming.
2. What do they need? Aggregated metrics, raw events, or historical snapshots?
3. How will they access it? Direct SQL, a BI tool such as Tableau or Power BI, or a REST API?

Example: marketing wants to see how shoppers move through the purchase funnel (home page → search → product view → add to cart → purchase) and where they drop off:

They'll want to slice it by segments: new vs returning users, free vs paid, device type (a 2% desktop conversion rate against 6% on mobile might mean the desktop checkout is broken). So the functional requirement becomes: daily funnel metrics, segmented by user type and device type, accessed in Tableau.

Non-functional requirements: how the system must behave

Same prompt, two different systems

Same question, two different designs: the requirements determine the answer. See non-functional requirements and batch vs stream processing.

Back-of-the-envelope: let numbers make decisions

Once requirements are clear, do quick math. Most candidates skip it, yet it shows you think in numbers, and the numbers make design decisions for you. Agree each assumption with the interviewer as you go. For scenario A:

Users:        5M daily active users
Activity:     2 sessions/user/day × 30 events/session (views, clicks, searches, add-to-cart)
Events/day:   5M × 2 × 30                       = 300M events/day
Event size:   one JSON (user_id, session_id, event_type, timestamp, page_url…) ≈ 700 bytes
Daily volume: 300M × 700 B                      ≈ 210 GB/day
Monthly:      210 GB × 30                       ≈ 6.3 TB/month
Bronze, 12 months: 6.3 TB × 12                  ≈ 75 TB raw
In Parquet/Delta (2–5× compression)             ≈ 15–38 TB on object storage

• Compute: 210 GB/day is too much for pandas or Polars on one machine → Spark.
• Format and layout: Parquet or Delta, partitioned by event date (yyyy-MM-dd) so hourly jobs read only the partitions they need. See data layout optimization.
• Orchestration: hourly/daily freshness → a scheduled Airflow DAG.
• What you can rule out: no streaming stack (Kafka, Flink) is needed at all.

Four decisions from five lines of arithmetic. See back-of-the-envelope estimation.

Steps 2–6 at a glance

• Pipeline design: batch runs roughly 90% of workloads (whenever cost matters more than latency): source → object storage (S3, ADLS) → bronze, silver, gold → dashboard, orchestrated by Airflow. Streaming (Kafka → Spark Structured Streaming or Flink → Redis/DynamoDB/Cassandra for serving, Delta for history) is for sub-minute needs such as live dashboards and fraud. When you need both, reach for Lambda or Kappa, or the lakehouse compromise.
• Data modeling: medallion layers (raw bronze, cleaned and modeled silver, aggregated gold), star schema vs one big table, and slowly changing dimensions. Expect push-back: "why not denormalize?", "what happens when this dimension changes?"
• Storage and formats: columnar vs row formats (Parquet for reads, Avro for streaming writes), open table formats (Delta, Iceberg, Hudi) for ACID and schema enforcement, layout optimization, and SQL vs NoSQL for serving stores.
• Quality and observability: checks grouped by quality dimension (completeness, accuracy, consistency, freshness, uniqueness), data contracts at ingestion, and pipeline monitoring (did the DAG run, how long did it take). See pipeline error handling.
• Scale and DataOps: idempotency (MERGE instead of INSERT), backfills by overwriting partitions atomically, and schema evolution (flexible in bronze, strict in silver and gold).

For a complete worked answer, see the food delivery analytics pipeline case study. For the architecture vocabulary, see data engineering design patterns.

Why knowledgeable candidates still fail

• They jump to tools before understanding the problem, and skimp on requirements.
• They design only the happy path and never discuss failures, late data, reruns or schema changes.
• They choose silently. Make your reasoning visible: when you pick batch over streaming, Delta over plain Parquet, or a star schema over a wide table, say why and what you're giving up.

Candidates who pass slow down, ask questions, think in numbers, and narrate trade-offs. Knowledge of Kafka, Spark or Delta is necessary, but structure is what gets noticed.