System Design for Data Engineers: A Framework, the Building Blocks, and Worked Designs

• How to use this guide
  • Running the examples
• The core problem and architecture: a framework for any design prompt
  • Why the data design round is different
  • The seven-step framework
  • Step 1 in practice: the questions to ask
  • Step 2 in practice: freshness, completeness and correctness are three targets
  • Step 2 in practice: capacity estimation
  • Step 3 in practice: the data contract
  • Step 4 in practice: the reference architecture
  • Step 5 in practice: correctness and recovery
  • Step 7 in practice: trade-off sentences
  • What interviewers grade, and the mistakes that cost the round
  • Framework interview questions
• Under the hood: the building blocks, in data-flow order
  • Ingestion: how data enters the platform
  • The log: Kafka as the system’s buffer and replay store
  • Processing: batch, micro-batch and continuous
  • Delivery semantics: where exactly-once actually lives
  • Storage: lake, warehouse and lakehouse tables
  • Serving: pick the store by the query
  • Orchestration and backfills
  • Data quality and observability
  • Architecture patterns in one table
  • Building-block interview questions
• Hands-on demo: an exactly-once Kafka to Iceberg pipeline
  • Step 1: create the topic
  • Step 2: the producer
  • Step 3: the streaming job
  • Step 4: run, crash, replay
  • Step 5: why the in-batch dedup matters
  • What the demo proves, and a pitfall it found
  • Clean up
  • Demo interview questions
• Real-world scenarios: ten worked designs
  • Design 1: clickstream analytics for a large website
  • Design 2: CDC from an OLTP database to the lakehouse in under five minutes
  • Design 3: a real-time operations dashboard with a 10-second SLA
  • Design 4: log aggregation for 10,000 servers
  • Design 5: a fraud feature pipeline with a 100 ms decision
  • Design 6: IoT telemetry from one million devices
  • Design 7: a multi-tenant file ingestion service
  • Design 8: an e-commerce warehouse for daily reporting
  • Design 9: a data quality platform for 500 tables
  • Design 10: GDPR right to erasure across the lake
  • Production failures: where designs break
  • Scenario interview questions
• Optimisation, tuning knobs and best practices
  • Partitioning and skew
  • Backpressure and rate limits
  • Small files and table maintenance
  • Cost
  • Tuning checklist
  • Tuning interview questions
• Summary and key takeaways
• References
• Trademarks

TL;DR

• Start from the consumer and the decision the data supports. Tools come last. An answer that opens with “Kafka, Spark, Snowflake” has skipped the part being graded.
• State freshness, completeness and correctness as separate targets. “Real time” is not a requirement until it has a number and a consumer.
• Estimate before you draw: events per second at peak, bytes per day, log disk with replication, lake storage after compression, and the partition count those numbers imply.
• Exactly-once is a property of a boundary, not of a pipeline. Name the boundary, then make the sink idempotent so that replay is safe.
• Every design needs a replay story: where the raw data lives, how far back it goes, and how a backfill runs without starving the live path.
• Monitor the data, not just the jobs. A job that succeeds with zero rows is the most expensive failure in data engineering because nobody gets paged.
• Close every choice with its cost: “X buys A at the cost of B, and we rejected Y because C”.

How to use this guide

A pipeline that double-counts revenue after a node restart is a system design failure, not a Spark bug. The data engineering design round tests whether you can see that failure coming: whether you ask what one row means, where the data can be replayed from, and which write is allowed to happen twice. This guide gives you a repeatable way to answer that round and the mechanics you need to defend each answer.

It follows the order you would use in the room:

1. A framework for any design prompt, with a capacity estimate you can do in two minutes.
2. The building blocks in the order data flows through them: ingestion, the log, processing, delivery guarantees, storage, serving, orchestration and quality.
3. A runnable demo of the property interviewers probe hardest: a Kafka to Iceberg stream that is replayed after a simulated crash, with one sink that duplicates and one that does not.
4. Ten worked designs of the prompts that come up most often, from clickstream analytics to GDPR erasure.
5. Production failures and tuning knobs, so the trade-offs you name are ones you can back with a config key or an error message.

Interview questions with model answers close the framework, building-block, demo, design and tuning sections. Each answer is written the way an interviewer wants to hear it: the decision, the mechanism, and what it costs.

If you have not yet read them, the companion guides cover the ground this one assumes: the data modeling guide for grain, SCD history, CDC patterns and Lambda versus Kappa in depth, and the Structured Streaming internals post for the micro-batch engine the demo runs on.

Running the examples

The demo scripts run on the public datalake-playground stack, which ships Spark 3.5.9 with the Iceberg runtime on disk. Start it from its root:

git clone https://github.com/rangareddy/datalake-playground.git
cd datalake-playground
SPARK_VERSION=3.5.9 sh run_datalake.sh start
sh run_datalake.sh status

The demo reads from Kafka, which only the all profile starts (the broker is kafka:29092), so start the stack with that profile instead:

PROFILE=all SPARK_VERSION=3.5.9 sh run_datalake.sh start

The PySpark scripts use an Iceberg catalog named sd of type hadoop, whose warehouse is file:///tmp/sd_blog/warehouse inside the spark-master container, and one Kafka topic, sd_blog_orders. They do not touch the shared Hive Metastore or MinIO buckets. Spark reads Kafka on this stack without extra --jars. The capacity estimate in the next section is plain Python and runs anywhere.

The core problem and architecture: a framework for any design prompt

Why the data design round is different

A backend design round asks whether a service stays up and answers fast. A data design round asks whether the numbers are still right after the system has failed, been replayed, and been changed by three teams over a year. The state that matters is not a request in flight; it is every row ever written.

That difference is why the framework below spends two of its seven steps on contracts and recovery, which a backend round barely touches.

The seven-step framework

 45-minute round
 ┌────────┬──────────┬────────┬──────────┬────────────┬────────┬─────────┐
 │ 1      │ 2        │ 3      │ 4        │ 5          │ 6      │ 7       │
 │Consumer│Numbers & │Data    │End-to-end│Correctness │Ops &   │Trade-   │
 │and     │non-funct.│contract│flow      │and recovery│govern- │offs and │
 │decision│targets   │        │(diagram) │            │ance    │stress   │
 ├────────┼──────────┼────────┼──────────┼────────────┼────────┼─────────┤
 │ ~4 min │ ~5 min   │ ~4 min │ ~10 min  │ ~10 min    │ ~5 min │ ~7 min  │
 └────────┴──────────┴────────┴──────────┴────────────┴────────┴─────────┘
   ask        estimate   define     draw       defend       operate  attack

The timings are a budget, not a script. What matters is the order: an architecture drawn before step 3 has no grain to be correct about, and a diagram defended before step 5 has never been replayed.

