Data Engineering System Design Interview Questions and Answers

These 24 questions are what data engineering system design rounds actually ask. The first eight test the method and the big trade-offs. The other sixteen are design prompts: each has a short model answer you can say in about two minutes, and a link to a full case study with requirements, scale numbers, architecture and follow-up questions.

For day-to-day troubleshooting questions (“a rerun double-counted revenue”, “Kafka lag keeps growing”), see the scenario-based interview questions. For a study plan, see the system design interview roadmap.

Question list

Show all 24 questions

1. How do you approach a data engineering system design question?
2. Which clarifying questions do you ask first?
3. How do you estimate scale in a design interview?
4. When do you choose batch and when streaming?
5. Warehouse, lake or lakehouse: how do you pick the storage layer?
6. How do you make a pipeline safe to rerun?
7. How does your design handle late and out-of-order data?
8. How do you cover data quality, monitoring and cost in a design?
9. Design a daily batch pipeline for 2 TB of new data
10. Design a real-time analytics dashboard
11. Design a clickstream data platform
12. Design change data capture from an OLTP database
13. Design a Kafka ingestion layer for many teams
14. Design a company-wide lakehouse
15. Design a payment events pipeline with exactly-once results
16. Design a real-time fraud detection pipeline
17. Design a feature store
18. Design a data quality framework
19. Design a data catalog and lineage system
20. Design a GDPR-compliant pipeline for personal data
21. Design an IoT sensor data pipeline
22. Design a log ingestion and search platform
23. Design the data pipeline behind a RAG application
24. Design a multi-tenant data platform

The method and the big trade-offs

1. How do you approach a data engineering system design question?

Intermediate · Very Common · Framework

Use the same six steps every time, and say them out loud so the interviewer can follow:

1. Clarify who uses the data, for what, and how fresh it must be.
2. Estimate scale: events per second, daily volume, history kept, number of users.
3. Sketch end to end: sources, ingestion, storage layers, processing, serving.
4. Go deep on the one or two parts that carry the most risk.
5. Cover reliability, security and cost: reruns, late data, quality checks, monitoring, access, spend.
6. State trade-offs: what you chose, what you rejected, and what would change your mind.

Spend about five minutes on steps 1 and 2. Candidates who start drawing boxes straight away usually design the wrong system.

2. Which clarifying questions do you ask first?

Beginner · Very Common · Framework

Ask the questions whose answers change the design:

• Who consumes it and how? Dashboards, an ML model, an API, another team.
• How fresh? Seconds, minutes, hourly or daily. This decides batch vs streaming.
• How big? Events per second at peak, bytes per event, years of history.
• How correct? Can numbers be approximate, or must they match finance to the cent?
• What are the sources? Databases, files, APIs, event streams, and who owns them.
• Any rules? Personal data, retention, regions, audit.

Write the answers on the board as requirements. Every later choice should point back to one of them.

3. How do you estimate scale in a design interview?

Intermediate · Common · Framework

Round hard and show your working. A day has about 86,400 seconds, so call it 100,000.

Example: 50 million events a day at 1 KB each is 50 GB a day, about 18 TB a year before compression. Columnar formats like Parquet often shrink that several times. Average rate is about 600 events per second; plan for peaks of 5 to 10 times that.

Then say what the numbers mean: “18 TB a year fits easily in a warehouse or lakehouse; 6,000 events per second at peak is a small Kafka cluster with a handful of partitions.” The point is not precision. It is showing that sizes drive the choices.

4. When do you choose batch and when streaming?

Intermediate · Very Common · Trade-offs

Start from the freshness requirement, not the technology.

• Batch when hourly or daily data is enough: reporting, finance, most analytics. It is cheaper, simpler to rerun and easier to test.
• Streaming when an action depends on fresh data: fraud checks, alerts, live operations, personalisation.
• Micro-batch (for example every 5 minutes) is often the honest middle ground.

Many designs use both: a stream for the fresh view and a batch job that recomputes the same numbers later as the source of truth. Name the cost of streaming: always-on compute, state to manage, and harder reprocessing.

Learn more: Batch vs streaming

5. Warehouse, lake or lakehouse: how do you pick the storage layer?

Intermediate · Very Common · Trade-offs

• Warehouse (Snowflake, BigQuery, Redshift): structured data, SQL users, strong governance, least to operate.
• Data lake (files on object storage): cheap storage for raw and semi-structured data, but no transactions on its own.
• Lakehouse (Delta Lake, Iceberg or Hudi tables on object storage): lake prices with transactions, schema checks, time travel and MERGE, readable by Spark, SQL engines and ML tools.

In an interview, pick from the requirements: mostly BI on structured data points to a warehouse; large raw volumes plus ML and streaming points to a lakehouse. Keep raw data either way so you can reprocess.

Learn more: Data lake vs lakehouse vs data warehouse

6. How do you make a pipeline safe to rerun?

Intermediate · Very Common · Reliability

Make every write idempotent: running the same job for the same input twice gives the same result.

