Data Engineering System Design

Data Engineering & Modern Data Platforms · Lesson 64 · Production Data Engineering and Capstone

Lesson Overview

The interviewer draws a box labeled "learning progress" and waits. You have forty-five minutes. They want to see how you clarify requirements before naming Kafka, how you size before you decorate, how you separate the Monday board's 06:00 promise from a five-minute tile, and how you talk about failure without hand-waving "we'll make it highly available." TechLearningPro's completion platform is the prompt: ingest learner progress, land it in a lake, publish a mart, serve a board, alert when the contract breaks. This lesson is an architect-level walkthrough of that design interview—not a catalog of every tool in Module 5.

You already priced the board in Data Platform Cost and Performance Optimization, contracted freshness in Data Reliability and SLA/SLO Design, and made prod reviewable in Data Pipeline Deployment and Infrastructure as Code. Here you assemble those decisions under time pressure: clarify, estimate, propose APIs and schemas, draw the path ingest→lake→mart→board+alerts, name failure modes, and show capacity math that survives a follow-up question. No proprietary fake APIs. Use public patterns and the teaching volumes the course has kept fixed: about 40 GB compacted Parquet progress per day, tens of gigabytes in the mart, about forty Monday readers.

Learning Objectives

By the end of this lesson you will be able to:

1. Run a requirements clarification script for a data platform design interview.
2. Separate functional needs (board grain, definitions) from non-functional needs (freshness, cost, security).
3. Estimate volumes, throughput, storage growth, and query concurrency from first principles.
4. Propose an end-to-end architecture: produce → ingest → lake → transform → mart → serve → observe.
5. Define event and table schemas, including definition_version and publish markers.
6. Choose batch versus streaming paths without collapsing them into one cursor.
7. Enumerate failure modes and map them to detection, mitigation, and RTO/RPO.
8. Size orchestration, lake, and serving capacity for the Monday spike and quiet week.
9. Explain trade-offs aloud: one mart, fail-closed gates, IaC, error budgets.
10. Deliver a whiteboard narrative that a staff engineer would accept as complete enough to implement.

Prerequisites

• Lessons 58, 62, and 63 for cost, SLO, and deploy constraints that bound the design.
• Ingestion, orchestration, Spark/Kafka, and lakehouse modules for mechanism depth when the interviewer digs.
• Security and governance lessons for identity and audit as design requirements, not footnotes.
• Modeling lessons for grain clarity—the interview fails early if grain is fuzzy.

Detailed Core Concepts

What interviewers grade

They grade clarity of requirements, correctness of grain, realism of numbers, coherence of the data path, honesty about failures, and trade-off speech. They do not grade whether you picked Redshift or BigQuery first. Naming a warehouse before you know the Monday query and the freshness deadline is a negative signal. Naming ten services for forty readers is a negative signal. Ignoring late data, duplicates, and definition changes is a negative signal.

Clarification is the first design artifact

Before architecture, lock answers to:

1. Who consumes what? Internal Monday board (~40 people)? External learner-facing streak? Finance export?
2. What is the grain? Learner-lesson-day attempt? Session event? Video heartbeat?
3. What is on time? 06:00 yesterday's certified grain? Or five-minute provisional?
4. What is wrong vs late? May we miss the clock to protect correctness?
5. Volumes? Events/day, peak events/sec, payload size, retention.
6. Access control? PII, region, role-based board access.
7. History and backfill? How many years, how often definitions rewrite history.
8. Budget posture? One cloud, one serving engine preference, cost ceiling.

Write assumptions if the interviewer shrugs. Spoken assumptions beat silent ones.

Functional versus non-functional

Functional: capture progress events; publish completion attempts; show board aggregates by lesson; pin definition versions; emit alerts. Non-functional: freshness SLO, query latency, durability of raw, idempotent merge, cost envelope, deployability, auditability. System design interviews for data lean non-functional. The SQL is easy to invent. The clock and the failure story are not.

Capacity math without false precision

Use order-of-magnitude arithmetic and say so.

Teaching baseline:

• ~80M progress-related events/day at peak curriculum push is a possible stress sketch; many days are lower. Prefer deriving from: active learners × lessons touched × events per lesson.
• Compacted Parquet ~40 GB/day for progress facts used in this course's narrative.
• Raw JSON could be ~1–2 TB/day if you kept uncompressed landings—design to compact early.
• Mart tens of GB, daily partitions, selective scans of a few GB for the board.
• 40 concurrent readers Monday 06:00–10:00; handful mid-week.
• Merge window ends with margin before 06:00; query p95 on pruned mart in seconds, not minutes.

Grow storage: 40 GB/day × 365 ≈ 15 TB/year hot Parquet if never tiered. State retention tiers as a decision.

Batch, micro-batch, and stream

Certified board: batch or scheduled micro-batch ending in a daily publish marker is enough and easier to gate. Provisional tile: stream or frequent micro-batch into a separate landing table. Interview answer that merges both into one table fails lesson 62. Interview answer that claims sub-second certified completion for forty internal analysts overbuilds.

