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
In a nutshell
  • COUNT(*) counts all rows; other aggregates ignore NULLs.
  • APPROX_COUNT_DISTINCT uses HyperLogLog for fast, approximate distinct counts.
  • WHERE filters rows before grouping, HAVING filters groups after, QUALIFY filters 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

FunctionReturnsNote
COUNT(*), COUNT(col)Row count, non NULL countCOUNT(*) counts all rows; COUNT(col) skips NULLs
COUNT_IF(condition)Rows where the condition is trueShorter than SUM(CASE ...)
SUM, AVG, MIN, MAX, MEDIANNumeric summariesIgnore NULL values
LISTAGG, ARRAY_AGGConcatenated string, ARRAYCombine values from many rows
APPROX_COUNT_DISTINCTEstimated distinct countUses HyperLogLog; much cheaper than COUNT(DISTINCT) on large data
APPROX_PERCENTILE, APPROX_TOP_KEstimated percentile, most frequent valuesFast estimates for large tables

Grouping options:

  • GROUP BY lists the grouping columns; GROUP BY ALL groups by every non aggregate column in the SELECT list.
  • HAVING filters groups after aggregation, while WHERE filters rows before it.
  • GROUP BY ROLLUP, CUBE and GROUPING SETS add subtotal and grand total rows in one query.
RowsWHERE filtersRemaining rowsGROUP BYGroupsaggregateOne row per groupHAVING filtersResult

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.

FunctionUse
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, LEADValue from the previous or next row, for period over period change
FIRST_VALUE, LAST_VALUEFirst 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.

RowsPARTITION BY customerWindowsORDER BY timeRanked rowsROW_NUMBERQUALIFY = 1Latest row per customer
Exam tip

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:

PracticeWhy 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 dateMetadata pruning skips whole micro-partitions
Compare raw columns, not functions of them, in WHEREA function or cast on the column can stop pruning
Filter and aggregate before a joinFewer rows reach the join and less memory is used
Join on proper keysAvoids exploding joins and cartesian products
Use UNION ALL when duplicates are impossible or acceptableUNION adds a costly duplicate removal step
Use approximate functions for large distinct countsFar less memory than an exact COUNT(DISTINCT)
Use QUALIFY instead of a self join or nested subqueryOne pass over the data
Add LIMIT when exploring dataStops 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.

Watch out

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_DISTINCT uses HyperLogLog for fast, approximate distinct counts.
  • WHERE filters rows before grouping, HAVING filters groups after, QUALIFY filters window results.
  • Window functions keep every row; PARTITION BY groups, ORDER BY orders, the frame sets the rows used.
  • ROW_NUMBER is unique, RANK leaves gaps after ties, DENSE_RANK does not.
  • Optimize by selecting fewer columns, filtering on raw prunable columns, reducing rows before joins and using UNION ALL where 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.