Query Engine Tuning Basics

Who this is for

This subskill is for data platform engineers, analytics engineers, and anyone who reads query plans and needs predictable, fast SQL on warehouses and lakehouses.

Prerequisites

• Comfortable with SQL (joins, aggregates, window functions).
• Basic understanding of your platform's storage (tables, partitions, files).
• Ability to view an execution plan (EXPLAIN or UI in your engine).

Why this matters

Real tasks you will face:

• Reducing a 20-minute dashboard query to under 1 minute without adding hardware.
• Stabilizing an ELT job that sometimes spills to disk or times out.
• Keeping costs predictable by minimizing scanned bytes and shuffles.

Concept explained simply

A query engine turns SQL into a plan: scan data, filter, join, aggregate, and sort. Each step has a cost: reading bytes, moving data across nodes (shuffle), keeping state in memory, or spilling to disk. Tuning is about reducing these costs by helping the optimizer make better decisions and by writing SQL that is easy to optimize.

Mental model

Imagine water flowing through pipes:

• Scans are water intake. Partition pruning and predicate pushdown narrow the intake.
• Joins are junctions. Broadcast is a small sidecar tank; shuffle is a big mixing station.
• Aggregations are filters that reduce flow early if possible (pre-aggregate).
• Sorts/window functions are expensive sieves; keep their input small.

Core techniques you will use often

• Partition pruning: filter directly on partition columns with sargable predicates (no functions on the column).
• Predicate pushdown: avoid wrapping columns in functions in WHERE/JOIN conditions.
• Join strategy: broadcast small tables; use sort-merge or hash for large-to-large; avoid nested loops on big sets.
• Skew handling: detect hot keys; use salting, skew join hints, or pre-aggregation by key.
• Data layout: appropriate partitioning, clustering/sorting (e.g., by frequently filtered columns), and avoiding tiny files.
• Memory and spill: right-size shuffle partitions, limit parallelism if needed, and reduce row size early (project needed columns).
• Caching/materialization: cache small dimensions or use materialized views for repeated heavy aggregations.
• SQL hygiene: avoid SELECT *, unnecessary DISTINCT, and global ORDER BY unless required.

Worked examples

SELECT f.date, d.segment, SUM(f.revenue) AS rev
FROM fact_sales f
JOIN dim_customer d ON f.customer_id = d.customer_id
WHERE f.date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1,2;

-- Pseudocode with a broadcast hint
SELECT /*+ BROADCAST(d) */ f.date, d.segment, SUM(f.revenue) AS rev
FROM fact_sales f
JOIN dim_customer d USING (customer_id)
WHERE f.date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY f.date, d.segment;

-- Bad (non-sargable)
WHERE DATE(f.event_ts) = '2025-01-01'

-- Good (sargable)
WHERE f.event_date = '2025-01-01'

-- Salting approach (conceptual)
SELECT customer_id, salt, SUM(revenue) rev
FROM (
  SELECT customer_id,
         CASE WHEN customer_id = 0 THEN MOD(rand_seeded, 16) ELSE 0 END AS salt,
         revenue
  FROM fact_sales
) s
GROUP BY customer_id, salt; -- then aggregate again without salt

SELECT DISTINCT *
FROM big_table
WHERE region = 'EU';

SELECT account_id, region, updated_at
FROM big_table
WHERE region = 'EU';

Learning path

1. Read plans: learn to spot scans, shuffles, broadcasts, sorts.
2. Partition pruning: rewrite filters to be sargable and verify pruned partitions.
3. Join strategies: practice choosing between broadcast and shuffle joins.
4. Handle skew: detect hot keys and apply salting or pre-aggregation.
5. Optimize window/sort: reduce input size before expensive operations.
6. Materialize/cache where it pays off; remove unnecessary DISTINCT/ORDER BY.
7. Do the exercises below, then take the Quick Test.

Common mistakes and self-check

• Using functions on partition columns. Self-check: Does the plan show partition pruning? If not, rewrite filters.
• Forcing large-to-large broadcast or preventing a broadcast of small tables. Self-check: Check join strategy in the plan.
• Global ORDER BY for non-user-facing jobs. Self-check: If sort is not needed, remove it.
• SELECT * inflating row size. Self-check: Are there columns unused downstream? Project only what you need.
• Ignoring skew. Self-check: One task much slower than others? Investigate key distribution.
• Too many tiny files. Self-check: Does the scan stage open thousands of files? Consider compaction.

Practical projects

• Take one slow BI query from your environment, reduce runtime by 50% using only SQL and data layout tweaks.
• Create a small benchmark: 3 queries (filter, big join, window). Measure runtimes and scanned bytes before and after tuning.
• Design a partitioning and clustering scheme for a new events table; validate with two representative queries.

Exercises

Do these in order. They mirror the exercises below and include a checklist. Solutions are available in the collapsible sections.

SELECT d.country, COUNT(*) AS c
FROM sales s
JOIN dim_geo d ON CAST(s.geo_id AS STRING) = d.geo_id
WHERE DATE(s.event_ts) BETWEEN '2025-03-01' AND '2025-03-07'
GROUP BY d.country;

Checklist before you run

• Filters are on partition columns without wrapping functions.
• Only necessary columns are selected before heavy operations.
• Join keys have matching data types.
• Small tables are broadcast where appropriate.
• Skewed keys are mitigated (salting, pre-aggregation, or engine hint).

Mini tasks

• Open a recent slow query plan. Identify the top two costliest stages and write one change to reduce each.
• List your three most common filters. Are those columns partitioned or clustered? If not, why?
• Find one DISTINCT you can remove safely by changing upstream logic.

Next steps

• Deep-dive into join algorithms on your engine (broadcast thresholds, adaptive execution).
• Master data layout: partitioning strategies, clustering/sorting, file sizing and compaction.
• Learn to use materialized views and result caching strategically.