APIs and contracts (public patterns only)

Describe interfaces by shape, not by inventing vendor product names that do not exist:

• Producer API: application emits progress events to a topic or HTTPS ingest with schema fields event_id, learner_id, lesson_id, event_time, event_type, payload, schema_version.
• Ingest contract: at-least-once delivery; idempotency via event_id; schema registry or equivalent versioning.
• Lake layout: raw/progress/event_date=.../ then compacted/progress/event_date=.../.
• Publish API: not a public HTTP API—an internal job writes mart tables + ops.publish_status.
• Serve: SQL interface to published.completion_attempt for the BI identity; optional metrics API that only reads aggregates.
• Alert: webhook or PagerDuty-style integration consuming SLI miss events.

Failure modes catalog

Trade-offs you should say out loud

• One certified mart vs dual-engine bake-off leftovers.
• Fail-closed gates vs hitting 06:00.
• Pause capacity vs resume risk.
• Stream everything vs batch certified.
• Multi-region replica vs restore-tested primary.
• Convenience console vs IaC.

Each ties to lessons 58–63. The interview loves that you have a system of decisions, not a pile of tools.

Requirements template you can reuse on any prompt

Copy this onto the corner of the whiteboard:

textCopy
Consumers:
Primary:
Secondary:
Grain:
Certified clock:
Provisional clock:
Correctness vs freshness:
Events/day (assume):
Peak/sec (assume):
Payload size:
Retention raw / mart:
PII:
Regions / residency:
Budget / cloud preference:
Out of scope:

Filling it is the interview. Architecture boxes without it are decoration.

Non-goals are design

Explicit non-goals save you from polite scope creep mid-session: no learner-facing personalized API in v1; no multi-region active-active; no sub-second certified grain; no second warehouse for the same mart; no finance ledger in the same publish without recon SLIs. If the interviewer adds a non-goal back in, renegotiate SLOs and capacity before redrawing.

Consistency model in one paragraph

Raw ingest is append-only and at-least-once. The mart for day D is a deterministic function of raw(D) plus definition_version, published atomically from the reader's point of view via pointer or snapshot isolation the engine provides. Readers may see day D−1 until the marker for D appears—that is freshness, not dirty read of a half merge. Say this when someone asks "is it ACID?" Answer: "publish is atomic to readers; ingest is at-least-once; analytics consistency is definition-scoped, not OLTP serializable across the app database."

Backfill as a first-class load pattern

Design for rebuild of arbitrary day ranges without blocking the critical path: separate queue pool, rate limits, and markers per day. Backfills update historical markers and must invalidate extracts for those dates. Capacity plan includes "redefinition rewrite of 90 days" as a rare but real load—Northline and TechLearningPro both hit this when completion rules change.

Organizational design (often forgotten)

Name owning teams on the diagram: App emits contracts; Platform owns lake/mart/SLOs/deploy; BI owns dashboards and extract invalidation; SRE or platform on-call owns P1 freshness. Interviews at senior level expect ownership edges, not only arrows between S3 and Spark.

Architecture and Design Diagrams

End-to-end happy path:

mermaidCopy
flowchart LR
  APP["Learning app"] --> ING["Ingest topic or pipe"]
  ING --> RAW["Raw lake zone"]
  RAW --> COMP["Compact Parquet"]
  COMP --> XF["Validate transform"]
  XF --> MART["Published mart"]
  MART --> BI["Board / BI"]
  XF --> MARK["Publish marker"]
  MARK --> OBS["SLI monitors"]
  BI --> OBS
  OBS --> ALT["Alert route"]
  ING --> PROV["Provisional landing"]
  PROV --> TILE["Five-minute tile"]

Interview sequencing (what you draw in order):

mermaidCopy
flowchart TD
  Q["Clarify consumers grain clocks"] --> N["Numbers and growth"]
  N --> P["Path ingest lake mart serve"]
  P --> S["Schemas and versioning"]
  S --> F["Failures and SLOs"]
  F --> C["Capacity and cost"]
  C --> D["Deploy and ownership"]

Capacity sketch for Monday:

mermaidCopy
flowchart LR
  subgraph night [Night]
    COMP2["Compact"] --> MERGE["Merge + gates"]
    MERGE --> PUB["Marker ≤ 05:40"]
  end
  subgraph morning [Morning]
    RES["Resume if needed"] --> READ["40 readers"]
  end
  PUB --> RES

Technical Explanation

Running the clarification script (spoken form)

"I'll confirm scope. Primary consumer is an internal completion board for about forty people needing yesterday's certified completion by 06:00 local. Grain is learner-lesson attempt per event_date with a pinned definition_version. Secondary consumer may be a provisional live tile; I'll isolate it. I'll assume ~40 GB/day compacted progress and a mart in tens of GB unless you have other numbers. Correctness gates can burn freshness. One serving mart. Is finance-grade reconciliation in scope or only the board?"

