Data System Design Overview and Interview Atlas

Answer First: Data-system design is the art of matching ingest, storage, processing, serving, reliability, and cost to the business SLA.

Memory Map: clarify -> scale math -> ingest -> store -> process -> serve -> operate.

Ingest, Store, Serve

Answer First: Remember INGEST-STORE-SERVE: define how data arrives, where it lives, and how consumers read it before naming tools.

Memory Map: INGEST-STORE-SERVE -> arrival mode -> durable layer -> serving path -> SLA check -> tradeoff.

This keeps answers tool-neutral: Kafka, S3, Snowflake, Delta, Redis, and APIs are choices inside the framework, not the framework itself.

Seven-step design answer framework

Answer First: A strong design answer clarifies requirements, estimates capacity, sketches APIs, draws architecture, deep-dives one hard component, names tradeoffs, and closes with monitoring/failure modes.

Memory Map: clarify -> capacity -> API -> high-level boxes -> deep dive -> tradeoffs -> monitoring.

In a 45-minute interview, protect time: 5 minutes clarify, 10 minutes architecture, 15 minutes deep dive, 10 minutes tradeoffs, 5 minutes operations.

Requirements and Scale Math

Answer First: Before choosing tools, convert the prompt into latency, throughput, storage, retention, consistency, and cost numbers.

Memory Map: business SLA -> QPS -> event size -> GB/day -> retention -> partition count -> cost guardrail.

Scale math to memorize

Answer First: Use rough orders of magnitude: 1 Gbps is 125 MB/s, 10M events/day at 500 B is 5 GB/day, and one Kafka partition is often a tens-of-MB/s planning unit.

Memory Map: latency numbers -> bandwidth conversion -> row size -> daily volume -> annual storage -> partition estimate.

Scale math prevents magical architecture. If a prompt says 100K events/sec at 500 B, say "about 50 MB/s before overhead."

Fintech 100K transactions/sec design

Answer First: For high-rate fintech, partition Kafka by account, stream-score fraud, keep hot balances in a low-latency store, land immutable history in a lakehouse, and design for multi-AZ durability.

Memory Map: 100K/sec -> account partition -> fraud stream -> hot balance store -> lakehouse retention -> multi-AZ DLQ.

Call out regulatory retention and exactly/effectively-once accounting before drawing tools.

Slow 10 TB dashboard

Answer First: Fix slow dashboards by walking from pruning and file layout to pre-aggregation, materialized serving, warehouse sizing, and result cache—not by only scaling compute.

Memory Map: 10 TB scan -> partition pruning -> clustering/compaction -> aggregate table -> serving engine -> cache and warehouse.

If latency must be sub-second, consider a serving OLAP store such as ClickHouse or Druid instead of raw table scans.

Storage, Compute, Lakehouse, and Serving Layers

Answer First: Storage choices define access pattern, consistency, cost, and evolution; compute choices define latency, concurrency, and operational complexity.

Memory Map: access pattern -> storage layout -> compute engine -> serving latency -> governance -> cost.

Lake vs warehouse vs lakehouse

Answer First: A lake is cheap flexible object storage, a warehouse is governed analytical compute/storage, and a lakehouse adds ACID/table features to lake storage.

Memory Map: raw lake -> warehouse BI -> lakehouse ACID -> schema enforcement -> ML friendliness -> cost tradeoff.

Choose lakehouse when you need open storage plus table management features such as transactions, schema evolution, and time travel.

OLTP vs OLAP vs HTAP

Answer First: OLTP serves many small row transactions, OLAP serves large analytical scans, and HTAP attempts both with hybrid tradeoffs.

Memory Map: row transactions -> column scans -> hybrid serving -> production isolation -> workload fit -> blast radius.

Never put open-ended analytical scans on the primary production OLTP database without isolation.

Columnar vs row storage

Answer First: Row storage wins for point reads and writes; columnar storage wins for analytical scans, compression, and reading a subset of columns.

Memory Map: point lookup -> row layout -> analytical aggregate -> column pruning -> compression -> serving choice.

Use this as the bridge from "what query" to "what storage layout."

Feature store design

Answer First: Feature stores keep offline historical features for training and online low-latency features for serving while preventing training-serving skew.

Memory Map: events -> stream features -> offline Delta -> online Redis/DynamoDB -> serving API -> parity check.

Mention freshness, point-in-time correctness, and feature parity; those are the interview differentiators.

Schema evolution in a lakehouse

Answer First: Additive schema changes are safe; breaking changes need table versioning, column mapping or compatibility layers, CI contract tests, and consumer-safe selects.

Memory Map: add nullable column -> breaking rename/drop -> table version -> contract test -> consumer projection -> registry.

Data contracts prevent silent breakage when producers and consumers evolve independently.

Streaming, Batch, Micro-batch, and Ingestion Patterns

Answer First: Choose streaming only when business value degrades with delay; otherwise batch or micro-batch is cheaper, simpler, and easier to replay.

Memory Map: latency need -> batch simplicity -> micro-batch compromise -> streaming cost -> replay plan -> checkpoint.

Batch vs streaming vs micro-batch

Answer First: Batch trades latency for simplicity and cost, streaming trades cost/complexity for real-time response, and micro-batch sits between them.

Memory Map: hourly OK -> batch -> seconds needed -> micro-batch -> milliseconds needed -> streaming.

The decision question is not "Can we stream?" It is "What business KPI gets worse if this is late?"

Lambda vs Kappa

Answer First: Lambda keeps separate batch and speed layers; Kappa uses one streaming pipeline and relies on log replay for backfill.

Memory Map: two pipelines -> merge complexity -> one stream path -> Kafka replay -> backfill constraint -> codebase cost.

Modern data platforms often prefer Kappa-like designs when durable logs and lakehouse tables make reprocessing practical.

