What you will be able to do
- Set and read a Spark configuration property at session scope in a Databricks notebook, and tell session scope apart from compute scope
- Explain what spark.sql.shuffle.partitions controls, what happens with too few or too many partitions, and why Databricks recommends auto
- Describe how adaptive query execution coalesces shuffle partitions at runtime and which settings steer it
- Explain what determines parallelism on a cluster, and know where this lesson's sources stop covering spark.default.parallelism
Key concept
Shuffle partition count — Joins and aggregations redistribute (shuffle) data across the cluster. The number of partitions that data lands in sets how many tasks the next stage runs and how big each one is. Most basic Spark tuning comes down to getting that number and the size of each partition right.
1.Where tuning parameters live, and when to leave them alone
Every parameter in this lesson is a Spark configuration property, or Spark conf. Before you change one, know the Databricks position: Databricks generally recommends against setting most Spark properties. Old configurations carried over from open-source Spark or an earlier runtime can override newer defaults that Databricks tuned for you. Treat a tuning parameter as a deliberate experiment, not something you copy into every job.
Where you set a conf decides what it affects. If you set it in the compute configuration, it applies to every notebook and job that runs on that compute. If you set it inside a notebook, it applies only to that notebook's SparkSession. In a notebook you can use SQL SET or spark.conf.set. To check the current value, call spark.conf.get("configuration_name").
spark.conf.set("spark.sql.ansi.enabled", "true")Serverless compute narrows this further. It does not let notebooks or jobs set most Spark properties. Only a short list is settable, and spark.sql.shuffle.partitions is on it. So the shuffle setting is one tuning parameter you can still change on serverless.
| Property | Default | What it controls |
|---|---|---|
| spark.sql.shuffle.partitions | auto | Number of partitions used when shuffling data for joins or aggregations |
| spark.sql.files.maxPartitionBytes | 134217728 (128 MB) | Maximum bytes packed into a single partition when reading files |
| spark.sql.ansi.enabled | true | Whether Spark SQL uses an ANSI compliant dialect |
Checkpoint 1 of 5· Check yourself
A data engineer runs spark.conf.set("spark.sql.shuffle.partitions", "64") in one notebook attached to a shared cluster. What does the change affect?
Properties set inside a notebook apply only to that notebook's SparkSession. To affect every notebook and job on the compute, set the property in the compute configuration.
“Only the SparkSession for the current notebook.”Source: docs.databricks.com
Sources1
2.spark.sql.shuffle.partitions: sizing the shuffle
spark.sql.shuffle.partitions sets how many partitions a join or aggregation shuffles its data into. The Apache Spark tuning guide lists a fixed default of 200, and the Databricks AQE page also lists 200 as the property's default. The Databricks page for serverless compute lists auto as the default there. Setting the value to auto enables auto-optimized shuffle, which determines the number from the query plan and the input data size, and Databricks recommends spark.sql.shuffle.partitions=auto so Spark chooses the number itself. So on a classic compute, check with spark.conf.get rather than assuming which default you have.
A fixed number is hard to get right because the best value depends on how much data there is. With too few partitions, each one is large, and the tasks that process them may need to spill data to disk during sorts and aggregations. With too many, each one is tiny. That means a lot of small network fetches to read shuffle blocks, and having a large number of tasks also puts more burden on the Spark task scheduler.
The too-few case also shows up as a memory problem. Databricks's Spark UI guide puts "Too few shuffle partitions" first in its list of causes of memory problems, ahead of large broadcasts, UDFs and skew. So a job that fails with lost executors in a shuffle-heavy stage is a reason to check this parameter.
Checkpoint 2 of 5· Exam question
A data engineer runs a Spark SQL query that joins and aggregates a dataset of only 50,000 rows. The Spark UI shows the shuffle stage spawns 200 tasks, most finishing in under 100ms with only a handful of rows each, and cluster utilization stays low because scheduling overhead dominates the stage. Which change is most likely to reduce elapsed time for this stage?
Correct answer: B — Lower `spark.sql.shuffle.partitions` to a value closer to the available core count so each shuffle partition holds a meaningful share of the small dataset.
- A. `spark.default.parallelism` sets the partition count for RDD-based operations and operations without an explicit partitioner, not for SQL or DataFrame shuffles. Raising it would not change the 200 shuffle tasks produced by this join and aggregation.
- B. `spark.sql.shuffle.partitions` directly controls the number of partitions produced by SQL and DataFrame shuffle operations, and it defaults to 200 regardless of input size. Lowering it toward the core count means each partition carries enough rows to justify a task, cutting scheduling overhead.
- C. Executor memory headroom is not the bottleneck when tasks already finish in under 100ms with only a few rows each. The problem is too many undersized tasks, not insufficient memory per task.
- D. The broadcast threshold governs how join inputs are distributed at planning time; it has no effect on a standalone aggregation stage's shuffle partition count. Raising it would not reduce the number of tiny tasks in this stage.
Checkpoint 3 of 5· Check yourself
A job's aggregation stage spills heavily to disk, and every task handles a very large partition. Which shuffle partition change matches the problem?
Too few partitions make each partition large, and tasks processing large partitions may spill. More partitions, or letting auto pick the count, shrinks each one.
“the tasks to process these large partitions may need to spill data to disk”Source: www.databricks.com
3.How AQE takes over shuffle partition tuning
Spark can choose a good shuffle partition count at runtime because of adaptive query execution (AQE). AQE re-optimizes a query while it runs. At the end of each shuffle or broadcast exchange it has accurate statistics, so it can choose a better physical strategy or a better post-shuffle partition size and number. AQE is on by default. It applies to non-streaming queries that contain at least one exchange, which usually means a join, aggregate or window, or a subquery.
One of its features addresses the too-many-partitions problem directly: after a shuffle exchange, AQE combines small partitions into reasonably sized ones. Very small tasks have worse I/O throughput and pay proportionally more scheduling and setup overhead, so merging them saves resources. The Apache Spark guide describes the resulting approach: set a large enough initial number of shuffle partitions, and Spark picks the proper number at runtime. If you don't set spark.sql.adaptive.coalescePartitions.initialPartitionNum, the initial number is simply spark.sql.shuffle.partitions.
One caveat on the advisory size: the Apache Spark docs say that by default (spark.sql.adaptive.coalescePartitions.parallelismFirst is true) Spark ignores the advisory target when coalescing and respects only the minimum partition size, to maximize parallelism. The advisory size is the target in the Databricks AQE reference below, but do not assume coalesced partitions always land near it.
Streaming is different. AQE applies to non-streaming queries, and for Structured Streaming the spark.sql.shuffle.partitions value cannot be changed between query restarts from the same checkpoint location.
| Property | Default | Effect |
|---|---|---|
| spark.sql.adaptive.coalescePartitions.enabled | true | Turns partition coalescing on or off |
| spark.sql.adaptive.advisoryPartitionSizeInBytes | 64MB | Target size after coalescing; partitions end up close to but no bigger than it |
| spark.sql.adaptive.coalescePartitions.minPartitionSize | 1MB | Coalesced partitions are no smaller than this |
| spark.sql.adaptive.coalescePartitions.minPartitionNum | 2x no. of cluster cores | Minimum partition count after coalescing; not recommended |
Checkpoint 4 of 5· Match them up
Match each AQE setting to what it does
Tap a term, then the definition that fits it.
The advisory size is the target, the minimum size is the floor, and an explicit minimum partition count overrides the minimum size, which is why Databricks recommends against setting it.
“Not recommended, because setting explicitly overrides spark.sql.adaptive.coalescePartitions.minPartitionSize.”Source: docs.databricks.com
4.spark.default.parallelism and where parallelism comes from
The exam guide also lists spark.default.parallelism. The documentation behind this lesson does not define that property or give its default, so this lesson does not state either. It does cover the ideas around it.
First, the cluster sets a hard ceiling. Databricks's compute sizing guidance says total executor cores, meaning the number of cores across all executors, determine the maximum parallelism of a compute. No partition setting can run more tasks at once than there are cores. The Databricks XGBoost guide uses the related sc.defaultParallelism value to mean the total number of Spark task slots in the cluster, and the Apache Spark tuning guide shows spark.sql.files.minPartitionNum defaulting to "Default Parallelism".
Second, parallelism for lower-level RDD operations is set differently from SQL shuffles. The Apache Spark RDD guide says that by default, the level of parallelism in a transformation's output depends on how many partitions the parent RDD has. You can pass an optional numPartitions argument to operations such as groupByKey and reduceByKey to change the number of tasks. DataFrame and SQL joins and aggregations use spark.sql.shuffle.partitions instead.
Third, file reads have their own partitioning: spark.sql.files.maxPartitionBytes caps how many bytes go into a single partition when reading files, and defaults to 128 MB.
About 32, the total number of executor cores. The other partitions wait in later waves, because total executor cores set the maximum parallelism of the compute.
Checkpoint 5 of 5· Check yourself
What sets the maximum parallelism of a Databricks classic compute resource?
Partition settings decide how work is split into tasks, but only as many tasks can run at once as there are executor cores.
“The total number of cores across all executors. This determines the maximum parallelism of a compute.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.On Databricks, spark.sql.shuffle.partitions is always auto, so spark.conf.get will return auto on any compute.Why is that wrong?
The serverless table lists auto as the default, but the Databricks AQE page lists 200 as the property's default. auto is a value you can set, which enables auto-optimized shuffle, and Databricks recommends it.
2.Serverless compute lets you set any Spark property from a notebook, like classic compute.Why is that wrong?
Serverless compute supports only a short list of Spark properties for notebooks and jobs. spark.sql.shuffle.partitions is on the list; most others are not.
Covered in Where tuning parameters live, and when to leave them alone
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/spark/confOfficial docs
“Databricks generally recommends against configuring most Spark properties.”
↩︎ Where tuning parameters live, and when to leave them alone“legacy Spark configurations can override new default behaviors that optimize workloads.”
↩︎ Where tuning parameters live, and when to leave them alone“The maximum number of bytes to pack into a single partition when reading files.”
↩︎ spark.default.parallelism and where parallelism comes from“The default number of partitions to use when shuffling data for joins or aggregations.”
↩︎ Key concept“Serverless compute does not support setting most Spark properties for notebooks or jobs.”
↩︎ Exam trap 2“Only the SparkSession for the current notebook.”
↩︎ Checkpoint“spark.sql.shuffle.partitions | auto”
↩︎ Prediction - 2.
“Databricks recommends you set spark.sql.shuffle.partitions=auto to let Spark pick the number of optimal shuffle partitions automatically.”
↩︎ spark.sql.shuffle.partitions: sizing the shuffle - 3.https://docs.databricks.com/aws/en/optimizations/aqeOfficial docs
“Setting the value auto enables auto-optimized shuffle”
↩︎ spark.sql.shuffle.partitions: sizing the shuffle“Dynamically coalesces partitions (combine small partitions into reasonably sized partitions) after shuffle exchange.”
↩︎ How AQE takes over shuffle partition tuning“Very small tasks have worse I/O throughput and tend to suffer more from scheduling overhead and task setup overhead.”
↩︎ How AQE takes over shuffle partition tuning“For Structured Streaming, this configuration cannot be changed between query restarts from the same checkpoint location.”
↩︎ How AQE takes over shuffle partition tuning“Setting the value auto enables auto-optimized shuffle”
↩︎ Exam trap 1“Not recommended, because setting explicitly overrides spark.sql.adaptive.coalescePartitions.minPartitionSize.”
↩︎ Checkpoint - 4.https://spark.apache.org/docs/latest/sql-performance-tuning.htmlSecondary source
“Configures the number of partitions to use when shuffling data for joins or aggregations.”
↩︎ spark.sql.shuffle.partitions: sizing the shuffle“Spark can pick the proper shuffle partition number at runtime once you set a large enough initial number of shuffle partitions”
↩︎ How AQE takes over shuffle partition tuning“Spark ignores the target size specified by spark.sql.adaptive.advisoryPartitionSizeInBytes (default 64MB) when coalescing contiguous shuffle partitions”
↩︎ How AQE takes over shuffle partition tuning - 5.https://www.databricks.com/blog/2020/05/29/adaptive-query-execution-speeding-up-spark-sql-at-runtime.htmlSecondary source
“Having a large number of tasks also puts more burden on the Spark task scheduler.”
↩︎ spark.sql.shuffle.partitions: sizing the shuffle“the tasks to process these large partitions may need to spill data to disk”
↩︎ Checkpoint - 6.
“Too few shuffle partitions”
↩︎ spark.sql.shuffle.partitions: sizing the shuffle - 7.
“The total number of cores across all executors. This determines the maximum parallelism of a compute.”
↩︎ spark.default.parallelism and where parallelism comes from - 8.
“which sets num_workers to the total number of Spark task slots in the cluster”
↩︎ spark.default.parallelism and where parallelism comes from - 9.https://spark.apache.org/docs/latest/rdd-programming-guide.htmlSecondary source
“By default, the level of parallelism in the output depends on the number of partitions of the parent RDD.”
↩︎ spark.default.parallelism and where parallelism comes from