Data System Design Interview cheat sheet
How to frame, size, and defend a data platform design — ingestion, storage, processing, serving, and the failure modes interviewers push on.
Framing the interview
How should you structure the first five minutes of a data system design interview?- Restate the problem, then drive out requirements before drawing anything — who consumes the data, what freshness they need, what the query patterns are, and what scale you are designing for. Write the requirements where both of you can see them and get explicit agreement. Candidates who start drawing Kafka and Spark in minute two fail on scope, not on technology knowledge.
What questions should you always ask before proposing an architecture?- Volume and growth rate, event size, read and write patterns, required freshness (sub-second, minutes, or daily), retention and replay requirements, correctness tolerance (can you accept approximate counts), access and compliance constraints, and existing platform commitments. Freshness and correctness tolerance are the two that most change the design, so ask them early and pin down numbers rather than adjectives.
How do you handle it when the interviewer keeps the requirements vague?- State an assumption explicitly, mark it as an assumption, and design against it — for example, assume ten thousand events per second average with a five times peak and 90-day retention. Vague requirements are usually deliberate, testing whether you can bound an open problem. Revisit assumptions when the interviewer adds a constraint rather than quietly abandoning your earlier reasoning.
What is the biggest difference between a data system design interview and a general backend one?- Backend design centers on request latency, statelessness, and availability. Data design centers on throughput, correctness semantics, schema evolution, reprocessing, and cost per terabyte scanned. The questions that separate strong candidates are how you backfill after a bug, how you evolve a schema without breaking consumers, and what your pipeline costs per month.
How do you talk about cost without guessing?- Anchor on a few durable numbers — object storage is roughly 20 to 25 dollars per terabyte-month, cross-region egress is the expensive network path, and warehouse compute is billed per second of an active cluster. Then reason in orders of magnitude: a 50 TB table scanned fully by a dashboard forty times a day is a cost bug, not a performance bug. Interviewers want the reasoning shape, not an exact invoice.
Capacity estimation
Walk through sizing a clickstream pipeline for 100 million events per day.- That is roughly 1,150 events per second average; assume a five to ten times peak, so design for about 10,000 per second. At 1 KB per event that is 100 GB per day raw, which compresses to roughly 10 to 20 GB as Parquet with Zstandard. A year of retention is a few terabytes — small enough that the interesting constraints are peak throughput and query patterns, not storage.
What compression ratio should you assume for columnar storage?- Plan on 3 to 10 times for JSON to Parquet with Snappy, and better with Zstandard, because columnar layout plus dictionary and run-length encoding exploits low-cardinality repeated values. Say the ratio depends on cardinality and sort order — sorting on a low-cardinality column before writing often improves compression more than changing codecs. Never claim a specific ratio without naming what it depends on.
How do you size a Kafka cluster in an interview?- Compute ingress bytes per second, multiply by the replication factor (usually 3) for disk write, and multiply ingress by the number of consumer groups for egress. Retention days times daily ingress times replication gives disk. Then check partition count against target consumer parallelism, since maximum consumer parallelism per group equals the partition count. Mention that partitions can be increased but not decreased, and that increasing them breaks key-to-partition affinity.
How many partitions should a topic have?- Enough to reach the required consumer parallelism plus headroom for growth, typically sized so each partition handles perhaps 10 MB per second. Too many partitions increases controller metadata, open file handles, rebalance duration, and end-to-end latency; a few thousand per broker is a practical ceiling for the classic architecture, and KRaft mode raised the cluster-wide limit substantially. Give a number and the reason behind it.
How do you estimate warehouse query cost for a dashboard?- Bytes scanned per query times refresh frequency times number of concurrent dashboards. A dashboard scanning 500 GB and refreshing every fifteen minutes reads 48 TB a day, which is the whole argument for a pre-aggregated serving table. On per-byte pricing that is directly convertible to dollars; on per-second compute pricing you convert through scan throughput instead.
Ingestion design
How would you ingest from 200 operational databases without hammering them?- Use log-based CDC reading the write-ahead log or binlog rather than periodic SELECT polling, so the load on the source is near zero and you capture deletes and intermediate states. Land the change stream in Kafka or a managed equivalent, then apply into the warehouse with MERGE or a snapshot-plus-log pattern. Call out the operational risks — replication slot growth when the consumer stalls can fill the primary disk, and initial snapshots need throttling.
Push or pull ingestion — how do you choose?- Pull gives the platform control over rate, retries, and scheduling and works when the source has a queryable API or log. Push suits high-volume telemetry where the producer must not block and you cannot poll millions of clients. Push requires you to own buffering, authentication, and backpressure at the edge, which is why the usual answer is push to a durable log and pull from there.
How do you design the pipeline so a bad deploy does not lose data?- Keep the durable log as the boundary — write raw payloads to Kafka or object storage before any transformation, with retention long enough to replay (7 days is a common floor, 30 for critical streams). Then a transformation bug is recoverable by reprocessing rather than by asking the source to resend. Immutable raw storage is the single most valuable property of the design and worth stating explicitly.
How do you handle schema evolution from upstream producers?- Enforce a schema registry with a compatibility mode — BACKWARD (new schema reads old data) is the common default and permits adding optional fields and deleting fields, while FORWARD permits adding fields old consumers can ignore. Reject incompatible changes at produce time rather than discovering them in a failing downstream job. For the warehouse, decide up front whether a new column is auto-added or quarantined.
What is the dead letter queue pattern and where does it go wrong?- Records that fail parsing or validation go to a separate topic or table with the raw payload and the error, so the main stream keeps flowing. It goes wrong when nobody monitors it — a silently filling dead letter queue is data loss with extra steps. Always pair it with an alert on rate and age, and a documented replay path once the bug is fixed.
How do you rate-limit or backpressure a pipeline that is falling behind?- Prefer backpressure that propagates to the producer where the producer can slow down, and buffering in a durable log where it cannot. In consumer terms, bound in-flight records and let lag grow rather than exhausting memory, then alert on consumer lag and lag derivative. Dropping data should be a deliberate, documented choice for a specific low-value stream, never an accident of an unbounded queue.
Storage and modeling choices
Object storage with an open table format, or a warehouse? How do you decide?- Open table formats on object storage win when multiple engines must read the same data, when you need engine independence, or when data volume makes proprietary storage pricing painful. A warehouse wins on operational simplicity, mature governance, and low-latency BI concurrency. In 2026 the honest answer is often both — Iceberg tables as the shared truth with a warehouse engine as one of several readers.
How do you choose a file format?- Parquet for analytical columnar reads, which is the default for anything queried by column. Avro for row-oriented streaming and schema-registry-backed messages where whole records are read and written. JSON only at the raw landing boundary where you cannot control the producer. The reason is access pattern — columnar formats let the engine read only the referenced columns and use per-column statistics for skipping.
How would you design the partition layout for an event table?- Partition on the event date derived from event time, not ingestion time, so backfills and late data land in the correct partition and time-range queries prune. Add a second partition dimension only if it is in most filters and does not fragment the table below sensible file sizes. Iceberg hidden partitioning is worth naming because it lets the layout change later without rewriting every query.
Where do you draw the line between raw, cleaned, and serving layers?- Raw is byte-faithful to the source and never edited. Cleaned applies typing, deduplication, and standard naming without business opinion. Serving encodes business definitions and is shaped for consumption. The value of the split is that a business logic change rebuilds only the top layer, and a source outage does not corrupt what you already captured. Say what each layer is allowed to do and who owns it.
When would you add a key-value store or a search index next to the warehouse?- When the access pattern is point lookups at high queries per second and single-digit millisecond latency, which columnar scanning cannot serve economically. Serve the aggregate from the warehouse into DynamoDB, Redis, or Elasticsearch as a derived read model, and treat that store as rebuildable rather than authoritative. Interviewers will ask about consistency between the two — the answer is a versioned or timestamped write plus a rebuild path.
Batch, streaming, and processing
How do you decide between batch and streaming for a given requirement?- Start from the freshness the consumer will actually act on. If the decision is made hourly, streaming buys nothing and costs you an always-on system with harder correctness semantics. Choose streaming when latency requirements are sub-minute, when the data is unbounded and reprocessing whole days is uneconomical, or when the source is already a log. Micro-batch every few minutes covers a surprisingly large middle ground.
Explain the Lambda and Kappa architectures and whether you would use either name today.- Lambda runs parallel batch and speed layers and merges them at query time, paying for two implementations of the same logic. Kappa runs a single streaming path and reprocesses by replaying the log. Modern practice largely dissolves the debate — a unified engine over an open table format handles both, and the useful part of the discussion is whether you can express the same logic once and reprocess it.
How do you handle exactly-once semantics end to end?- You get effectively-once, not literally-once — the pattern is at-least-once delivery plus idempotent writes or transactional sinks. Kafka provides an idempotent producer and transactions across a consume-transform-produce cycle; Flink combines checkpoints with two-phase commit sinks. For warehouse sinks, an idempotent MERGE on a deterministic key is simpler and more robust than distributed transactions. Say which mechanism you are relying on at each hop.
How would you design for reprocessing three months of history?- Keep the raw log or landed files for the whole window, make the transformation a pure function of input plus code version, and write output to a versioned or shadow location so production readers are untouched until you swap. Parallelize by partition with checkpointing, throttle so you do not starve live pipelines, then validate against known aggregates before the swap. If your design cannot answer this, it is not production-ready and interviewers know it.
Where do you put business logic — the streaming job, the warehouse, or the BI tool?- Put as little in the streaming job as you can, because it is the hardest place to change and reprocess. Put conformance and metrics in the warehouse where SQL is testable and versioned, and put only presentation in the BI tool. The rule of thumb is that logic should live at the layer where you can rebuild its output cheaply.
Serving and consumption
How do you serve sub-second dashboards over billions of rows?- Pre-aggregate to the grain the dashboard actually queries and serve from a small table, or use a purpose-built OLAP store such as ClickHouse, Apache Druid, or Apache Pinot that indexes for that pattern. Raw-scan-per-page-load does not survive concurrency. The interviewer follow-up is what happens when a user drills to a grain your aggregate does not cover — the answer is a slower drill-through path against the detail table, explicitly labeled as such.
How do you expose data to other teams safely?- Publish versioned tables or views as a contract, with documented schema, freshness guarantee, and a deprecation policy, rather than letting consumers query your internal models. Enforce with a separate schema and grants so internal tables are not readable. The point of the contract is that you can refactor internals without a coordination meeting, which is the actual business value.
What is a data contract and what does it contain in practice?- A machine-readable specification of schema, semantic definitions, freshness and completeness expectations, and ownership, checked in CI against the producing job. In practice it looks like a schema file plus tests that fail the producer build on a breaking change. The reason it works where documentation does not is that it fails a pipeline rather than a code review.
How would you handle a consumer that needs a different freshness than everyone else?- Do not raise the freshness of the shared pipeline to satisfy one consumer, because everyone pays that cost forever. Add a separate faster path for the specific tables they need, or offer an incremental view over the same source. Make the freshness difference visible in metadata so nobody compares two numbers computed as of different times and files a bug.
How do you support both analytics and machine learning from one platform?- Share the same curated tables as the source of truth and add point-in-time-correct feature retrieval on top, because the failure mode unique to machine learning is training on features that were not available at prediction time. That means feature tables carry event timestamps and joins are as-of joins, not equality joins. Online serving needs a low-latency store fed from the same definitions to avoid training-serving skew.
Reliability and failure modes
Your pipeline has been silently producing wrong numbers for two weeks. Walk me through the response.- Stop the bleeding by pausing downstream publication, quantify the blast radius with the affected tables, time window, and consumers, then communicate before you fix. Correct by reprocessing from raw with the fixed logic into a shadow table, validate, and swap. Afterward, add the assertion that would have caught it and a monitor on the metric that moved. The structure — contain, quantify, communicate, correct, prevent — matters more than the specific bug.
What do you monitor on a data platform, beyond job success and failure?- Freshness (age of the newest record per table), volume (row counts against a forecast band), schema drift, distribution shift on key columns, null and duplicate rates on business keys, and consumer lag on streams. Job success alone misses the most common production failure, which is a job that succeeds while processing zero or half the expected rows.
How do you define an SLA, SLO, and SLI for a data pipeline?- The SLI is the measurement, such as the percentage of days the fact table is complete by 07:00. The SLO is the internal target, say 99 percent over a rolling 30 days. The SLA is the external commitment with consequences, usually looser than the SLO. Freshness and completeness are the two SLIs that matter most, and the useful follow-up is what you are allowed to do when the error budget is exhausted.
How do you design for a full region outage?- Decide the recovery point and recovery time objectives first, because they determine cost. Object storage cross-region replication plus infrastructure as code to rebuild compute gives hours of recovery time at low cost; an active-active streaming setup with mirrored topics gives minutes at multiples of the cost. State that most analytical workloads genuinely tolerate hours, and say so rather than over-engineering.
What happens when an upstream team changes a column type without telling you?- The pipeline should fail loudly at the contract boundary rather than silently coercing or nulling values, so the failure is a broken build with a clear owner instead of a wrong dashboard next quarter. Practically that means typed schema enforcement on ingestion, a quarantine path for nonconforming rows, and an alert routed to both teams. The organizational half of the answer, that the contract has a named owner, is what senior candidates add.
From DataLane — tutorials at/blog, practice SQL live in theplayground.