#Performance Engineering, Debugging and Cost

The flagship file. The JD says "remain hands-on with Spark performance, debugging and optimisation" — which means there will be a moment where someone describes a broken job and watches how you think. This file makes that moment your strongest, not your weakest.

#1. The rule that wins this round

Never propose a fix before naming the evidence.

Candidates who say "I'd repartition and increase the cluster size" sound like they are guessing, because they are. Candidates who say "first I'd look at the stage timeline to see whether this is one slow stage or many, then compare max to median task duration in the worst stage" sound like they have done it a hundred times.

Use this frame every single time:

Symptom → Scope → Evidence → Hypothesis → Test → Fix → Prevent

The Prevent step is what senior candidates add and mid-level candidates forget. It is also the whole point of an architect: you are supposed to stop the tenth occurrence, not just the first.

#2. The triage order — memorise this

When someone says "the job got slow", work outside-in. Cheap checks first.

1. DID ANYTHING CHANGE?        code deploy · data volume · runtime version · cluster config · upstream schema
                               ↓ (most incidents die here — check before you profile anything)
2. WHERE IS THE TIME?          job → stage timeline. One dominant stage, or spread across many?
                               ↓
3. IS IT EVEN RUNNING?         waiting for resources / queued / autoscale lag / cluster start-up
                               ↓  (a 12-minute job with 8 minutes of cluster start-up is not a Spark problem)
4. IN THE WORST STAGE:
   a. SKEW?                    max task duration vs median. >5-10x ⇒ skew
   b. SPILL?                   spill (memory) and spill (disk) columns non-zero and large
   c. TOO MANY / TOO FEW TASKS? thousands of sub-second tasks ⇒ overhead. A handful of huge tasks ⇒ no parallelism
   d. GC?                      GC time as a share of task time
                               ↓
5. IS IT I/O?                  bytes read vs bytes the query actually needs.
                               Files scanned vs files pruned. Small-file count.
                               ↓
6. IS IT THE PLAN?             explain / query profile. Wrong join strategy? Missing pruning?
                               Nested loop join? Photon fallback? Stale statistics?
                               ↓
7. IS IT NOT SPARK AT ALL?     source system throttling · external API in a UDF · driver bottleneck ·
                               concurrent workload contention · a MERGE conflicting and retrying

Step 1 is the most valuable and the most skipped. Say it first, always. In real incidents, "what changed" resolves the majority of regressions, and in an interview it demonstrates operational maturity before you have touched a single metric.

#3. Reading the evidence

#The Spark UI, in the order you should open it

The single most useful number: in the slow stage, max task duration ÷ median task duration.

• ≈1 → uniform work; the stage is honestly big. Scale or reduce the work.
• 5–10×+ → skew. Go to §5.

The second most useful: bytes read vs bytes needed. If a query needing one day of data reads a year, the problem is layout and pruning, not compute — and adding nodes just burns money faster.

#The query profile (Databricks SQL)

For SQL warehouse workloads use the query profile rather than the raw Spark UI. It gives you, per operator: rows and bytes processed, time spent, files pruned vs files read, spill, and whether the operator ran in Photon or fell back. The "files pruned" figure is the fastest way to prove a data-layout problem to a customer, and it shows well in a whiteboard conversation.

#4. The cost side of the same coin

A performance conversation with an architect must become a cost conversation, because that is the difference between an engineer's answer and an architect's answer.

The DBU model: you pay a Databricks unit rate (varying by SKU — all-purpose, jobs, SQL, serverless — and by whether Photon is on) plus the underlying cloud infrastructure for classic compute. So:

Cost = rate × time. Photon raises the rate and (usually) cuts the time by more. Serverless changes the shape: no idle, no start-up, but less tuning surface.

#Where enterprise Databricks spend actually goes

In rough order of how often each is the top finding in a cost review:

