The Design Session

The method, in one screen

Everything in this guide collapses into six moves you run in order on any scenario. The point of the method isn't the diagram at the end — it's that every box you draw and every choice you make traces back to a requirement you established in the first eight minutes. Keep this on the inside of your eyelids:

The scenario & clarifying Q&A

Here is the prompt, delivered exactly as an interviewer would:

Do not draw yet. The prompt is deliberately underspecified, and the first thing scored is whether you close the gaps before committing. Ask five or six sharp questions, then reflect the answers back as the requirements you'll design against. Below is the exchange, with plausible answers — treat the answers as the frozen spec for the rest of the chapter.

Now close the framing by reading the requirements back — this is the sentence that tells the interviewer you listened:

That paragraph already contains the thesis of the whole design: tiered freshness (so mostly batch, one streaming path), a split correctness bar (so CDC gets a reconciliation story), and a small team (so buy-heavy). Everything downstream is just making those three commitments concrete. See Ch 02 for the full requirements taxonomy.

Back-of-envelope sizing

Two minutes of arithmetic on the board. The goal isn't precision — it's an order of magnitude that tells you whether this is a laptop problem or a distributed-systems problem, and roughly what it costs. Do it out loud.

CLICKSTREAM
  50,000,000 events/day x 1 KB          = 50 GB/day raw (JSON)
  peak factor ~4x avg  => avg 580/s, peak ~2,300 events/s
  50 GB/day x 365 x 2yr retention        = ~36 TB raw over 2 years
  columnar + compression (Parquet ~5-8x) => ~5-7 TB stored
  growth 3x/yr => ~150 GB/day and ~7,000 events/s peak within a year

POSTGRES CDC
  ~few thousand writes/s peak; change events ~1 KB each
  say ~2,000 changes/s peak => ~170M changes/day worst case
  but most are small; realistic steady state hundreds/s
  initial snapshot: low hundreds of GB, one-time

READ SIDE
  BI: tens of analysts, dashboards refresh hourly/daily -> low QPS, big scans
  ad-hoc: bursty, unpredictable, wants cheap separated compute
  fraud features: online lookups at scoring time -> needs a low-latency store,
    NOT the warehouse (point reads, single-digit ms)

ROUGH COST (order of magnitude, managed cloud)
  object storage ~5-7 TB @ ~$23/TB/mo      => ~$150/mo  (cheap; storage is not the problem)
  warehouse compute (elastic, ~hours/day)  => the real bill; $1-5k/mo range early
  streaming backbone (managed Kafka/Kinesis)=> $ hundreds/mo at this volume
  => compute dominates. Design to keep scans cheap and streaming narrow.

The architecture on the board

Now draw — left to right, boxes are systems, arrows are data labeled with a cadence and a shape, orchestration and governance as bands underneath, and a small ◆ wherever a real decision lives. This is the diagram you'd reproduce on any whiteboard; talk through it as you build it, don't reveal it finished.

  SOURCES              INGESTION                 STORAGE (lakehouse)        TRANSFORM              SERVING
 ------------      --------------------      ----------------------     --------------      ---------------------

 [Clickstream] --stream 50M/day,~2k/s peak-->[  Kafka / Kinesis  ]
   SDK/events                                 [   (log backbone)  ]--+
                                                      |             |
                          ◆ stream vs batch           | (a) sink   | (b) branch for
                                                       v            v    real-time
                                              +-----------------+  [ Flink /        ]  ~seconds
                                              |  RAW / BRONZE    |  [ streaming job  ]------+
                                              |  object store    |   fraud features        |
 [ Postgres  ] --Debezium CDC, log-based----->|  (Iceberg tables)|                         v
   OLTP app      before/after + op + LSN      |  append-only     |                  [ FEATURE STORE ]
   ◆ CDC method  ~hundreds/s                   +--------+--------+                   [ online: KV/Redis]--> fraud
   (log vs batch)                                       |                            [ offline: table  ]     model
       |  initial snapshot (one-time) --------->        | MERGE / upsert                     ^   (in-session)
       |                                                v    (Ch 05 pipelines)                |
       |                                       +-----------------+     +--------------+       | batch
       +-- reconcile to source of record ----->|  SILVER (clean, |---->|  dbt models  |-------+ features
                    (revenue, to the penny)     |  deduped, typed)|     |  ELT / tests |
                                                +-----------------+     +------+-------+
                                                         ◆ lake vs                |
                                                    warehouse vs lakehouse        v
                                                                          +----------------+   SLA: hourly/daily
                                                                          | GOLD / MARTS   |-->[ BI dashboards ]
                                                                          | (warehouse/    |-->[ ad-hoc SQL    ]
                                                                          |  lakehouse SQL)|-->[ reverse ETL   ]--> SaaS
                                                                          +----------------+
 ================================================================================================================
  ORCHESTRATION  (Airflow / Dagster)  — schedules batch, backfills, triggers dbt, watches freshness   [Ch 03 pipes]
  CATALOG + GOVERNANCE  — lineage, schema registry, PII tagging, access control, GDPR deletion         [Ch 09]
 ================================================================================================================
  SLAs:  fraud features  ~seconds   |   BI / ad-hoc  hourly-daily   |   ML training features  daily batch
  ◆ = a decision point defended in the next section