Then shut up and listen. Adjust.

Event schema (illustrative)

jsonCopy
{
  "event_id": "01JBEXAMPLEUUID",
  "learner_id": "L123",
  "lesson_id": "de-64",
  "event_time": "2026-11-10T18:22:11Z",
  "event_type": "lesson_progress",
  "schema_version": 3,
  "properties": {
    "progress_pct": 80,
    "course_id": "data-engineering"
  }
}

Mart grain (illustrative):

sqlCopy
-- published.completion_attempt
-- grain: learner_id, lesson_id, event_date, definition_version
learner_id STRING,
lesson_id STRING,
event_date DATE,
definition_version STRING,
attempted BOOLEAN,
completed BOOLEAN,
last_event_time TIMESTAMP

Marker:

sqlCopy
event_date DATE,
definition_version STRING,
published_at TIMESTAMP,
row_count BIGINT,
pipeline_run_id STRING,
ddl_sha STRING,
degraded BOOLEAN

Ingest design choices

Push to a log (Kafka/PubSub/Kinesis-style): good when many producers, need replay, variable load. Partition by learner_id for session locality or by random for scale—state the skew risk if popular courses hot-key a partition.

HTTPS ingest to object storage: simple, good for batch dumps; watch small files.

CDC from an OLTP progress table: valid if the app already persists progress transactionally; then the log is the database WAL. Still land immutable raw before heavy transforms.

For TechLearningPro teaching narrative, event log → raw lake → compact → transform is the default story. Mention CDC as an alternative if the interviewer says the app is database-centric.

Transform and publish

Nightly (or hourly micro-batch into daily publish): read compacted day D, apply completion definition completion-v4, dedupe by event_id, compute grain, run gates (volume band, null checks, prohibited columns, grain uniqueness), write build or target table, write marker. Idempotent for day D: rerun replaces D safely.

Use Spark/SQL engine as appropriate to volume. At 40 GB compacted/day, a modest batch job suffices; you do not need a heroic cluster. Say that. Over-sizing compute is a cost interview trap.

Serving

Board identity runs certified SQL with event_date predicate and definition_version pin against the pointer view. Warehouse or lakehouse engine per prior cloud choice—one primary. Result cache: do not budget Monday's first refresh as a hit. Isolate ELT pool from board pool. Optional materialized aggregate refreshed after marker, only if refresh fits margin.

Observability and alerts

SLIs from lesson 62: marker freshness, gate results, query success/p95, volume floors. Multi-window burn for pages vs tickets. Lineage: raw object → job run → mart files → marker. On-call runbook links deploy SHA.

Security

Producers authenticate; lake blocks public access; encryption with documented keys; board role read-only on published; merge role write published + marker; no human standing write in prod; audit on vault-class data if any; progress may be sensitive—confirm PII fields and masking for staging.

Capacity plan (talk track)

"Storage: ~15 TB/year Parquet if all hot; tier older raw. Compute: batch job sized to finish by 05:20 with headroom; board capacity for 40 concurrent pruned queries; pause only if resume probe < threshold. Cost: forbid raw JSON board scans; single mart; measure bytes."

Multi-region

Default: single region with versioning + restore drill meeting RTO. Multi-region only with active failover tested and paid for. Do not lead with active-active for this audience size.

Detailed sequence: one day in production

00:00–04:00 local: events continue to land in raw; compaction may run hourly on closed hours if you micro-batch files, but the certified day watermark waits until the day is declared closed. 04:00: watermark for yesterday closes per policy; late events route to late path. 04:00–04:45: compact remaining small files for yesterday; verify file-size metrics. 04:45–05:20: merge/transform into build or target; ELT on isolated capacity. 05:20–05:35: gates—volume band, nulls, grain uniqueness, prohibited columns, definition pin. On fail: no marker, alert, stop. On pass: 05:35–05:40 write marker and advance pointer if needed. 05:40–05:50: resume board capacity if paused; run synthetic certified query probe. 06:00: humans open the board; query latency SLI active; caches may miss first refresh. Mid-morning: on-call watches burn; quiet ELT. Evening: optional compaction for today; no certified publish for "today" until tomorrow's watermark.

Narrating this timeline shows you understand pipeline versus query latency without a lecture.

Storage layout example

textCopy
s3://tlp-progress-lake-prod/
  raw/progress/event_date=2026-11-10/...
  compacted/progress/event_date=2026-11-10/part-*.parquet
  published/completion/definition=completion-v4/event_date=2026-11-10/...
  artifacts/completion/<git-sha>.tgz
  ops/publish_status/event_date=2026-11-10.json

Whether you use Hive paths or Iceberg metadata, the logical zones remain. Published is not a free-for-all dump of raw.

Orchestration DAG shape (logical)

