Performance Optimization, Querying, and Transformation · Topic 6 of 6
Aggregate Functions, Window Functions and SQL for Optimization
Use Snowflake aggregate and window functions, QUALIFY and approximate functions, and write SQL that prunes, scans fewer columns and avoids costly joins.
- 5 min
COUNT(*)counts all rows; other aggregates ignore NULLs.APPROX_COUNT_DISTINCTuses HyperLogLog for fast, approximate distinct counts.WHEREfilters rows before grouping,HAVINGfilters groups after,QUALIFYfilters window results.
What it is
Aggregate functions collapse many rows into one value per group, such as a total per region. Window functions compute a value across related rows but keep every row, such as a rank within each region or a running total.
Applying SQL for query optimization means writing those queries so Snowflake reads as little data as possible. Snowflake stores data in columns and prunes micro-partitions, so the shape of your SQL directly changes how much is scanned.
How it works
Aggregate functions
| Function | Returns | Note |
|---|---|---|
COUNT(*), COUNT(col) | Row count, non NULL count | COUNT(*) counts all rows; COUNT(col) skips NULLs |
COUNT_IF(condition) | Rows where the condition is true | Shorter than SUM(CASE ...) |
SUM, AVG, MIN, MAX, MEDIAN | Numeric summaries | Ignore NULL values |
LISTAGG, ARRAY_AGG | Concatenated string, ARRAY | Combine values from many rows |
APPROX_COUNT_DISTINCT | Estimated distinct count | Uses HyperLogLog; much cheaper than COUNT(DISTINCT) on large data |
APPROX_PERCENTILE, APPROX_TOP_K | Estimated percentile, most frequent values | Fast estimates for large tables |
Grouping options:
GROUP BYlists the grouping columns;GROUP BY ALLgroups by every non aggregate column in theSELECTlist.HAVINGfilters groups after aggregation, whileWHEREfilters rows before it.GROUP BY ROLLUP,CUBEandGROUPING SETSadd subtotal and grand total rows in one query.
Window functions
A window function uses OVER (PARTITION BY ... ORDER BY ...). PARTITION BY defines the group, ORDER BY defines the order inside it, and an optional frame such as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines which rows count.
| Function | Use |
|---|---|
ROW_NUMBER() | Unique sequence in each partition; ties get different numbers |
RANK() | Ties share a rank, and the next rank is skipped (1, 1, 3) |
DENSE_RANK() | Ties share a rank, with no gaps (1, 1, 2) |
LAG, LEAD | Value from the previous or next row, for period over period change |
FIRST_VALUE, LAST_VALUE | First or last value in the window |
SUM(...) OVER (ORDER BY ...) | Running total |
NTILE(n) | Split rows into n buckets |
QUALIFY filters on the result of a window function, the way HAVING filters on an aggregate. It removes the need for a subquery:
SELECT customer_id, order_id, order_ts
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1;
This returns each customer's latest order, a common deduplication pattern.
If the question says "keep every row and add a rank or running total", the answer is a window function. If it says "one row per group", it is an aggregate with GROUP BY.
Applying SQL for query optimization
These habits reduce the bytes scanned and the work done:
| Practice | Why it helps |
|---|---|
Select only the columns you need, not SELECT * | Columnar storage reads only the named columns |
| Filter on columns the table is naturally ordered or clustered by, such as a date | Metadata pruning skips whole micro-partitions |
Compare raw columns, not functions of them, in WHERE | A function or cast on the column can stop pruning |
| Filter and aggregate before a join | Fewer rows reach the join and less memory is used |
| Join on proper keys | Avoids exploding joins and cartesian products |
Use UNION ALL when duplicates are impossible or acceptable | UNION adds a costly duplicate removal step |
| Use approximate functions for large distinct counts | Far less memory than an exact COUNT(DISTINCT) |
Use QUALIFY instead of a self join or nested subquery | One pass over the data |
Add LIMIT when exploring data | Stops early instead of returning every row |
EXPLAIN shows the plan without running the query, including total partitions and the partitions assigned after compile time pruning, which helps check whether a filter is pruning well.
Common mistakes
Using HAVING to filter rows before grouping. HAVING runs after aggregation, so row filters belong in WHERE. Moving them to WHERE also reduces the data that is grouped.
Mixing up RANK and DENSE_RANK. With two rows tied for first, RANK gives the next row 3 and DENSE_RANK gives it 2. ROW_NUMBER gives the tied rows 1 and 2.
Wrapping the filter column in a function. WHERE TO_CHAR(order_date, 'YYYY') = '2026' makes pruning harder than WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01', which compares the raw column with a range.
SELECT * on a wide table reads every column from storage. In a columnar engine that is the most common avoidable cost, and the exam treats "select only needed columns" as a basic optimization.
What to remember for the exam
COUNT(*)counts all rows; other aggregates ignore NULLs.APPROX_COUNT_DISTINCTuses HyperLogLog for fast, approximate distinct counts.WHEREfilters rows before grouping,HAVINGfilters groups after,QUALIFYfilters window results.- Window functions keep every row;
PARTITION BYgroups,ORDER BYorders, the frame sets the rows used. ROW_NUMBERis unique,RANKleaves gaps after ties,DENSE_RANKdoes not.- Optimize by selecting fewer columns, filtering on raw prunable columns, reducing rows before joins and using
UNION ALLwhere safe.
Check yourself
How do you keep only the most recent order per customer without a subquery?
Use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) with QUALIFY to keep rows equal to 1.
A report needs the number of distinct visitors in a ten billion row table and a small error is acceptable. Which function fits?
APPROX_COUNT_DISTINCT, which estimates distinct values with HyperLogLog using far less memory than COUNT(DISTINCT).
Why is UNION ALL usually faster than UNION?
UNION removes duplicate rows, which needs an extra sort or hash step, while UNION ALL simply appends the results.
Topic 6 of 6 in Performance Optimization, Querying, and Transformation
Test yourself on Performance Optimization, Querying, and Transformation
The full Snowflake SnowPro Core COF-C03 question bank has 845 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.