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
- Compile happens in cloud services using metadata; runtime happens on the warehouse.
- Pruning is decided from metadata;
JoinFilteris 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.
How it works
Compile time versus runtime
| Stage | Where it runs | What happens |
|---|---|---|
| Compile | Cloud services layer | Parse, check privileges, build the plan, choose join order, push filters down, and decide which micro partitions to scan from metadata |
| Runtime | Virtual warehouse | Read 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.
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
LIMITwithORDER BYwhen you need only the top rows, so Snowflake can useSortWithLimit. - Reuse work. Put repeated sub queries in a common table expression or a temporary table.
- Use approximate functions such as
APPROX_COUNT_DISTINCTwhen 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.
| Pattern | Effect |
|---|---|
| Equality join on matching types | Fast; the optimizer can build a lookup of the smaller input |
| Missing join condition | Cartesian product; join explosion |
| Condition that matches many rows on both sides | Row multiplication, high output row count |
Non equality or OR conditions | Slower; the engine cannot use the simple equality approach |
| Function on a join key | Can 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
WHEREclause 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 BYwithLIMITfor top N results. - Use
QUALIFYwith a window function to filter ranked rows without an extra sub query.
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;
JoinFilteris 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 BYwithLIMITis 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
LIMITto anORDER BYfor 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.