QUALIFY filters rows by the result of a window function, in the same query. It sits after the window functions are computed, the way HAVING sits after GROUP BY. That removes the usual wrapper subquery.
Latest row per customer
Without it:
SELECT *
FROM (
SELECT c.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
FROM customers_history c
) t
WHERE rn = 1;With it:
SELECT * FROM customers_history QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1;
Same result, one level less. You do not have to add an rn column and then select around it.
Where it works
Snowflake, BigQuery, Databricks SQL, DuckDB and Teradata support it. Postgres and MySQL do not, so use the subquery form there. Always check before using it on a new engine.
Order of evaluation
The logical order is FROM, WHERE, GROUP BY, HAVING, window functions, QUALIFY, then SELECT's final projection and ORDER BY. That is why you can put a window function in QUALIFY but not in WHERE. If you have seen the question "why can't I use a window function in WHERE", this is the answer: WHERE runs before window functions exist.
For tie handling, remember ROW_NUMBER always picks exactly one row, even among ties, so give it a tie-breaker in the ORDER BY. RANK() = 1 keeps all tied rows. Choose on purpose.