Snowflake SQL extensions: QUALIFY and friends

These extensions remove whole subqueries from everyday analytics SQL.

Snowflake SQL is ANSI-compatible with handy additions.

QUALIFY filters on window functions the way HAVING filters on aggregates, so "latest row per key" needs no subquery: QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1.
GROUP BY ALL groups by every non-aggregate expression in the select list.
**SELECT * EXCLUDE (col1, col2) and SELECT * RENAME (a AS b)** avoid spelling out wide column lists. REPLACE is also available.
IFF(cond, a, b) is a compact two-branch CASE; NVL, ZEROIFNULL, NULLIFZERO and EQUAL_NULL handle NULLs.
ILIKE is case-insensitive LIKE; LIKE ANY ('a%', 'b%') matches a list of patterns.
MIN_BY(x, y) / MAX_BY(x, y) return x from the row with the min or max y.
expr::type is shorthand for CAST; TRY_CAST and TRY_TO_NUMBER return NULL instead of failing.
Column aliases can be reused later in the same select list and in WHERE.
SAMPLE (10) reads a random 10% of rows, and RESULT_SCAN(LAST_QUERY_ID()) lets you query the previous result.

Date helpers worth knowing: DATE_TRUNC('month', ts), DATEADD(day, -7, CURRENT_DATE()) and DATEDIFF(day, a, b).

Example

-- Latest status per order, no subquery needed
SELECT order_id, status, updated_at,
       IFF(status = 'shipped', 'done', 'open') AS state
FROM order_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

SELECT DATE_TRUNC('month', created_at)        AS month,
       region,
       COUNT(*)                              AS n_orders,
       COUNT_IF(status ILIKE 'cancel%')      AS cancelled,
       ROUND(cancelled / n_orders, 3)        AS cancel_rate,    -- reuses aliases
       MAX_BY(order_id, amount)              AS biggest_order
FROM orders
WHERE created_at >= DATEADD(month, -6, CURRENT_DATE())
GROUP BY ALL
ORDER BY month, region;

SELECT * EXCLUDE (internal_notes, raw_payload) FROM customers LIMIT 10;

Remember

QUALIFY filters window-function results directly
GROUP BY ALL and SELECT * EXCLUDE cut boilerplate
MAX_BY / MIN_BY, IFF, ILIKE and :: are daily shortcuts

Watch out for

Writing QUALIFY in a dialect that lacks it when porting SQL elsewhere, e.g. to PostgreSQL.

Check yourself

Which clause keeps only the first row per customer by a window function without a subquery?

Official docs: docs.snowflake.com/en/sql-reference/constructs/qualify

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks