Snowflake SQL Patterns

Latest-per-key with QUALIFY

Keep the current row per key with QUALIFY and ROW_NUMBER — and why DISTINCT is the wrong tool.

The problem

You have several versions of the same entity — events for a user, staged loads for an order, slowly changing attributes — and you need one winner row per key. Usually that means “the latest updated_at,” sometimes “the highest version,” sometimes “the first event of the day.”

DISTINCT does not solve this. It removes exact duplicate rows, not “extra versions of the same key.” GROUP BY can keep a key and an aggregate, but it will not cleanly return the other columns from the winning row unless you re-join or use a clumsy ARRAY_AGG trick.

A working example

QUALIFY filters on a window function the way HAVING filters on an aggregate. Official Snowflake documentation places it after window functions are computed, and after WHERE / GROUP BY / HAVING.

WITH orders AS (
  SELECT * FROM (VALUES
    (1, DATE '2026-01-01', 10.00),
    (1, DATE '2026-03-01', 20.00),
    (2, DATE '2026-02-01', 15.00)
  ) t(user_id, order_date, amount)
)
SELECT user_id, order_date, amount
FROM orders
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY order_date DESC
) = 1
ORDER BY user_id;

Executed result in DuckDB 2.0.0:

user_id order_date amount
1 2026-03-01 20.00
2 2026-02-01 15.00

The same logic without QUALIFY needs a subquery (this is the form official docs contrast with QUALIFY):

SELECT user_id, order_date, amount
FROM (
  SELECT
    user_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY order_date DESC
    ) AS rn
  FROM orders
)
WHERE rn = 1;

On Snowflake you can also filter on a window alias in the select list, which official docs show explicitly:

SELECT user_id, order_date, amount,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) AS rn
FROM orders
QUALIFY rn = 1;

That alias form was not re-run here (DuckDB accepted the first query, which puts the window function in the QUALIFY predicate). Treat the alias form as Snowflake syntax from official docs.

If ties are possible — two rows with the same order_date — ROW_NUMBER still picks exactly one row, but which tied row is not meaningful unless you add a deterministic tie-breaker (ORDER BY order_date DESC, load_id DESC). RANK or DENSE_RANK will keep all ties, which is a different requirement.

The gotcha

QUALIFY is not ANSI SQL. Official Snowflake docs say so. It is supported in several other engines (including DuckDB and BigQuery), but a query you paste into Postgres will fail.

QUALIFY also requires a window function in the select list or in the QUALIFY predicate. A QUALIFY amount > 10 with no window function is not a substitute for WHERE.

And DISTINCT after a join is still the wrong instinct: if user 1 has two different amounts on two dates, they are not duplicates, and DISTINCT will keep both.

When not to use this

Verification

The QUALIFY … ROW_NUMBER() = 1 example was executed in DuckDB 2.0.0 and returned the March row for user 1 and the February row for user 2. Snowflake clause order, the reserved-word status of QUALIFY, the non-ANSI note, and the window-alias form are from official Snowflake documentation. This was not executed on Snowflake.

Official docs