05.7 SQL Query Optimization & Query Plans

Writing correct SQL is only half the job. At enterprise scale, an analytics engineer must understand what the engine actually executes.

1. Learning objectives

By the end of this topic you should be able to:

• Distinguish logical, optimized logical, physical, and executed plans.
• Use EXPLAIN and EXPLAIN FORMATTED systematically.
• Read scans, filters, joins, exchanges, aggregates, sorts, and windows.
• Diagnose full scans, excessive shuffles, skew, spills, exploding joins, and bad cardinality.
• Understand predicate and column pruning.
• Use Query History and Query Profile for production troubleshooting.
• Separate query problems from SQL warehouse sizing problems.
• Build a repeatable optimization workflow.

2. Optimization mental model

Business requirement
       ↓
SQL expression
       ↓
Logical plan
       ↓
Optimized logical plan
       ↓
Physical plan
       ↓
Distributed execution
       ↓
Query Profile / runtime metrics

Your job is not to make SQL look clever. Your job is to reduce unnecessary work while preserving semantics.

3. The four optimization questions

1. Read less — Can the engine avoid files, rows, and columns?
2. Move less — Can we avoid or reduce shuffles?
3. Compute less — Can filters, aggregation, and joins happen earlier or more efficiently?
4. Prove it — Did the runtime metrics actually improve?

4. EXPLAIN

EXPLAIN FORMATTED
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY customer_id;

Use the plan to ask:

• Where is the scan?
• Are predicates pushed into the scan?
• Which operator introduces an exchange?
• Where does sorting occur?
• Which join strategy was selected?
• Is there an unexpected projection or cast?
• Is aggregation happening before or after large data movement?

5. Reading the physical plan

Operator	Meaning	What to inspect
Scan	Reads source data	files, bytes, pruning
Filter	Removes rows	predicate selectivity
Project	Selects/calculates columns	unnecessary columns
Exchange	Redistributes data	shuffle volume
HashAggregate	Groups and aggregates	cardinality, memory
Sort	Orders data	rows, spill
Join	Combines relations	strategy, size, skew
Window	Row-level analytics	partition/order cost
Union	Combines datasets	duplicated input work

An Exchange is particularly important because it means data has to be redistributed across execution boundaries.

6. Query Profile — production microscope

Databricks Query Profile provides an interactive DAG and operator metrics such as time, rows, memory, and I/O. It can expose full scans, exploding joins, and other bottlenecks. Official Query Profile documentation.

Typical workflow:

Query History → slow query → Query Profile → Top operators → dominant bottleneck → one change → re-run → compare

Wall-clock time is not the same as aggregate task time because work is parallelized across compute resources.

7. Query History

Query History can be filtered by user, date, compute, duration, status, statement type, statement ID, and query tags. Official Query History documentation.

Use it to answer:

• Which queries are slowest?
• Which workloads fail?
• Which users/applications consume the warehouse?
• Are queries waiting for capacity?
• Did a query regress after a release?
• Is the issue query-specific or warehouse-wide?

Databricks also exposes query history through system.query.history and APIs for programmatic monitoring.

8. Scan optimization

Bad:

SELECT * FROM sales WHERE customer_id = 'C100';

Better:

SELECT order_id, order_date, amount
FROM sales
WHERE customer_id = 'C100';

Column pruning avoids unnecessary columns. Predicate pushdown applies filters as close to the data source as possible.

9. Partition pruning and data skipping

Conceptually:

Table: 100 TB → filter → candidate data: 8 TB → actually read: 1.2 TB

The objective is not merely shorter SQL; it is less physical data read.

10. Filter early — but preserve semantics

SELECT c.customer_name, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= DATE '2026-01-01'
GROUP BY c.customer_name;

Filtering early can reduce join and aggregation work. But moving predicates across an outer join can change semantics, so correctness always constrains optimization.

11. Projection pruning

Bad pipeline: 100 columns → join → window → aggregate → select 5.

Better: select required columns → join → window/aggregate.

Carry only what downstream operators need.

12. Join optimization

Ask:

1. How many rows enter each side?
2. What is the join cardinality?
3. Can the smaller side be broadcast?
4. Is the join key skewed?
5. Are filters applied before the join?
6. Are duplicate keys causing row explosion?
7. Does the physical plan match expectations?

A many-to-many join can multiply rows dramatically. That is often a grain/data-model problem before it is a tuning problem.

13. Broadcast joins

When one side is sufficiently small, a broadcast strategy can avoid a large shuffle of the other side. Do not force broadcast blindly; inspect actual size, memory, concurrency, and the physical plan.

14. Shuffle optimization

Typical causes: joins, aggregations, windows, global ordering, and repartitioning.

Reduce shuffle by filtering earlier, selecting fewer columns, pre-aggregating, choosing appropriate join strategies, avoiding unnecessary global sorts, and controlling skew.

15. Aggregation optimization

If 10 billion events ultimately become daily customer totals, reduce the grain before expensive downstream work:

10B events → filter → customer/day aggregate → 50M rows → downstream join/analytics

Pre-aggregation is safe only when the required business result is preserved.

16. Window optimization

Optimize windows by filtering unnecessary rows first, projecting only required columns, avoiding huge partitions, using deterministic but minimal ordering keys, combining compatible windows where possible, and replacing a window with GROUP BY when row-level output is unnecessary.

17. Sorts and global ORDER BY

Global ordering can be expensive because distributed data must be coordinated. Ask whether the consumer truly needs globally sorted output, whether ORDER BY ... LIMIT N is sufficient, or whether sorting can happen after reducing the result set.

18. CTEs are readability tools, not automatic materialization

