What you will be able to do
- Recognise data-skipping problems in a profile and choose the matching statistics or layout fix
- Spot exploding and over-reading joins, and confirm whether AQE switched a sort merge join to a broadcast hash join
- Diagnose shuffle-related spill and skew, and pick the matching remediation
1.Data-skipping inefficiencies: when scans read too much
Data skipping relies on per-file statistics: the minimum and maximum values, null counts and record totals for each file. If a filter can't be checked against those statistics, the scan opens every file and filters rows inside them. In a profile this shows up as a low pruning percentage on the Scan operator, with bytes read close to the full table size. Several performance insights name the exact cause. Each one points to a different fix.
| Insight in the profile | Recommendation |
|---|---|
| Table is clustered by keys that aren't used in filters during the scan | Add filters on the clustering keys |
| Table is partitioned by keys that aren't used in filters during the scan | Add filters on the partitioning keys |
| Delta data skipping statistics missing or incomplete, so in-file filtering is used | Collect Delta statistics |
| Table scan reads many small files | Enable Predictive Optimization, run OPTIMIZE, or switch partitioned tables to liquid clustering |
| Query projects all columns from the table | Project only the columns you need |
The statistics insight also reports a status for each filter, which tells you why skipping failed. Unused is the subtle case. Statistics exist, but the filter converts the column's data type, so they can't be applied. Collecting more statistics won't fix that; rewriting the predicate will.
Checkpoint 1 of 6· Match them up
Match each statistics status reported by the data-skipping insight to its meaning.
Tap a term, then the definition that fits it.
These four statuses come straight from the missing-statistics insight. Only Unused is fixed by changing the query rather than by collecting statistics.
“Unused: Statistics can't be used because the filter converts the data type.”Source: docs.databricks.com
For Unity Catalog external tables, statistics cover the first 32 columns by default. Predictive optimization on managed tables collects them on the columns used most often in filters. When the filtered column isn't covered, name it explicitly. Changing the property does not recompute statistics for existing data, so on Databricks Runtime 14.3 LTS and above you then trigger a recompute yourself.
ALTER TABLE table_name SET TBLPROPERTIES('delta.dataSkippingStatsColumns' = 'col1, col2, col3')Checkpoint 2 of 6· Fill the gap
After you change the statistics columns, which keyword completes the command that recomputes data-skipping statistics for existing files?
ANALYZE TABLE table_name COMPUTE ? STATISTICSANALYZE TABLE ... COMPUTE DELTA STATISTICS recomputes the per-file skipping statistics in the Delta log after you change the statistics columns.
Source: docs.databricks.com2.Join strategies: row explosion, over-reading, and AQE's switch to broadcast
For Join operators, compare rows in with rows out. The insights flag both directions. If a join produces significantly more rows than it reads, that's an exploding join: tighten the join condition or reduce the input on both sides. If it produces significantly fewer rows than it reads, most of the input was discarded, so filter before the join. A third insight catches an outer join that didn't change the outer side's row count and used no columns from the joined table. That join can be removed, or replaced with primary key or unique constraints. Missing cost-based optimizer statistics are flagged separately, because without them the planner falls back to standard heuristics when it picks a join plan. The guidance on joins backs this up: join the smallest tables first, avoid cross joins in latency-sensitive work, and keep statistics fresh with ANALYZE TABLE table_name COMPUTE STATISTICS or predictive optimization.
Adaptive query execution (AQE) is on by default. It applies to non-streaming queries that contain at least one exchange or subquery. One of its main jobs is to change a sort merge join into a broadcast hash join when runtime statistics show that one side is small enough. The runtime threshold is spark.databricks.adaptive.autoBroadcastJoinThreshold, which defaults to 30MB. If the physical join node in the final plan differs from the one in the initial plan, AQE made the switch. Broadcasting skips the shuffle of the large side, so the strategy chosen for a join directly affects shuffle cost. The profile may also show an Accelerated insight saying Databricks chose a broadcast join instead of shuffling, based on workload history. On compute with Photon enabled, the best join type is always selected for you.
Checkpoint 3 of 6· Check yourself
A Join operator in the profile outputs 900 million rows from inputs of 2 million and 5 million rows. What does the matching performance insight recommend?
Output far larger than input is the exploding-join insight. The fix is in the join logic. Changing the join strategy or file layout leaves the row explosion in place.
“The join produces significantly more rows than it reads.”Source: docs.databricks.com
Checkpoint 4 of 6· Exam question
A nightly pipeline joins a 2 TB fact table with a dimension table using a `/*+ BROADCAST(dim) */` hint that was added months ago when the dimension table was under 10 MB. The dimension table has since grown to roughly 6 GB. The query profile now shows the join node broadcasting a 6 GB object to every executor, followed by a long-running broadcast exchange stage and heavy spill to disk. What does the query profile most likely indicate, and what should the engineer do?
Correct answer: A — The stale `BROADCAST` hint is forcing a broadcast regardless of the dimension table's current size; removing the hint lets the optimizer choose a shuffle-based join strategy that fits the table's current size.
- A. A `BROADCAST` hint overrides `spark.sql.autoBroadcastJoinThreshold` and forces the hinted side to be broadcast no matter how large it has grown, which matches a 6 GB broadcast exchange and spill; removing the outdated hint lets the planner pick a shuffle join sized to the current data.
- B. Skew on the fact table's join key would show up as an uneven distribution of shuffle task times in a shuffle-based join, not as a broadcast exchange of the entire dimension table, so salting does not address what the profile is showing here.
- C. The number of shuffle partitions configured has no bearing on whether Spark chooses a broadcast join; a broadcast exchange bypasses the shuffle partitioning mechanism entirely, so this setting would not explain or fix the behavior.
- D. Missing statistics would make the optimizer more conservative about broadcasting, not less, and an explicit hint overrides statistics-based decisions anyway, so refreshing statistics would not remove a hint-forced broadcast.
3.Shuffle operations: spill, skew, and partition sizing
Joins, aggregations and windows all need a shuffle, and two problems tend to come with it. Spill happens when execution memory runs out and data is written to disk, which is most common during shuffling. The profile's spill insight recommends a larger warehouse for more memory, or fewer rows, fewer columns, or smaller large columns. Skew happens when one or a few tasks take much longer than the rest. The insight for uneven data distribution recommends key salting or pre-aggregation. On classic compute, the Spark UI test is the stage's Summary Metrics: if Max duration is more than 50% above the 75th percentile, you may have skew.
Spill happens when execution memory runs short. That memory is used for shuffles, joins, sorts and aggregations, and moving data from memory to disk is expensive. A plain scan-and-filter barely uses execution memory.
AQE also works on shuffles while the query runs. It coalesces small post-shuffle partitions into reasonably sized ones, and it splits skewed tasks in sort merge joins and shuffle hash joins. You can check the final plan to see whether it did: CustomShuffleReader with the Coalesced property means partitions were merged, and SortMergeJoin with isSkew set to true means skew handling kicked in.
| Property | Default | What it controls |
|---|---|---|
| spark.sql.shuffle.partitions | 200 | Partitions used when shuffling for joins or aggregations; auto enables auto-optimized shuffle |
| spark.sql.adaptive.coalescePartitions.enabled | true | Whether small post-shuffle partitions are coalesced |
| spark.sql.adaptive.advisoryPartitionSizeInBytes | 64MB | Target size of coalesced partitions |
| spark.databricks.adaptive.autoBroadcastJoinThreshold | 30MB | Threshold for switching to broadcast join at runtime |
Checkpoint 5 of 6· Check yourself
In a completed query's plan you find a SortMergeJoin node with isSkew as true. What does that tell you?
isSkew on SortMergeJoin is the plan marker for AQE's skew-join handling. Coalescing shows up as CustomShuffleReader with the Coalesced property instead.
“Dynamically handle skew join: node SortMergeJoin with field isSkew as true.”Source: docs.databricks.com
Checkpoint 6 of 6· Exam question
The query profile for a nightly aggregation job shows the exchange (shuffle) stage with `Spill (disk) bytes` in the tens of gigabytes, and several tasks report `Peak memory` well above the executor memory available per core. Which two changes would reduce this shuffle spill without changing the query's output? (Choose 2)(Select 2)
Correct answers: A, B — Increase `spark.sql.shuffle.partitions` so each shuffle task processes a smaller slice of the data and needs less memory to hold its partition before spilling.; Scale up to a worker type with more memory per core, giving each shuffle task additional headroom to hold its partition in memory instead of spilling to disk.
- A. More shuffle partitions means each task holds a smaller slice of the aggregation's grouped data in memory, which directly reduces the chance that a task exceeds its memory budget and spills to disk.
- B. A worker type with more memory per core gives each shuffle task more headroom before it has to spill its in-memory buffer to disk, directly reducing spill bytes for the same partition sizes.
- C. A `BROADCAST` hint applies to joins, not to a `GROUP BY` aggregation; an aggregation over a single large table still requires a shuffle to co-locate matching keys, so this hint would not remove the exchange stage.
- D. `ZORDER BY` is a Delta table maintenance command that reorders data files on disk; it is not a valid `GROUP BY` clause and has no effect on whether a runtime shuffle is required for an aggregation query.
- E. Disabling Adaptive Query Execution removes automatic partition coalescing but does not reduce how much data each statically sized partition holds, so it does not address the underlying cause of the spill.
- F. The disk cache stores copies of remote source files for faster repeated reads; shuffle spill files are already local temporary data written by the executor, so the disk cache has no role in serving them.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.SQL EXPLAIN shows the join strategy that will actually run.Why is that wrong?
EXPLAIN doesn't execute the query, so it shows only the initial plan. AQE can still swap a sort merge join for a broadcast hash join at runtime. Check the final plan or the profile to see what ran.
Covered in Join strategies: row explosion, over-reading, and AQE's switch to broadcast
2.Whenever the data-skipping insight fires, running ANALYZE to collect more statistics will fix it.Why is that wrong?
If the status is Unused, statistics exist but the filter converts the data type, so the predicate has to be rewritten. ANALYZE won't help.
Covered in Data-skipping inefficiencies: when scans read too much
3.Spill means the disks are too slow, so faster storage is the fix.Why is that wrong?
Spill means the data didn't fit in memory. The insight recommends a larger warehouse for more memory, or less data per row and fewer rows.
Covered in Shuffle operations: spill, skew, and partition sizing
Practise it for real
Use the query profile to confirm and fix a data-skipping problem on a Delta table, then measure the improvement.
1.Run a selective query that filters on one column of a Delta table, then open its profile from Query History and note the pruning percentage and bytes read on the Scan.
Why: This gives you a baseline from the filter icons and IO metrics.
You should see: A low pruning percentage, and possibly a Performance insight saying Delta data skipping statistics are missing or incomplete.
2.Run ALTER TABLE table_name SET TBLPROPERTIES('delta.dataSkippingStatsColumns' = 'col1, col2, col3') with your filter column in the list.
Why: Without statistics on the filter column, the scan can't skip files.
You should see: The property is set, but existing files are not re-analyzed yet.
3.Run ANALYZE TABLE table_name COMPUTE DELTA STATISTICS.
Why: Changing the property affects only future writes until you recompute.
You should see: Statistics for existing files are recomputed in the Delta log.
4.Re-run the query with a trivial change, such as a different LIMIT, and open the new profile.
Why: A result served from the query cache has no profile to compare.
You should see: A higher pruning percentage and fewer bytes read on the Scan operator.
Stuck? Get a nudge
If the insight's statistics status says Unused, look at the WHERE clause for a cast on the filtered column before you touch table properties.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Databricks uses per-file statistics (minimum and maximum values, null counts, and total records) at query time to skip irrelevant files and speed up queries.”
↩︎ Data-skipping inefficiencies: when scans read too much“For Unity Catalog external tables, statistics are collected on the first 32 columns defined in your table schema by default.”
↩︎ Data-skipping inefficiencies: when scans read too much“Updating these properties doesn't automatically recompute statistics for existing data.”
↩︎ Data-skipping inefficiencies: when scans read too much - 2.
“Delta data skipping statistics are missing or incomplete for the table scan file filters, so the query uses in-file filtering.”
↩︎ Data-skipping inefficiencies: when scans read too much“Switch to liquid clustering for partitioned tables, because Predictive Optimization can't compact small files across partitions.”
↩︎ Data-skipping inefficiencies: when scans read too much“Recommendation: Determine which result subset you need, then add filters before the join to reduce input rows.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“An outer join did not change the outer side's row count, and no columns from the joined table are used.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“Cost-based optimizer statistics are missing or incomplete, so the query plan uses standard heuristics.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“It chose a broadcast join instead of shuffling data across the cluster, based on your workload history.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“Recommendation: Review the data distribution, then use key salting or pre-aggregation to balance the workload.”
↩︎ Shuffle operations: spill, skew, and partition sizing“Unused: Statistics can't be used because the filter converts the data type.”
↩︎ Exam trap 2“Data spilled to disk during query execution because the data did not fit in memory.”
↩︎ Exam trap 3“Unused: Statistics can't be used because the filter converts the data type.”
↩︎ Checkpoint“The join produces significantly more rows than it reads.”
↩︎ Checkpoint - 3.
“Join order matters. When performing multiple joins, always join your smallest tables first and then join the result with larger tables.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“Compute with Photon enabled always selects the best join type.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast - 4.https://docs.databricks.com/aws/en/optimizations/aqeOfficial docs
“Dynamically changes sort merge join into broadcast hash join.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“The threshold to trigger switching to broadcast join at runtime.”
↩︎ Join strategies: row explosion, over-reading, and AQE's switch to broadcast“Dynamically handles skew in sort merge join and shuffle hash join by splitting (and replicating if needed) skewed tasks into roughly evenly sized tasks.”
↩︎ Shuffle operations: spill, skew, and partition sizing“Setting the value auto enables auto-optimized shuffle, which automatically determines this number based on the query plan and the query input data size.”
↩︎ Shuffle operations: spill, skew, and partition sizing“As SQL EXPLAIN does not execute the query, the current plan is always the same as the initial plan”
↩︎ Exam trap 1“As SQL EXPLAIN does not execute the query, the current plan is always the same as the initial plan”
↩︎ Prediction“Dynamically handle skew join: node SortMergeJoin with field isSkew as true.”
↩︎ Checkpoint - 5.
“It starts to move data from memory to disk, which can be expensive. It is most common during data shuffling.”
↩︎ Shuffle operations: spill, skew, and partition sizing“If the Max duration is 50% more than the 75th percentile, you may be suffering from skew.”
↩︎ Shuffle operations: spill, skew, and partition sizing