Performance Monitoring and Tuning · Topic 3 of 16

Efficient Queries - Compile and Runtime Optimization, Joins, Grouping and Sorting

Write efficient Snowflake queries: compile versus runtime optimization, the execution path, effective join conditions, grouping, sorting and ordering.

  • 5 min
In a nutshell
  • Compile happens in cloud services using metadata; runtime happens on the warehouse.
  • Pruning is decided from metadata; JoinFilter is a runtime reduction.
  • Read the plan bottom up: reduce rows early, columns early, sort last.

What it is

An efficient query reads as little data as it can, carries as few rows and columns as it can through each step, and uses the cheapest operator for the job. Snowflake's optimizer does much of this for you, so the administrator's work is to avoid writing SQL that defeats it.

The exam asks which rewrite or setting speeds up a slow query, and which stage of processing a given optimization belongs to.

SQL textsubmittedparse and optimizeCompilecloud services, metadataplanExecutewarehouse, micro partitionsrowsResultreturned or cached

How it works

Compile time versus runtime

StageWhere it runsWhat happens
CompileCloud services layerParse, check privileges, build the plan, choose join order, push filters down, and decide which micro partitions to scan from metadata
RuntimeVirtual warehouseRead the chosen partitions, run operators, spill if memory runs out, and apply runtime filters such as a JoinFilter

Compile time work uses only metadata, so it needs no warehouse. Partition pruning decided from min and max values in metadata is an example. A query served from the result cache, or answered from metadata, skips the runtime stage altogether.

Runtime work depends on the data actually flowing. A JoinFilter node, for instance, drops rows that cannot match a later join as the data arrives. In QUERY_HISTORY, COMPILATION_TIME and EXECUTION_TIME let you tell which side is slow.

Exam tip

High compilation time with fast execution points at very complex SQL, such as deeply nested views. A bigger warehouse will not help because compilation does not run on it.

The execution path

Reading a plan from the bottom up gives the execution path: scan, filter, join, aggregate, sort, return. The administrator can show it before running with EXPLAIN, or after running with the Query Profile. Articulating the path means naming each step and where rows are reduced or multiplied.

An efficient path reduces rows early. Filters go as close to the scan as possible, only needed columns are read, and big sorts happen last on small data.

Habits that make queries efficient

  • Select only the columns you need. Storage is columnar, so fewer columns mean fewer bytes read. Avoid SELECT * on wide tables.
  • Filter on columns that prune. Filter on the clustering or natural ordering column, and avoid wrapping that column in a function.
  • Use LIMIT with ORDER BY when you need only the top rows, so Snowflake can use SortWithLimit.
  • Reuse work. Put repeated sub queries in a common table expression or a temporary table.
  • Use approximate functions such as APPROX_COUNT_DISTINCT when an estimate is enough for a big table.

Effective joining conditions

A join is efficient when the condition is selective and uses equality on columns of the same data type.

PatternEffect
Equality join on matching typesFast; the optimizer can build a lookup of the smaller input
Missing join conditionCartesian product; join explosion
Condition that matches many rows on both sidesRow multiplication, high output row count
Non equality or OR conditionsSlower; the engine cannot use the simple equality approach
Function on a join keyCan block optimizations; clean the data once instead

The telltale sign in the Query Profile is a Join node whose output rows are far greater than its inputs. The fix is to add or correct the condition, remove duplicates in one input first, or join on a key that is unique on at least one side.

-- Risky: no condition, every row meets every row
SELECT o.order_id, c.name FROM orders o, customers c;

-- Efficient: equality on the key, filter early
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

Grouping, sorting and ordering

GROUP BY becomes an Aggregate node, and ORDER BY becomes a Sort node. Both need memory, so large inputs can spill to local and then remote storage.

  • Reduce the input first with a WHERE clause before grouping.
  • Group by fewer, lower cardinality columns when you can; very high cardinality grouping keeps huge state in memory.
  • Sort only at the end and only if needed. Sorting inside a sub query or a CTE is usually wasted work.
  • Pair ORDER BY with LIMIT for top N results.
  • Use QUALIFY with a window function to filter ranked rows without an extra sub query.
Filter earlyWHEREfewer rowsAggregateGROUP BYfewer rowsSort and LIMITtop Nsmall resultResult

Common mistakes

Blaming the warehouse size for slow compilation. Compile time runs in cloud services.

Joining with a comma and forgetting the condition. The result is a Cartesian product.

Putting ORDER BY in every sub query. Only the outermost ordering matters for the final output.

Using SELECT DISTINCT to hide a bad join. It removes the duplicates afterward but does not stop the explosion that created them.

What to remember for the exam

  • Compile happens in cloud services using metadata; runtime happens on the warehouse.
  • Pruning is decided from metadata; JoinFilter is a runtime reduction.
  • Read the plan bottom up: reduce rows early, columns early, sort last.
  • Missing or loose join conditions cause join explosion; use equality on matching data types.
  • ORDER BY with LIMIT is cheaper than a full sort.

Check yourself

  • Which layer compiles a query?

    The cloud services layer, using metadata and without a running warehouse.

  • A join returns many more rows than either input. What do you check?

    The join condition, for a missing condition or a many to many match.

  • Why add LIMIT to an ORDER BY for top results?

    It allows a sort with limit instead of a full sort of the whole set.

Topic 3 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.