How to Answer Data Pipeline System Design

A repeatable framework for the design round — clarify, size, sketch, then defend your trade-offs — with a full worked example you can adapt to almost any prompt.

Design rounds are not looking for the 'right' architecture. They are testing whether you ask the right questions, make explicit trade-offs, and know where things break. A structured, average design beats a brilliant design delivered as a monologue.

The framework: 5 steps, roughly 45 minutes

Step	Time	What you do
1. Clarify	5–8 min	Ask about volume, latency, consumers, correctness needs, budget
2. Size it	3–5 min	Back-of-envelope: records/sec, GB/day, retention, peak vs average
3. Sketch	10 min	Draw the boxes: ingest → land → process → model → serve
4. Deep-dive	15 min	Go deep where they push: schema, failure handling, cost
5. Trade-offs	5 min	State what you chose not to do and why; name the weak points

Step 1 — The questions to always ask

Volume — records per second, average and peak? How bursty?
Latency — do consumers need seconds, minutes, or is next morning fine?
Consumers — dashboards, alerting, ML features, regulators? Each implies a different serving layer
Correctness — is approximate acceptable, or must it reconcile exactly (billing, compliance)?
History — how far back must data be queryable, and must it be replayable?
Constraints — cloud, existing skills, budget, data residency, PII rules

Tip — The highest-value question

'What happens if this data is 1 hour late — who notices and what breaks?' The answer tells you whether to build batch or streaming, and it demonstrates that you design from business impact rather than from tech fashion.

Step 2 — Size it out loud

Worked example: telecom network events

  2,000,000 events/min  = ~33,000 events/sec
  1 KB per event        = ~33 MB/sec  = ~2 GB/min  = ~2.8 TB/day

  Peak (evening + incident) = 3x average  -> design for ~100k events/sec
  Raw retention 90 days     -> ~250 TB raw (compressed Parquet ~1/5 -> ~50 TB)
  Aggregated daily KPIs     -> a few GB/day, trivial to serve

Conclusion: ingestion must be horizontally scalable and decoupled;
raw storage must be cheap object storage, not warehouse storage;
dashboards must read pre-aggregates, never the raw fact.

Step 3 — The reference architecture

Almost every good answer is a variation of this: land raw immutably, transform in layers, serve narrow.

Ingest — Kafka/Pub/Sub as a durable buffer; producers never block on downstream slowness
Land raw — immutable, append-only, partitioned by date/hour in GCS or a raw dataset. This is your replay tape
Process — streaming for the low-latency path, batch for the correctness path
Model — stage → core → marts with dbt; tests gate promotion between layers
Serve — partitioned and clustered marts for BI; pre-aggregates for dashboards; an API or cache if apps consume it
Operate — orchestration, lineage, freshness/volume/quality monitoring, alerting with runbooks

Watch out — Never skip the raw landing zone

If you transform in flight and only store the result, then every transformation bug is permanent data loss. Landing raw first is what makes backfills, reprocessing and audits possible — and it is the detail junior candidates most often omit.

Step 4 — Where they will push you

Probe	What they want to hear
'What if a consumer goes down for 6 hours?'	The buffer retains data; the consumer resumes from its committed offset and catches up. Retention must exceed worst-case downtime
'What if a bad deploy corrupts a day of data?'	Replay from raw for that partition, overwrite it — because every layer is rebuildable and idempotent
'How do you prevent duplicates?'	At-least-once delivery plus an idempotent sink: deterministic keys and MERGE or partition overwrite
'The bill doubled. What do you do?'	Find the top queries by bytes scanned; check partition pruning, `SELECT *`, and dashboards hitting raw instead of aggregates
'How do you handle PII?'	Classify at ingestion, policy tags for column-level security, tokenise identifiers, restrict raw access, honour residency and retention rules
'How do you evolve the schema?'	Additive by default, contracts at boundaries, versioned models, raw always preserves the original payload

Step 5 — Close with trade-offs

End by naming two or three explicit trade-offs. For example: *'I chose micro-batch over continuous processing because 30-second latency satisfies the alerting requirement and exactly-once is far simpler to guarantee. I chose to keep 90 days of raw rather than 2 years because reprocessing beyond a quarter has never been needed and the storage cost is real. The weakest point is the single Kafka cluster, so I would prioritise multi-zone replication next.'*

Practice prompts

QDesign a system to detect network anomalies in near real time.show

Split the fast path from the accurate path, and be explicit that they answer different questions.

Fast path — Kafka → Structured Streaming with 1-minute event-time windows → compare against a rolling baseline → alert. Optimised for latency, tolerant of small inaccuracies
Accurate path — the same raw events land in BigQuery; a nightly batch recomputes the baselines and corrects the record with late data included
Baselines — store per-site rolling statistics; refresh daily so seasonality is captured without recomputation in the hot path
Alert quality — suppress duplicates with a cooldown per site, and require N consecutive breaches to avoid paging on a single noisy window
Feedback — persist alert outcomes so thresholds can be tuned and false-positive rate can be measured

QDesign a data platform serving both real-time dashboards and monthly regulatory reports.show

One ingestion path, one raw store, two serving paths with different SLAs and different guarantees.

Shared: durable buffer → immutable raw → core dimensional model. Do not build two ingestion stacks
Dashboards — streaming aggregates into partitioned tables, refreshed continuously; approximate is acceptable
Regulatory — batch, reconciled, versioned, immutable snapshots with full lineage and sign-off; must be reproducible years later
Key insight to state: regulatory reporting needs reproducibility, so freeze the inputs and the code version, and never let a late correction silently change a published number

← Previousdbt and the Transformation Layer Next →Hadoop to Cloud Migration: The Full Story