textCopy
close_watermark → compact_day → transform_day → run_gates → write_mart → write_marker → probe_board_query → notify_publish

Parallelism: compact may shard by prefix; gates may run as a single critical section before marker. Downstream notify is best-effort relative to marker truth—the marker is the SLI source, not the Slack message.

What "good enough" CAP tradeoffs mean here

You are not building a globally consistent OLTP store. You choose availability of ingest (accept events even if transform is down) and durability of raw, while certified reads wait for a consistent publish snapshot. Partitioning the product into raw durability versus certified freshness is the real CAP conversation for analytics platforms. Say that clearly if the interviewer invokes CAP by name.

Cost envelope talk track

"At illustrative scan prices from lesson 58, forty readers scanning raw terabytes is hundreds of dollars per meeting; scanning a few gigabytes of mart is dollars. Idle warehouse all month dominates if pause is possible. I keep gates even if they cost minutes. I refuse a second mart. I tier old raw. Unit prices change; the ratios are the design."

Practical Implementation or Design Walkthrough

Problem (interview prompt)

Design a data platform for TechLearningPro learning progress that supports a certified Monday completion board and optional near-real-time tile, including ingestion, storage, processing, serving, SLOs, failure handling, and deployment posture.

Minute 0–5: Clarify and assume

Speak the script. Get confirmation on 06:00, grain, forty readers, correctness over lateness. Note provisional tile as separate. Ask retention (e.g., 2 years hot mart, raw 90 days hot then warm).

Minute 5–10: Numbers

textCopy
Assume 500k MAU learners, 5% daily active on weekdays = 25k DAU
Each DAU emits ~20 progress events → 500k events/day typical
Campaign days 10× → 5M events/day
Avg event 1 KB → ~5 GB raw JSON on busy day (order-of-magnitude)
Compacted columnar progress facts aligned to course teaching: tens of GB; use 40 GB/day as planning ceiling from course narrative
Peak ingest: busy day / 86400 ≈ tens of events/sec average; minute peaks hundreds/sec — size for peaks with buffer
Board: 40 readers × 1–2 QPS short morning burst

Adjust if interviewer gives numbers. Show you can recompute.

Minute 10–20: Draw the path

Draw app → ingest → raw → compact → transform/gates → mart → BI. Draw provisional branch. Draw marker → monitor → alert. Speak ownership boundaries: app owns event quality; platform owns publish contract; BI owns extracts invalidation.

Minute 20–30: Schemas, idempotency, definitions

Detail event_id dedupe, definition_version, pointer cutover for v5, fail-closed gates. Mention late events policy.

Minute 30–40: Failures, SLO, capacity

State freshness 99% by 06:00, error budget, query p95, producer floor. Walk two failures: gate fail (no marker) and cold warehouse (latency SLI). Cost refusals. Deploy: IaC + SHA promote; no clickops.

Minute 40–45: Summary and open questions

"One certified mart, batch publish with marker, isolated provisional tile, SLOs with budget, IaC deploys. Open: exact event rates, PII class, whether finance recon is required."

Reference architecture component list

Label alternatives when asked ("Kafka vs Kinesis vs Pub/Sub—all fine if replay + consumer groups exist").

Sample certified query

sqlCopy
SELECT lesson_id,
       COUNTIF(completed) AS completions,
       COUNT(*) AS attempts
FROM published.completion_attempt
WHERE event_date = DATE '2026-11-10'
  AND definition_version = 'completion-v4'
GROUP BY lesson_id;

Dry-run bytes should be small relative to raw. Say you would attach lesson 58 measurement discipline.

Alert policy sketch for the whiteboard

textCopy
P1: business morning marker missing after 06:00 and not excluded
P1: correctness gate failed after retry (no marker)
P2: slow-window freshness burn elevated
P2: query p95 > SLO three mornings
P3: margin < 10 minutes five days in a row

Enterprise Real-World Scenarios

Interview variant: "Make it real-time"

Interviewer pushes for second-level certified completion. Push back with product: forty analysts at 06:00 do not need second-level certified grain; streaming raises cost and weakens gates. Offer provisional tile at minutes latency and keep daily certified path. If they insist on certified sub-minute, revise SLO, cost, and gate strategy explicitly—windowed completeness is harder; say how watermarking and speculative publishes work and what you will not claim.

Interview variant: "Global learners, multi-region"

Ask where the board operators sit and what RTO is. Propose primary region near producers or near the board, with backup restore. Active-active marts imply dual publish and conflict rules—usually unjustified here. Data residency may force regional raw with aggregated non-PII marts—clarify legal before drawing arrows.

TechLearningPro after a failed interview hire

A candidate drew twenty boxes, no grain, no clock. The hiring bar for this course's alumni is the opposite: fewer boxes, sharper contract. Use that as a teaching foil when you practice aloud.

Northline twist in a panel interview

