In Snowflake, a function is a reusable piece of SQL logic that takes input parameters, performs a defined operation, and returns a single value or a table result. Functions are fundamental to writing efficient, maintainable queries and are categorized into built-in functions (like COUNT, DATEADD, or LATERAL_FLATTEN) and user-defined functions (UDFs) that you create to encapsulate custom logic.
What Exactly Is a Function in Snowflake?
A function in Snowflake is a named object that encapsulates a specific computation. When you call it in a query, you pass arguments, and it returns a result. Snowflake supports a rich library of built-in functions covering string manipulation, numeric operations, date/time handling, semi-structured data (JSON, Avro, Parquet), and more. Functions are deterministic or non-deterministic; for example, RANDOM() returns a different value each time, while UPPER() always returns the same for the same input.
Beyond built-ins, Snowflake allows you to create user-defined functions (UDFs) in SQL, JavaScript, Java, or Python. These are stored in a schema and can be reused across queries, making complex logic reusable and easier to maintain.
Built-in Function Categories
Snowflake groups its hundreds of built-in functions into logical categories. Knowing these helps you find the right tool quickly:
- Aggregate functions:
SUM,AVG,MIN,MAX,COUNT– summarize rows. - Scalar functions: operate on a single value, e.g.,
LEN,ROUND,CONCAT. - Window functions:
ROW_NUMBER,LAG,LEAD– compute across a set of rows related to the current row. - Date/time functions:
CURRENT_DATE,DATEADD,DATEDIFF. - String functions:
SUBSTR,REPLACE,SPLIT. - Conversion functions:
TO_NUMBER,TO_VARCHAR,TO_TIMESTAMP. - Semi-structured functions:
PARSE_JSON,FLATTEN,GET_PATH. - Table functions: return a set of rows, e.g.,
LATERAL_FLATTEN,GENERATOR.
You can find the complete list in Snowflake’s documentation under “SQL Function Reference”.
How to Use a Function in a Query
Using a function is straightforward: you call it in the SELECT list, WHERE clause, or even in the FROM clause if it’s a table function. For example, to calculate the number of days since an order date:
SELECT order_id, DATEDIFF(day, order_date, CURRENT_DATE) AS days_since_order
FROM orders;
For a table function, you use the LATERAL keyword to join its output with the rest of the query. For instance, flattening a JSON array:
SELECT
t.customer_id,
f.value:product_id AS product_id
FROM customers t,
LATERAL FLATTEN(input => t.purchase_history) f;
Note that Snowflake uses => for named parameters in some functions, and the LATERAL keyword is required for table functions that reference columns from preceding tables.
Creating a User-Defined Function (UDF)
When built-ins don’t cover your logic, you can write a UDF. The simplest form is a SQL UDF. For example, to create a function that doubles a number:
CREATE OR REPLACE FUNCTION double_it(x NUMBER)
RETURNS NUMBER
AS
$$
x * 2
$$;
Then call it like any built-in:
SELECT double_it(21) AS result; -- returns 42
You can also create JavaScript UDFs for procedural logic, or Python UDFs for more complex data processing. The syntax varies slightly; for Python, you specify the handler and return type. Always test your UDF with sample data before deploying.
Choosing Between Built-in and UDF
As a rule of thumb, always prefer a built-in function if one exists. Built-ins are optimized by Snowflake’s engine and are generally faster and more reliable than custom UDFs. UDFs are for logic that is not available out of the box, such as a specialized business calculation or a complex string transformation that would be cumbersome in pure SQL.
When writing a UDF, consider performance: SQL UDFs are inlined by the optimizer, making them very efficient. JavaScript and Python UDFs run in a separate process, so they are slower but offer more flexibility. For heavy data processing, consider using Snowflake’s stored procedures or external functions instead.
Common Mistakes and Misconceptions
One common mistake is confusing stored procedures with functions. A function returns a value and cannot modify data (unless it’s a stored procedure). In Snowflake, UDFs cannot contain DML statements like INSERT or UPDATE; they are read-only. If you need to perform actions, use a stored procedure.
Another misconception is that all functions are deterministic. Some, like RANDOM() or CURRENT_TIMESTAMP, are non-deterministic, meaning they return different results each time they are called. This matters in materialized views or when you rely on consistent results.
Also, be careful with NULL handling. Most functions return NULL if any input is NULL, unless explicitly handled. For example, CONCAT('a', NULL) returns NULL, not ‘a’. Use IFNULL or COALESCE to handle nulls.
Performance and Best Practices
To get the best performance from functions in Snowflake:
- Use built-in functions whenever possible – they are compiled and optimized.
- For UDFs, prefer SQL UDFs over JavaScript/Python for simple logic to avoid overhead.
- Avoid using functions in WHERE clauses on large tables if they prevent predicate pushdown. For example,
WHERE DATEADD(day, -7, CURRENT_DATE) < order_dateis better thanWHERE order_date > DATEADD(day, -7, CURRENT_DATE)? Actually, the latter is better because it allows the optimizer to use the index on order_date. - When flattening semi-structured data, use
LATERAL FLATTENwith a filter inside the function to reduce rows early. - Test UDFs on a small dataset before running on production.
Frequently Asked Questions
Can I use a function to modify data in a table?
No. In Snowflake, user-defined functions (UDFs) are read-only and cannot contain INSERT, UPDATE, DELETE, or MERGE statements. If you need to modify data, use a stored procedure instead, which can execute DML and even call other functions.
What is the difference between a function and a stored procedure in Snowflake?
A function returns a single value (scalar) or a table, and is used within a query. A stored procedure is a standalone program that can perform multiple operations, including DML, and is called with the CALL statement. Stored procedures can also return results, but they are not used inside a SELECT.
How do I list all functions available in Snowflake?
You can query the INFORMATION_SCHEMA. For example, to list all functions in the current database: SELECT * FROM INFORMATION_SCHEMA.FUNCTIONS;. This shows function names, schemas, and argument types. You can also filter by function name using LIKE.
Can I create a function that returns a table?
Yes. Snowflake supports SQL UDFs that return a table (also called table functions). You define them with RETURNS TABLE(...) and use them in the FROM clause. For example, a function that returns a range of numbers can be used to generate rows.
Are Snowflake functions case-sensitive?
Function names are case-insensitive in Snowflake. You can write COUNT, count, or Count and it will work. However, string literals and user-defined identifiers (like column names) are case-preserving and can be case-sensitive if quoted.
Related
- Databricks SQL CONTAINS: Find Text in a String
- Split Syntax in Java: Examples and Common Pitfalls
- JavaScript String split(): Syntax, Examples, and Tips
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Text Join and Filter in Excel: Formulas and ExamplesWith names in A2:A10 and their statuses in B2:B10, use =TEXTJOIN(", ",TRUE,FILTER(A2:A10,B2:B10="Open","No open items")) to return one comma-separated…
- Function Call Split? How to Divide Calls in ExcelYes, you can split a function call in Excel by nesting one function inside another, separating arguments with…
- Excel Number Range Formula: Calculate, Count, and ListTo calculate the numerical range—the difference between the largest and smallest values—in cells A2:A10, use =MAX(A2:A10)-MIN(A2:A10). If by…
- Rank in Excel With Ties: RANK.EQ and Unique RanksTo give equal values the same rank in Excel, enter a RANK.EQ formula. With scores in B2:B6, put…
- Average If Excel Date Range? Formulas That WorkTo average values in Excel only when their dates fall within a chosen range, use AVERAGEIFS with a…
- Excel Split Function: How to Split Text into ColumnsIn Excel for Microsoft 365, the function for splitting a text string is TEXTSPLIT. It separates text at…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.