Data System Design
The 45-minute whiteboard round, sequenced — requirements, back-of-envelope sizing, storage choice, and the tradeoffs you volunteer before being asked.
The design round is 45 minutes and most candidates spend 35 of them drawing boxes. The sequence below is what actually gets scored, in the order to do it.
The question
“Design a system that ingests clickstream events from a website and gives analysts next-day dashboards, plus a real-time view of orders in the last five minutes.”
Note the two latency requirements in one question. That is deliberate — it is testing whether you notice, and whether you build one system or two.
Minutes 0-8: requirements and sizing
Ask before drawing. Six questions, each of which changes the design:
Volume how many events/day? peak events/second?
Latency "next-day dashboards" and "last 5 minutes" — two different systems?
Retention how long must raw events be kept? regulatory constraints?
Correctness can we drop 0.1% of events, or must it reconcile exactly?
Consumers analysts in SQL? a product surface? both?
Team who operates this at 3am, and what do they already run?Then do the arithmetic out loud. This is the step that most separates candidates:
events_per_day = 500_000_000
bytes_per_event = 1_200 # JSON envelope, before compression
peak_multiplier = 4 # traffic is not uniform across 24h
raw_gb_day = events_per_day * bytes_per_event / 1024**3
avg_eps = events_per_day / 86_400
peak_eps = avg_eps * peak_multiplier
parquet_gb = raw_gb_day * 0.12 # ~8x with zstd + columnar
yearly_tb = parquet_gb * 365 / 1024
print(f"raw JSON {raw_gb_day:8.0f} GB/day")
print(f"as parquet+zstd {parquet_gb:8.0f} GB/day")
print(f"one year {yearly_tb:8.1f} TB")
print(f"average {avg_eps:8,.0f} events/sec")
print(f"peak {peak_eps:8,.0f} events/sec")
print(f"S3 storage/yr ${yearly_tb * 1024 * 0.023 * 12 / 2:8,.0f} (avg half-year held)")raw JSON 559 GB/day
as parquet+zstd 67 GB/day
one year 23.9 TB
average 5,787 events/sec
parquet+zstd peak 23,148 events/sec
S3 storage/yr $3,377 (avg half-year held)Those numbers now decide things. 23,000 events/sec at peak rules out a single-instance consumer and justifies Kafka partitioning. 67 GB/day rules in a warehouse and rules out loading it into pandas. $3,377/year for storage says retention is not the cost driver — compute will be — so that is where to spend design effort.
Say one of these out loud: “So this is a mid-size problem — hundreds of gigabytes a day, not petabytes. That means I do not need a specialised system; a standard lakehouse handles it.”
Minutes 8-23: the design
┌─────────────────────────────────────┐
web / app SDK ──────────► Kafka (topic: events, 24 partitions)
│ key = session_id, retention 7 days │
└───────┬──────────────────┬───────────┘
│ │
┌─────────────▼──────┐ ┌───────▼─────────────┐
│ Sink connector │ │ Flink / Spark SS │
│ → S3 bronze │ │ 5-min tumbling agg │
│ parquet, hourly │ │ → Redis / Pinot │
│ partition=dt/hour │ │ (real-time view) │
└─────────────┬──────┘ └─────────────────────┘
│
┌────────▼─────────┐
│ dbt on warehouse │ bronze → silver → gold
│ daily, 03:00 UTC │ + tests as gates
└────────┬─────────┘
│
BI / analystsTwo paths from one log, and say why: “The batch path and the streaming path read the same Kafka topic rather than the streaming path feeding the batch one. That way a bug in the real-time aggregation cannot corrupt the historical record, and the batch path is the source of truth for anything reconciled.”
Decisions to state as you draw, each with a reason and an alternative:
| Decision | Reason | Alternative |
|---|---|---|
| Kafka | replay, multiple independent consumers, back-pressure | Kinesis — less to operate, 7-day cap, harder replay |
Key by session_id | sessionisation needs ordering per session | key by user_id if the analytics are user-centric |
| 24 partitions | ~1,000 events/sec each at peak, room to grow | fewer if consumers cannot keep up |
| Parquet + zstd | 8× smaller, typed, column pruning | Avro on the wire, Parquet at rest |
Partition by dt/hour | queries filter by time; ~2.8 GB/hour is a healthy size | daily if hourly files get small |
| dbt for transforms | tests gate publication; build skips downstream on failure | Spark SQL if the warehouse is the bottleneck |
| Redis for real-time | sub-ms reads of a small aggregate | Apache Pinot if analysts need to slice it |
The one to volunteer: “I would not use the streaming path to serve the daily dashboards. Streaming state is harder to backfill and reprocessing means replaying a topic rather than re-running a day.”
Minutes 23-33: the deep dive
The interviewer picks a component. Prepare for the two most common.
“Walk me through exactly-once into S3.”
“The sink connector commits Kafka offsets and the file write together, so a crash replays the batch rather than losing it. On S3 that means writing to a temp key and renaming, or using a table format — Iceberg or Delta — where the commit is an atomic metadata operation and a duplicate batch id is recognised and skipped. Without a table format I would accept at-least-once and deduplicate on
event_idin the bronze-to-silver step, because duplicates are recoverable and lost events are not.”
“How do you handle a 10× traffic spike?”
for mult in (1, 3, 10):
eps = 23_148 * mult
partitions_needed = eps / 1_000 # ~1k events/sec per consumer task
print(f"{mult:>2}x peak: {eps:>7,.0f} eps → {partitions_needed:>4.0f} partitions/consumers") 1x peak: 23,148 eps → 23 partitions/consumers
3x peak: 69,444 eps → 69 partitions/consumers
10x peak: 231,480 eps → 231 partitions/consumers“Kafka absorbs the spike as lag rather than dropping events — that is the point of the buffer — so the immediate risk is retention, not throughput. At 7 days I have plenty of headroom to catch up. Consumers scale to the partition count and no further, so I would over-partition now: 48 rather than 24, since increasing partitions later breaks key ordering for existing keys. The batch path is unaffected because it reads from S3 at its own pace.”
That last clause — over-partitioning because repartitioning breaks ordering — is a detail that only comes from having done it.
Minutes 33-40: failure modes, volunteered
Do not wait to be asked. Go through them briskly:
failures = [
("Kafka broker loss", "replication factor 3, min.insync.replicas 2 — writes continue"),
("Consumer crash", "offsets committed with the write; replay is idempotent"),
("Schema change", "contract check in producer CI; unknown fields land in raw payload"),
("Late events", "batch path re-reads a 3-day window and merges on event_id"),
("Warehouse down", "S3 bronze is unaffected; backfill when it returns"),
("Bad transform deployed","dbt tests gate publication; previous gold tables stay live"),
("Traffic spike", "Kafka buffers; lag alert at 15 minutes; consumers autoscale"),
("Real-time path down", "batch path unaffected — dashboards degrade, history does not"),
]
for f, mitigation in failures:
print(f"{f:<24} {mitigation}")Kafka broker loss replication factor 3, min.insync.replicas 2 — writes continue
Consumer crash offsets committed with the write; replay is idempotent
Schema change contract check in producer CI; unknown fields land in raw payload
Late events batch path re-reads a 3-day window and merges on event_id
Warehouse down S3 bronze is unaffected; backfill when it returns
Bad transform deployed dbt tests gate publication; previous gold tables stay live
Traffic spike Kafka buffers; lag alert at 15 minutes; consumers autoscale
Real-time path down batch path unaffected — dashboards degrade, history does notThe last line is the design justifying itself: because the two paths are independent, losing the real-time view costs a dashboard rather than the historical record.
Then monitoring, briefly and specifically: “Consumer lag, S3 object count per hour against trend, dbt test results per run, and a freshness check on the gold tables run from a separate monitor — because if the scheduler never fires, a pipeline-side alert never fires either.”
Minutes 40-45: cost and tradeoffs
monthly = {
"Kafka (3 brokers, m5.2xlarge)": 3 * 0.384 * 730,
"S3 storage (avg 12 TB)": 12 * 1024 * 0.023,
"S3 PUT requests (24k/day)": 24_000 * 30 / 1000 * 0.005,
"Warehouse compute (2h/day)": 2 * 30 * 4 * 3.00,
"Streaming job (2 nodes)": 2 * 0.192 * 730,
}
for k, v in monthly.items():
print(f"{k:<34} ${v:>9,.0f}")
print(f"{'TOTAL':<34} ${sum(monthly.values()):>9,.0f}/month")Kafka (3 brokers, m5.2xlarge) $ 841
S3 storage (avg 12 TB) $ 283
S3 PUT requests (24k/day) $ 4
Warehouse compute (2h/day) $ 720
Streaming job (2 nodes) $ 280
TOTAL $ 2,128/monthVery few candidates cost their design, and it changes the conversation: “Kafka is 40% of the bill and it exists mainly for replay and multiple consumers. If the real-time requirement were dropped, Kinesis Firehose straight to S3 would cut this to about $1,000 and remove a system to operate. That is the tradeoff I would put to the product team.”
Close by naming what you would push back on:
“I would ask how the five-minute view is actually used. If it is a wall dashboard nobody acts on within the hour, the streaming path is buying latency nobody needs and costing a system to run. If it drives fraud checks or live inventory, it is clearly worth it. That single question changes about a third of this design.”
The scoring
| Behaviour | Signal |
|---|---|
| Asked for volume and latency before drawing | senior |
| Did arithmetic and used it to justify choices | senior |
| Volunteered failure modes without being asked | senior |
| Costed the design, even roughly | senior |
| Pushed back on a requirement with a reason | senior |
| Named alternatives alongside each choice | mid-to-senior |
| Correct architecture, discussed failures when prompted | mid |
| Drew boxes for 30 minutes, no numbers | junior |
Two failure patterns worth avoiding. Over-engineering: proposing Kafka, Flink, a lakehouse and a serving layer for 50 MB a day. And tool-listing: naming eight technologies with no reason for any, which reads as a résumé rather than a design.
A reusable skeleton
1. Requirements volume, latency, retention, correctness, consumers, team
2. Sizing GB/day, events/sec, storage/year, rough cost
3. Data flow ingest → land raw → transform → serve
4. Storage format, partitioning, table format, retention
5. Processing batch or stream, engine, idempotency, backfill
6. Serving warehouse, cache, API — who reads it and how often
7. Failure modes each component, and what degrades vs what breaks
8. Observability freshness, volume, quality, lag — and who is paged
9. Cost the big line items, and the cheaper alternative
10. Tradeoffs what you would push back on, and what you would do differently at 10xSteps 7 to 10 are where most candidates run out of time, and where the round is decided. If you are 25 minutes in and still drawing, skip detail and get to failure modes.
Practice
1. Size a 500M events/day system before designing it.
raw JSON 559 GB/day
as parquet+zstd 67 GB/day
peak 23,148 events/secThose three numbers rule several architectures in and out. Two minutes of arithmetic turns “I’d use Kafka” into “I need at least 23 partitions”.
2. Work out what a 10× spike requires.
10x peak: 231,480 eps → 231 partitions/consumersAnd the insight: Kafka absorbs the spike as lag, so retention is the constraint, not throughput. Over-partition now, because repartitioning later breaks key ordering.
3. Cost the design.
TOTAL $2,128/month — Kafka is 40% of itWhich makes the tradeoff concrete: dropping the real-time requirement halves the bill and removes a system to operate. Almost no candidate does this.
4. Name what you would push back on.
"How is the five-minute view actually used?"If nobody acts within the hour, the streaming path buys latency nobody needs. Questioning a requirement — with a reason — is the strongest single signal in this round.
Next: the take-home project — what is actually being marked.