1. Design a data pipeline to ingest 1B events/day.
Kafka ingestion -> stream/batch processing (Spark/Flink) -> warehouse/lake, partition & schema mgmt.
System Design interview questions and answers for data engineers and analytics engineers
System design for data roles is about pipelines, warehouses, and failure modes, what happens when a job re-runs, when events arrive late, or when the data does not fit in memory. Real questions with worked answers below.
16 System Design questions
2. Design a data warehouse schema for an e-commerce company.
Star schema: fact_orders plus dimensions (user, product, date, geo), with slowly changing dimensions where attributes drift.
3. How would you design a feature store for ML?
Offline (batch) + online (low-latency) stores, feature registry, point-in-time correctness.
4. Batch vs streaming: how do you decide for a given use case?
Latency needs, cost, complexity, data freshness SLAs, and correctness guarantees.
5. Design a metrics/experimentation platform.
Assignment service, event logging, metric computation, stats engine, guardrails, dashboards.
6. How do you ensure data quality in a pipeline?
Schema tests, freshness/volume checks, anomaly detection, contracts, alerting (e.g., dbt tests).
7. A nightly pipeline fails halfway through. How do you design it so re-running is safe?
Make each step idempotent and partition-scoped: a run owns a date partition and fully replaces it, so re-running converges instead of accumulating. Checkpoint at partition boundaries rather than per row, recovery granularity should match the unit you can safely recompute. Side effects that cannot be repeated (incrementing a counter, sending an alert, charging something) either move outside the retryable path or get keyed and deduplicated.
8. Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?
Partition by event time, not ingestion time, and reprocess a trailing window, re-run the last few days each night so late events land in the day they actually belong to. The alternative is a watermark plus published corrections. Either way, state the tradeoff out loud: you wait and are complete but late, or publish early and restate. Bucketing by ingestion time is what makes the warehouse and the source system disagree, and nobody can tell you why.
9. When would you denormalize a warehouse table, and what do you give up?
Denormalize when read patterns are stable and join cost dominates, wide fact tables or pre-joined marts, so analysts are not writing six joins for every question. You trade storage, which is cheap, for write complexity, which is not: one source change now has to update several places, so consistency becomes your problem instead of the database's. Normalize what changes often, denormalize what is read often.
10. Design an incremental dbt model for a table where events can arrive late. What do you use as the incremental filter?
Filter on event time with a lookback window (in is_incremental(), where event_time >= (select max(event_time) from this model) minus a few days) and set a unique_key so late rows update instead of duplicating. Use merge or delete+insert on the partition, not append. State the tradeoff: a wider lookback is more correct but reprocesses more each run.
11. How do you model a Type 2 slowly changing dimension, and how do you test it?
One row per version of the entity with valid_from / valid_to and a current flag, and a surrogate key per version so facts join to the state as of the event. Test that exactly one version is current per natural key, that the validity windows never overlap, and that valid_from < valid_to, dbt unique/not_null plus a custom overlap test.
12. Two dashboards report different numbers for active users. How do you fix it for good?
The cause is two definitions, not a data bug. Define the metric once in a shared model or a semantic/metrics layer and have both dashboards read it, instead of each re-deriving it in its own SQL. Encode the grain and filters (which events, what window, the dedup rule) in one tested model. The fix is governance (a single source for the definition) not another patch on one dashboard.
13. What is the difference between a staging model and a mart in dbt, and why keep them separate?
Staging is one-to-one with a source: rename, cast, and clean only, no business logic, no joins. Marts are the joined, business-shaped tables analysts query. Separating them means a source change is absorbed in one place, logic is reusable across marts, and each layer's contract can be tested on its own.
14. A model that ran in 2 minutes now takes 40. How do you diagnose and fix it?
Check the query plan and the volume first: usually a full refresh where an incremental would do, a fan-out join from a non-unique key, or a missing partition/cluster filter. Fix by making it incremental, deduping the join key, or partitioning on the filter column. Measure before and after, it feels faster is not a diagnosis.
15. A dashboard is slow to load. Where do you look?
Usually the query, not the tool: too fine a grain pulled to the client, a live query where an extract would do, or unfiltered scans. Pre-aggregate in the warehouse to the grain the dashboard actually shows, push filters down, and cache or extract where freshness allows. Row-level detail behind a summary should load on demand, not upfront.
16. How do you validate a new dashboard before you ship it?
Reconcile totals against a trusted source (finance, the source system, a known-good query), spot-check a few rows end to end, and confirm filters and date logic behave at the edges (empty selection, a single day, a year boundary) at every drill level. A dashboard that is wrong once loses trust for good, so validation is the job, not an afterthought.