Read it back in one breath so the interviewer sees the shape before the detail: "Two sources land in one raw lakehouse zone — clickstream through a log backbone, Postgres through log-based CDC. Raw is append-only and immutable; a MERGE step turns the CDC change log into current-state silver tables; dbt builds gold marts for BI and ad-hoc. One narrow streaming branch peels off the clickstream for fraud features into an online store. Orchestration and governance run underneath everything. There are four decisions on the board — the diamonds — and I've defended each against a requirement." Then stop and invite the pull: "Where do you want to dig in?"

The decisions, defended

This is where most of the score lives. Four named forks, each carrying the same shape: name the requirement, make the call, state the honest cost, and give the condition that would flip it. Here is the master matrix I'd sketch, then the narration for each row.

CDC: log-based, snapshot-plus-stream

"Postgres feeds the audited revenue path, so I need complete, ordered change capture including deletes — a batch updated_at high-water-mark misses hard deletes and any row that changed twice between polls, and it scans the source on a schedule. So log-based CDC: Debezium reading the Postgres WAL via a replication slot, emitting before/after images with an op type and an LSN. It's low-impact on the source and gives me ordering for free." The mechanics: an initial snapshot to seed current state, then the stream picks up at the snapshot's LSN so there's no gap or double-count at the handoff. Land the change events append-only in bronze, then MERGE into current-state silver tables — I won't re-teach the merge here, that's Ch 05 of the pipelines guide. The honest cost: a replication slot that, if a consumer stalls, holds WAL and can fill the source disk — so I monitor slot lag and alert on it. Full treatment in Ch 04.

Batch vs streaming: earn the stream

"Only fraud needs seconds, so only fraud gets streaming. I peel a branch off the same clickstream backbone into a Flink job that computes windowed features — events-per-device-per-minute, velocity, geo-distance — and writes them to an online feature store (a low-latency KV store) the model reads at scoring time. Everything else — BI, ad-hoc, ML training features — is batch, orchestrated hourly/daily, because it meets the SLA at a fraction of the cost and complexity. Reaching for streaming across the board would pay latency's tax on data nobody needs fast." Event-time vs processing-time and watermarks matter for the fraud windows — the deep version is Ch 04 of the pipelines guide; here I just flag that late events get a bounded watermark and the offline feature table is the reconciled source of truth for training. Full trade-off: Ch 05.

Storage: lakehouse, not a bare choice between lake and warehouse

"I want cheap raw retention at 36 TB, ACID and schema evolution on that raw layer, and good BI SQL — that's exactly what a lakehouse buys. I'd land data as Iceberg tables on object storage in a medallion layout: bronze (raw/immutable, including the CDC change log), silver (cleaned, deduped, typed, current-state), gold (dimensional marts). BI and ad-hoc query gold via a warehouse-grade engine; the ML team reads silver/gold directly on the open format without a second copy. A warehouse-only design makes raw expensive and puts ML behind an export; a bare lake gives me no ACID and a swamp. Iceberg specifically for engine-neutrality — Trino, Spark, Snowflake, and Databricks can all read it, which is my lock-in hedge." Iceberg vs Delta vs Hudi and the columnar/partitioning detail live in Ch 06.

Build vs buy: buy the commodity, build the glue

"Six engineers and no SRE team is the loudest requirement in the room. Self-hosting Kafka + Debezium + a query engine is a full-time platform job I don't have the headcount for, so I buy the commodity layers and build only what's differentiating." Component by component:

"The heuristic is buy to start, build when the bill or the constraint justifies it. At 3× growth I'd re-run this table annually — the first thing likely to flip is the SaaS CDC bill (Fivetran prices on rows), at which point self-hosted Debezium on the managed Kafka I already run becomes cheaper than the connector." Full framework and scorecard: Ch 07.

Scale, failure & cost

The last eight minutes. The interviewer stresses the design — good, this is where staff-level answers separate. Four pulls, ready to go.

Scaling & hotspots

