Data Platform System Design
A repeatable framework for lakehouse design interviews, plus three fully worked designs.
As of mid-2026, candidate reports converge on one signature Databricks design prompt: "design a scalable ingestion-to-lakehouse pipeline (Spark + Delta + cloud storage) that handles latency, schema evolution, and access control." Partner and customer interviews run scenario variants of the same thing, and the RSA panel presentation is this exercise performed in front of people role-playing a customer. You do not need a new architecture per question — you need one framework you can run in your sleep, and you have to hit governance, operations, and cost before time runs out, because that is where senior candidates separate from mid-level ones.
The 9-step framework, with a 45-minute budget
Rehearse this as a script. At each step, say the headline out loud ("Next I want to cover storage layout") — interviewers grade coverage and structure, and narrating your plan buys you forgiveness when you go deep on one box.
| # | Step | Minutes | What you say and write down |
|---|---|---|---|
| 1 | Clarify requirements & consumers | 4 | Who consumes this, for what decision, how fresh, what query shapes (dashboard vs ad hoc vs API vs ML)? What exists today? What does success look like? Ask before drawing. |
| 2 | Quantify volume / velocity / variety + SLAs | 4 | Rows and GB per day, peak vs average, growth, number and type of sources, retention, and the SLA as a number ("dashboards correct by 06:00"). Write the numbers in a corner of the board. |
| 3 | Ingestion design | 6 | One pattern per source class: CDC for databases, Auto Loader / COPY INTO for file drops, Structured Streaming from Kafka/Event Hubs, scheduled pulls for APIs. State idempotency and raw retention explicitly. |
| 4 | Storage layout & modeling | 6 | Medallion semantics (Bronze raw-immutable, Silver conformed/validated, Gold consumer-shaped), Delta features you rely on (ACID, schema enforcement, time travel), dimensional model and SCD strategy, partitioning vs liquid clustering, target file sizes. |
| 5 | Transformation & orchestration | 6 | Incremental processing (MERGE, Change Data Feed), the dependency DAG, batch jobs vs declarative pipelines, data-quality expectations, dev/test/prod promotion and CI/CD. |
| 6 | Serving & consumption | 5 | SQL warehouse for BI, semantic layer choice, concurrency and caching, ML/feature consumers, external shares or APIs. Match warehouse sizing to the concurrency number from step 2. |
| 7 | Governance & security | 4 | Unity Catalog hierarchy, row/column-level controls, PII tagging, lineage, audit logs, secrets and service principals. Name the personas and what each can see. |
| 8 | Operations | 6 | Monitoring and alerting, quality gates that block vs warn, failure handling (retries, quarantine, dead-letter), backfill and reprocessing strategy, runbooks and on-call. |
| 9 | Cost | 3 | Derive compute from the step-2 numbers: job clusters vs all-purpose, autoscaling, spot, Photon, serverless auto-stop, OPTIMIZE cadence, storage tiering. Give a rough monthly figure and the biggest lever. |
That is 44 minutes; the last minute is your summary: restate the SLA, the two riskiest decisions, and what you would prototype first. If the interviewer drags you deep into one box, compress steps 6–9 into a 90-second sweep rather than skipping them — say each headline and the one decision that matters.
Whiteboard layout
Draw the canvas before the architecture. Left-to-right data flow in planes, governance and operations as horizontal bands underneath (they span everything), and a numbers box top-right that you fill during step 2 and point back at constantly. Label SLAs on the arrows, not in your head.
+------------------------------------------------------[ NUMBERS ]-+
| SOURCES -> INGEST -> BRONZE -> SILVER -> GOLD -> SERVE | 2B ev/d |
| (per- (pattern (raw, (valid, (star, (BI, | 2TB/day |
| system) per src) immut.) SCD2) KPIs) ML) | SLA 5m |
+-------------------------------------------------------------------+
| GOVERNANCE: Unity Catalog · RLS/CLS · lineage · audit |
+-------------------------------------------------------------------+
| OPERATIONS: orchestration · monitoring · DQ gates · backfill |
+-------------------------------------------------------------------+"Design a pipeline that ingests heterogeneous sources into a lakehouse. Address latency, schema evolution, and access control." — the canonical prompt, near-verbatim from candidate reports.
Strong outline: clarify consumers and freshness first (two minutes of questions); quantify volumes; then per-source ingestion patterns into an append-only Bronze with permissive schema and rescued-data capture; Silver enforces contracts (schema enforcement on, explicit evolution via mergeSchema or versioned DDL, drift alerts); Gold serves BI and ML. Latency: state the SLA per consumer and pick batch, micro-batch, or streaming per table — not one mode for everything. Access control: Unity Catalog with catalog-per-environment, groups not users, row filters and column masks on PII, lineage for impact analysis. Close with failure handling and cost. That ordering — requirements before boxes — is most of the grade.
Curveballs you will get, and answers that hold up
Late-arriving data
Separate event time from processing time. In streaming, a watermark (e.g., withWatermark("event_time", "2 hours")) bounds state and admits events up to that lateness into aggregates; later events still land in Bronze because Bronze is append-only and unconditional. Pair the streaming layer with a periodic batch correction job that recomputes affected aggregate windows from Bronze. In batch finance pipelines, the equivalent is a rolling re-merge window: every run re-MERGEs the last N days keyed on business keys, so a journal posted late simply lands on the next run. Say the trade-off out loud: bigger watermark = more state and cost; smaller = more corrections downstream.
GDPR / right-to-be-forgotten in a lakehouse
DELETE FROM on Delta rewrites files but old versions survive in time travel, so the data is not gone until VACUUM removes unreferenced files past the retention window — set retention to fit your compliance deadline (e.g., 30 days) and run VACUUM on schedule. Propagate deletes downstream via Change Data Feed so Silver/Gold and any derived copies also purge. Use Unity Catalog lineage to find every copy, including exports. Mention crypto-shredding (encrypt PII per subject, destroy the key) as the pattern when physical rewrite everywhere is impractical.
Schema evolution breaking downstream consumers
Treat the Bronze/Silver boundary as a contract. Bronze is permissive: new columns are captured (schema evolution or a rescued-data column) and nothing fails. Silver enforces: schema enforcement on by default, evolution only by explicit, reviewed change. Detect drift and alert on it before consumers notice. Expose Gold through views so additive changes are invisible and breaking changes ship as a versioned view (v2) with a deprecation window. The interviewer is checking that you protect consumers without making ingestion brittle.
"How do you get exactly-once?"
Be precise: Structured Streaming gives exactly-once results into Delta via checkpointed offsets plus transactional commits — replayed micro-batches are detected and not double-applied. Across arbitrary sinks or in foreachBatch, you get at-least-once delivery and must make the write idempotent yourself (idempotent MERGE on business keys, or transactional version markers). The honest phrase is "effectively-once end to end: at-least-once delivery plus idempotent writes." Claiming unconditional exactly-once across systems is a red flag they probe for.
Reprocessing a bad day of data
Because Bronze is immutable, you can always rebuild downstream. Sequence: stop or fence affected jobs; quantify blast radius with lineage; if the bad write was yesterday's run, RESTORE the table to the prior version (time travel) for instant rollback; fix the logic; replay the affected date range through parameterized, idempotent backfill jobs (run-date as a parameter, MERGE not append, so re-runs are safe); validate against control totals before unfencing consumers. Mention testing the fix on a clone of the table before touching production.
Multi-region
First ask which problem it is: data residency (data must stay in region) or disaster recovery (data must survive a region). Residency: regional workspaces with region-scoped catalogs, aggregate only what is legally shareable. DR: storage-level replication or scheduled Delta deep clones to a secondary region, with IaC to re-create workspaces and jobs; state your RPO/RTO and note that cross-region egress and duplicate storage are the cost drivers. Active-passive is almost always the right answer for analytics; active-active is rarely worth it.
Worked Design A — daily-close enterprise finance lakehouse
Requirements. Consolidate general ledger and sub-ledger data from multiple ERPs (SAP, JDE) plus a consolidation system (HFM) into one governed platform. Daily trial-balance refresh by 06:00; a hard month-end close window where the books must reconcile to source to the cent; full auditability (immutable raw, lineage, multi-year retention, reproducibility of any past report); consumers are CFO-level dashboards and finance analysts doing ad hoc drill-down.
Capacity estimate. Volume is modest, correctness is brutal: say 30–50 core source tables, tens of millions of journal lines per month, daily incrementals of 5–20 GB, low single-digit TB per year of growth. The constraint is not throughput — it is the dependency chain: ERP batch close finishes ~01:00, reports due 06:00, so the entire pipeline has a five-hour window with reconciliation gates inside it.
SAP / JDE (CDC or delta extracts) HFM (file) DB2 (JDBC)
| | |
v v v
ADF / scheduled ingestion --> ADLS + Delta BRONZE (raw, immutable)
| [01:00-02:30]
v
SILVER: conformed dims (account, entity, cost center; SCD2),
FX rates, validated journal facts
GATE: debits = credits, control totals vs source
| [02:30-04:30]
v
GOLD: star schemas + KPI marts (P&L, Budget vs Actual)
| [04:30-05:30]
v
SQL warehouse --> Power BI (refresh by 06:00) [SLA 06:00]Walkthrough. Ingestion is CDC where the source supports it, vendor delta extracts or file drops where it does not; every load is idempotent (MERGE on document keys) so a re-run never double-posts a journal. Bronze is append-only with load metadata for audit. Silver conforms dimensions across ERPs — the real work is mapping three charts of accounts onto one — with SCD Type 2 on account, entity, and cost-center hierarchies so any historical report reproduces under the hierarchy that was true at the time. Reconciliation is a blocking gate, not a dashboard: if control totals do not match source, Gold does not publish and on-call gets paged, because publishing wrong numbers to a CFO is strictly worse than publishing late. Gold is dimensional star schemas plus pre-aggregated KPI marts. Orchestration is a DAG with explicit dependency on ERP-close completion signals, often coordinated by an enterprise scheduler that owns the cross-system chain end to end, with the BI refresh as the final task.
Trade-offs. Batch, not streaming — finance wants reconciled-correct, not fresh-but-provisional, and the close calendar is inherently batch. SCD2 everywhere costs storage and join complexity but is non-negotiable for restatements. Blocking quality gates trade availability for trust; for this consumer that is the right trade. Month-end adds 5–10x volume in a shorter window: handle with autoscaling job clusters, not year-round overprovisioning.
Cost. Compute is small — right-sized job clusters with Photon, a serverless SQL warehouse with auto-stop for analysts, scheduled OPTIMIZE. The dominant costs are people and source-system licensing, so say so: in this design your FinOps story is mostly about not running all-purpose clusters for production jobs and not letting BI warehouses idle.
Worked Design B — clickstream analytics at billions of events/day
Requirements. Product clickstream from web and mobile SDKs; near-real-time dashboards (1–5 minute freshness) for ops and growth teams; sessionization and funnel analysis; deduplication (SDKs retry); events arrive late by minutes to hours; app teams add event properties weekly without telling anyone.
Capacity estimate. 2B events/day ≈ 23K events/sec average; assume peak 5x ≈ 120K events/sec. At ~1 KB/event that is ~2 TB/day raw, ~60 TB/month; 13 months of raw retention lands you near 800 TB before compression. These numbers drive everything: Kafka/Event Hubs partition counts, streaming cluster size, and why storage lifecycle policy is a first-class design decision, not an afterthought.
Web/mobile SDKs --> Kafka / Event Hubs (buffer, replay)
|
v Structured Streaming (micro-batch ~1 min)
BRONZE: append-only events, permissive schema + _rescued_data
|
v dedup on event_id within watermark; parse/flatten
SILVER: clean events (liquid-clustered: event_date, event_type)
| |
v streaming agg v nightly batch (availableNow)
GOLD: per-minute counters GOLD: sessions, funnels,
(drive live dashboards) late-data corrections
| |
+-----------> SQL warehouse --> dashboards [SLA 5 min]Walkthrough. The message bus decouples producers from the platform and gives you replay. Streaming ingestion lands events in Bronze with no validation — permissive schema plus a rescued-data column so a new property never drops an event. Silver dedups on event ID within the watermark window and enforces the contract. Hot Gold tables are streaming aggregations feeding live dashboards; heavy work (sessionization, funnels) runs as nightly batch over Silver, which also recomputes any window touched by events that arrived after the watermark — the dashboards self-correct overnight. The dedup core:
events = (spark.readStream.table("bronze.events")
.withWatermark("event_time", "2 hours")
.dropDuplicates(["event_id", "event_time"]))
# late > 2h: excluded from streaming state, but still in Bronze;
# the nightly job re-aggregates affected windows from Bronze.Trade-offs. A 2-hour watermark balances state size against correction volume — defend whichever number you pick with the lateness distribution from step 2. The streaming-plus-batch-correction split is deliberately lambda-flavored: pure streaming for everything is cleaner on the slide and more expensive and stateful in production. Micro-batch around one minute meets a 5-minute SLA at a fraction of the cost of chasing seconds. Liquid clustering over hive-style partitioning avoids the small-file and high-cardinality traps at this volume.
Cost. The 24/7 streaming cluster is the dominant compute cost — size it from peak events/sec and protect it from skew. Run warm-but-not-hot aggregates with Trigger.AvailableNow on a schedule instead of continuously. Storage at ~800 TB makes lifecycle tiering of aged Bronze and aggressive compaction (OPTIMIZE) genuinely material, not hygiene.
"Your streaming job crashed and restarted. The dashboard counted some events twice — why, and how do you prevent it?"
Strong outline: Structured Streaming replays the failed micro-batch from the checkpoint, so the source is at-least-once on restart. Writing to Delta, the commit protocol makes the replay idempotent — the batch is transactionally applied once. Double counting appears when something non-idempotent sits in the path: a foreachBatch appending to an external sink, an aggregate maintained by blind increments, or two jobs sharing a checkpoint incorrectly. Fixes: keep checkpoints per query, write results not increments, make foreachBatch writes idempotent (MERGE on keys or batch-version markers). Close with: "and I would prove it by killing the job mid-batch in staging and diffing the counts."
Worked Design C — ML + GenAI platform on the lakehouse
Requirements. The lakehouse from Designs A/B exists; now layer on: feature pipelines for churn/forecast models, a RAG assistant over internal policy documents, natural-language Q&A over governed tables, online serving, and — the part most candidates skip — evaluation and monitoring so the thing stays trustworthy after launch. PII governance applies to features, prompts, and logs, not just tables.
Capacity estimate. Keep it concrete: ~10M customer rows refreshed daily into ~50 features; a document corpus of ~100K pages chunking to ~1M vectors; interactive load of tens of QPS on serving endpoints; every request/response logged for evaluation. Small data, but latency-sensitive serving and per-token LLM cost change the economics completely.
SILVER/GOLD tables Docs (PDF, HTML, wikis)
| |
v v parse -> chunk -> embed
Feature pipelines Delta chunk table
| | (delta-sync)
v v
UC feature tables Vector Search index
| |
v v
MLflow train/registry RAG agent (LLM endpoint + retrieval)
| | + Text2SQL/Genie path
v v
Model Serving <---- router / multi-agent ----> App / UI
|
v
Inference tables --> eval harness + drift monitoringWalkthrough. Features are computed by the same governed pipelines as everything else and registered as Unity Catalog feature tables, so training and serving read identical definitions — that one sentence kills the train/serve-skew question. Models train under MLflow tracking and promote through the registry. On the GenAI side, document parsing/chunking/embedding is just another incremental pipeline writing a Delta table; the vector index syncs from that table, so document updates flow to retrieval without a parallel stack. The assistant routes by intent: structured questions go to a SQL-grounded path over governed tables (Genie-style, showing the generated query builds trust); policy questions go to RAG with citations; a router agent fronts both. Everything is logged to inference tables; evaluation is a curated golden Q&A set scored on every change (LLM-as-judge plus human spot checks), and monitoring watches retrieval hit rates, answer quality drift, latency, and token spend. Access control is the hard requirement: the agent must respect row- and column-level permissions of the asking user, not of a privileged service account.
Trade-offs. RAG over fine-tuning for policy knowledge that changes monthly — cheaper to update, citable, auditable. Managed vector search synced from Delta over an external vector DB — one governance and lineage story instead of two. Batch daily features over real-time unless a use case proves it needs sub-hour freshness, because streaming features double the operational surface. Scale-to-zero on dev/low-traffic endpoints versus cold-start latency on the ones users touch.
Cost. Token spend and always-warm serving endpoints dominate; pipelines are rounding error. Levers: smaller/cheaper models behind the router for easy intents, response caching, batching embedding refresh, scale-to-zero where latency tolerates it, and per-endpoint budget alerts from day one — GenAI is the one workload where cost can 10x silently from adoption alone.
The three designs, side by side
| A — Finance close | B — Clickstream | C — ML + GenAI | |
|---|---|---|---|
| Driving constraint | Correctness + audit + close window | Throughput + freshness | Trust, latency, token economics |
| Latency posture | Daily batch, hard 06:00 SLA | Minutes (streaming + batch correction) | Interactive serving; batch pipelines |
| Ingestion pattern | CDC + governed extracts, idempotent MERGE | Message bus + Structured Streaming | Reuses A/B + doc parsing/embedding |
| Hardest problem | Reconciliation gates, SCD2 hierarchies | Late data, dedup, state management | Permission-aware retrieval, evaluation |
| Dominant cost | People; compute is small | 24/7 streaming compute + storage | LLM tokens + warm endpoints |
| Failure stance | Block publish, page on-call | Degrade freshness, self-correct overnight | Fall back to citation-only / refuse |
Run each design as a full 45-minute rehearsal against the clock, out loud, drawing the canvas first. Then swap the scenario (IoT telemetry, retail orders, healthcare claims) and notice that the nine steps do not change — only the numbers and the trade-offs do. That realization is the whole module.