What you will be able to do
- Tell scalar, aggregate, window and table functions apart by how many rows each one takes in and gives back
- Predict how aggregate functions treat NULL values, including multi-column COUNT and SUM
- Call a table function correctly inside a FROM clause using TABLE()
- Recognise system functions and call SYSTEM$-prefixed functions correctly
Key concept
Function shape (rows in, rows out) — Snowflake groups its functions by how many rows go in and how many come out. A scalar function returns one value per row. An aggregate function turns many rows into one. A table function can return a whole set of rows for each input row. If you can name the shape a scenario needs, you know which kind of function to use.
1.Scalar and aggregate functions, and how they treat NULL
Start with the two most common kinds. A scalar function returns one value each time it is called, which in practice usually means one value per row. Snowflake sorts its scalar functions into categories, including conditional expression, conversion, date & time, context, data generation, hash, numeric, semi-structured and structured data, string & binary, and regular-expression functions. File, metadata and geospatial functions are scalar categories too.
An aggregate function works across rows. It calculates things like sums, averages, counts, minimums and maximums, standard deviations and estimates. Given the three-row table simple with x = 10, 20, 30, COS(x) returns three rows but SUM(x) returns a single row containing 60. Aggregates always produce exactly one row, even when there are no input rows to read. In that case the result is usually NULL, though some aggregates return 0 or an empty string.
Some aggregates skip NULL values, and a function that receives only NULLs returns NULL. Things get less obvious when you pass more than one column. In the example table, the rows are (1, 2), (3, NULL), (NULL, 6) and (NULL, NULL). COUNT(x, y) returns 1, because it skips every row that has a NULL in any listed column. SUM(x + y) returns 3 for a similar reason: if either column in a row is NULL, x + y is NULL and that row is dropped. GROUP BY x, y works differently and keeps all four combinations, including the ones with NULLs.
SELECT COUNT(x, y) FROM test_null_aggregate_functions;Checkpoint 1 of 5· Check yourself
The table holds (1, 2), (3, NULL), (NULL, 6) and (NULL, NULL). What does SELECT SUM(x + y) return?
Only the row (1, 2) gives a non-NULL x + y. In the other three rows the expression is NULL, so those rows are ignored and the sum is 3.
“then the expression evaluates to NULL, and the row is ignored”Source: docs.snowflake.com
2.Window functions: analytics without collapsing rows
Window functions cover the cases where you need a calculation across related rows but still want every row in the result. The typical uses are running totals, moving averages and rankings. The ranking group includes ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, CUME_DIST and PERCENT_RANK.
Most familiar aggregates also appear in the window function list, including SUM, AVG, COUNT, MIN, MAX, MEDIAN and ARRAY_AGG. There are window-only helpers as well: RATIO_TO_REPORT, CONDITIONAL_CHANGE_EVENT, CONDITIONAL_TRUE_EVENT, and the gap-filling functions INTERPOLATE_BFILL, INTERPOLATE_FFILL and INTERPOLATE_LINEAR. Note a couple of syntax details. LISTAGG, PERCENTILE_CONT and PERCENTILE_DISC use WITHIN GROUP syntax, and PERCENT_RANK only supports RANGE BETWEEN window frames without explicit offsets.
Compare the shapes on the simple table (x = 10, 20, 30). The scalar COS(x) gives one output row for each input row. The aggregate SUM(x) gives one row for the whole input. A window function sits in between for analytics: it is a calculation across related rows, such as a running total or a ranking, while the result keeps a row for every input row. A table function is different again, because it can return zero, one or many rows for each input row.
Checkpoint 2 of 5· Check yourself
An analyst needs a 7-day moving average of sales next to each daily row, and also a rank of each day by revenue. Which family of functions fits?
Moving averages and rankings are standard window-function jobs. Grouping by date with an aggregate would collapse the rows instead of putting the calculation next to each one.
“Window functions are analytic functions that you can use for various calculations such as running totals, moving averages, and rankings.”Source: docs.snowflake.com
Checkpoint 3 of 5· Exam question
An analyst queries a table `orders` (`customer_id`, `order_id`, `order_ts`, `amount`) and needs exactly one row per customer: the most recent order. The team wants a single query block with no subquery or CTE. Which change achieves this?
Correct answer: A — Append `QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1` to the query so the window result filters rows after evaluation.
- A. Correct: QUALIFY filters on window function results after they are computed, so ROW_NUMBER() = 1 per customer partition keeps the latest order without a subquery or CTE.
- B. Incorrect: window functions are not allowed in the WHERE clause because WHERE is evaluated before windows are computed, so the query fails with an error.
- C. Incorrect: `order_ts` is neither grouped nor aggregated in the SELECT list, so the query is invalid, and even a valid form would drop the other columns of the latest row.
- D. Incorrect: `DISTINCT ON` is PostgreSQL syntax and Snowflake does not support it, so the statement fails to parse. Snowflake uses QUALIFY with ROW_NUMBER for this pattern.
3.Table functions: many rows out, called from FROM
A table function, also called a tabular function, returns a set of rows for each input row. That set can have zero, one or many rows, and each row can have several columns. Most are 1-to-N, where each output row depends on a single input row. A function listing record temperatures for a date might return no rows for one date and 40 for another. Snowflake also supports M-to-N table functions, where each output row can depend on several input rows, as in a 10-day moving average.
Table functions return rows, so you use them where SQL expects rows: the FROM clause. Snowflake also requires you to wrap the call in the TABLE() keyword. Every argument must be a scalar expression. It can be a literal, or a column from a table that appears earlier in the FROM clause. Built-in table functions and user-defined ones (UDTFs) are called the same way.
| Sub-category | Function |
|---|---|
| Semi-structured Queries | FLATTEN |
| Data Generation | GENERATOR |
| Data Conversion | SPLIT_TO_TABLE, STRTOK_SPLIT_TO_TABLE |
| Query Results | RESULT_SCAN |
| Data Loading | INFER_SCHEMA, VALIDATE |
Checkpoint 4 of 5· Fill the gap
Which keyword completes this table function call?
SELECT city_name, temperature FROM ? (record_high_temperatures_for_date('2021-06-27'::DATE)) ORDER BY city_name;Snowflake needs the TABLE() wrapper so the SQL compiler recognises the function call as a source of rows.
Source: docs.snowflake.comSources4
4.System functions: control and information
System functions are about the Snowflake system rather than your data. There are three kinds. Control functions carry out actions, such as aborting a query. System information functions report on the system, such as the clustering depth of a table. Query information functions report on queries, such as their EXPLAIN plans.
Many system functions start with SYSTEM$. For those functions the prefix is part of the name, so you must include it when you call them.
SELECT SYSTEM$TYPEOF('a');Checkpoint 5 of 5· Check yourself
A runaway query needs to be stopped from SQL. Which kind of system function does this?
Control functions carry out actions in the system, and the docs give aborting a query as their example. Information functions only report.
“Control functions that allow you to execute actions in the system (for example, aborting a query).”Source: docs.snowflake.com
Sources5
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An aggregate over an empty input returns no rows.Why is that wrong?
An aggregate always returns exactly one row. With zero input rows that row usually holds NULL, though some aggregates return 0 or an empty string.
Covered in Scalar and aggregate functions, and how they treat NULL
2.COUNT(col1, col2) counts every row in which at least one of the columns has a value.Why is that wrong?
A multi-column aggregate skips any row where any listed column is NULL, so only rows with no NULLs are counted.
Covered in Scalar and aggregate functions, and how they treat NULL
3.A table function can go in the FROM clause like a normal function call, without a wrapper.Why is that wrong?
Snowflake requires the call to be wrapped in TABLE() so the compiler treats it as a source of rows.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A scalar function is a function that returns one value per invocation”
↩︎ Scalar and aggregate functions, and how they treat NULL - 2.
“This behavior differs from the behavior of GROUP BY, which does not discard rows when some columns are NULL”
↩︎ Scalar and aggregate functions, and how they treat NULL“The scalar function returns one output row for each input row.”
↩︎ Window functions: analytics without collapsing rows“An aggregate function always returns exactly one row, even when the input contains zero rows.”
↩︎ Exam trap 1“In these instances, the aggregate function ignores a row if any individual column is NULL.”
↩︎ Exam trap 2“AVG calculates the average of values 1, 5, and NULL to be 3”
↩︎ Prediction“then the expression evaluates to NULL, and the row is ignored”
↩︎ Checkpoint - 3.
“Window functions are analytic functions that you can use for various calculations such as running totals, moving averages, and rankings.”
↩︎ Window functions: analytics without collapsing rows - 4.
“Snowflake also supports M-to-N table functions: each output row can depend upon multiple input rows.”
↩︎ Table functions: many rows out, called from FROM“Specifically, table functions are used in the FROM clause of a SQL statement.”
↩︎ Table functions: many rows out, called from FROM“A table function returns a set of rows for each input row.”
↩︎ Key concept“Snowflake requires that the table function call be wrapped by the TABLE() keyword.”
↩︎ Exam trap 3 - 5.
“For the system functions that use this prefix, you must specify the prefix when calling the function.”
↩︎ System functions: control and information“Control functions that allow you to execute actions in the system (for example, aborting a query).”
↩︎ Checkpoint