Step 1, the consumer and the decision. Ask who reads the output and what they do with it. “The fraud team blocks a card within 200 ms” and “finance closes the month on day 3” lead to different systems from the same events. Write the consumer and the decision at the top of the whiteboard.

Step 2, numbers and non-functional targets. Turn adjectives into numbers: volume, peak rate, record size, key cardinality, retention, growth. Then set the targets in the next subsection.

Step 3, the data contract. Define the key, the grain (what one row means), the event timestamp, the schema format and its compatibility rule, and who owns it. Most double-counting starts with a grain nobody wrote down.

Step 4, the end-to-end flow. Producers, ingestion, durable log, processing, storage layers, serving, and the control plane around them. Draw it left to right and walk one record through it.

Step 5, correctness and recovery. Delivery semantics at each boundary, deduplication, late data, dead-letter handling, replay and backfill. This is where most candidates lose the round.

Step 6, operations, security and governance. Freshness and volume alerts, lineage, access control, PII masking, retention and deletion, ownership.

Step 7, trade-offs and stress tests. Attack your own design: peak traffic at 10x, a hot key, a source outage of six hours, a breaking schema change, a backfill of a year. Say what bends and what breaks.

Step 1 in practice: the questions to ask

Ask five or six of these, not all ten. Then confirm the one constraint that dominates: “So the binding requirement is fraud decisions under 200 ms, and analytics can be an hour behind. Correct?”

Step 2 in practice: freshness, completeness and correctness are three targets

“Real time” fuses three properties that a design trades against each other:

A dashboard can be fresh and incomplete at the same time: at 10:02 the 10:00-10:01 bucket is visible but late mobile events have not arrived yet. Stating both targets forces the late-data question that step 5 answers.

Step 2 in practice: capacity estimation

Estimation earns its time when it changes a decision. Do it in this order: average rate, peak rate, bytes per day, log disk, lake storage, and the partition count. Every input is an assumption to say out loud.

"""Back-of-envelope sizing for an event pipeline, as done in an interview.

Every input is an assumption to state out loud. The per-partition budget and
the compression ratio in particular must be measured on your own data.
"""
import math

events_per_day = 2_000_000_000      # stated by the interviewer
avg_event_bytes = 1_000             # JSON, measured from a sample
peak_to_avg = 3                     # evening peak vs daily average
kafka_retention_days = 7
kafka_replication = 3
partition_budget_mb_s = 5           # per-partition write budget, measured
parquet_ratio = 5                   # JSON -> zstd Parquet, measured
lake_retention_days = 365

SECONDS_PER_DAY = 86_400
MB, GB, TB = 10**6, 10**9, 10**12

avg_eps = events_per_day / SECONDS_PER_DAY
avg_mb_s = avg_eps * avg_event_bytes / MB
peak_mb_s = avg_mb_s * peak_to_avg
raw_per_day = events_per_day * avg_event_bytes
kafka_disk = raw_per_day * kafka_retention_days * kafka_replication
lake_per_day = raw_per_day / parquet_ratio
partitions = math.ceil(peak_mb_s / partition_budget_mb_s)

print(f"average rate     : {avg_eps:,.0f} events/s, {avg_mb_s:.1f} MB/s")
print(f"peak rate        : {avg_eps * peak_to_avg:,.0f} events/s, "
      f"{peak_mb_s:.1f} MB/s")
print(f"raw per day      : {raw_per_day / TB:.1f} TB")
print(f"kafka disk       : {kafka_disk / TB:.0f} TB "
      f"({kafka_retention_days} days x RF {kafka_replication})")
print(f"min partitions   : {partitions} at {partition_budget_mb_s} MB/s each")
print(f"lake per day     : {lake_per_day / GB:,.0f} GB Parquet")
print(f"lake per year    : {lake_per_day * lake_retention_days / TB:,.0f} TB")

Run with python3 capacity_estimate.py:

average rate     : 23,148 events/s, 23.1 MB/s
peak rate        : 69,444 events/s, 69.4 MB/s
raw per day      : 2.0 TB
kafka disk       : 42 TB (7 days x RF 3)
min partitions   : 14 at 5 MB/s each
lake per day     : 400 GB Parquet
lake per year    : 146 TB

Read the numbers back as decisions. 69 MB/s at peak is a modest Kafka load, so the cluster is sized by retention disk, 42 TB, not by throughput. Fourteen partitions is the floor from throughput; the real count is set by consumer parallelism and headroom, so 24 or 48 is a defensible answer if you say why. 400 GB of Parquet a day means daily partitions hold hundreds of 512 MB files, which is healthy; hourly partitions would hold about 17 GB each, also fine. 146 TB a year puts a lifecycle policy on the table.

The two inputs marked “measured” are the ones an interviewer may challenge. Say that the per-partition budget comes from a load test on your brokers and message sizes, and the compression ratio from writing a day of real data. A number you can explain beats a number that sounds precise.

Step 3 in practice: the data contract

 event: order_status_changed            owner: orders-service team
 ┌──────────────┬───────────┬──────────────────────────────────────────┐
 │ field        │ type      │ contract                                 │
 ├──────────────┼───────────┼──────────────────────────────────────────┤
 │ event_id     │ string    │ unique per emitted event; dedup key      │
 │ order_id     │ string    │ entity key; Kafka message key            │
 │ status       │ enum      │ PLACED, PAID, SHIPPED, CANCELLED         │
 │ amount       │ decimal   │ order total in minor units, never float  │
 │ order_ts     │ timestamp │ business time the order was placed (UTC) │
 │ updated_ts   │ timestamp │ event time of this change; orders events │
 │ schema_ver   │ int       │ registry id; BACKWARD compatible only    │
 └──────────────┴───────────┴──────────────────────────────────────────┘
 grain of the sink table: one row per order_id, latest status wins

Three fields carry most of the correctness: a dedup key (event_id), an entity key that becomes the Kafka message key and the upsert key (order_id), and an event time that orders versions of the same entity (updated_ts). Without the last one, “latest wins” means “last to arrive wins”, and a retried old event silently overwrites a newer one. The demo in this guide feeds exactly that stale event into the pipeline.

Step 4 in practice: the reference architecture

  SOURCES        INGEST            LOG            PROCESS         STORE / SERVE
 ┌────────┐   ┌───────────┐   ┌───────────┐   ┌────────────┐   ┌──────────────┐
 │OLTP DB │──►│CDC        │──►│           │──►│stream      │──►│bronze (raw)  │
 │(WAL)   │   │(Debezium) │   │  Kafka    │   │(Spark /    │   │silver (clean)│
 ├────────┤   ├───────────┤   │  topics,  │   │ Flink)     │   │gold (agg)    │
 │apps,   │──►│SDK /      │──►│  keyed,   │   ├────────────┤   │ Iceberg/Hudi/│
 │devices │   │collector  │   │  RF = 3   │   │batch       │──►│ Delta on S3  │
 ├────────┤   ├───────────┤   └───────────┘   │(Spark,     │   ├──────────────┤
 │files,  │──►│landing    │──────────────────►│ dbt)       │──►│OLAP / KV /   │
 │SaaS API│   │zone + pull│                   └────────────┘   │search / APIs │
 └────────┘   └───────────┘                                    └──────────────┘
 ─────────────────────────────── CONTROL PLANE ────────────────────────────────
  schema registry · catalog & lineage · orchestration (Airflow) · data quality
  checks · freshness / volume / lag alerts · access control & PII masking · cost