Finance wants the same platform to close revenue. Add reconciliation SLI, forbid degraded publish, possibly stricter freshness budget, row-level security. Do not silently reuse the internal board's loose degraded mode. Scope expansion changes SLOs first, topology second.

Best Practices

• Clarify consumers, grain, and clocks before tools.
• State assumptions and volumes with units.
• Separate certified and provisional paths.
• Put definition_version and markers in the design, not as "later."
• Fail closed on correctness; budget freshness.
• One serving mart; measure scans.
• Name failure detections with owners.
• Size for Monday spike and quiet week separately.
• Include deploy/IaC and identity in the architecture.
• End with open questions and risks.

Common Mistakes and Anti-Patterns

Tool salad first. Kafka, Flink, three warehouses, mesh—then ask who the user is. Reverse it.

Fuzzy grain. "Progress data" is not a grain. Interviews die here.

One table for two clocks. Certified and live cursors mixed.

100% freshness with no budget. No change policy.

Ignoring duplicates and late data. At-least-once is normal.

Raw JSON for BI. Cost and latency trap.

Multi-region as default wallpaper. Unpriced and untested.

No identity story. Who writes the mart?

No rollback. Pointer cutovers exist for a reason.

Silence on cost. Staff interviewers ask; lesson 58 answers.

Hand-wavy HA. RPO/RTO numbers or admit rebuild-from-raw.

Skipping deploy. If you cannot ship it, it is a sketch, not a design.

Troubleshooting and Design Review

Use this as a self-review after a practice whiteboard:

The interviewer looks bored at minute five. You are listing technologies. Return to requirements.

They ask "how many partitions?" You never estimated volume. Recompute aloud.

They ask "what if the job fails at 05:50?" Missing failure/SLO story. Marker, retry margin, alert, budget.

They ask "why not query OLTP?" Protect OLTP, history, definitioned grain, cost isolation—lake/mart exists for those reasons.

They challenge Spark vs SQL. Pick based on volume and team; at teaching volumes SQL warehouse ELT may suffice; say what would push you to Spark (huge joins, heavy compaction).

They want APIs signed. Give event fields and SQL contract; avoid inventing proprietary product endpoints.

Design review checklist:

• Grain sentence written?
• Two clocks separated?
• Numbers with units?
• Marker + gates?
• Failure table at least five rows?
• Capacity for 40 readers?
• Cost refusals named?
• Deploy path named?
• PII/auth mentioned?
• Open questions listed?

Interview Questions and Answers

Q1. How do you start a data system design interview?

I clarify consumers, grain, freshness versus correctness priorities, volumes, retention, and security. I state assumptions if answers are missing. Only then do I draw ingest→lake→mart→serve and attach SLOs.

Q2. Design TechLearningPro's progress platform end to end.

Events from the learning app enter a replayable ingest channel, land in a versioned raw lake, compact to Parquet by event_date, transform into a completion mart with definition_version, pass fail-closed gates, write a publish marker before 06:00, and serve via one engine to the board identity. A provisional tile uses a separate landing table. Monitors burn error budgets; deploys are IaC plus SHA promotion. Capacity is sized for forty Monday readers of pruned SQL, not for raw scans.

Q3. How do you estimate storage?

Estimate events × payload for raw; use columnar compaction ratios for facts; multiply by retention days. For this course narrative I also anchor on ~40 GB/day compacted progress and ~15 TB/year if kept hot. I tier or expire with product approval.

Q4. Batch or streaming for the Monday board?

Batch or scheduled micro-batch into a daily certified publish. Streaming for provisional UX only unless product truly needs certified sub-hour grain and pays for the gate complexity.

Q5. How do you handle exactly-once?

I rarely promise end-to-end exactly-once. I design for at-least-once ingest plus idempotent merge on event_id and deterministic publishes per day. That is operationally achievable.

Q6. What SLOs do you set?

Freshness: 99% of business mornings have yesterday's marker by 06:00. Correctness: no marker if gates fail. Query: availability and p95 on the certified SQL during the morning window. Error budgets drive deploy freezes.

Q7. How does the design control cost?

Board reads the mart with partition predicates; compaction targets sensible file sizes; ELT isolated from board capacity; single serving engine; pause only after resume probes; no untested cross-region copies. Refusals include dropping gates or scanning raw for the board.

Q8. What is your failure story for a bad definition deploy?

Dual-run before pointer flip; fail closed if compare drifts; rollback pointer to previous build; invalidate BI extracts; burn freshness if needed; freeze further definition changes until budget recovers.

Q9. How do you size the serving layer?

Measure pruned query bytes and latency; size concurrency for forty readers; keep ELT off that pool; plan Monday vs quiet baselines separately. Prefer evidence over "start large."

Q10. Where does IaC fit in system design?

Buckets, IAM, orchestrator, warehouse schedules, and secret resources are part of the architecture diagram's operability. Without them the design cannot be promoted safely or frozen under error budget.

Q11. How would you extend this for mobile offline progress sync?

