Scenario Drills — Data & GenAI¶
Last reviewed: October 2026
Open-ended, real-world scenarios for system design and "it broke in production" situations — the parts of interviews (and the job) that trivia can't prepare you for. Each drill gives you the prompt, a framework to attack it, a reference walkthrough, and curveballs the interviewer (or reality) will throw next.
How to practice
- Read the prompt and set a timer (10–15 min).
- Think out loud — talk through your approach before peeking.
- Reveal the framework, then the walkthrough; compare to yours.
- Handle the curveballs without looking. That's the real test.
Related concept pages are linked at the bottom of each drill.
How to attack any scenario¶
A repeatable structure beats memorized answers. Use this every time:
flowchart LR
R[Restate + clarify<br/>scope, scale, constraints] --> A[Assumptions<br/>state them out loud]
A --> D[Design / diagnose<br/>simplest correct first]
D --> T[Trade-offs<br/>cost, latency, failure modes]
T --> N[Next steps<br/>what you'd verify / do with more time]
- Clarify before solving. Scale? Latency target? Batch or real-time? Budget?
- State assumptions so a wrong one is cheap to correct.
- Simplest correct design first, then optimize — same as live coding.
- Name trade-offs — that's what separates senior from junior.
- Say how you'd verify — metrics, tests, a canary. Shows operational maturity.
System-design drills¶
Design a near-real-time Oracle → Snowflake pipeline that keeps history.
Clarify first: latency target (minutes? seconds?), volume, do we need hard deletes, how much history to retain.
Framework → walkthrough:
1. Extract: log-based CDC (not full-table polling) — low source load,
captures updates + deletes.
2. Land: raw change events append to a bronze/staging table.
3. Merge: scheduled MERGE upserts latest per key into a current-state
table; prior versions archived to a timestamped history table (SCD2).
4. Idempotent: keyed upserts + checkpoints so retries/backfills don't
duplicate.
5. Quality gates: uniqueness/freshness tests before publish; bad rows to a
dead-letter table.
6. Serve: current for ops, history for audit, a view/UNION for the full
picture. Tag history for compliance.
Trade-offs: Streams+Tasks (imperative, custom logic) vs Dynamic Tables (declarative, less code) vs Snowpipe Streaming (lowest latency, more cost).
Curveballs:
- "Source has no reliable updated_at." → log-based CDC, not query-based.
- "Late-arriving data." → lookback window on the merge, not just > MAX(ts).
- "Cost doubled." → check warehouse auto-suspend, Snowpipe on tiny files.
See: Data Engineering · Snowflake
Design a production RAG assistant over a company's internal docs.
Clarify: how many docs, how often they change, do answers need citations, latency/cost budget, on-prem/governance constraints.
Framework → walkthrough: 1. Ingest + chunk docs to semantic units (sections, not fixed tokens). 2. Embed + index — hybrid (vector + keyword) so exact terms aren't lost. 3. Retrieve top-k → rerank for precision; apply metadata filters. 4. Assemble context within the token budget; instruct "answer only from context, cite sources, say 'not found' otherwise." 5. Generate with low temperature (or low reasoning effort) and a structured output schema; validate citations. 6. Guardrails + eval: injection defense, an eval set (recall@k, faithfulness), feedback capture, quality-drift monitoring.
Trade-offs: self-managed vector DB vs managed (e.g. Cortex Search); one big model vs routing cheap→expensive.
Curveballs: - "It cites the wrong doc." → retrieval problem first (log top-k), not prompting. - "Docs change hourly." → incremental re-index / target-lag, not full rebuild. - "A user saw a doc they don't have access to." → enforce source ACLs at retrieval time, and propagate permission changes and deletes to the index. - "Data can't leave our account." → in-warehouse (Cortex) or VPC-private inference.
See: RAG · AI Engineer
Design a multi-agent assistant that answers questions AND takes actions.
Clarify: which actions (read vs write?), how reversible, who approves, latency tolerance.
Framework → walkthrough: 1. Supervisor plans and routes to specialists (data / docs / actions). 2. Tools: narrow, typed, least-privilege; read tools open, write tools gated behind validation + human approval. 3. Loop: plan → act → observe with a step cap and cost budget. 4. Grounding: tools return real data; treat tool output as untrusted. 5. Guardrails + tracing: every step logged; outcome verified vs real state.
Trade-offs: single agent (simpler, reliable) vs multi-agent (only when domains genuinely differ). Orchestrate as a graph (LangGraph) for durable state + human-in-the-loop.
Curveballs: - "It looped forever." → step cap + repeated-action detection. - "It deleted something it shouldn't." → destructive actions must be propose-then-approve, never direct. - "A tool result said 'ignore your instructions'." → injection; data ≠ commands. - "A third-party MCP server changed its tool descriptions overnight." → rug pull: pin and hash approved definitions, re-approve on change.
Design a metrics/BI layer that stays fast as data grows 100x.
Clarify: query patterns, freshness needs, number of concurrent users, biggest tables.
Framework → walkthrough: 1. Model as star schema (facts + conformed dims) for predictable joins. 2. Pre-aggregate heavy rollups (materialized/dynamic tables) so dashboards don't scan raw. 3. Cluster/partition big fact tables on the dominant filter (usually date). 4. Isolate workloads — separate warehouses for ETL vs BI so they don't contend; multi-cluster for concurrency spikes. 5. Cache — result cache + BI extract layer for repeated queries.
Trade-offs: precompute freshness vs cost; wide tables vs normalized.
Curveballs:
- "A dashboard got slow after 10x growth." → Query Profile: pruning, spill,
exploding joins.
- "Costs spiked." → resource monitors, auto-suspend, kill SELECT *.
"It broke in production" drills¶
The nightly pipeline's cost doubled overnight. Diagnose it.
Attack: don't guess — measure. Rank credit/compute burn by warehouse and
query (WAREHOUSE_METERING_HISTORY, QUERY_HISTORY). Common causes: a
warehouse with auto-suspend too high (idle burn), multi-cluster scaling out, a
full-scan report running frequently, Snowpipe on many tiny files, or a backfill
someone kicked off. Fix + prevent: tighten auto-suspend, resource monitors
with alerts, workload isolation, right-size. Say how you'd confirm: compare
today's top queries to last week's.
See: Snowflake
A Spark job passed on sample data but hangs at full scale.
Attack: almost always data skew in a shuffle. Open the Spark UI, one task processes far more than the rest. Fix: enable AQE skew-join; broadcast the small side to skip the shuffle; salt the hot key if still skewed. Also check partition sizing (~100–200 MB) and spill. Verify: re-run, watch the long stage's task distribution flatten.
See: Databricks
Your incremental model silently drops rows. Find the bug.
Attack: row counts lower than source → usually the watermark or the key.
(1) Watermark uses > MAX(updated_at) but a late-arriving row has an older
timestamp → add a lookback window. (2) Non-unique unique_key collapses
rows in the merge → pick a truly unique/surrogate key. (3) Schema drift dropped
a column → set on_schema_change. Verify: --full-refresh rebuild + a
row-count reconciliation test.
See: dbt
A deploy broke prod and users are affected. What do you do?
Attack, in order: (1) Restore service first — roll back / flip blue-green. Confirm recovery on dashboards. Don't debug a burning prod. (2) Then investigate with logs/traces around the change window. (3) Prevent recurrence: smaller deploys, canary with auto-rollback, feature flags to decouple deploy from release. (4) Blameless postmortem. The instinct to debug-before-restore is the classic junior mistake.
See: DevOps
The LLM feature is too slow and too expensive. Cut both.
Attack — levers: model routing (cheap model for easy cases), provider prompt caching for the stable prefix plus exact/semantic response caching, prompt/context trimming (retrieve less, compact history), batch APIs for offline work, lower reasoning effort where evals allow, and streaming to cut perceived latency. Tokens = cost + latency, so right-size context. Verify: measure per-request cost/ latency before and after; optimize the top offenders, not everything.
See: AI Engineer
Self-check¶
After a drill, can you answer these about your solution?
- What did I clarify before designing?
- What assumptions did I state?
- What's the simplest correct version, and how would I optimize?
- What are the top 2 failure modes and how do I handle them?
- How would I verify it works (metric, test, canary)?
More labs
This is the Scenario track. See also the Live-Coding Drills and Hackathon Build Challenges.