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

  1. Scenario given
  2. Ask clarifying questions and state assumptions
  3. Is it a design question? Yes: Walk sources → ingestion → layers → jobs → serving → governance. No: Diagnose with run history and Spark UI.
  4. Propose the solution or the fix with reasons
  5. Discuss monitoring, cost and failure handling
  6. 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 → measure

Example

-- 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic