Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 38 ยท Querying and Semi-structured Data

SELECT and Snowflake SQL Extras

Go beyond standard SQL with Snowflake extras: QUALIFY for window filters, ILIKE, GROUP BY ALL, SELECT EXCLUDE, SAMPLE, MATCH_RECOGNIZE and TRY_ functions.

Snowflake SQL follows the ANSI standard closely, so everything you know about joins, grouping and window functions applies. On top of that it adds conveniences that remove boilerplate. This lesson shows the most useful ones and, where possible, the portable equivalent that runs in your browser.

QUALIFY: filter on window functions

In standard SQL you cannot put a window function in WHERE, so you wrap the query in a subquery or CTE. Snowflake's QUALIFY is to window functions what HAVING is to aggregates: it filters after they are computed.

SQL
-- Snowflake SQL (read-only)
-- Each customer's most recent order
SELECT customer_id, id AS order_id, ordered_at, total
FROM shop.raw.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) = 1;

That one clause replaces a CTE. The portable form works everywhere, including here:

SQL Latest order per customer (portable form of QUALIFY) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

QUALIFY is superb for de-duplication, which you will do constantly on loaded data: keep the newest row per key.

SQL Stand-in: de-duplicate keeping the newest row

Press Run (or Ctrl + Enter) to see the result here.

Smaller conveniences

GROUP BY ALL and ORDER BY ALL

SQL
-- Snowflake SQL (read-only)
SELECT region, product_line, SUM(total) AS revenue
FROM shop.raw.sales
GROUP BY ALL
ORDER BY ALL;

GROUP BY ALL groups by every non-aggregate expression in the select list; ORDER BY ALL sorts by every selected column left to right.

SELECT * EXCLUDE, RENAME and REPLACE

SQL
-- Snowflake SQL (read-only)
SELECT * EXCLUDE (internal_notes, ssn) FROM shop.raw.customers;
SELECT * RENAME (id AS customer_id) FROM shop.raw.customers;
SELECT * REPLACE (UPPER(country) AS country) FROM shop.raw.customers;

Handy when a table has 80 columns and you want all but two. Portable SQL has no such shortcut; you list the columns you need.

Alias reuse and lateral column references

You can use a select-list alias later in the same select (see Your First Queries). It makes long calculations readable.

Case-insensitive matching: ILIKE

ILIKE is a case-insensitive LIKE. You can also ILIKE ANY ('%a%', '%b%'), LIKE ALL and RLIKE for regular expressions.

SQL
-- Snowflake SQL (read-only)
SELECT name FROM shop.raw.customers WHERE name ILIKE ANY ('%smith%', 'jo%');

The portable version lowercases both sides:

SQL Case-insensitive search (portable) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Safe conversions: TRY_ functions

CAST errors stop a query when one value is bad. The TRY_ family returns NULL instead: TRY_TO_NUMBER, TRY_TO_DATE, TRY_TO_TIMESTAMP, TRY_PARSE_JSON, TRY_CAST. The browser version can use CASE to guard the cast:

SQL Stand-in: a safe number conversion

Press Run (or Ctrl + Enter) to see the result here.

Other helpful functions

Function What it does
IFF(cond, a, b) Short CASE: if cond then a else b
NVL(x, y), ZEROIFNULL(x) Replace NULL
DIV0(a, b) Division that returns 0 instead of an error when b is 0
DATEADD, DATEDIFF, DATE_TRUNC Date arithmetic
LISTAGG(col, ', ') Concatenate values of a group
ARRAY_AGG(col) Collect values into an array
MEDIAN, MODE, APPROX_COUNT_DISTINCT Statistics, the last is fast and approximate
CONVERT_TIMEZONE(from, to, ts) Time zone conversion
SQL
-- Snowflake SQL (read-only)
SELECT DATE_TRUNC('month', ordered_at) AS month,
       COUNT(DISTINCT customer_id)     AS buyers,
       APPROX_COUNT_DISTINCT(product_id) AS approx_products,
       DIV0(SUM(total), COUNT(*))      AS avg_order
FROM shop.raw.orders
GROUP BY ALL;

Sampling

To explore a huge table quickly, use SAMPLE (or TABLESAMPLE):

SQL
-- Snowflake SQL (read-only)
SELECT * FROM shop.raw.events SAMPLE (1);            -- about 1 percent of rows
SELECT * FROM shop.raw.events SAMPLE (1000 ROWS);    -- about 1000 rows
SELECT * FROM shop.raw.events SAMPLE BLOCK (10) SEED (42);   -- ~10 percent of partitions, repeatable

Row sampling (BERNOULLI) is the default; block sampling (SYSTEM) picks micro-partitions and is faster but less random. Use SEED to repeat a sample.

Set operators and more

Standard UNION, INTERSECT and EXCEPT work, and Snowflake also supports MINUS as a synonym for EXCEPT. UNION ALL BY NAME matches columns by name instead of by position, useful when sources have the same columns in different orders.

Advanced: MATCH_RECOGNIZE

MATCH_RECOGNIZE finds patterns in ordered rows, for example "a price rose three days in a row". It is a powerful but specialized feature, supported in Snowflake with SQL regular-expression-style patterns. For ordinary row-to-row comparisons the window functions LAG and LEAD are simpler. You can try that portable approach:

SQL Stand-in: detect consecutive increases with LAG

Press Run (or Ctrl + Enter) to see the result here.

When you write Snowflake-only syntax, add a comment with the portable alternative. It makes the query easier to port and review.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does QUALIFY do?

    QUALIFY is like HAVING for window functions, so you need no subquery to filter on ROW_NUMBER or RANK.
  2. 2. Which clause selects all columns except two?

    Snowflake's EXCLUDE removes the listed columns from a star select.
  3. 3. TRY_TO_NUMBER('abc') returns:

    TRY_ functions return NULL when conversion fails, instead of stopping the query.
  4. 4. What is SAMPLE (1) used for?

    SAMPLE returns an approximate fraction of rows, which is cheap on large tables.

Summary

  • QUALIFY filters window-function results; it is ideal for de-duplication and top-N per group.
  • GROUP BY ALL, ORDER BY ALL, SELECT * EXCLUDE/RENAME/REPLACE cut boilerplate.
  • ILIKE, IFF, DIV0, TRY_* functions make everyday SQL safer and shorter.
  • SAMPLE explores big tables cheaply.
  • Keep portable equivalents in mind; the in-browser examples show them.

Next: Semi-structured Data and the VARIANT Type