• Process by a fixed window (a date or hour partition), not “everything since now”.
• Write by overwriting that partition or by MERGE on a business key, never by blind appends.
• Deduplicate on an event ID in the first clean layer.
• Keep raw data so any window can be rebuilt.

Then say how you would prove it: run yesterday’s job twice in a test and compare row counts and totals. Interviewers ask “what happens if the job runs twice?” in almost every design round.

Learn more: Idempotency in data pipelines

7. How does your design handle late and out-of-order data?

Advanced · Common · Reliability

Use event time (when it happened), not arrival time, for anything that is reported by time.

• Streaming: set a watermark, such as “accept events up to 10 minutes late”, and decide what happens to later ones: drop, send to a side table, or correct in a later batch.
• Batch: reprocess a rolling window, for example the last 3 days, on every run, so late rows land in the right day.
• Reporting: tell users when a day is final.

State the trade-off: a longer wait means more complete numbers but slower results and more state to hold.

Learn more: Incremental loading, watermarks and backfills

8. How do you cover data quality, monitoring and cost in a design?

Intermediate · Common · Operations

Add them as parts of the design, not as an afterthought:

• Quality: checks at each layer boundary (row counts, nulls, uniqueness, value ranges, freshness). Stop or quarantine bad batches before they reach the gold layer.
• Monitoring: job success, run time, data freshness against the SLA, and volume compared with normal. Alerts go to an owner.
• Cost: partition and cluster for the common filters, autoscale or schedule compute, keep hot data short and archive the rest, and tag spend by team.

One sentence each is enough unless the interviewer digs in.

Learn more: Pipeline observability

Design prompts

9. Design a daily batch pipeline for 2 TB of new data

Intermediate · Very Common · Batch

Land source extracts in object storage as raw files, partitioned by load date. A Spark job cleans, types and deduplicates them into a silver table, then builds gold aggregates and facts with MERGE or partition overwrite so reruns are safe. An orchestrator such as Airflow runs the steps in order, with retries and an SLA alert. Quality checks sit between layers. For 2 TB a day, partition by date, keep files around 128 MB to 1 GB, and size the cluster so the run finishes well inside the window, with time left for one retry. Mention backfills: the same job, run for past dates.

Full case study: Scalable batch pipeline

10. Design a real-time analytics dashboard

Advanced · Very Common · Streaming

Events go to Kafka, keyed so related events stay in order. A stream processor (Flink or Spark Structured Streaming) aggregates by event-time windows with a watermark, and writes results to a store built for fast reads, such as a real-time OLAP database. The dashboard queries that store. Raw events also go to the lakehouse, where a batch job recomputes the same metrics as the trusted numbers. Discuss end-to-end latency targets, exactly-once or deduplicated writes, late events, and what the dashboard shows when the stream falls behind.

Full case study: Real-time analytics pipeline

11. Design a clickstream data platform

Advanced · Common · Streaming

A collection endpoint receives browser and app events, validates them against a schema and writes them to Kafka. From there, one path lands raw events in the lakehouse for sessionisation and product analytics, and another feeds real-time uses. Key points: a schema registry so apps cannot break consumers, bot filtering, deduplication on event ID, sessions built in event time, and handling consent and personal data (drop or hash IP addresses and user identifiers where required). Volumes are high, so talk about partitions, compaction of small files, and cost.

Full case study: Clickstream data platform

12. Design change data capture from an OLTP database

Advanced · Very Common · CDC

Read the database’s transaction log (for example with Debezium) rather than polling tables, so you get inserts, updates and deletes without loading the source. Changes go to Kafka, one topic per table, keyed by primary key so changes to one row stay in order. A consumer applies them to the lakehouse or warehouse with MERGE, using the log position to ignore duplicates and older versions. Cover the first full snapshot, schema changes, deletes (soft or hard), and how to rebuild a table if the stream breaks.

Full case study: Change data capture platform

13. Design a Kafka ingestion layer for many teams

Advanced · Common · Streaming

Treat Kafka as a shared platform. Set naming rules and ownership for topics, require schemas in a schema registry with compatibility checks, and choose partition counts from expected throughput and consumer parallelism. Use replication factor 3 and acks=all for important data. Provide standard connectors to land topics in the lakehouse, quotas so one team cannot starve others, and dashboards for consumer lag. Discuss retention, compaction for “latest value” topics, dead-letter topics for bad messages, and how teams request new topics.

Full case study: Kafka ingestion system

14. Design a company-wide lakehouse

Advanced · Common · Platform

Use open table formats on object storage with bronze, silver and gold layers. Bronze keeps raw data, silver holds cleaned and joined data, gold holds business-ready tables owned by domains. One catalog controls access, lineage and discovery. Separate compute for ingestion, transformation, BI and ML so they do not slow each other down, and charge cost back by team. Cover table maintenance (compaction, clustering, cleaning old files), data contracts at the bronze-to-silver boundary, and how a new team onboards.

Full case study: Scalable lakehouse

15. Design a payment events pipeline with exactly-once results

Advanced · Common · Streaming

Every payment event carries a unique ID from the producer. Kafka producers use idempotence and transactions; the stream processor checkpoints its state and commits output and offsets together, or the sink upserts by payment ID so a replay changes nothing. Ledgers are append-only with corrections as new rows, never edits. Reconcile daily against the payment provider and the bank, and alert on any difference. Say clearly that “exactly-once” means exactly-once results: messages may be delivered twice, but they are counted once.

Full case study: Payment events pipeline with exactly-once

16. Design a real-time fraud detection pipeline

Advanced · Common · Streaming and ML

The payment service calls a scoring service that must answer within about 100 ms. That service reads precomputed features from a low-latency online store, applies rules and a model, and returns approve, review or decline. A stream processor keeps velocity features (payments per card in the last minute, hour, day) up to date. All decisions and features are logged to the lakehouse, and chargeback labels that arrive weeks later are joined back point-in-time to build training data. Cover the fallback when scoring fails, shadow testing of new models, and audit.

Full case study: Fraud detection pipeline

17. Design a feature store

Advanced · Common · ML platform

Define each feature once, with its source, logic, owner and freshness. Batch and streaming jobs compute features into an offline store (lakehouse tables with full history, for training) and an online store (a key-value database with the latest values, for serving in milliseconds). Training sets use point-in-time joins so a row only sees feature values known at that moment, which prevents leakage. The main risk to discuss is training-serving skew: use the same definitions for both stores and monitor that online and offline values match.

Full case study: Feature store

18. Design a data quality framework

Intermediate · Common · Quality

Let table owners declare checks in code next to the pipeline: schema, not-null, unique keys, accepted values, ranges, freshness and volume compared with history. Run them in the pipeline at layer boundaries, and decide per check whether a failure blocks publishing or only warns. Store every result so you can show trends and a quality score per table. Route alerts to the owning team, and quarantine bad rows instead of dropping them. For many tables, start with automatic baseline checks and add custom rules for critical tables.

Full case study: Data quality framework

19. Design a data catalog and lineage system

Advanced · Occasional · Governance

Collect metadata automatically: table schemas from the warehouse and lakehouse, run events from the orchestrator and Spark (OpenLineage is a common standard), and query logs. Store it as a graph of datasets, jobs and columns. Users search for tables, see owners, descriptions, freshness and quality, and follow lineage up to sources and down to dashboards. Use lineage for impact analysis before changes and to find every copy of personal data. Discuss keeping metadata fresh and getting owners to write descriptions.

Full case study: Data catalog and lineage system

20. Design a GDPR-compliant pipeline for personal data

Advanced · Common · Privacy

Classify personal data columns at ingestion and keep them in restricted tables. Replace direct identifiers with tokens or keyed hashes in the analytics layers, so most users never see raw personal data. For deletion requests, keep a lookup of where each person’s data lives, delete or anonymise it in every layer with MERGE, then run table maintenance so old file versions are removed too. Mention retention limits, access logs, consent flags, region rules, and that backups need a plan as well.

Full case study: GDPR and PII compliant pipeline

21. Design an IoT sensor data pipeline

Advanced · Occasional · Streaming

Devices send readings through a gateway or MQTT broker into Kafka, keyed by device. A stream job validates readings, drops duplicates, handles devices whose clocks are wrong, and raises alerts on thresholds. Data lands in a time-series store for recent dashboards and in the lakehouse for long-term analysis, downsampled as it ages (raw for days, per-minute for months, hourly for years). Discuss devices that go offline and then send hours of buffered data at once, which is a late-data problem at scale.

Full case study: IoT sensor data pipeline

22. Design a log ingestion and search platform

Advanced · Occasional · Platform

Agents on each host ship logs to Kafka, which absorbs spikes and lets several consumers read the same stream. One consumer parses and indexes recent logs into a search engine for troubleshooting; another writes everything to cheap object storage for long retention and audit. Keep a hot tier of days in the search index and a cold tier of months in storage. Cover structured log formats, sampling noisy debug logs, removing secrets and personal data before indexing, and cost per GB per day.

Full case study: Log ingestion and search platform

23. Design the data pipeline behind a RAG application

Advanced · Common · AI data

Ingest documents from their sources with change detection, so only new or edited documents are processed. Extract text, split it into chunks, create embeddings, and write them with metadata (source, access rights, version, date) to a vector index. Deleting or updating a document must remove its old chunks. At query time, filter by the user’s permissions before ranking. Cover re-embedding when the model changes, evaluation sets to measure retrieval quality, and cost of embedding calls.

Full case study: LLM RAG data ingestion pipeline

24. Design a multi-tenant data platform

Advanced · Occasional · Platform

Decide the isolation level first: separate storage and compute per tenant (strongest, most costly), shared compute with separate schemas, or shared tables with a tenant ID and row-level security (cheapest, easiest to get wrong). Large or regulated customers often get their own space while small ones share. Enforce tenant filters in one place, such as views or policies, never in each query. Cover per-tenant quotas, cost tracking, noisy-neighbour protection, onboarding and offboarding, and per-tenant deletion.

Full case study: Multi-tenant data platform