Using QUALIFY in Snowflake

What is QUALIFY in Snowflake?

The QUALIFY clause in Snowflake is a filtering mechanism used specifically with analytic (window) functions. It allows you to filter the results of a query after the window functions have been applied, making it easier to retrieve ranked or deduplicated data.

Many SQL users are familiar with WHERE and HAVING for filtering, but QUALIFY provides a way to filter based on window function outputs, eliminating the need for subqueries or Common Table Expressions (CTEs).

How to Use QUALIFY in Snowflake

The QUALIFY clause works similarly to WHERE but applies only to window functions. Here’s a simple example where we rank customers by their order amount and filter to show only the top-ranked orders per customer:

SELECT
    customer_id,
    order_id,
    order_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
FROM orders
QUALIFY order_rank = 1;

In this example:

The RANK() function assigns a rank to each order within a customer.
QUALIFY order_rank = 1 ensures that only the top order for each customer is included in the final result.

QUALIFY vs WHERE in Snowflake

Clause	When It’s Applied	Purpose
`WHERE`	Before aggregation or window functions	Filters raw data before processing
`HAVING`	After aggregation (`GROUP BY`)	Filters grouped results
`QUALIFY`	After window functions	Filters based on analytic function results

Example: WHERE vs QUALIFY

-- Using WHERE (filters before ranking)
SELECT * FROM (
    SELECT
        customer_id,
        order_id,
        order_amount,
        RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
    FROM orders
    WHERE order_amount > 100
) t
WHERE order_rank = 1;

-- Using QUALIFY (filters after ranking)
SELECT
    customer_id,
    order_id,
    order_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
FROM orders
QUALIFY order_rank = 1;

With QUALIFY, the ranking function is executed first, and then filtering is applied, making queries cleaner and more efficient.

QUALIFY and WHERE in Snowflake

Since WHERE applies before window functions and QUALIFY applies after, they are often used together. For example:

SELECT
    customer_id,
    order_id,
    order_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS order_rank
FROM orders
WHERE order_amount > 50
QUALIFY order_rank = 1;

Here, WHERE ensures only orders greater than $50 are considered, and QUALIFY keeps only the top-ranked order per customer.

Best Practices for Using QUALIFY in Snowflake

Use it for filtering window function results – If you need to filter on ROW_NUMBER(), RANK(), DENSE_RANK(), etc., QUALIFY is the most efficient approach.
Combine with WHERE for pre-filtering – Use WHERE to remove unnecessary data before window functions are computed.
Avoid unnecessary subqueries – QUALIFY helps simplify queries by eliminating the need for CTEs or subqueries just to filter window function results.

Using Snowflake with Evidence

If you’re working with Snowflake and need an easy way to visualize and analyze your data, Evidence provides a seamless integration. With Evidence, you can connect to Snowflake, build interactive reports, and share insights with your team in a streamlined, code-based workflow.

Benefits of Using Evidence with Snowflake:

Direct Snowflake Integration – Query Snowflake data directly from Evidence.
Flexible Data Exploration – Use SQL to create dynamic reports and dashboards.
Version Control & Collaboration – Manage reports with Git-based versioning.
Automated Reporting – Schedule and distribute Snowflake-based reports effortlessly.

To get started, check out the Evidence documentation and learn how you can transform Snowflake queries into meaningful visualizations.