"At 3×/year the clickstream hits ~150 GB/day and ~7k events/s peak within a year. Because storage and compute are separated, the answer is 'add compute,' not 're-architect.' The real risk is a hot partition: if I key Kafka by user_id, a handful of power users or a bot skews one partition and one consumer lags. Mitigations — key by a higher-cardinality field, or salt the hot key, or partition raw tables by event-time + a bucketed hash. On the warehouse side, cluster gold on the columns BI filters (date, tenant) and compact small files — the small-files problem is the classic lakehouse hotspot. Depth in Ch 08."

Schema evolution

"Two schema surfaces. Clickstream events evolve constantly, so producers publish through a schema registry with backward-compatible rules — additive fields only; a breaking change is a new event version, enforced in CI as a data contract. Postgres DDL flows through CDC — Debezium emits schema-change events; Iceberg's schema evolution lets me add columns without rewriting data. The trap is a producer silently dropping a field the fraud model depends on, so the contract test gates the deploy. Modeling + contracts detail: Ch 09."

Failure & DR

"Each stage degrades independently. Kafka retention (say 7 days) is my replay buffer — if a downstream job dies, I reprocess from offset, no data lost. The CDC replication slot is the fragile spot: a stalled consumer holds WAL and threatens the source disk, so slot-lag alerting is non-negotiable, and I cap it with a dead-letter path. Bronze is immutable, so any silver/gold corruption is fixed by a backfill — reprocess from raw rather than restore from backup. For DR: object storage is already multi-AZ and durable; cross-region replication of raw plus infra-as-code gives an RPO near zero for raw (it's replayable) and an RTO of hours to rebuild marts. That's proportionate — I'm not paying for hot multi-region standby on hourly-SLA BI. Delivery-semantics depth: Ch 07 of the pipelines guide."

Cost

"Storage is ~$150/mo — a rounding error. Compute is the bill, so the levers are: separated elastic compute that scales to zero off-hours; incremental dbt models so I reprocess deltas, not full history (Ch 05 of the pipelines guide); partition pruning so BI scans don't full-table-scan 36 TB; and keeping the streaming path narrow — Flink running 24/7 is a fixed cost I only pay because fraud earns it. For governance, chargeback per team surfaces the ad-hoc users running $400 queries. GDPR deletion is a cost and compliance item: Iceberg's row-level deletes let me honor erasure without rewriting whole partitions."

A second prompt: attack outline

You won't always get the platform-greenfield prompt. Rehearse a cold start on a different shape so the method — not a memorized answer — is what carries you. Here's a second prompt and the bulleted attack I'd run in the room. Try covering the bullets and answering it aloud first.

This is deliberately the opposite shape from scenario one: low-to-moderate volume, high source variety, and the dominant cost is integration and maintenance, not throughput. The attack:

• Requirements first. Who consumes (finance, growth, execs)? Freshness — almost certainly daily/hourly is plenty; no near-real-time claim to chase. Correctness: Stripe/finance data must reconcile; marketing attribution can be approximate. Volume is small; variety and connector maintenance are the real problem.
• Estimate — but the number that matters is different. Not events/sec — it's rows synced/month (drives SaaS ELT pricing) and number of connectors × their fragility (drives ops load). Data volume fits a warehouse comfortably; sizing is about cost of integration, not storage.
• Architecture. This is the textbook modern data stack, buy-heavy: managed ELT (Fivetran/Airbyte) → cloud warehouse (Snowflake/BigQuery) → dbt → BI, plus reverse ETL to push modeled data back into the SaaS tools. Schema-on-write into the warehouse; no lake needed at this scale.
• Decisions, defended.
  • Warehouse over lakehouse — volume is small, workload is pure BI/SQL, team wants simplicity; the lake's cheap-raw advantage doesn't pay for itself here (Ch 06).
  • Buy the connectors, emphatically — 30 hand-built, hand-maintained API integrations is a maintenance sinkhole; managed ELT is exactly the commodity to buy (Ch 07). Revisit only for a high-volume source where per-row pricing bites.
  • Batch, full stop — no near-real-time requirement exists; nightly/hourly ELT meets every need (Ch 05).
  • CDC only for the production DBs — SaaS sources come via API connectors; the two app DBs use log-based CDC if they need freshness, batch snapshots if daily is fine (Ch 04).
• The hard parts to name. Entity resolution — the same customer across Salesforce, Stripe, and Zendesk needs a conformed identity in dbt; that modeling is the actual value. Data contracts / freshness monitoring — SaaS schemas change without warning, so connector-drift alerting and contract tests matter (Ch 09). Cost governance — ELT row pricing and warehouse compute can surprise you.
• Evolution. 10× here means more sources, not more volume — so invest in a source-onboarding template and a semantic layer so mart definitions don't fork per team.

Driving the room

The design is only half the job; the other half is running the conversation so the interviewer sees the reasoning. Four habits that turn a solid design into a strong signal:

• Lead the structure, hand over the depth. Get the whole platform on the board fast, then offer forks: "there are real decisions at CDC, at the batch/stream split, and at storage — where do you want to dig?" You control the shape; they choose the pull.
• Defend with the sentence pattern. Every fork: "Given [requirement], I'd choose [X] over [Y] because [fit]; the cost is [downside], acceptable because [context]; I'd revisit if [flip condition]." The flip condition is what reads as senior — it shows you know the boundary of the decision, not just the decision.
• Update on pushback without collapsing. If they say "assume 100× the volume and sub-second everywhere," don't defend the old design — say "then my batch assumption breaks; I'd move the spine to streaming-first and reconsider the warehouse." Changing your mind on new evidence while still committing is the judgment they're testing.
• Never lead with tooling. Requirements, then sizing, then boxes, then names. "I'd stream this because it's modern" fails; "the fraud SLA is seconds, so this one path streams" passes.

✓ Check yourself

Answer from memory before opening each one.

1. The prompt says some use cases need near-real-time data. Which clarifying answer in the walkthrough turns that into a single streaming path, and what would you have built without it?

   Show answer

   Asking which use case needs near-real-time and how near revealed that only the fraud features need seconds, and everything else tolerates hours. Without that, the safe-sounding choice is to stream everything, which pays latency cost and complexity on data nobody reads that fast. See the scenario and clarifying Q&A.

2. A variant of the scenario has 100 million events a day at 2 KB each, kept for one year, with Parquet compression of about 6 to 1. Estimate the average event rate and the stored size. Does storage or compute drive the bill?

   Show answer

   About 1,150 events a second on average, 200 GB a day raw, roughly 73 TB raw over the year, and about 12 TB stored. At object storage prices that is a few hundred dollars a month, so compute is the bill. That argues for separated, elastic compute and pruned scans. See back-of-envelope sizing.

3. The Postgres consumer of the replication slot stalls over a weekend. What happens to the source, what signal should have warned you, and what do you do about it?

   Show answer

   The slot holds back log segments the consumer has not read, so the log accumulates on the source's disk and can fill it. Alert on slot lag, cap what the slot may retain, and restart or re-snapshot the connector. Because bronze is immutable, you can rebuild downstream state afterwards. See scale, failure and cost.

4. The interviewer asks how you know revenue reconciles to the penny. Which three parts of the design answer it?

   Show answer

   CDC gives an ordered, complete change log, so silver is a faithful copy of the source. A scheduled reconciliation compares gold totals with the system of record and alerts on drift. The merge is idempotent on primary key and log position, so replays and out-of-order delivery converge. See the decisions, defended.

5. A second prompt has 30 SaaS sources, modest volume and spreadsheet-driven analysts. Why does the design land on a warehouse rather than the lakehouse from the first prompt, and where is the real work?

   Show answer

   Volume is small, the workload is pure SQL BI, and nothing needs to be fast, so a managed warehouse is simpler and the lake's cheap raw storage does not pay off. The real work is entity resolution across systems, contract tests for drifting SaaS schemas, and cost control for per-row ELT pricing. See a second prompt.

6. In the first scenario, which build-vs-buy call is the first to flip as volume triples each year, and what replaces it?

   Show answer

   The managed CDC connector, because it is priced on rows and the bill grows with volume. Once it exceeds an engineer's loaded cost with margin, self-hosted Debezium on the managed Kafka already in place becomes cheaper. Re-run the table annually. See the decisions, defended.

Takeaway

• Tiered requirements settle the shape: seconds for fraud, hours for everything else, an audited revenue path and a best-effort product path.
• At 50 GB a day storage costs about $150 a month and compute is the bill, so separate storage from compute and keep scans cheap.
• Draw the batch spine completely first. Add the streaming branch as an exception you are justifying.
• Defend each fork with the requirement, the call, the rejected alternative and the condition that flips it.
• Revenue reconciles through ordered CDC, a scheduled check against the source of record, and an idempotent merge on key and log position.
• Failure plans name the signal and the fix: replay from Kafka retention, backfill from immutable bronze, alert on replication slot lag.
• The same method gives opposite designs. A 30-source SaaS consolidation lands on a buy-heavy warehouse stack with no streaming.

The method is clear, so now drill it on questions you answer aloud. → Practice Questions

Further reading

• Replication slots — PostgreSQL documentation. Why an unconsumed slot makes the server retain log files and fill its disk.
• WAL disk space consumption — Debezium documentation. The failure the slot-lag alert guards against, and how to bound it.
• Timely Stream Processing — Apache Flink documentation. Event time and watermarks, behind the fraud feature job.
• Apache Iceberg table spec — Apache Software Foundation. The snapshot and metadata model behind the immutable bronze and backfill story.