A CTE improves logical organization, but do not assume it creates a physical intermediate dataset. The optimizer can transform the logical plan. Use EXPLAIN to see what actually happens.

19. Query cache vs execution performance

A query returning instantly does not necessarily mean the SQL itself is optimized. Databricks notes that Query Profile is unavailable for queries served from the query cache. For benchmarking, control caching conditions, run multiple trials, and compare bytes read and execution time.

20. Query problem vs warehouse problem

Symptom	Likely query issue	Likely warehouse issue
Full table scan	Yes	No
Exploding join	Yes	No
Massive shuffle	Often	Sometimes
One skewed task	Yes	Sometimes
Many queries waiting for capacity	No	Yes
Broad memory pressure	Sometimes	Yes

Do not solve every slow query by making the warehouse larger.

21. Warehouse monitoring

Databricks documents system.query.history patterns for monitoring performance, usage, and cost. Warehouse monitoring documentation.

Example:

SELECT statement_id, executed_by, total_duration_ms, execution_duration_ms,
       waiting_at_capacity_duration_ms, read_rows, produced_rows, statement_text
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 1 DAY
ORDER BY total_duration_ms DESC
LIMIT 50;

Queries waiting for capacity:

SELECT statement_id, total_duration_ms, waiting_at_capacity_duration_ms, statement_text
FROM system.query.history
WHERE start_time >= NOW() - INTERVAL 7 DAY
  AND waiting_at_capacity_duration_ms > 0
ORDER BY waiting_at_capacity_duration_ms DESC;

22. Performance insights

Databricks can surface performance insights such as small-file compaction, statistics collection, or compute resizing recommendations. Treat these as evidence, then understand the underlying cause.

23. Repeatable optimization workflow

Step 1 — Establish the baseline

Record wall-clock duration, execution duration, rows/bytes read, rows returned, queue time, spill, and warehouse configuration.

Step 2 — Inspect the profile

Find the longest operators and largest data movements.

Step 3 — Inspect the plan

Use EXPLAIN FORMATTED.

Step 4 — Classify the bottleneck

Scan → pruning / files / columns
Join → cardinality / strategy / skew
Shuffle → movement / partitioning
Aggregate → cardinality / memory
Sort → ordering / spill
Window → partition / order / skew
Queue → warehouse capacity

Step 5 — Make one meaningful change

Do not change ten things simultaneously.

Step 6 — Re-measure

Compare the same workload.

Step 7 — Document the reason

Record the before/after metric and why the change worked.

24. Enterprise example — 4 TB sales query

Initial: 4 TB input, 100% scan, many-to-many join, 2.1 TB shuffle, 19 minutes.

Investigation: filter was late, dimension keys were duplicated, 80 columns were carried though 12 were needed, and a global ORDER BY existed only for presentation.

Redesign:

Filter early → select 12 columns → deduplicate dimension → appropriate join → pre-aggregate → remove unnecessary global sort

The lesson is the method: measure → identify dominant work → change one bottleneck → prove improvement.

25. Hands-on labs

Lab A — Read the plan

Create fact and dimension tables. Run a filtered aggregation and identify scan, filter, exchange, and aggregate operators.

Lab B — Exploding join

Create duplicate dimension keys intentionally. Measure row multiplication before and after correcting the grain.

Lab C — Projection pruning

Compare SELECT * with required columns and inspect bytes read and runtime.

Lab D — Filter placement

Compare filtering before versus after a large join where semantics allow it.

Lab E — Warehouse diagnosis

Use system.query.history to identify slow queries and queries waiting for capacity.

Lab F — Performance regression

Run the same workload before and after an optimization and record runtime, bytes read, rows read, shuffle/spill, and cost proxy.

26. Architect decision matrix

Bottleneck	First investigation	Typical action
Too much data read	Scan/profile	Filter, pruning, projection
Join explosion	Cardinality	Fix grain / deduplicate
Large shuffle	Physical plan	Reduce movement / strategy
Skew	Task distribution	Rebalance / redesign
Sort spill	Profile	Reduce rows/columns or adjust compute
Window bottleneck	Partition sizes	Reduce input / improve grain
Queue time	Query history	Capacity/concurrency tuning
Repeated expensive computation	Workload pattern	Materialize/precompute where justified

27. Interview questions

1. What is the difference between a logical and physical plan?
2. What does EXPLAIN do?
3. What is an Exchange?
4. What is predicate pushdown?
5. What is column pruning?
6. A query scans 10 TB to return 1 GB. What do you investigate?
7. A join suddenly takes 20 minutes. How do you diagnose it?
8. One task runs much longer than the others. What does that suggest?
9. What does spill indicate?
10. How do you distinguish query inefficiency from warehouse under-sizing?
11. How would you establish a performance baseline?
12. How do you optimize a 5 TB dashboard workload?
13. When would you pre-aggregate?
14. How would you design SQL performance governance across hundreds of workloads?

28. Production checklist

• Business grain is explicit.
• Required columns only.
• Selective predicates are applied as early as semantics allow.
• Join cardinality is understood.
• Duplicate keys are intentional.
• No unnecessary global sort.
• Window partitions are reasonable.
• Shuffle is understood.
• Query Profile has been inspected.
• Baseline metrics are recorded.
• Warehouse capacity has been checked.
• Cost impact has been considered.
• Correctness has been validated.

29. Key takeaway

SQL optimization is not about writing clever SQL. It is about understanding the work the engine performs and removing unnecessary work.

Your enterprise mental model should be: Read less → move less → compute less → measure → prove.

Next: 05.8 SQL Query History, Monitoring & Cost Optimization.

Official references

• Databricks Query Profile
• Databricks Query History
• SQL warehouse monitoring
• Query history system table