What you will be able to do
- Explain what spark.sql.autoBroadcastJoinThreshold controls, how to disable it, and how broadcast hints and AQE interact with it
- Choose the Databricks way to give executors or the driver more memory instead of hardcoding spark.executor.memory
- Use the Spark UI to measure a job before and after a tuning change: longest stage, I/O columns, spill and skew
1.spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side
A join usually shuffles both sides so that matching keys end up on the same partition. If one side is small, Spark can instead copy, or broadcast, that whole side to every worker node and skip the shuffle. spark.sql.autoBroadcastJoinThreshold is the maximum size in bytes a table can be for Spark to broadcast it automatically. The Apache Spark default is 10485760 bytes (10 MB). Setting the value to -1 disables automatic broadcasting.
You can also ask for a broadcast explicitly with a join hint. A BROADCAST hint makes Spark prefer broadcasting the hinted relation even if its estimated size is above the threshold. Spark does not guarantee it will follow a hint, though, because some strategies don't support every join type.
SELECT /*+ BROADCAST(r) */ * FROM src s JOIN records r ON s.key = r.keyCheckpoint 1 of 6· Fill the gap
Which hint value makes this DataFrame join broadcast the records table?
spark.table("src").join(spark.table("records").hint(" ? "), "key").show()The broadcast hint marks records as the side to send to every worker. MERGE, SHUFFLE_HASH and SHUFFLE_REPLICATE_NL are other join strategy hints.
Source: spark.apache.orgOn Databricks, AQE adds a runtime path to the same result: it can change a sort merge join into a broadcast hash join once it knows the real relation sizes. That runtime switch has its own setting, spark.databricks.adaptive.autoBroadcastJoinThreshold, with a default of 30MB. Don't confuse it with the static spark.sql.autoBroadcastJoinThreshold (10 MB Apache default), which applies when the join is planned before the query runs.
The runtime path is late, though. AQE may not switch until both sides have already been shuffled, so a statically planned broadcast is usually faster, and a hint is still a good choice when you know your data. AQE respects hints the same way static planning does.
If a relation is under the threshold and still isn't broadcast, check the join type first. For example, the left relation of a LEFT OUTER JOIN cannot be broadcast. Also check the size of what you broadcast. Databricks lists "Large broadcast" as a cause of memory problems, so raising the threshold carelessly causes problems of its own.
Checkpoint 2 of 6· Exam question
A job aggregates a 2 TB dataset while `spark.sql.shuffle.partitions` is left at its default of 200. The Spark UI shows each shuffle task processing several gigabytes of data, heavy spill to disk, and stage durations far longer than comparable jobs on smaller data. Which change should be applied and re-measured first?
Correct answer: A — Increase `spark.sql.shuffle.partitions` so each shuffle partition holds a smaller amount of data and disk spill is reduced.
- A. Raising the shuffle partition count spreads a fixed volume of data across more, smaller partitions, so each task handles less data and spills less to local disk. This directly targets the observed multi-gigabyte-per-task spill pattern.
- B. A 2 TB dataset is far larger than any practical broadcast threshold, and forcing a broadcast of that size would exhaust executor memory rather than fix the spill. The broadcast threshold governs join planning, not aggregation shuffle sizing.
- C. `spark.default.parallelism` applies to RDD-based operations and does not override the SQL-specific shuffle partition setting for DataFrame or SQL aggregations. Raising it would not change the aggregation's shuffle partition count.
- D. Shuffle spill happens on the executors that hold the shuffle data, not on the driver, so increasing driver memory has no effect on executor-side spill during this aggregation.
2.Executor and driver memory on Databricks
The exam guide names spark.executor.memory and spark.driver.memory. spark.executor.memory sets the memory given to each Spark executor, which is a Java process; Databricks's own example sets it to 4g in the cluster config. On Databricks, though, you usually get more memory by choosing larger instances rather than by setting these properties. Databricks's compute guidance tells you to avoid hardcoding Spark configurations such as spark.executor.memory in compute or job definitions. Hardcoded values override Databricks's built-in optimizations and often lead to wasted spend or worse performance. The one case in these sources where lowering it is advised is a hybrid Spark and Ray workload, where a small executor memory leaves room for Ray.
For executors, the lever is the worker instance type. Total executor memory decides how much data can be held in memory before Spark spills it to disk. If you see heavy spill or out-of-memory (OOM) errors, increase the memory available on your instances. Memory errors are often generic, so Databricks suggests an experiment: double the memory per core and see whether the problem changes. For example, move from a 4-core, 16 GB worker to a 4-core, 32 GB worker. What matters is the ratio of memory to cores. If the job fails later or not at all, you're on the right track.
The driver works the same way. By default the driver node type matches the worker node type. Choose a larger driver with more memory if you plan to collect() a lot of data back to the notebook. So when a question asks how to give the driver more memory for a large collect(), the Databricks mechanism is a larger driver node type, not a hardcoded property. A driver can also be overloaded by too many concurrent streams, queries or jobs. In that case, Databricks recommends first doubling the size of the driver and seeing how that affects the job.
| Symptom | Lever | Avoid |
|---|---|---|
| Significant spill to disk or OOM errors on executors | Worker instance with more memory per core | Hardcoding spark.executor.memory |
| Driver busy with many concurrent streams, queries or jobs | Double the driver size, reduce concurrency, or spread load over clusters | Running more threads of Spark jobs on the same driver |
| Planning to collect() a lot of data to the notebook | Larger driver node type with more memory | Leaving the driver node type the same as the worker node type |
Checkpoint 3 of 6· Check yourself
A job on classic compute keeps losing executors with a generic error. You suspect memory. What does Databricks suggest trying first to confirm it?
Doubling memory per core is the recommended way to confirm a memory problem, because the ratio of memory to cores is what matters. Hardcoding spark.executor.memory overrides Databricks optimizations.
“you can verify the issue by doubling the memory per core to see if it impacts your problem.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A notebook calls `.collect()` on a DataFrame that returns several million summarized rows to the client, and the job fails with a driver out-of-memory error after all executor stages had already completed successfully. Which tuning parameter should be adjusted first for this job?
Correct answer: C — Increase `spark.driver.memory` so the driver process has more heap space to hold the rows gathered by the collect operation.
- A. The executor stages already completed successfully, and the failure occurs specifically when results are gathered onto the driver, so extra executor heap space would not prevent this crash.
- B. Shuffle partition count affects how data is partitioned during joins and aggregations on the executors; it has no bearing on how much memory the driver needs to hold rows returned by a collect operation.
- C. `.collect()` pulls all result rows into the driver process's heap, so a driver-side out-of-memory error after successful executor stages points directly at insufficient driver memory for holding that result set.
- D. The broadcast threshold governs whether a join input is distributed to executors during query planning; it does not apply to a collect operation that pulls results back to the client.
3.Re-measure: reading the Spark UI before and after a change
Every change above is an experiment: change one parameter, re-run, and compare. Databricks's Spark UI guide sets out the order for the comparison. Start with the jobs timeline to find major issues. Then find the longest stage, check it for skew or spill, and decide whether it is I/O bound.
To find the longest stage, sort the job's stages by duration. Note the Input, Output, Shuffle Read and Shuffle Write columns, because they show where a shuffle-partition or broadcast change should make a difference. Note the task count too: a long stage with just one task can itself be a sign of a problem.
On the stage page, look for spill first. Spill happens when Spark runs low on execution memory and moves data to disk, and it is most common during shuffles. Next, look for skew in the Summary Metrics. If the Max task duration is 50% or more above the 75th percentile, you may have skew. Run the same checks after your change. Less spill, smaller shuffle columns and a shorter longest stage show whether the parameter helped.
Checkpoint 5 of 6· Put it in order
Put the Spark UI diagnosis steps in the order Databricks's guide follows
- 1.Look at longest stage
- 2.Look for skew or spill
- 3.Determine if longest stage is I/O bound
- 4.Use the Jobs Timeline to identify major issues
The guide goes from the overall timeline to the longest stage, checks that stage for skew and spill, and then decides whether it is I/O bound.
“The guide contains the following steps in order:”Source: docs.databricks.com
Checkpoint 6 of 6· Check yourself
After a tuning change, a stage's Summary Metrics show a 75th-percentile task duration of 40 s and a Max of 70 s. What does this suggest?
70 s is 75% above 40 s, which exceeds the 50% rule of thumb for skew. Spill shows up in the stage details, not as a gap between task durations.
“If the Max duration is 50% more than the 75th percentile, you may be suffering from skew.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.To fix executor OOMs on Databricks, add spark.executor.memory to the cluster or job Spark config.Why is that wrong?
Databricks advises against hardcoding spark.executor.memory because it overrides built-in optimizations. Choose a worker instance with more memory per core instead.
Covered in Executor and driver memory on Databricks
2.With AQE enabled, broadcast hints are pointless because AQE converts joins to broadcast automatically.Why is that wrong?
AQE may switch to a broadcast only after both sides have been shuffled. A statically planned broadcast is usually faster, so a hint can still be a good choice.
Covered in spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side
3.Any relation under spark.sql.autoBroadcastJoinThreshold will be broadcast, whatever the join type.Why is that wrong?
Broadcast isn't supported for some join types. For example, the left relation of a LEFT OUTER JOIN cannot be broadcast.
Covered in spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side
Practise it for real
Change one shuffle setting in a notebook session and measure its effect in the Spark UI
1.Run spark.conf.get("spark.sql.shuffle.partitions") in the notebook.
Why: Record the starting value before you change anything.
You should see: The current value; on Databricks the default is auto.
2.Run a join or aggregation, open the Spark UI, sort the job's stages by duration, and note the longest stage's Shuffle Read, Shuffle Write and task count.
Why: This is the baseline to compare against.
You should see: Figures for the longest stage, plus any spill shown in its stage details.
3.Run spark.conf.set("spark.sql.shuffle.partitions", ...) with a different value in the same notebook.
Why: A notebook-level setting affects only this SparkSession, so other users of the cluster are unaffected.
You should see: Later queries in this notebook use the new value.
4.Re-run the same query and repeat the Spark UI checks: duration, spill, and Max versus 75th-percentile task duration.
Why: Re-measuring is the only way to tell whether the change helped.
You should see: A before-and-after comparison; if nothing improved, set the value back to auto.
Stuck? Get a nudge
Change only one parameter per run, or you won't know which change caused the difference.
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/optimizations/aqeOfficial docs
“Dynamically changes sort merge join into broadcast hash join.”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side“The threshold to trigger switching to broadcast join at runtime.”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side“the left relation of a LEFT OUTER JOIN cannot be broadcast.”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side“So using a broadcast hint can still be a good choice if you know your query well.”
↩︎ Exam trap 2“the left relation of a LEFT OUTER JOIN cannot be broadcast.”
↩︎ Exam trap 3“A statically planned broadcast join is usually more performant than a dynamically planned one by AQE”
↩︎ Prediction - 2.https://spark.apache.org/docs/latest/sql-performance-tuning.htmlSecondary source
“Configures the maximum size in bytes for a table that will be broadcast to all worker nodes when performing a join.”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side“By setting this value to -1, broadcasting can be disabled.”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side“Note that there is no guarantee that Spark will choose the join strategy specified in the hint”
↩︎ spark.sql.autoBroadcastJoinThreshold: when Spark broadcasts a join side - 3.
“Hardcoded values override the built-in optimizations that Databricks provides, often leading to wasted spend or degraded performance.”
↩︎ Executor and driver memory on Databricks“This determines how much data can be stored in memory before spilling it to disk.”
↩︎ Executor and driver memory on Databricks“Avoid hardcoding Spark configurations (such as spark.executor.memory or spark.dynamicAllocation.*) in compute or job definitions.”
↩︎ Exam trap 1 - 4.
“For example, set spark.executor.memory 4g in the Databricks cluster config.”
↩︎ Executor and driver memory on Databricks - 5.
“It's the ratio of cores to memory that matters here.”
↩︎ Executor and driver memory on Databricks“you can verify the issue by doubling the memory per core to see if it impacts your problem.”
↩︎ Checkpoint - 6.https://docs.databricks.com/aws/en/compute/configureOfficial docs
“You can choose a larger driver node type with more memory if you are planning to collect() a lot of data from Spark workers”
↩︎ Executor and driver memory on Databricks - 7.https://docs.databricks.com/aws/en/optimizations/spark-ui-guide/spark-driver-overloadedOfficial docs
“Databricks recommends you first try doubling the size of the driver and see how that impacts your job.”
↩︎ Executor and driver memory on Databricks - 8.
“Start by identifying the longest stage of the job.”
↩︎ Re-measure: reading the Spark UI before and after a change“If you see one task, that could be a sign of a problem.”
↩︎ Re-measure: reading the Spark UI before and after a change - 9.
“Spill is what happens when Spark runs low on execution memory.”
↩︎ Re-measure: reading the Spark UI before and after a change“If the Max duration is 50% more than the 75th percentile, you may be suffering from skew.”
↩︎ Re-measure: reading the Spark UI before and after a change
Also cited
“The guide contains the following steps in order:”
↩︎ Checkpoint