Snowflake QUALIFY for Beginners: What It Is and How to Use It

As a data professional working with Snowflake, you may find yourself needing to filter query results based on window functions. Traditionally, this requires subqueries or complex filtering logic. However, Snowflake provides a powerful alternative: the QUALIFY clause. 

In this guide, I’ll break down how to use QUALIFY to streamline filtering, with step-by-step examples and best practices.

What is the QUALIFY Clause in Snowflake?

The QUALIFY clause in Snowflake is used to filter results with window functions. You can define window functions either as part of the SELECT list or reference them directly within the QUALIFY clause.

Typically, WHERE filters raw rows before aggregation, while HAVING filters after a GROUP BY aggregation.

Instead,  QUALIFY allows filtering on the results of SQL statements like ROW_NUMBER(), RANK(), and DENSE_RANK(). This means we can utilize it in our window functions for outputs and immediately filter on them.

You can think of QUALIFY as doing for window functions what HAVING does for aggregates—filtering the result after it’s been calculated.

There are several use cases where the QUALIFY clause proves particularly useful: 

• You need to filter query results based on window function outputs.
• You want to simplify your SQL queries by reducing the need for subqueries.
• You need to rank, deduplicate, or segment data efficiently within partitions.
• You’re preparing a report or dashboard that requires top-performing records per category.

> If you're new to Snowflake, our Introduction to Snowflake course provides a hands-on starting point before diving into clauses like QUALIFY. You can also explore the broader capabilities of Snowflake with this beginner-friendly tutorial.

Syntax of QUALIFY in Snowflake

Let’s review the basic syntax of QUALIFY so you understand its parts. Here’s what a simple query using QUALIFY might look like:

SELECT
 column1,
 column2, 
 window_function() OVER (PARTITION BY column3 ORDER BY column4) AS rank
FROM table_name
QUALIFY window_function_condition;

You have your usual SELECT clause followed by a few columns. You then have your window_function() written in the usual format to get us a column called rank.

There’s the usual FROM statement, which tells us the table, and finally our QUALIFY. This QUALIFY is followed by the “window_function_condition”, which is often an equality and can look something like QUALIFY rank = 1

Note: You can reference either the alias of a window function or rewrite the entire window function expression inside the QUALIFY clause. Both approaches work.

> If you need a review of window functions, take a look at this window function cheat sheet. 

Examples of Using QUALIFY in Snowflake

Now that you know the overall structure of QUALIFY, I will showcase some specific examples using this clause.

Example 1: Filtering top N rows per group

Suppose we want to find the top 3 highest-paid employees in each department. First, let’s look at an example that doesn’t use QUALIFY, followed by a more concise version that does.

/* First we have to rank all the employees */
WITH ranked_employees AS (
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees)

/* Then we query to subquery and filter using WHERE */
SELECT *
FROM ranked_employees
WHERE rank <= 3;

As you can see, the above example requires a CTE, but with QUALIFY, the query is more straightforward:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank <= 3;

The query above assigns a rank to each employee within their department and returns the top 3 in each department. It uses the QUALIFY statement to immediately filter without the need for a subquery or CTE. This is especially useful in dashboards or APIs where minimizing query length and response time is important.

Example 2: Filtering duplicate records

If a table has duplicate records and we want to retain only the first occurrence based on a ranking function, we can use QUALIFY with ROW_NUMBER():

SELECT 
order_id, 
customer_id, 
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS row_num
FROM orders
QUALIFY row_num = 1;

The row_num column is generated by partitioning on each customer and sorting them by order date. This assigns a sequential number to each order, starting with the earliest. By filtering using QUALIFY row_num = 1, we ensure that only the earliest order for each customer is kept.

This technique is commonly used in deduplication tasks during data cleaning.

Example 3: Filtering based on window function in aggregates

Let's get a little fancier. Using QUALIFY with aggregate window functions allows for more flexible and dynamic filtering.

For instance, if we want to find the employees whose salaries are above the department’s average, we can use QUALIFY with AVG() over a window function:

SELECT 
employee_id, 
department, 
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees
QUALIFY salary > avg_salary;

This kind of logic is great when identifying outliers or top performers within peer groups.

> If you're looking to strengthen your SQL skills for more complex queries like these, check out our Intermediate SQL course.

Best Practices for Using QUALIFY in Snowflake

Here are some best practices and uses for a QUALIFY clause. 

Simplify complex queries

The power of QUALIFY lies within its ability to simplify queries by removing the need for extra subqueries and CTEs. Lean into this functionality. Use it whenever you write a window function and need to filter based on its output, without creating a subquery.

Fewer CTEs mean easier debugging, clearer logic, and better maintainability.

Combine QUALIFY with other window functions

You can combine multiple window functions for more refined filtering. For instance, using ROW_NUMBER() along with RANK() to resolve ranking ties while limiting results:

SELECT 
employee_id, 
department, 
salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, employee_id) AS row_num
FROM employees
QUALIFY rank <= 3 AND row_num <= 3;

This query returns employees who fall within the top 3 salary ranks in their department. However, if more than three employees tie within that top 3, it will only show the first three ordered by employee ID.

Performance considerations

While QUALIFY simplifies queries in terms of written code, some performance aspects should be considered.

Window functions can be computationally expensive, and poorly structured queries may become resource-intensive. Make sure to keep these concepts in mind:

• Window functions can be computationally expensive on large datasets, so be careful about at what stage in your process you use them.
• Ensure proper indexing and partitioning to optimize performance.
• Use LIMIT in conjunction with QUALIFY for even better efficiency during testing.
• Use Snowflake’s Query Profile tool to identify performance bottlenecks in windowed queries.

Troubleshooting QUALIFY Queries

It can be easy to run into pitfalls with QUALIFY statements, given the complexity of window functions. Here are some tips to help with the more common problems you might run into.

1. Using window functions in the WHERE clause

Attempting to use window functions directly in the WHERE clause will result in errors because WHERE is evaluated before window functions are processed.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
WHERE rank = 1;

Solution: Use the QUALIFY clause instead, which is evaluated after window functions.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

2. Omitting the predicate in QUALIFY

The QUALIFY clause requires a predicate to filter the results of window functions. Omitting the predicate will lead to errors.

-- This will cause an error
SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC);

Solution: Ensure that the QUALIFY clause includes a predicate, such as = 1 to filter for the top-ranked row.

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

3. Ambiguous aliases

Using the same alias for both a column and a window function can lead to confusion and unexpected results.

-- Potentially ambiguous
SELECT salary AS rank,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Solution: Use distinct aliases to avoid ambiguity.

SELECT salary AS salary_amount,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees
QUALIFY salary_rank = 1;

4. Incorrect partitioning or ordering

Incorrectly specifying the PARTITION BY or ORDER BY clauses in window functions can lead to unexpected results.

Solution: Carefully define the partitioning and ordering to match the desired logic. For example, to get the top salary per department:

SELECT employee_id, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;

Conclusion

The QUALIFY clause in Snowflake is a powerful tool for filtering results based on window functions, reducing the need for subqueries and making queries more readable. By understanding how to use it effectively, data scientists can simplify their queries and improve efficiency when working with ranked, grouped, or aggregated data.

Ready to take your Snowflake and SQL skills further? Start with our Introduction to Snowflake course, sharpen your analytical queries in the PostgreSQL window functions course, or advance your career with the Associate Data Engineer in SQL track!