System Design Interview Questions for Data Engineers

System design interview questions for data engineers usually start vaguely: "design a pipeline for ride data" or "design real-time analytics for an app". Candidates often answer with a diagram of fashionable components. Interviewers are looking for something else: requirements turned into numbers, and design choices that follow from those numbers. This guide gives a framework for the round and works its three hardest parts on real traffic from a busy day of New York taxi trips (NYC TLC: trip record data).

A framework for the round

1. Clarify requirements. Who consumes the data, how fresh must it be, what accuracy is acceptable, and what happens if data is late or duplicated?
2. Size the load. Volume per day, average and peak rates, growth, retention.
3. Choose batch, streaming or both, from the freshness requirement and what the source can provide.
4. Design the data flow: ingestion, storage, processing, serving, with partitioning and keys.
5. Plan for failure: retries, idempotency, backpressure, late data, monitoring.
6. Discuss trade-offs and what you would build first.

Steps 2 and 4 are where numbers matter most, so the rest of this guide works them on real data. Spend the first five to ten minutes of a 45-minute round on steps 1 and 2; candidates who rush them usually redesign later.

import duckdb

db = duckdb.connect()
TRIPS = "yellow_tripdata_2025-01.parquet"                       # NYC TLC yellow taxi trips, January 2025
db.execute(f"""CREATE TABLE trips AS SELECT tpep_pickup_datetime AS ts, PULocationID AS zone FROM '{TRIPS}'
               WHERE tpep_pickup_datetime >= '2025-01-01' AND tpep_pickup_datetime < '2025-02-01'""")

busiest = db.execute("SELECT CAST(ts AS DATE) AS d FROM trips GROUP BY d ORDER BY COUNT(*) DESC LIMIT 1").fetchone()[0]
per_minute = [n for (n,) in db.execute(f"""
    SELECT COUNT(t.ts) FROM range(TIMESTAMP '{busiest}', TIMESTAMP '{busiest}' + INTERVAL 1 DAY, INTERVAL 1 MINUTE) m(minute)
    LEFT JOIN trips t ON date_trunc('minute', t.ts) = m.minute GROUP BY m.minute ORDER BY m.minute""").fetchall()]
avg, peak = sum(per_minute) / len(per_minute), max(per_minute)
print(f"1. busiest day {busiest}: {sum(per_minute):,} trips, average {avg:.0f} a minute, peak minute {peak}, "
      f"peak/average {peak / avg:.1f}x")

print("\n2. a consumer that processes a fixed number of events a minute, fed the real arrivals")
for capacity in (round(avg), round(avg * 1.5), peak):
    backlog, worst, waited = 0, 0, 0
    for arrived in per_minute:
        backlog = max(0, backlog + arrived - capacity)
        worst = max(worst, backlog)
        waited += backlog                                       # events still waiting at the end of each minute
    print(f"   capacity {capacity:>4}/min: largest backlog {worst:>6,} events, worst delay about {worst / capacity:5.1f} min, "
          f"average delay {waited / sum(per_minute):4.2f} min (Little's law: backlog / arrival rate)")

print("\n3. partitioning by pickup zone: how even is the load?")
shares = [s for (s,) in db.execute("SELECT COUNT(*) / SUM(COUNT(*)) OVER () FROM trips GROUP BY zone ORDER BY 1 DESC").fetchall()]
print(f"   {len(shares)} zones; busiest zone {shares[0]:.1%} of trips, top 10 zones {sum(shares[:10]):.1%}")
partitions = 12
load = [0.0] * partitions
for zone_share, zone in db.execute("SELECT COUNT(*) / SUM(COUNT(*)) OVER (), zone FROM trips GROUP BY zone").fetchall():
    load[hash(int(zone)) % partitions] += zone_share
print(f"   {partitions} partitions keyed by zone: busiest partition {max(load):.1%}, quietest {min(load):.1%} "
      f"(an even split would be {1 / partitions:.1%})")