Expect bursty late events and duplicate syncs. Strengthen idempotency, widen late path windows, and keep certified watermark policy explicit. Do not let offline sync write directly to the certified mart.

Q12. What do you ask if the interviewer gives no volume numbers?

I propose an explicit assumption set, show the arithmetic, and invite correction. I do not invent silent precision. I also ask which number they care about most—peak events or board concurrency—because those size different layers.

Q13. How do you talk about data modeling in this interview?

I lead with grain and definition_version, show the fact table for attempts, and mention dimensions for lessons and learners as controlled inputs. I avoid a twenty-entity ER diagram that eats the clock.

Q14. Compare lakehouse-only serving versus warehouse copy.

Lakehouse-native can work if governance, concurrency, and latency meet the board SLO. A warehouse copy can help interactive concurrency and security features. I pick one primary for certification; I do not dual-serve without a reopen condition.

Hands-On Exercises

Exercise: Whiteboard the learning-progress platform

Problem statement. In forty-five minutes (timer), design TechLearningPro's learning-progress data platform for the certified board plus optional provisional tile. Produce a one-page architecture, numbers, SLOs, failure table, and open questions. No proprietary fake APIs.

Tasks.

1. Write clarification questions and your assumptions.
2. Show volume and storage arithmetic.
3. Draw ingest→lake→mart→board+alerts (and provisional branch).
4. Specify event fields, mart grain, and marker fields.
5. Define SLOs and error-budget policy pointers.
6. Fill a failure-mode table (at least six rows).
7. Outline capacity for merge vs Monday reads.
8. State deploy/IaC and identity posture.
9. List cost refusals and open questions.
10. Rehearse a two-minute summary aloud.

Expected deliverables. Diagram, assumptions list, schema sketches, SLO bullets, failure table, summary script.

Evaluation rubric.

Full worked design narrative (read aloud practice)

"I'd start by confirming the primary consumer is an internal completion board for roughly forty people who need yesterday's certified learner-lesson attempts by 06:00 local, with definition_version pinned. Correctness gates can miss the clock; we will not ship a known-wrong grain to hit the meeting. There may be a provisional live tile; that is a second product with a second table and a lag SLO in minutes, not the certified cursor.

Volumes: I'll assume order-of-magnitude hundreds of thousands to low millions of progress events per day, planning ceiling aligned with this course of about forty gigabytes of compacted Parquet daily, mart in tens of gigabytes, about fifteen terabytes per year if we keep hot Parquet. Peak ingest is bursty around campaigns; the log and lake absorb bursts; the certified path remains a batch publish.

Architecture: learning app emits schema-versioned events with event_id to a replayable ingest channel; raw zone in versioned object storage; compaction to daily Parquet in the hundred-megabyte file range; Airflow-class orchestration runs validate, transform, gates, and publish; serving through one warehouse or lakehouse table behind a stable pointer view; BI reads with date and version filters; ops.publish_status marker drives freshness SLIs; monitors use multi-window burn; provisional micro-batch writes only to a landing table for the live tile.

Idempotency via event_id dedupe; daily reruns safe. Definition changes use build tables, dual-run, atomic pointer flip, extract invalidation. Failures: producer volume floor jointly owned; gate fail means no marker; serving cold is query-latency SLI with resume probes; IAM and DDL shipped as IaC with CI SHA promote; break-glass only with reconcile. Cost: no raw board scans, isolate ELT, single mart, pause only if resume fits. Error budget freeze blocks risky deploys when reliability is poor.

Open questions: exact event rates, PII classification, finance reconciliation scope, retention tiers, and whether multi-region is a legal need or a habit."

Time that speech. If it runs over three minutes, you are ready; if it runs over eight, cut tool names first, not the contract.

Capacity spreadsheet sketch (whiteboard table)

Replace sketches with measurements during implementation; the interview rewards the shape.

API-style contracts without fake proprietary products

When interviewers say "define the API," answer with interfaces:

1. Event produce: HTTPS or SDK publish to the org's chosen log with the JSON fields above; response is ack with broker offset or request id—not a custom "ProgressService.v3" invention.
2. Consumer commit: platform jobs checkpoint offsets or list processed objects in a control table.
3. Publish notification: optional cloud event or topic message {event_date, definition_version, published_at} for downstream caches after marker write.
4. Board read: SQL or a thin metrics read model generated from SQL; if you offer REST, it is a cache over the mart, not a second definition of completion.
5. Admin replay: authenticated internal endpoint or job trigger to rebuild day D—audited, not public.

Scaling story: 10× learners

If DAU grows 10×, events and compacted GB grow ~10×; Monday readers might only grow 2× if the board stays internal. Scale lake and merge first; board capacity second; consider partitioning strategies and Spark if SQL ELT wall-clock exceeds the critical path. Do not jump to multi-region or mesh as the first 10× response. Revisit partition grain only if daily partitions become too large for efficient retries—then consider hashing inside the day, not hourly folder explosion for a daily board.