Push vs pull ingestion

Answer First: Push gives sources control and suits real-time high-volume events; pull gives consumers control and suits scheduled APIs and bounded extracts.

Memory Map: source pushes -> backpressure challenge -> consumer pulls -> natural rate control -> failure ownership -> SLA fit.

Pair push with queues/logs; pair pull with orchestrators and idempotent checkpoints.

Clickstream analytics design

Answer First: A clickstream platform usually uses a collector, Kafka buffer, stream aggregation, cold object storage, hot OLAP serving, and dashboard/cache layer.

Memory Map: web event -> collector -> Kafka -> stream aggregation -> cold lake -> OLAP serving -> dashboard.

Name both hot and cold paths: live dashboard and durable replay/history.

Ingest 200 Oracle databases

Answer First: Use log-based CDC into Kafka, land raw append-only events, merge into lakehouse silver tables, bootstrap with snapshots, and monitor per-table lag plus DLQ volume.

Memory Map: Oracle logs -> CDC connector -> Kafka topics -> bronze append -> silver merge -> schema registry -> lag alert.

Reliability, Consistency, Observability, and Data Quality

Answer First: Production design answers must explain what fails first, how the system detects it, and what recovery path keeps data correct.

Memory Map: failure mode -> signal -> alert -> replay -> idempotency -> data quality proof.

CAP theorem for distributed stores

Answer First: During network partition, distributed stores choose between stronger consistency and availability; partition tolerance is not optional.

Memory Map: partition event -> CP consistency -> AP availability -> eventual read -> business correctness -> user impact.

Tie CAP to the business: balances and bookings often prefer correctness; feeds and analytics often tolerate eventual consistency.

Synchronous vs asynchronous with a queue

Answer First: Synchronous calls are simpler for immediate answers; asynchronous queues decouple slow or flaky work, enable retry/DLQ, and support fan-out.

Memory Map: immediate response -> sync call -> flaky dependency -> queue buffer -> retry/DLQ -> fan-out.

Use async when the caller should not block on downstream work.

Exactly-once and effectively-once

Answer First: True exactly-once needs coordinated transactions; most data systems achieve practical correctness through idempotent writes, dedupe keys, and replay-safe pipelines.

Memory Map: transaction boundary -> idempotent consumer -> dedupe key -> coordinated commit -> replay safety -> audit proof.

In design rounds, say what the sink can guarantee; a non-transactional API changes the answer.

Debugging wrong dashboard numbers

Answer First: Reproduce the wrong cell, walk backward through gold, silver, bronze, and source, compare row counts/aggregates, isolate the break, and add tests to prevent recurrence.

Memory Map: reproduce cell -> trace lineage -> compare layers -> find dropped rows -> fix logic -> add quality tests.

Common culprits: inner joins, timezone filters, dedupe order, late data, and non-idempotent reruns.

Data System Design Scenarios, Gotchas, and Mock Interviews

Answer First: The fastest way to sound senior is to name the tradeoff, quantify it, and describe the failure mode before the interviewer asks.

Memory Map: prompt -> tradeoff -> number -> architecture -> failure mode -> monitor.

Common production gotchas

Answer First: The common failures are treating cache as a write fix, postponing sharding, overusing Kafka, promising exactly-once, joining everything in Spark, and storing all data as JSON.

Memory Map: cache invalidation -> shard too late -> Kafka misuse -> exactly-once myth -> shuffle pain -> schema archaeology.

These are interview traps because they sound like simple fixes until you name the operational consequence.

Batch data warehouse design

Answer First: A batch warehouse design lands raw source data, transforms with governed SQL models, publishes gold marts, schedules jobs, tests quality, and controls warehouse cost.

Memory Map: source connectors -> raw bronze -> dbt silver -> gold marts -> BI -> quality tests -> cost controls.

Keep ETL and BI compute isolated so dashboards do not fight nightly loads.

Reporting pipeline for a small data team

Answer First: For a small team, prefer managed ingestion, SQL-first transformation, a managed warehouse, lightweight orchestration, RBAC, and automated tests over a bespoke platform.

Memory Map: managed ingestion -> warehouse -> dbt -> orchestrator -> BI -> tests -> RBAC.

The senior answer is not maximum tooling; it is maintainable ownership for three engineers.

Final readiness checklist

Answer First: Be ready to draw ingest-store-serve, estimate throughput, choose batch vs stream, explain CP/AP, design common platforms, debug slow queries, and walk the seven-step framework.

Memory Map: draw framework -> estimate numbers -> choose tradeoff -> pick store -> debug quality -> operate failure -> communicate.

If your answer lacks a number, a failure mode, or a monitoring signal, it is probably too shallow.

Data System Design Interview Questions

These questions are link-only so there is one canonical answer owner.

Q-SD-001: Design a data platform for a fintech processing 100K transactions/sec

Answer owner: Fintech 100K transactions/sec design

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L307.

Q-SD-002: A dashboard is slow. It queries a 10 TB Delta table. Fix it.

Answer owner: Slow 10 TB dashboard

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L325.

Q-SD-003: How do you handle schema evolution in a lakehouse?

Answer owner: Schema evolution in a lakehouse

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L338.

Q-SD-004: Design ingestion from 200 legacy Oracle databases into lakehouse

Answer owner: Ingest 200 Oracle databases

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L349.

Q-SD-005: Dashboard shows wrong number. How do you debug data quality?

Answer owner: Debugging wrong dashboard numbers

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L360.

Q-SD-006: Build reporting pipeline for team of 3 data engineers, 5 analysts, 100 stakeholders

Answer owner: Reporting pipeline for a small data team

Alternate source wording: content/systemdesign/SD_01_Confusions_Labs_MockInterview.md#L376.