SQL functions in Snowflake are built-in or user-defined operations that take values as input and return a value or set of rows. They let you transform text, calculate totals, work with dates, handle missing values, and query semi-structured data directly in SQL.
What SQL functions mean in Snowflake
A function is an expression you use inside a SQL statement. It may accept one or more arguments, such as a column, a literal value, or the result of another expression. For example, UPPER(product_name) returns an uppercase version of a product name, while ROUND(price, 2) rounds a number to two decimal places.
Snowflake provides a large library of built-in functions, organized by purpose and documented in its SQL reference. Function names are generally case-insensitive, so LOWER and lower refer to the same function. SQL keywords and function names are often written in uppercase for readability, but that is a style choice rather than a requirement.
Functions are not limited to the SELECT list. You can use many of them in filters, join conditions, grouping expressions, ordering, and other parts of a query, subject to the rules for that expression. A function call can also be nested inside another call, such as ROUND(AVG(amount), 2).
Main categories of Snowflake SQL functions
The most useful distinction is what a function returns and how it processes rows. Snowflake’s function reference groups functions by domain, but these behavioral categories help explain how they fit into a query:
- Scalar functions return one value for each input row. Examples include
ABS(amount),LOWER(email), andDATE_TRUNC('month', order_date). - Aggregate functions combine values from multiple rows into a result, often one result per group.
COUNT(*),SUM(amount), andAVG(amount)are common examples. - Window functions calculate across a related set of rows while retaining a result for each row. They use an
OVERclause, as inROW_NUMBER() OVER (PARTITION BY account_id ORDER BY created_at). - Table functions return rows rather than a single scalar value. They appear in the
FROMclause, commonly throughTABLE(...)or a lateral join.FLATTENis frequently used to expand arrays or objects stored in semi-structured data. - Conditional functions choose a result based on conditions.
COALESCE,NULLIF,IFF, andCASEexpressions help express this logic. - Conversion functions change or interpret data types, such as
TO_DATE,TO_VARCHAR, andTRY_TO_NUMBER.
These categories can overlap in practical use. For instance, SUM(amount) OVER (...) uses an aggregate function as a window function: the aggregate is calculated over a window, rather than collapsing the whole group to one output row.
Common functions for everyday tasks
Snowflake includes functions for the tasks that appear in many analytical queries. The right function depends on the data type and the result you need; these examples show common choices:
- Text:
TRIMremoves leading and trailing spaces,REPLACEsubstitutes text,CONCATjoins strings, andSUBSTRextracts part of a string.LOWERandUPPERare useful for normalizing case. - Numbers:
ROUNDcontrols decimal precision,ABSreturns an absolute value, andMODreturns a remainder. Aggregates such asSUM,MIN, andAVGsummarize numeric columns. - Dates and times:
CURRENT_DATE()returns the current date,DATEADDadds a specified interval, andDATEDIFFmeasures the difference between date or time values in a selected unit. - Conditional results:
COALESCE(a, b)returns the first non-NULL argument.IFF(condition, value_if_true, value_if_false)is a compact conditional function; aCASEexpression is often clearer for several branches. - Counting and ranking:
COUNTcounts rows or non-NULL values, while window functions such asRANKandROW_NUMBERrank rows within a defined partition.
For example, to group order totals by calendar month, you can apply a date function before grouping. With a table named orders containing order_date and amount, SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM orders GROUP BY 1 returns one row per month represented in the data. The first expression truncates each date to the start of its month; the aggregate adds the amounts in each resulting group.
Using aggregate and window functions
Aggregate functions and window functions can look similar, but they answer different questions. A query using GROUP BY customer_id normally returns one row per customer. A window calculation can show a customer-level calculation alongside every original order row.
For example, to calculate a running order total per customer, use SELECT customer_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders. The partition restarts the calculation for each customer, and the ordered frame includes rows from the start of that customer’s sequence through the current row. If multiple orders share the same date, add a stable tie-breaker such as an order ID to the window’s ORDER BY when their sequence matters.
Snowflake also supports QUALIFY, which filters results after window functions are evaluated. To keep the most recent order per customer, you can calculate ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) and use QUALIFY to retain rows where the row number equals one. This can be more direct than wrapping the window calculation in a subquery.
NULL values and safe type conversion
A frequent source of unexpected results is NULL. In SQL, NULL represents an unknown or missing value; it is not the same as zero, an empty string, or the text 'NULL'. Comparisons such as column = NULL do not test for missing values. Use IS NULL or IS NOT NULL instead.
Many functions propagate NULL: for example, arithmetic involving a NULL value usually produces NULL. COALESCE can provide a fallback, as in COALESCE(discount, 0), but replacing missing data with zero is a business decision, not merely a formatting fix. It changes the interpretation of the data and should be used only when zero is an appropriate substitute.
Conversion functions also deserve care. TO_NUMBER and TO_DATE attempt to convert values and can raise an error when the input cannot be interpreted. Their TRY_ counterparts, such as TRY_TO_NUMBER and TRY_TO_DATE, return NULL for invalid input instead of failing the conversion. That can help a data-cleaning query continue, but it is wise to count or inspect the resulting NULLs so malformed values do not disappear unnoticed.
When converting text dates or numbers, check the expected format and the source type. Automatic conversion can depend on the value and session settings; explicit formats can make a query easier to understand and more reliable when input formats vary.
Functions for semi-structured data
Snowflake can store semi-structured values in types such as VARIANT, which is commonly used for JSON-like data. Path expressions let you access nested fields. For a VARIANT column named payload, an expression such as payload:customer.name refers to a nested value; casting it with ::STRING requests a string result.
For example, if each row in orders has an array at payload:items, a lateral FLATTEN call can turn its elements into separate rows: SELECT o.id, f.index, f.value::STRING AS item FROM orders AS o, LATERAL FLATTEN(INPUT => o.payload:items) AS f. The output includes the order ID, each array element’s index, and its value converted to text. If an item is itself an object, select its relevant fields from f.value rather than casting the whole object to a string.
Flattening changes the row count: an order with several array elements can produce several output rows, and an empty or missing array may produce no flattened rows in the ordinary form. This matters when you later count records or join the results. Snowflake’s FLATTEN options, including OUTER, can affect how zero-row expansions are represented, so check the function reference when preserving parent rows is important.
Choosing functions and avoiding common problems
Start by identifying the intended output: one value per input row, one value per group, a calculation across rows, or a row expansion. That determines whether a scalar, aggregate, window, or table function is appropriate. Then check the input and return types, NULL behavior, and whether the function accepts the kind of value you actually have.
- Use explicit casts when intent matters. A value stored as
VARIANTor text may need conversion before numeric comparisons or date calculations. - Watch grouping rules. A selected non-aggregate expression generally needs to be included in the grouping logic or otherwise handled according to Snowflake’s SQL rules.
- Define window ordering carefully. If the ordering columns contain ties, row numbering or running calculations may not produce the intended sequence unless a tie-breaker is added.
- Do not assume a function changes stored data. A function in a query usually transforms the value in the query result; changing table contents requires a data-changing statement such as
UPDATE. - Validate results on edge cases. Test NULLs, empty strings, invalid dates, duplicate keys, and empty arrays where those cases can occur.
Functions can also affect query efficiency, especially when used on large columns or in filters. A function applied to a column may make a predicate less straightforward for the optimizer than a direct comparison, although actual performance depends on the query, data layout, and execution plan. Avoid assuming a rewrite is faster without checking query history or the query profile. In Snowflake, ordinary function calls do not generally have a separate per-call price; queries consume warehouse compute, while external functions or connected services may involve additional service charges.
Built-in functions, user-defined functions, and alternatives
Built-in functions are usually the simplest option when they cover the requirement: they are available in SQL without creating a separate object, and their behavior is described in Snowflake documentation. If the required logic is missing, Snowflake supports user-defined functions (UDFs), including scalar and table functions. Depending on the supported language and configuration, a UDF can encapsulate reusable logic that would otherwise be repeated across queries.
Use a UDF when it makes repeated logic clearer or provides functionality that ordinary SQL does not express conveniently. It is not automatically faster than built-in SQL. A UDF can add maintenance and execution considerations, and its language, handler, packages, permissions, and deployment method matter. For straightforward transformations, native SQL expressions are often easier for other analysts to read and optimize.
A stored procedure is related but not interchangeable with a function. A function is designed to return a value or rows for use in an expression or query. A procedure is invoked as a statement and is typically used to execute a sequence of operations. Choose based on whether the SQL needs a reusable result inside a query or a callable process that performs work.
Frequently Asked Questions
Are Snowflake SQL functions case-sensitive?
Function names are generally case-insensitive, so uppercase and lowercase spellings are equivalent. Identifier rules are a separate issue: quoted identifiers can preserve case and may need to be referenced with matching quotes and capitalization.
What is the difference between a function and a stored procedure in Snowflake?
A function returns a value or rows and can be used in a query where that type of result is allowed. A stored procedure is called as a statement and is intended to run procedural logic or a sequence of operations.
How do I handle invalid dates or numbers without an error?
Use a suitable conversion function such as TRY_TO_DATE or TRY_TO_NUMBER. Invalid inputs return NULL rather than raising a conversion error, so inspect those NULL results if you need to identify bad source values.
Can I use a window function with GROUP BY?
Yes, but the expressions must fit the query’s grouping rules. A common approach is to aggregate in a subquery or common table expression, then apply a window function to the aggregated rows when that produces the intended calculation.
Where can I find the full list of Snowflake functions?
Use the Snowflake SQL reference and browse its function categories, such as string, date and time, conversion, aggregate, window, and semi-structured data functions. Check the entry for a specific function for its argument types, return type, NULL behavior, and usage notes.
Related
- How to Combine Two Parts in Onshape
- Hide Column Names in Power BI: What Actually Works
- Excell Battery P/N 2EXL7431-001: What It Means
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- Extract Day from Date in Excel: Formula and StepsTo extract the day of the month from an Excel date, put the date in A2 and enter…
- Excel Formula for Mileage Calculation: How ToThe core Excel formula for mileage calculation is simple: =EndOdometer - StartOdometer for distance, or =MilesDriven * RatePerMile…
- Due Date Calculation in Excel: Formulas and Examples GuideFor a due date a set number of calendar days after a start date, put the start date…
- Excel Formula to Add Dates in SequenceTo add dates in sequence in Excel, enter a starting date in A2, then enter =A2+1 in A3…
- Excel: How Many Days Between Dates? Simple FormulasTo find how many days separate two dates in Excel, subtract the start date from the end date:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.