1. All-purpose clusters used for scheduled work. Interactive SKU is materially more expensive than jobs compute. Moving scheduled jobs to job clusters is often the single biggest line-item win.
2. Idle interactive clusters. No/long auto-termination. Free money burned overnight and at weekends.
3. Over-provisioned always-on SQL warehouses where serverless with auto-stop would cost a fraction.
4. Small-file and layout problems making every query read far more than it needs.
5. Rerunning full pipelines where incremental processing would do — full refresh as a habit.
6. Oversized clusters chosen by superstition ("it failed once, so we doubled it and never revisited").
7. Ungoverned GenAI / model serving spend — the newest and fastest-growing category.

#The instruments

• System tables (system.billing.usage, system.access.audit, system.query.history, system.compute.*) are the source of truth. A cost story with no system-table evidence is an opinion.
• Tagging — cluster/warehouse/job tags flowing into billing records are what make chargeback possible. Design the tagging taxonomy before the first workspace, because retrofitting tags across an estate is miserable. Minimum: cost centre, environment, domain/product, owner.
• Budget policies to attribute and cap serverless spend.
• Predictive Optimization to stop paying humans to write OPTIMIZE jobs.

The line that lands with a CFO:

"I don't want to make the platform cheaper — I want to make it attributable. Once every pound is tagged to a product team, the teams optimise their own spend without me policing anything, and the conversation stops being 'why is Databricks expensive' and starts being 'is this use case worth what it costs'."

#5. Playbook: the six failures you will be asked about

Work each of these aloud before the interview, using the seven-step frame.

#5.1 "The job was 20 minutes, now it's 70. Nothing changed."

