Scenario Questions: Design a Pipeline, Fix a Slow Job
Databricks · Certification & Interview
Theory
Senior and mid-level interviews often give an open scenario and watch how you reason. Two classics: (1) "Design a pipeline that ingests daily order files and a CRM database and serves a sales dashboard." A good answer walks through sources, ingestion choice (Auto Loader for files, Lakeflow Connect for the CRM), medallion layers in Unity Catalog, a Lakeflow pipeline with expectations and AUTO CDC for customers, gold materialized views, a Lakeflow Job with triggers, retries and alerts, an AI/BI dashboard, and governance and cost controls. (2) "A nightly job went from 20 to 90 minutes. What do you do?" A good answer starts with evidence: run history, Spark UI or query profile, then checks data growth, skew, spill, small files, missing clustering, UDFs, wrong compute or a changed join, fixes one thing and measures again.
State assumptions (volumes, latency, team size) before designing.
Why we use it
Scenarios test judgment, which certifications cannot. Interviewers want structured thinking, sensible trade-offs and awareness of quality, monitoring, security and cost, not just tool names.
When to use it
Use the design framework for any "build" question and the diagnose-fix-measure loop for any "slow or failing" question. Ask clarifying questions first; do not jump to tools before you know volumes, freshness needs and consumers.
How it works
- Scenario given
- Ask clarifying questions and state assumptions
- Is it a design question? Yes: Walk sources → ingestion → layers → jobs → serving → governance. No: Diagnose with run history and Spark UI.
- Propose the solution or the fix with reasons
- Discuss monitoring, cost and failure handling
- Summarise trade-offs and next improvements
Syntax
Design framework: Requirements → Sources → Ingestion → Bronze/Silver/Gold → Quality → Orchestration → Serving → Governance → Monitoring & cost
Slow job loop: Compare runs → Spark UI / query profile → find bottleneck (skew, spill, shuffle, small files, UDF) → one fix → measureExample
-- Slow job: evidence first
SELECT run_id, period_start_time, period_end_time, result_state
FROM system.lakeflow.job_run_timeline
WHERE job_id = 123456789
ORDER BY period_start_time DESC
LIMIT 14;
DESCRIBE DETAIL main.sales.orders; -- numFiles exploded? size grew 4x?
-- Typical fixes after diagnosis
ALTER TABLE main.sales.orders CLUSTER BY (order_date, region); -- better data skipping
OPTIMIZE main.sales.orders; -- compact small files
# Skewed join on a few big customers: let AQE handle skew, or broadcast the small side
from pyspark.sql.functions import broadcast
result = orders.join(broadcast(customers), "customer_id")Key points
- Ask about volume, latency, sources and consumers before designing
- Name the ingestion tool per source and say why
- Include data quality, monitoring, alerting, security and cost, not just the happy path
- For slow jobs, gather evidence (run history, Spark UI) before changing anything
- Change one thing at a time and compare duration and DBUs
- Close with trade-offs and what you would improve with more time