1. busiest day 2025-01-16: 138,711 trips, average 96 a minute, peak minute 255, peak/average 2.6x

2. a consumer that processes a fixed number of events a minute, fed the real arrivals
   capacity   96/min: largest backlog 36,128 events, worst delay about 376.3 min, average delay 87.88 min (Little's law: backlog / arrival rate)
   capacity  144/min: largest backlog 11,489 events, worst delay about  79.8 min, average delay 24.26 min (Little's law: backlog / arrival rate)
   capacity  255/min: largest backlog      0 events, worst delay about   0.0 min, average delay 0.00 min (Little's law: backlog / arrival rate)

3. partitioning by pickup zone: how even is the load?
   261 zones; busiest zone 4.9% of trips, top 10 zones 37.4%
   12 partitions keyed by zone: busiest partition 17.5%, quietest 2.8% (an even split would be 8.3%)

Q1. Should you size for average or peak load?

For peak, or for a backlog you can tolerate. On 16 January 2025, the busiest day of the month, 138,711 trips started: 96 a minute on average, but 255 in the busiest minute, 2.6 times the average. A rate quoted per day hides this completely. Ask for the shape of the traffic, not just the total, and size from the busy hours.

Q2. What happens when processing falls behind?

A backlog builds, and delay follows from it. Feeding the real minute-by-minute arrivals to a consumer that handles a fixed number of events per minute made this concrete. Sized at the daily average of 96 a minute, the consumer fell behind for most of the day: the backlog reached 36,128 events and the worst delay was over six hours. At 144 a minute, 1.5 times the average, the worst delay was still about 80 minutes. Only at the peak rate of 255 did the backlog stay at zero.

Little's law ties these together: the average number of items waiting equals the arrival rate times the average time each waits (Wikipedia: Little's law). That is how the script turned backlog into average delay. In an interview, it lets you move between "how many events are queued" and "how stale is the dashboard" with one line of arithmetic.

What to say: decide the acceptable delay first, then size capacity, or autoscaling, to keep the backlog within it during peaks, and alert on consumer lag rather than on CPU.

Q3. How do you choose a partition key?

With the access pattern and the load distribution in mind. Keying events by pickup zone looks natural, because it keeps each zone's events together and in order. In Kafka, events with the same key go to the same partition and are read in order (Kafka: introduction). But zones are far from equal: the busiest of 261 zones produced 4.9% of trips, and the top ten 37.4%. Hashing zones onto 12 partitions gave the busiest partition 17.5% of all traffic and the quietest 2.8%, against an even 8.3%. The busiest partition limits the whole pipeline, because one consumer reads it.

Options to discuss: a higher-cardinality key such as the trip or vehicle ID when per-zone ordering is not needed, more partitions, or splitting hot keys with a suffix and merging later.

Q4. Batch or streaming?

Let the requirement and the source decide. These public trip files are published monthly, so a design built on them is batch by necessity, as the sizing in data engineering interview questions and answers shows. If the same data arrived as live events and a dashboard needed minute-level freshness, streaming would be justified, and the capacity and partitioning questions above become central. Many real systems run both: streaming for fresh, approximate views and batch for complete, corrected history.

Q5. How do you make the pipeline reliable?

Assume every step can fail and be retried. Make writes idempotent with keys and merges, keep raw data so any step can be replayed, handle late events with a watermark and a correction path, and monitor lag, freshness and row counts end to end. The patterns are covered in ETL interview questions and handling late-arriving data.

Mistakes that cost offers

• Drawing components before asking about freshness and volume.
• Sizing for daily averages.
• Choosing a partition key without checking its skew.
• No plan for retries, duplicates and late data.
• Never stating what you would build first, or what you would leave out.

For streaming specifics, see Kafka interview questions and real-time analytics architecture; for storage-side design, data warehouse interview questions.

System design for data pipelines is part of the Data Engineering course.

Sources

• TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
• Little's law (Wikipedia) — Wikipedia
• Introduction (Apache Kafka 4.2 documentation) — The Apache Software Foundation