Security design slice

Threats: public bucket misconfig, overbroad human admin, secret leakage in DAGs, PII in staging, malicious DAG deploy. Controls: IaC policy checks, separated roles, secret manager, scanned CI, sampled staging data, signed promote approvals on protected paths. Mention encryption and audit aligned to lesson 57/61 without eating the clock.

How this design cites earlier lessons in one minute

"Lesson 58 sets cost refusals and file targets. Lesson 62 sets 06:00 SLOs and budgets. Lesson 63 makes buckets, IAM, and DAG+SQL+DDL promote with freeze. Observability lesson supplies signals. This whiteboard is those constraints drawn as one system."

Interviewers who took adjacent courses hear maturity.

Alternate prompt: clickstream marketing site

If the panel switches to "design clickstream analytics," reuse the method: clarify funnels vs certified revenue, separate real-time counters from daily marts, estimate events (often higher QPS), watch bot noise as a correctness problem, keep PII out of marketing marts, and still refuse tool salad. The TechLearningPro progress design remains your deepest practiced example; the method transfers.

Scoring yourself after a mock

Give one point each: grain sentence; two clocks; numbers with units; diagram with marker; idempotency; SLO+budget; five failures; capacity split; cost refusal; deploy/IAM. Ten of ten is hire-ready for this course's bar. Below seven, schedule another timed mock focused on the missing cells.

Appendix: clarifying questions bank (memorize ten)

1. Who are the consumers and how many concurrent readers at peak?
2. What is the exact grain of the certified table?
3. What wall-clock freshness is required, in which timezone?
4. Is a late correct publish better than an on-time wrong one?
5. Expected events per day and peak per second?
6. Average event size and retention for raw and mart?
7. Any PII or residency constraints?
8. Do we need a provisional near-real-time path as well?
9. How often do definition rules change, and must history rewrite?
10. What is the disaster RTO expectation—minutes, hours, or next business day?

Ask until answers or assumptions fill the template. Then draw.

Appendix: sample failure conversation

Interviewer: "Kafka is down for an hour." You: "Ingest availability fails; raw stops growing; volume floor trips before 04:00 if still down; joint incident with app/platform; certified publish excluded or delayed per sheet; provisional tile stale; when Kafka returns, catch-up consumers replay; compaction and merge may need extra capacity; marker may miss 06:00 and burn budget; we do not invent data."

Interviewer: "Someone dropped the mart table." You: "Serving availability fails; RTO path rebuilds from compacted or raw for needed days; pointer may fail; pages fire on query errors; restore drill numbers tell us hours not minutes; IAM review for who could drop; IaC and privileges tightened; communications to board channel."

Interviewer: "Definition engineer hotfixes SQL in prod UI." You: "That is an unauthorized deploy; dual-run skipped; correctness risk; freeze and revert to last SHA; incident review; remove UI write grants."

Appendix: comparison table you may draw

Appendix: schema evolution example

v4 → v5 adds completion_source and changes "completed" to require a passed check event. Dual-run seven days: compare COUNTIF(completed) by lesson within 0.5% or investigate. Board SQL pins v5 only after pointer flip. Old extracts purged. Markers carry definition_version so monitors do not false-page.

Appendix: estimating peak ingest

If busy day is 5M events and 50% arrive in 4 evening hours, average evening rate is 5e6/2/14400 ≈ 174 events/sec. Design consumers and lake writers for 5–10× short spikes (network retries, mobile sync storms). Buffering in the log protects the mart batch from needing to match peak second-by-second.

Appendix: what not to say

Do not claim exactly-once end-to-end without defining every hop. Do not claim active-active multi-region without conflict rules. Do not claim the board can be both fail-closed and never late without staffing and architecture to match. Do not invent a SaaS product name that does not exist. Do not skip ownership. Do not skip how you deploy.

Appendix: linking alerts to runbooks

Each P1 alert links to a runbook section: check marker table, check DAG run id, check gate report, check warehouse resume, check upstream volume, declare exclude or not, communicate ETA. System design includes the existence of those runbooks even if you do not write them fully on the board.

Appendix: thirty-minute compressed version

If the panel only gives thirty minutes, keep: clarification template filled; grain + dual clocks; one diagram; volume arithmetic; marker+gates; three failures; SLO sentence; one cost refusal; deploy/IAM one-liner; open questions. Cut: multi-region debate, tool comparisons, ER diagrams, lengthy API fantasy. Practice both thirty- and forty-five-minute cuts so pacing is muscle memory.

Appendix: metrics API caution

Product may ask for a "progress metrics microservice." That can be a fine cache layer over the mart for mobile, but it must not redefine completion. Generation job reads published.completion_attempt, writes aggregate documents keyed by lesson and date and definition_version, and invalidates on marker. If the microservice computes completion from raw events with its own rules, you have created a second mart under another name—the Northline drift failure mode.

Appendix: load-test outline

