Launch Deal · 50% off 1st month ·

3.8 — Optimize Spark and Query Performance

DP-700 Domain 3 · Skill 3.8

What You Need to Know

Spark tuning on the exam has two parts. One is choosing the right compute and engine settings. The other is writing code that avoids the classic Spark bottlenecks: shuffles, skew, small files and bringing data to the driver.

Compute and engine settings

Setting What it gives you
Native execution engine (Environment → Acceleration, Runtime 1.3+) Runs supported Spark SQL and DataFrame operators in a vectorized C++ engine (Velox and Gluten). Faster and cheaper with Parquet and Delta, no code changes. It falls back to normal Spark for operators it doesn't support
Resource profiles (spark.fabric.resourceProfile) writeHeavy (the default for new workspaces; V-Order off), readHeavyForSpark, readHeavyForPBI (V-Order and larger optimize-write bins) or custom
Autotune Adjusts settings such as shuffle partitions and the broadcast threshold from earlier runs of the same query
Pool and node size Larger nodes for memory-hungry jobs; autoscale and dynamic allocation so that executors follow the load
High concurrency and starter pools Faster session starts and lower cost (lesson 1.1)
Spark advisor Recommendations in the notebook, such as skew warnings and hints to use broadcast

Code-level optimization

Problem Symptom Fix
Large shuffle in joins Slow join stages Broadcast the small table (F.broadcast(dim)); join on keys of the same type; filter before joining
Skew A few tasks run far longer than the rest Adaptive query execution's skew-join handling; salt the hot keys; repartition
Wrong partition count Thousands of tiny tasks, or a few huge ones Tune spark.sql.shuffle.partitions (or let AQE coalesce); repartition before a wide write; coalesce to reduce the number of files
Reading too much Long scan stages Select only the columns you need (column pruning); filter on partition or clustered columns (data skipping); keep tables compacted
Recomputing The same DataFrame is evaluated several times cache() or persist() it when it's reused, and unpersist() afterwards
Driver bottleneck Driver out of memory, long single-threaded steps Avoid collect() and toPandas() on big data; use Spark functions instead of Python UDFs (or vectorized pandas UDFs)
Small-file writes Slow reads later Turn on optimize write; batch the writes; OPTIMIZE on a schedule

Query performance across engines

  • SQL analytics endpoint and warehouse: Query insights to find long-running queries; statistics; data clustering; matching join types; avoid functions on filtered columns so that data skipping still works.
  • Power BI Direct Lake: tables compacted with V-Order, a sensible number of row groups and files, and no fallback to DirectQuery (for example caused by warehouse RLS or views).
  • KQL: time filters first, has over contains, materialized views (lesson 3.7).
  • Everywhere: model properly. A star schema with integer surrogate keys beats wide string joins in every engine.

Key Concept: First turn on the cheap wins (native execution engine, the right resource profile, compacted tables with V-Order). Then fix the code: broadcast small tables, deal with skew, prune columns, never collect big data.

Exam Traps

  • Rewriting code to use the native execution engine (it needs no code changes; you turn it on)
  • Broadcasting a large table, which causes driver or executor out-of-memory errors
  • Caching everything "for speed", which wastes memory when DataFrames are used only once
  • Increasing node size to fix skew (one hot key stays slow on bigger nodes)

Check your understanding

Practice Scenario

🔒 This practice scenario is part of Skillmarked Pro

The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.

Already Pro? Log in.