Walk one record through it. An order row changes in PostgreSQL; the change lands in the write-ahead log; Debezium reads the log and publishes a keyed event to Kafka, which replicates it to three brokers before acknowledging. A stream job reads it, validates it against the registered schema, and appends it to bronze. A second step deduplicates and upserts it into silver at the order grain. Batch jobs build gold aggregates from silver, and a serving store answers dashboards and APIs. Files and SaaS APIs skip the log and land in an object-store landing zone, from which the batch path picks them up.

The bottom band is not decoration. A design without a schema registry, a catalog, quality checks and freshness alerts works on the day it ships and degrades silently afterwards.

Step 5 in practice: correctness and recovery

Answer five questions for every boundary in the diagram:

1. What are the delivery semantics here? At-most-once, at-least-once, or effectively-once, and what makes it so.
2. Where is the dedup key, and who applies it? Usually the sink, through a MERGE on the entity key or a unique constraint.
3. What happens to late data? Updated in place, written to a correction partition, or dropped behind a watermark and counted.
4. Where do bad records go? A dead-letter topic or table with the error, the raw payload and the source offset, plus an alert on its rate.
5. How do we replay? From which store, how far back, at what rate, and what stops the replay from overwriting newer data.

Step 7 in practice: trade-off sentences

Interviewers grade the trade-off, not the tool. Use one sentence shape:

“Micro-batches every 30 seconds buy us idempotent, transactional table commits at the cost of about a minute of freshness. We rejected per-record streaming into the warehouse because each record would be a commit.”

Then stress-test: “At 10x the peak, the Kafka cluster holds because it is sized by disk; the stream job falls behind, lag grows, and maxOffsetsPerTrigger keeps each batch bounded while autoscaling catches up.”

What interviewers grade, and the mistakes that cost the round

Framework interview questions

Under the hood: the building blocks, in data-flow order

Ingestion: how data enters the platform

The incremental-query row hides a race that interviewers like: a transaction that started before the high-water mark was read but commits after it gets an updated_at below the mark and is skipped forever. Log-based CDC reads committed changes in commit order, so it has no such gap. The data modeling guide covers CDC mechanics in detail, including replication-slot retention.

For files and APIs, land the raw bytes first, unchanged, in an object-store prefix keyed by source and arrival time. Parsing comes after. If the parser has a bug, you re-parse from the landing zone instead of asking a partner to resend last month.

The log: Kafka as the system’s buffer and replay store

A durable log decouples producers from consumers in time. Producers write at their rate, consumers read at theirs, and the log holds the difference. Most data platform designs put one in the middle for three reasons: it absorbs bursts, it lets several consumers read the same events independently, and it is the first replay store.

 topic: orders  (3 partitions, replication factor 3)

 producer: key = order_id ──► partition = hash(key) % 3
                               (Kafka's default partitioner uses murmur2)
 partition 0  [o1 o4 o7 ...]  leader broker 1, followers 2, 3
 partition 1  [o2 o5 o8 ...]  leader broker 2, followers 3, 1
 partition 2  [o3 o6 o9 ...]  leader broker 3, followers 1, 2
               offset 0 1 2 ... append-only, ordered within a partition only

 consumer group "lake-writer"        consumer group "fraud"
   consumer A ◄─ partition 0, 1        consumer X ◄─ partition 0
   consumer B ◄─ partition 2           consumer Y ◄─ partition 1
                                       consumer Z ◄─ partition 2
 each group tracks its own committed offsets in __consumer_offsets

Read it from the top. The producer hashes the message key to pick a partition, so every event for o1 lands in partition 0 and stays in order relative to other o1 events. Ordering holds within a partition only; there is no global order across the topic. Each partition has a leader that takes writes and followers that copy it. A consumer group splits the partitions among its members, so a group can have at most as many active consumers as there are partitions, and the extra members sit idle. Each group commits its own offsets, which is why the fraud team and the lake writer can read the same topic at different speeds.

The settings below decide whether an acknowledged event can be lost:

acks=all with min.insync.replicas=2 and RF 3 means an acknowledged write exists on at least two brokers, and the cluster tolerates one broker loss without refusing writes. The cost is write latency: the leader waits for a follower. The idempotent producer attaches a producer id and a sequence number per partition, so a retried batch that the broker already has is discarded rather than appended twice. It removes duplicates caused by producer retries; it does nothing for an application that sends the same business event twice, which is why the contract still carries an event_id.

Choosing the key. The key decides ordering and load. Key by the entity whose events must stay ordered (order_id, account_id, device_id). If one key dominates, such as one huge merchant, its partition becomes hot. The fixes are a composite key (merchant_id plus a bucket) when per-merchant order does not matter, or a dedicated topic for the outlier. Increasing the partition count later changes hash(key) % n, so keys move partitions and per-key ordering breaks across the change. Over-provision partitions up front instead.

Processing: batch, micro-batch and continuous

The micro-batch engine is worth knowing in detail, because its recovery rule is what the demo exercises:

 Spark Structured Streaming, one micro-batch (batch N)

  1. plan    ── read latest Kafka offsets ──► write offsets/N to checkpoint
  2. execute ── read offsets(N-1)..offsets(N) ──► transform ──► sink
  3. commit  ── write commits/N to checkpoint
                ▲
                └─ crash here: offsets/N exists, commits/N does not.
                   On restart, Spark re-runs batch N with the SAME offsets.

The offsets log is written before the batch runs (a write-ahead log) and the commit log after the sink returns. If the process dies between steps 2 and 3, the restart finds an offset entry without a commit entry and re-executes batch N over exactly the same offset range. The engine guarantees the replay is the same input. Whether the output is duplicated depends entirely on the sink. A sink that appends writes the batch twice; a sink that upserts on a key writes the same final state twice, which is the same as writing it once.

Event time and watermarks. For aggregations by event time, the engine has to decide when a window is complete. A watermark is that decision: “no event older than the maximum event time seen minus 10 minutes is expected”. withWatermark("event_ts", "10 minutes") lets Spark finalise windows and drop state behind the watermark. Events older than the watermark are dropped by stateful operators. That is a correctness trade-off you should state: a larger delay keeps more state and publishes later; a smaller one drops more late data. Count the late events you drop, or route them to a correction path, rather than losing them silently.

State. Windowed aggregations, stream-stream joins and streaming deduplication keep state per key. Spark’s default state store provider is HDFSBackedStateStoreProvider, which holds state in executor JVM memory and checkpoints it to files. For large state, switch spark.sql.streaming.stateStore.providerClass to org.apache.spark.sql.execution.streaming.state.RocksDBStateStoreProvider, which keeps it in native RocksDB instances outside the JVM heap. Spark 3.5 also added dropDuplicatesWithinWatermark, which deduplicates on a key while bounding state with the watermark.

Delivery semantics: where exactly-once actually lives

“Exactly-once” is always scoped to a boundary. The mechanisms differ by boundary:

The sentence interviewers want: “Delivery is at-least-once everywhere; I make the effect exactly-once at the sink by upserting on a deterministic key, so a replay rewrites the same rows.”

Storage: lake, warehouse and lakehouse tables

A plain data lake is a directory of Parquet files: cheap, open, and with no transactions. Two writers can interleave, a reader can see half a job’s output, and an update means rewriting a partition by hand. A warehouse gives transactions and fast SQL but stores data in its own format, so every other engine pays to copy it out. An open table format (Iceberg, Hudi, Delta Lake) adds a transaction log over Parquet files in object storage, which is what a lakehouse is.

 Iceberg table commit: an atomic swap of one pointer

 catalog: shop.orders ──► metadata/v12.metadata.json   (current)
                               │ snapshots: [..., s11, s12]
                               ▼
                         snap-s12 manifest list (.avro)
                          ├─► manifest A (.avro) ──► day=2026-10-28/00001.parquet
                          │                      ──► day=2026-10-28/00002.parquet
                          └─► manifest B (.avro) ──► day=2026-10-27/00417.parquet

 writer: write new data files ──► write manifests ──► write v13.metadata.json
         ──► ask catalog: "swap v12 → v13 if current is still v12"
             success: readers see s13 atomically
             conflict: re-read, re-validate, retry (commit.retry.num-retries)

Writers never modify files in place. A commit writes new files and then compare-and-swaps the catalog pointer to a new metadata file. Readers resolve the pointer once and read a consistent snapshot, so they never see a half write. Concurrent writers use optimistic concurrency: the loser of the swap re-reads, checks that its change does not conflict, and retries up to commit.retry.num-retries times (default 4). Because each commit is a metadata file and a manifest, a design that commits every second creates thousands of snapshots and tiny files an hour. That is why streaming into a table format uses micro-batches of tens of seconds or more, plus scheduled compaction and snapshot expiry.

The open table formats comparison goes deeper on how Iceberg, Hudi and Delta Lake differ.

Serving: pick the store by the query

Most designs have two or three serving stores fed from one lakehouse. Say which is the source of truth (usually silver or gold in the lakehouse) and which are disposable projections you can rebuild by replay.

Orchestration and backfills

An orchestrator (Airflow, Dagster, or a managed equivalent) runs batch steps in dependency order, retries them and records what ran. Two properties make a DAG safe to retry and to backfill:

Tasks are idempotent per partition. Each run writes one logical partition, for example order_date = 2026-10-27, with an overwrite or a MERGE, never a blind append. Re-running the same date gives the same result.

Inputs are pinned. A run for 2026-10-27 reads the inputs for that date, not “everything new since the last run”. Then a backfill of last March is the same code with a different date, and it can run in parallel with a separate compute pool so it does not starve the live schedule.

For streaming jobs, the backfill is a replay: a second instance of the job with its own checkpoint reads a historical range (from the lake if the log has expired) and writes through the same idempotent sink.

Data quality and observability

Monitor five signals per dataset, not just job status:

Place the checks as gates. Bronze warns and continues, because blocking raw ingestion loses data. Silver and gold halt the publish step, so consumers keep the last good version instead of reading a bad one. Tie each alert to the dataset’s owner, and record lineage (OpenLineage, a catalog such as DataHub or OpenMetadata) so the alert can say which dashboards are affected.

Architecture patterns in one table

The data modeling guide’s Lambda versus Kappa section walks both architectures step by step.

Building-block interview questions

Hands-on demo: an exactly-once Kafka to Iceberg pipeline

The claim to prove: a replayed micro-batch duplicates rows in an append sink and leaves an idempotent MERGE sink unchanged. The demo also shows two contract rules from step 3: duplicates from producer retries are removed, and a stale event that arrives late does not overwrite newer state.

 produce_orders.py ──► Kafka sd_blog_orders (3 partitions, key = order_id)
                                │  maxOffsetsPerTrigger = 400
                                ▼
                     orders_stream.py  (Structured Streaming, availableNow)
                       foreachBatch(batch_df, batch_id)
                         ├─► append ──► sd.shop.orders_append   (naive)
                         └─► dedup in batch, MERGE on order_id
                                    ──► sd.shop.orders           (idempotent)
                       checkpoint: /tmp/sd_blog/checkpoints/orders
                                   offsets/N  commits/N

Step 1: create the topic

docker exec kafka kafka-topics --bootstrap-server kafka:29092 \
  --create --topic sd_blog_orders --partitions 3 --replication-factor 1

Replication factor 1 is only because the playground runs one broker.

Step 2: the producer

Run 1 publishes 1,000 orders plus 50 exact copies, the duplicates a producer retry creates when an acknowledgement is lost after the broker stored the write. Run 2 publishes 500 new orders, 30 SHIPPED updates for earlier orders, and 10 PENDING events whose updated_ts is older than the stored PLACED version.

"""Publish order events to Kafka for the system design demo.

Run 1 sends 1,000 new orders plus 50 exact duplicates, the way a producer
retry after a lost ack would. Run 2 sends 500 new orders, 30 status updates
for run-1 orders, and 10 stale updates that arrive out of order.
"""
import sys
from datetime import datetime, timedelta

from pyspark.sql import SparkSession

TOPIC = "sd_blog_orders"
BOOTSTRAP = "kafka:29092"
BASE = datetime(2026, 10, 28, 10, 0, 0)


def order(i, status, updated):
    return {
        "event_id": f"o{i:05d}-{status}",
        "order_id": f"o{i:05d}",
        "customer_id": f"c{i % 97:03d}",
        "amount": round(10 + (i * 7.31) % 490, 2),
        "status": status,
        "order_ts": (BASE + timedelta(seconds=i * 2)).isoformat(),
        "updated_ts": updated.isoformat(),
    }


def run_1():
    rows = [order(i, "PLACED", BASE + timedelta(seconds=i * 2))
            for i in range(1, 1001)]
    return rows + rows[:50]                       # retried sends


def run_2():
    rows = [order(i, "PLACED", BASE + timedelta(seconds=i * 2))
            for i in range(1001, 1501)]
    shipped_at = BASE + timedelta(hours=2)
    rows += [order(i, "SHIPPED", shipped_at) for i in range(1, 31)]
    # Older than the PLACED event already stored: must not win.
    rows += [order(i, "PENDING", BASE - timedelta(hours=1))
             for i in range(31, 41)]
    return rows


if __name__ == "__main__":
    spark = SparkSession.builder.appName("sd-produce-orders").getOrCreate()
    rows = run_1() if sys.argv[1] == "1" else run_2()
    df = spark.createDataFrame(rows)
    (df.selectExpr("order_id AS key", "to_json(struct(*)) AS value")
       .write.format("kafka")
       .option("kafka.bootstrap.servers", BOOTSTRAP)
       .option("topic", TOPIC)
       .save())
    print(f"published {len(rows)} events to {TOPIC}")
    spark.stop()

Step 3: the streaming job

Inside foreachBatch, each micro-batch goes to both sinks. Before the MERGE, a window keeps one row per order_id within the batch, the newest by updated_ts. That step is required, not a nicety: a MERGE whose source has two rows for one target row fails, as the last step shows. The MERGE itself only updates when the incoming version is newer, which is what rejects the stale events.

"""Kafka -> Spark Structured Streaming -> Iceberg, with an idempotent sink.

Each micro-batch is written twice: a naive append to orders_append, and a
deduplicating MERGE into orders keyed on order_id. Replaying a batch shows
which of the two sinks survives a crash between write and checkpoint commit.
"""
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
from pyspark.sql.types import (DoubleType, StringType, StructField,
                               StructType, TimestampType)

WAREHOUSE = "file:///tmp/sd_blog/warehouse"
CHECKPOINT = "file:///tmp/sd_blog/checkpoints/orders"

spark = (
    SparkSession.builder.appName("sd-orders-stream")
    .config("spark.sql.extensions",
            "org.apache.iceberg.spark.extensions."
            "IcebergSparkSessionExtensions")
    .config("spark.serializer",
            "org.apache.spark.serializer.KryoSerializer")
    .config("spark.sql.catalog.sd", "org.apache.iceberg.spark.SparkCatalog")
    .config("spark.sql.catalog.sd.type", "hadoop")
    .config("spark.sql.catalog.sd.warehouse", WAREHOUSE)
    .config("spark.sql.shuffle.partitions", "4")
    .getOrCreate()
)

spark.sql("CREATE NAMESPACE IF NOT EXISTS sd.shop")
for table in ("orders", "orders_append"):
    spark.sql(f"""
        CREATE TABLE IF NOT EXISTS sd.shop.{table} (
            order_id STRING, customer_id STRING, amount DOUBLE,
            status STRING, order_ts TIMESTAMP, updated_ts TIMESTAMP,
            kafka_partition INT, kafka_offset BIGINT)
        USING iceberg
        PARTITIONED BY (days(order_ts))""")

schema = StructType([
    StructField("event_id", StringType()),
    StructField("order_id", StringType()),
    StructField("customer_id", StringType()),
    StructField("amount", DoubleType()),
    StructField("status", StringType()),
    StructField("order_ts", TimestampType()),
    StructField("updated_ts", TimestampType()),
])

events = (
    spark.readStream.format("kafka")
    .option("kafka.bootstrap.servers", "kafka:29092")
    .option("subscribe", "sd_blog_orders")
    .option("startingOffsets", "earliest")
    .option("maxOffsetsPerTrigger", "400")
    .load()
    .select(F.from_json(F.col("value").cast("string"), schema).alias("e"),
            F.col("partition").alias("kafka_partition"),
            F.col("offset").alias("kafka_offset"))
    .select("e.*", "kafka_partition", "kafka_offset")
    .drop("event_id")
)


def write_batch(batch_df, batch_id):
    batch_df.persist()
    batch_df.writeTo("sd.shop.orders_append").append()

    # Keep the newest version of each order inside the batch, so MERGE
    # never sees two source rows for one target row.
    latest = Window.partitionBy("order_id").orderBy(
        F.col("updated_ts").desc(), F.col("kafka_offset").desc())
    (batch_df.withColumn("rn", F.row_number().over(latest))
        .where("rn = 1").drop("rn")
        .createOrReplaceTempView("batch_orders"))
    batch_df.sparkSession.sql("""
        MERGE INTO sd.shop.orders t
        USING batch_orders s
        ON t.order_id = s.order_id
        WHEN MATCHED AND s.updated_ts > t.updated_ts THEN UPDATE SET *
        WHEN NOT MATCHED THEN INSERT *""")
    print(f"batch {batch_id}: {batch_df.count()} events")
    batch_df.unpersist()


query = (
    events.writeStream
    .foreachBatch(write_batch)
    .option("checkpointLocation", CHECKPOINT)
    .trigger(availableNow=True)
    .start()
)
query.awaitTermination()

for table in ("orders", "orders_append"):
    # foreachBatch writes through a cloned session with its own catalog
    # cache; without REFRESH this session still sees the empty table.
    spark.sql(f"REFRESH TABLE sd.shop.{table}")
    n = spark.table(f"sd.shop.{table}").count()
    print(f"{table}: {n} rows")
spark.sql("""SELECT status, count(*) AS orders FROM sd.shop.orders
             GROUP BY status ORDER BY status""").show()
spark.stop()

Step 4: run, crash, replay

Copy both scripts into the container and publish run 1:

docker cp produce_orders.py spark-master:/tmp/
docker cp orders_stream.py spark-master:/tmp/
docker exec spark-master bash -c '$SPARK_HOME/bin/spark-submit \
  --master "local[2]" \
  --conf spark.eventLog.enabled=false --conf spark.ui.enabled=false \
  /tmp/produce_orders.py 1'

published 1050 events to sd_blog_orders

Run the streaming job:

docker exec spark-master bash -c '$SPARK_HOME/bin/spark-submit \
  --master "local[2]" \
  --conf spark.eventLog.enabled=false --conf spark.ui.enabled=false \
  --jars $(ls $ICEBERG_HOME/iceberg-spark-runtime*.jar) \
  /tmp/orders_stream.py'

batch 0: 398 events
batch 1: 398 events
batch 2: 254 events
orders: 1000 rows
orders_append: 1050 rows
+------+------+
|status|orders|
+------+------+
|PLACED|  1000|
+------+------+

maxOffsetsPerTrigger = 400 split the 1,050 events into three batches. The append table holds all 1,050 events, including the 50 duplicates. The MERGE table holds 1,000 orders, one per order_id.

Now simulate a crash after the sink wrote batch 2 but before Spark recorded the commit. Delete the commit entry (and its checksum file, or the next commit fails with FileAlreadyExistsException ... .2.crc already exists) and run the same job again with no new data:

docker exec spark-master rm /tmp/sd_blog/checkpoints/orders/commits/2 \
  /tmp/sd_blog/checkpoints/orders/commits/.2.crc
docker exec spark-master bash -c '$SPARK_HOME/bin/spark-submit \
  --master "local[2]" \
  --conf spark.eventLog.enabled=false --conf spark.ui.enabled=false \
  --jars $(ls $ICEBERG_HOME/iceberg-spark-runtime*.jar) \
  /tmp/orders_stream.py'

batch 2: 254 events
orders: 1000 rows
orders_append: 1304 rows
+------+------+
|status|orders|
+------+------+
|PLACED|  1000|
+------+------+

Spark found offsets/2 without commits/2 and re-ran batch 2 over the same 254 events. The append table grew by exactly 254 rows, to 1,304, while the source still holds 1,050 events. The MERGE table did not change.

Finally, publish run 2 and process it:

docker exec spark-master bash -c '$SPARK_HOME/bin/spark-submit \
  --master "local[2]" \
  --conf spark.eventLog.enabled=false --conf spark.ui.enabled=false \
  /tmp/produce_orders.py 2'
docker exec spark-master bash -c '$SPARK_HOME/bin/spark-submit \
  --master "local[2]" \
  --conf spark.eventLog.enabled=false --conf spark.ui.enabled=false \
  --jars $(ls $ICEBERG_HOME/iceberg-spark-runtime*.jar) \
  /tmp/orders_stream.py'

batch 3: 399 events
batch 4: 141 events
orders: 1500 rows
orders_append: 1844 rows
+-------+------+
| status|orders|
+-------+------+
| PLACED|  1470|
|SHIPPED|    30|
+-------+------+

The 540 new events became batches 3 and 4. The MERGE table now has 1,500 orders: 30 moved to SHIPPED, and the 10 stale PENDING events were rejected by s.updated_ts > t.updated_ts, so no order shows PENDING. The append table holds 1,844 rows for 1,590 published events: the 254-row replay, plus every duplicate and every superseded version.

Step 5: why the in-batch dedup matters

Remove the window and feed a MERGE two copies of one key, and Spark refuses the whole statement:

"""Show what MERGE does when one micro-batch carries the same key twice."""
from pyspark.sql import SparkSession

spark = (
    SparkSession.builder.appName("sd-merge-dup")
    .config("spark.sql.extensions",
            "org.apache.iceberg.spark.extensions."
            "IcebergSparkSessionExtensions")
    .config("spark.serializer",
            "org.apache.spark.serializer.KryoSerializer")
    .config("spark.sql.catalog.sd", "org.apache.iceberg.spark.SparkCatalog")
    .config("spark.sql.catalog.sd.type", "hadoop")
    .config("spark.sql.catalog.sd.warehouse", "file:///tmp/sd_blog/warehouse")
    .getOrCreate()
)
spark.sql("CREATE NAMESPACE IF NOT EXISTS sd.shop")
spark.sql("DROP TABLE IF EXISTS sd.shop.merge_dup PURGE")
spark.sql("CREATE TABLE sd.shop.merge_dup (order_id STRING, status STRING) "
          "USING iceberg")
spark.sql("INSERT INTO sd.shop.merge_dup VALUES ('o00001', 'PLACED')")
spark.createDataFrame(
    [("o00001", "SHIPPED"), ("o00001", "SHIPPED")],
    "order_id STRING, status STRING",
).createOrReplaceTempView("retried_batch")
try:
    spark.sql("""
        MERGE INTO sd.shop.merge_dup t USING retried_batch s
        ON t.order_id = s.order_id
        WHEN MATCHED THEN UPDATE SET *
        WHEN NOT MATCHED THEN INSERT *""")
except Exception as err:                     # the failure is the point
    code = "MERGE_CARDINALITY_VIOLATION"
    print(f"MERGE failed: {code}" if code in str(err) else err)
spark.sql("DROP TABLE sd.shop.merge_dup PURGE")
spark.stop()

MERGE failed: MERGE_CARDINALITY_VIOLATION

The full error is SparkRuntimeException: [MERGE_CARDINALITY_VIOLATION] The ON search condition of the MERGE statement matched a single row from the target table with multiple rows of the source table. In a streaming job this fails the batch, and every restart replays the same batch and fails again, so the query is stuck until the code changes. Deduplicate inside each batch before merging.

What the demo proves, and a pitfall it found

Replay is the engine’s job; idempotency is yours. Spark re-ran batch 2 with identical input, as its checkpoint protocol promises. Only the sink design decided whether that produced duplicates.

The catalog cache pitfall. The first version of orders_stream.py counted the tables at the end without REFRESH TABLE and printed 0 rows for both, although the data was there. Structured Streaming runs each query in a cloned SparkSession, and foreachBatch hands you a DataFrame bound to that clone, whose Iceberg catalog has its own table cache. The driver session still held the table it loaded at creation time, before any batch committed. REFRESH TABLE invalidates the cached entry. A monitoring job that reads row counts from a long-lived session can report stale numbers in the same way.

Clean up

docker exec spark-master rm -rf /tmp/sd_blog
docker exec kafka kafka-topics --bootstrap-server kafka:29092 \
  --delete --topic sd_blog_orders

Demo interview questions

Real-world scenarios: ten worked designs

Each design follows the framework in compressed form: assumptions, an estimate, the flow, the decisions that matter, and the failure modes an interviewer will push on. The numbers are assumptions to state, not facts about any company.

Design 1: clickstream analytics for a large website

Prompt. Collect page views and clicks from web and mobile, power product dashboards within five minutes, and keep history for analysis.

Assumptions and estimate. 10 TB a day of JSON events at about 1 KB each is 10 billion events a day, about 116,000 events a second on average and roughly 350,000 at a 3x peak, around 350 MB/s.

 web / mobile SDK ──► collector API (stateless, autoscaled, batches events)
                          │ key = session_id or user_id
                          ▼
                 Kafka clicks.raw ── 7 days ──► stream job:
                          │                      parse, validate, enrich
                          │                      (geo, user agent), dedup
                          ▼                      on event_id
                 bronze (Iceberg, raw JSON + parsed) ──► silver sessions
                 partitioned by event hour               (30-min gap)
                          │                                   │
                          ▼                                   ▼
                 gold: hourly metrics ──────────────► OLAP store (Pinot /
                 per page, funnel, cohort              ClickHouse) for
                                                       5-minute dashboards

Key decisions. The collector batches events and returns quickly; mobile SDKs buffer offline, so expect events hours or days late and partition bronze by event hour while accepting writes to old partitions. Sessionisation is a stateful operation keyed by user with an inactivity gap, typically 30 minutes; run it in the stream with a watermark, or hourly in batch if five-minute freshness only applies to page-level counts. Bot traffic is filtered in silver with a flag, not dropped, so the rule can change later.

Failure modes. A viral page makes one key hot if you key by page; key by session instead. A mobile release that floods duplicate events is absorbed by event_id dedup. Identity resolution (anonymous id to logged-in user) changes history, so keep it as a mapping table joined at read time rather than rewriting events.

Design 2: CDC from an OLTP database to the lakehouse in under five minutes

Prompt. Replicate 300 PostgreSQL tables into the lakehouse with under five minutes of lag, including deletes, for analytics.

 PostgreSQL ── logical replication slot ──► Debezium (Kafka Connect)
     │  snapshot first, then WAL changes          │ one topic per table
     │                                            │ key = primary key
     ▼                                            ▼
 outbox / heartbeat table               Kafka + Schema Registry (Avro)
                                                  │
                                                  ▼
                                stream job per table group, 1-min trigger
                                  bronze: append change events (op, lsn)
                                  silver: MERGE on PK, ordered by lsn;
                                          op = 'd' ──► DELETE

Key decisions. Debezium takes a consistent snapshot, then streams from the replication slot, so the lake starts from a known point. Order changes for a row by the source log position (the LSN for PostgreSQL), not by arrival time. Keep bronze append-only with every change event; silver is the MERGE result. Schema changes go through the registry with backward compatibility, and a column drop at the source becomes a nullable column in the lake rather than a failure. Merge-on-read tables suit high-update tables; compact them on a schedule.

Failure modes. If Debezium stops, the replication slot keeps WAL on the primary until the disk fills; cap it with max_slot_wal_keep_size and alert on slot lag. A large table’s initial snapshot can take hours; incremental snapshots let streaming continue during it. Reconcile row counts per table per hour against the source.

Design 3: a real-time operations dashboard with a 10-second SLA

Prompt. Show orders per minute, revenue and error rates per region with data at most 10 seconds old.

Key decisions. Ten seconds rules out lakehouse commits as the serving path, so the stream job aggregates in tumbling or sliding windows and writes the results to a low-latency store: an OLAP engine that ingests from Kafka directly (Pinot, Druid, ClickHouse with a Kafka engine) or a key-value store holding the current window counts with a TTL. The same events still land in the lakehouse for history and for correcting late data. The dashboard reads the fast store; anything older than a day reads gold.

Failure modes. Late events update a window already shown; decide whether the dashboard corrects old buckets or rolls forward, and say which. A burst that causes consumer lag makes the dashboard stale silently; show the data’s age on the dashboard itself.

Design 4: log aggregation for 10,000 servers

Assumptions and estimate. 10,000 servers at 5,000 lines a second each is 50 million lines a second; at 300 bytes a line that is 15 GB/s, about 1.3 PB a day before compression. The estimate is the design: at this volume, sampling and tiering are not optional.

 agents (Fluent Bit / Vector) ── batch, compress, local buffer on disk
          │  drop DEBUG at source; sample INFO; keep WARN+ at 100%
          ▼
 Kafka (per-service topics) ──► stream: parse, add metadata, route
          │                        ├─► search cluster: 7 days hot
          │                        └─► object storage: Parquet, zstd,
          │                             partitioned by service/hour,
          ▼                             queried with Trino; 90 days
 metrics extracted from logs ──► time-series store for alerting

Key decisions. Agents buffer to local disk so a Kafka outage does not lose logs or block applications. Filtering and sampling happen at the edge, because shipping and then dropping costs network and broker disk. Hot search holds days, the lake holds months, and retention is set per log level and service.

Failure modes. A noisy service floods the pipeline; enforce per-service quotas in Kafka. A parsing change breaks a format; keep the raw line in the lake so it can be re-parsed.

Design 5: a fraud feature pipeline with a 100 ms decision

Prompt. Score card transactions at 5,000 a second with features such as “transactions for this card in the last 10 minutes” and “distance from the last transaction”.

 payment API ──► Kafka txns (key = card_id) ──► Flink keyed state
                                                  per card: counts in
                                                  sliding windows, last
                                                  location, amount stats
                                                        │ upsert features
                                                        ▼
 payment API ── sync call ──► scoring service ──► online feature store
   (decision < 100 ms)          │ model             (Redis / DynamoDB,
                                │                    single-digit ms reads)
                                ▼
                     decision event ──► Kafka ──► lakehouse (training data,
                                                   point-in-time correct)

Key decisions. The decision path is synchronous and reads precomputed features; nothing heavy runs inside the 100 ms. Features are computed by a stateful stream keyed by card, so all of one card’s events go to one task. The same feature definitions feed the offline store with point-in-time joins, so training sees what the model would have seen at decision time; mismatched logic between the two is training-serving skew.

Failure modes. If the feature store is slow, the scoring service needs a fallback rule set and a timeout, decided with the business: allow, block, or review. Feature freshness lag shows up as missed fraud, so alert on it.

Design 6: IoT telemetry from one million devices

Assumptions and estimate. One million devices reporting every 5 seconds is 200,000 messages a second; at 200 bytes each, 40 MB/s.

Key decisions. Devices speak MQTT to a broker built for many long-lived connections, which bridges to Kafka keyed by device_id. The stream computes per-device rolling statistics and threshold alerts with keyed state (RocksDB for a million keys). Recent raw data goes to a time-series store with downsampling; everything lands in the lakehouse partitioned by date, sorted or clustered by device for per-device queries.

Failure modes. Devices reconnect after an outage and send hours of buffered data at once, so the watermark must tolerate it or the late data must go through a batch path. Device clocks drift; record both device time and ingestion time.

Design 7: a multi-tenant file ingestion service

Prompt. Customers upload CSV files of up to 5 GB; parse, validate, store and report errors back to them.

 customer ──► pre-signed upload URL ──► s3://landing/<tenant>/<upload_id>/
                                               │ object-created event
                                               ▼
                                      queue ──► validation job (per file)
                                       │   schema + row rules per tenant
                                       ├─► valid rows ──► bronze, tagged
                                       │                 tenant, upload_id
                                       └─► rejected rows ─► errors table +
                                                            report to tenant
 status table: upload_id, state (RECEIVED → VALIDATING → LOADED | FAILED)

Key decisions. Uploads go straight to object storage through pre-signed URLs, so the API never carries file bytes. Processing is keyed by upload_id, which makes a re-run of the same file a replace, not an append. Each tenant gets quotas and a separate prefix, and tenant id is a column in every table for isolation and deletion.

Failure modes. A malformed 5 GB file should fail fast on a sampled header check, not after an hour. One large tenant must not starve others; use per-tenant concurrency limits.

Design 8: an e-commerce warehouse for daily reporting

Prompt. 2 million orders a day across web and stores; finance, marketing and operations dashboards refreshed daily, with history kept as it was.

Key decisions. This is a batch design, and saying so early is a strength: 2 million orders a day is about 23 a second. Ingest with CDC or daily extracts into bronze, build silver with dbt or Spark, and model gold as a star schema: fact_order_line at one row per order line, conformed dim_customer and dim_product with SCD Type 2 history, and a date dimension. Partition the facts by order date. Separate compute for loading and for BI queries so a heavy dashboard does not delay the load. The data modeling guide covers this model in full.

Failure modes. Returns and cancellations arrive days later and change past totals; decide whether gold restates history or records adjustments. A fact joined to the current dimension version instead of the version valid at order time rewrites history.

Design 9: a data quality platform for 500 tables

Key decisions. Checks are declared next to the data, as YAML or dbt tests in version control, owned by the dataset owner. The orchestrator runs them as a stage after each load. Gold checks block publishing; bronze checks warn. Results go to a metrics store with history, so the platform can detect drift (row counts, null rates) against the past instead of fixed thresholds alone. Lineage connects a failed check to downstream consumers, and the catalog shows each table’s health.

Failure modes. Alert fatigue: hundreds of low-value checks get ignored. Start with freshness, volume and key uniqueness on the most-used tables, and add checks after incidents.

Design 10: GDPR right to erasure across the lake

Prompt. Delete a user’s personal data within 30 days of a request, across Kafka, the lakehouse, backups and derived tables.

Key decisions. Keep PII in as few tables as possible, keyed by a user id, and refer to users elsewhere by a pseudonymous key. Then erasure is a delete in those tables plus a rewrite of affected files: a table format DELETE writes new files or delete files, and only snapshot expiry and orphan-file cleanup physically remove the old bytes. Old snapshots still contain the data until they expire, so the expiry schedule is part of the compliance window. Kafka retention shorter than the erasure window avoids rewriting topics. An alternative is crypto-shredding: encrypt each user’s PII with a per-user key and delete the key.

Failure modes. Time travel and backups resurrect deleted data; document how long each retains it. Derived aggregates that cannot identify a person can stay; derived tables with user-level rows must be included.

Production failures: where designs break

Scenario interview questions

Optimisation, tuning knobs and best practices

Partitioning and skew

Partition storage by the column most queries filter on, usually a date or hour of event time, and keep partitions large enough to hold files of hundreds of megabytes. Over-partitioning (by date and user id, say) creates millions of tiny files. Within partitions, sort or cluster by a second frequent filter so file-level min and max statistics can skip files.

For skewed joins, Spark’s adaptive query execution splits oversized shuffle partitions. It is on by default (spark.sql.adaptive.enabled = true, spark.sql.adaptive.skewJoin.enabled = true). A partition is treated as skewed when it is larger than spark.sql.adaptive.skewJoin.skewedPartitionFactor (5.0) times the median and larger than spark.sql.adaptive.skewJoin.skewedPartitionThresholdInBytes (256 MB). When the skew is a known hot key, salting it or handling it in a separate branch is more predictable. The data skew post covers both.

Backpressure and rate limits

When consumers fall behind, the log absorbs it, and consumer lag is the signal. Act on it in order: scale the bottleneck stage, bound the batch size so a backlog does not create one enormous batch (maxOffsetsPerTrigger for Spark’s Kafka source), and shed or defer low-priority work. A backlog larger than retention is data loss, so alert on lag as time (“40 minutes behind”) against the retention window, not as a message count.

Small files and table maintenance

Each streaming commit adds files and a snapshot. Schedule maintenance per table:

Iceberg’s default snapshot age for expiry is 5 days (history.expire.max-snapshot-age-ms = 432000000), which also bounds how far back time travel works after expiry runs.

Cost

Name the cost drivers with the design, because the interviewer will ask:

Tuning checklist

Tuning interview questions

Summary and key takeaways

A data system design answer is judged on what happens after the happy path: the restart, the replay, the late event, the schema change, the bill. The framework puts those questions in the order an interviewer expects. Consumer and decision first, then numbers, then the contract, then the diagram, then recovery, operations and trade-offs.

The mental model to carry into the room:

• The log and the lake are your replay stores. Know how far back each goes, and design every sink so that replaying into it is safe.
• Delivery is at-least-once; effects are made exactly-once at the sink. The demo showed it: Spark replayed batch 2 with identical input, the append table grew from 1,050 to 1,304 rows, and the MERGE table stayed at 1,000.
• Event time orders versions; arrival time does not. A version guard in the MERGE rejected all 10 stale events.
• Commits are the unit of cost in a lakehouse. Batch them, compact them, and expire them.
• Quality is monitored on data. Freshness, volume, schema, validity and reconciliation, with owners and lineage.

Practise each worked design out loud against a 40-minute timer. For each one, say the binding requirement, the estimate, the replay story and one rejected alternative. If you can do that for all ten, the specific prompt matters much less.

References

• DataVidhya: data engineering system design interview questions for the question bank and pattern list this guide builds on
• PracHub: data engineering system design interview guide for the seven-step framework and scoring dimensions
• Apache Kafka documentation: design for partitions, replication, the idempotent producer and transactions
• Apache Kafka producer configs, topic configs and consumer configs for acks, enable.idempotence, retention.ms, min.insync.replicas, max.poll.interval.ms and isolation.level
• KIP-679: producer enables the strongest delivery guarantee by default for the Kafka 3.0 defaults
• Spark 3.5.9 Structured Streaming programming guide for checkpoints, foreachBatch, watermarks, dropDuplicatesWithinWatermark and the RocksDB state store
• Spark 3.5.9 Structured Streaming and Kafka integration for maxOffsetsPerTrigger and source options
• Spark 3.5.9 SQL performance tuning for adaptive query execution and skew join settings
• Iceberg 1.11.0 Spark writes, maintenance, procedures and table properties for MERGE INTO, compaction, snapshot expiry and commit retries
• Iceberg table spec for metadata files, manifest lists, manifests and atomic commits
• Delta Lake: idempotent writes in foreachBatch for txnAppId and txnVersion
• Apache Flink: fault tolerance guarantees of data sources and sinks and the Kafka connector for checkpoint-based exactly-once sinks
• Debezium PostgreSQL connector for snapshots, replication slots and change event structure
• PostgreSQL replication settings for max_slot_wal_keep_size
• Questioning the Lambda Architecture for the Kappa alternative
• The world beyond batch: Streaming 101 for event time, processing time and watermarks
• OpenLineage for lineage metadata
• Run every demo on this blog locally for the public Spark and lakehouse playground setup

Trademarks

Apache Spark, Apache Kafka, Apache Iceberg, Apache Hudi, Apache Parquet, Apache Flink, Apache Pinot, Apache Druid, Apache Airflow and Apache are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries. Delta Lake is a trademark of LF Projects, LLC. Debezium is a trademark of Red Hat, Inc. PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada. ClickHouse is a registered trademark of ClickHouse, Inc. Redis is a registered trademark of Redis Ltd. Elasticsearch is a trademark of Elasticsearch B.V. Other names are the property of their respective owners. Logos are shown unmodified to identify each project and do not imply endorsement.