Use synthetic events with unique event_ids into staging ingest; scale to 5× busy-day rate for thirty minutes; ensure consumer lag recovers; run compaction and merge on the synthetic day; verify gates and marker; run forty concurrent certified queries against the mart. Do not load-test production. Capture wall times into the capacity sheet. A design without a path to validate numbers is still a sketch.

Appendix: data quality dimensions mapped to SLIs

Completeness → volume bands and partition presence. Uniqueness → grain check. Validity → null and range checks. Consistency → definition_version dual-run. Timeliness → freshness marker. Accuracy → optional recon to app counters for a sample. Accuracy is hardest; do not pretend volume checks prove accuracy. Say what you measure and what you only spot-check.

Appendix: final whiteboard closing sentence

"This design makes yesterday's certified completion queryable by 06:00 for the board through a gated, idempotent publish into one mart, with a separate provisional path, explicit SLOs and budgets, and a deploy posture that can freeze when reliability burns—numbers assumed as stated and ready to revise with your real telemetry."

Appendix: producer contract enforcement

At the edge, validate required fields and schema_version before ack when using HTTPS ingest; with a log, use a schema registry compatible checker in the producer library. Malformed events go to a dead-letter topic with headers for reason codes. Certified path never reads DLQ into the mart without a deliberate replay job. Volume SLIs should count DLQ rate separately so a silent poison-pill storm does not look like "low learner activity."

Appendix: watermark policy options

Policy A: calendar day in America/Los_Angeles closes at 04:00 next local morning, allowing late mobile sync. Policy B: strict UTC day close at 00:30 UTC for global uniformity. Pick one, document it in the reliability sheet, and align compaction. Mixing policies by team creates empty partitions and false volume alerts. Late path retains events for N days and merges into historical days only through the backfill pool.

Appendix: BI consumer contract

Certified dashboards must declare dataset owner, SQL or semantic layer definition pinned to definition_version, refresh dependency on publish notification or marker poll, and cache TTL. Uncertified sandboxes may scan compacted data with cutoffs from lesson 58 but cannot use the "certified" badge. System design that ignores BI will fail in production even if the lake is perfect—the board is a BI surface.

Appendix: why this beats a vague "lakehouse platform" answer

Saying "we'll use a lakehouse" is not a design. Saying "raw append zone, compacted daily Parquet, gated publish to a version-pinned completion fact, marker-driven freshness SLO, one serving pointer, provisional tile isolated, IaC-deployed IAM and DAGs" is a design. The lakehouse is the style of storage/compute integration; the contracts are the system.

Appendix: practice regimen

Run three timed mocks: (1) TechLearningPro progress as in this lesson; (2) Northline orders with finance recon; (3) clickstream with bot noise. After each, score the ten-point checklist. Record yourself once. Gaps that usually remain are ownership edges, deploy posture, and refusing dual clocks in one table. Fix those before interviewing. The course's earlier modules supply mechanism depth when a panel digs into Kafka consumer groups or Iceberg commits—invite that dig only after the contract story is solid.

Appendix: handoff to the capstone

The capstone implements one cloud-shaped instance: pick AWS, Azure, or GCP patterns from lessons 54–56; keep the same grain, marker, SLO, and IaC rules. Do not "simplify" by removing gates to save time. The whiteboard you practiced here is the acceptance bar for the build.

Appendix: one more reminder on clocks

Pipeline latency ends at the publish marker. Query latency starts when the board runs SQL. Mixing them in a single whiteboard bubble is the most common way a strong candidate loses a senior bar. Draw two arrows. Name two SLIs. Sleep well.

Lesson Summary

System design for TechLearningPro progress is a contract-shaped architecture: clarify grain and clocks, estimate honestly, land immutable raw, compact, publish a gated mart with markers by 06:00, serve one pruned path to the board, isolate provisional latency, alert on burn, and deploy only through reviewable SHA-based paths. Tools are interchangeable within those constraints. What is not interchangeable is failing open on correctness, mixing cursors, or skipping the math.

Practice the forty-five minute timer. Data Engineering Interview Questions and Answers broadens the question bank. The capstone will ask you to implement one cloud-shaped instance of this design without abandoning the contracts you just defended.

Further Reading

• Designing Data-Intensive Applications (Kleppmann) for batch/stream concepts and reliability vocabulary used across interviews.
• Google SRE Book — Evolving Critical Systems and SLO chapters for disciplined non-functional design.
• Course lessons 58, 62, and 63 for cost, reliability, and deploy constraints that bound any "good" whiteboard.
• Apache Iceberg / Delta Lake docs for table pointer and time-travel cutover patterns you may cite when asked about rollback.
• Cloud architecture centers (AWS Well-Architected Analytics, Azure Analytics, Google Cloud analytics architectures) for vendor-shaped reference diagrams—translate them back to the grain and SLO first.
• Your own timed mock: record a whiteboard session and grade it with this lesson's design review checklist.