Scope: same code, same schedule → suspect data or environment, not logic. Evidence: input bytes and row counts over the last 30 runs (system.query.history, job run history, or the source table's history). Stage timeline — is one stage inflated, or everything? File count in the source. Cluster config / runtime diff. Hypotheses, ranked:

1. Input grew — often a backfill or a duplicate upstream load. Check row counts and distinctness.
2. Small files accumulated — a streaming or frequent-append source now has 400,000 tiny files; task overhead and listing dominate. Check average file size.
3. Statistics went stale after a volume change, flipping a broadcast join to sort-merge.
4. Skew appeared — a new dominant key value, e.g. a new default from an upstream change.
5. Contention — another workload now shares the cluster or warehouse. Test: run on a fixed prior snapshot via time travel (VERSION AS OF) — if the old data runs fast on today's code and cluster, it is a data problem, not a code or platform problem. This is a genuinely elegant move and interviewers notice it. Fix: compaction/OPTIMIZE or clustering for small files; refresh statistics; address skew. Prevent: predictive optimisation on the table; a monitor on input row count and file count; alert on job duration regression rather than only on failure.

#5.2 "One task runs for 40 minutes; the other 199 finish in 30 seconds."

That is skew, stated. Evidence: task summary max vs median shuffle read. Then SELECT key, count(*) ... GROUP BY key ORDER BY 2 DESC LIMIT 20 on the join key. Fix order: confirm AQE skew handling is enabled and the stage is eligible → filter junk keys (NULL, -1, 'UNKNOWN') that cannot join meaningfully → broadcast the small side if feasible → salt the hot keys → isolate hot keys into a separate job and union. Prevent: a data-quality expectation on key distribution; don't join on nullable keys without handling nulls explicitly.

#5.3 "The MERGE takes hours."

MERGE is the classic enterprise bottleneck. Diagnose in this order:

1. How many files does the match touch? If the merge condition can't prune, it rewrites the world. Include the partition/cluster key in the ON condition where semantically valid.
2. Are deletion vectors enabled? Without them, changing one row rewrites the whole file.
3. Is the source deduplicated? Multiple source rows matching one target row is both a correctness bug and a performance disaster.
4. Is the source broadcastable? A small daily delta against a large target should not shuffle the target.
5. Is the target laid out for the merge key? Liquid clustering on the key that the merge predicates on.
6. Are you merging when you could append? Immutable event data does not need MERGE. Prevent: standardise a CDC pattern (Auto Loader → declarative pipeline with AUTO CDC/APPLY CHANGES) rather than hand-written merges per table.

#5.4 "Executors keep dying with OOM."

Evidence: Executors tab for dead executors and their last GC/memory numbers; the failing stage's operator; spill columns; any broadcast in the plan. Hypotheses: build side of a hash join far larger than estimated (stale stats) · a broadcast of something not actually small · a single skewed partition too large for a slot · an exploding operation (explode, cross join, window without partitioning) · a Python UDF materialising large objects per row. Fix: refresh statistics; lower or disable the auto-broadcast threshold for that query; increase partition count to reduce per-task size; fix the skew; replace the UDF with built-ins or a pandas UDF. Note the ordering: "add memory" is the last resort and the one everyone reaches for first. Saying that explicitly scores points.

#5.5 "Our dashboard queries are slow but the warehouse is huge."

Almost never a compute problem. Evidence: query profile — files pruned vs scanned, bytes read vs result size, spill, whether the result could be served from cache, queue time vs execution time. Hypotheses: no effective data skipping (wrong or absent clustering) · dashboards querying raw bronze instead of a modelled gold layer · no aggregate/materialised layer for repeated queries · warehouse sized up to mask a layout problem · high concurrency causing queuing rather than slow queries. Fix: cluster the gold tables on the actual filter columns; build materialised views / aggregate tables for the repeated shapes; right-size and enable serverless with auto-stop; separate interactive BI from ETL onto different warehouses. Prevent: a standard that BI never queries bronze; review the top 20 queries by total cost monthly.

#5.6 "The streaming job's latency creeps up over days."

Evidence: batch duration trend, input rows per trigger vs processed, state store size over time, checkpoint sizes, sink file counts. Hypotheses: unbounded state from a missing or over-generous watermark (most likely) · small-file accumulation on the sink degrading downstream and compaction · a growing backlog with an unbounded first batch · a skewed key concentrating state on one partition · stateful operator retaining more than expected. Fix: set/tighten the watermark; RocksDB state store; bound maxOffsetsPerTrigger / maxFilesPerTrigger; auto-compaction on the sink; consider AvailableNow if true streaming latency is not required. Prevent: monitor state size and batch duration as first-class SLIs, not just job liveness.

#6. The optimisation hierarchy

When asked "how do you make this faster", answer in this order. It is roughly the order of cost-effectiveness, and stating it as a hierarchy is itself the senior signal.

1. Do less work. Filter earlier, project fewer columns, process incrementally instead of fully, don't recompute what hasn't changed. The cheapest query is the one you don't run.
2. Read less data. Layout: clustering, partitioning where genuinely appropriate, file sizing, statistics that enable skipping. Most "slow query" problems are "read too much" problems.
3. Shuffle less. Broadcast where possible, pre-aggregate before joining, avoid unnecessary repartitioning, reuse a shuffle by ordering operations well.
4. Fix the distribution. Skew, partition counts, spill.
5. Use a faster engine. Photon, the right compute type, an appropriate runtime version.
6. Add hardware. Last. It is the only one that always costs more and often fixes nothing.

The line: "Most teams start at step six and work backwards. I start at step one, because steps one and two are usually where the whole regression lives, and they make the bill smaller instead of bigger."

#7. Rules of thumb worth quoting (and caveating)

State these as heuristics, not laws — and say that. False precision is a trap.

#8. Five things to say that signal real experience

1. "Before I tune anything — what changed?"
2. "Let me look at max versus median task time in the slowest stage."
3. "I'd pin the data with time travel and re-run, so we separate a data problem from a code problem."
4. "Adding nodes to a skewed job makes it more expensive and exactly as slow."
5. "The fix is one thing; the monitor that catches it next time is the deliverable."