Calling a function in Snowflake means executing a built-in or user-defined function (UDF) to return a value, transform data, or perform an action. You can call functions in a SELECT statement, inside a WHERE clause, or as part of a CREATE statement. This guide covers the exact syntax for built-in functions, calling UDFs, and common pitfalls.
What You’ll Need
- A Snowflake account with access to a database and warehouse (or virtual warehouse) that is running.
- Appropriate privileges:
USAGEon the database, schema, and warehouse, plusEXECUTEon any user-defined function you call. - A SQL client (Snowsight web UI, SnowSQL CLI, or a connected BI tool) to run queries.
- If calling a UDF, the function must already exist in the schema you are using (or be fully qualified).
Step-by-Step
- Identify the function name and signature. Snowflake functions are case-insensitive but stored in uppercase. For example,
ROUND,UPPER,DATEADD. Check the exact parameter order usingSHOW FUNCTIONSor theDESCRIBE FUNCTIONcommand. - Write a basic SELECT call. The simplest way to call a function is in a
SELECTlist. For example:SELECT UPPER('hello');returnsHELLO. For functions with multiple arguments, separate them with commas:SELECT DATEADD('day', 5, CURRENT_DATE()); - Call a function on a column. Use the column name as the argument. For example, if you have a table
employeeswith a columnlast_name, run:SELECT UPPER(last_name) FROM employees; - Use a function in a WHERE clause. Functions can filter rows. Example:
SELECT * FROM orders WHERE YEAR(order_date) = 2024;HereYEAR()is called on each row. - Call a user-defined function (UDF). If you have a UDF named
calculate_discountthat takes a price, call it like:SELECT calculate_discount(price) FROM products;If the UDF is in a different schema, fully qualify it:SELECT my_db.my_schema.calculate_discount(price) FROM products; - Call a function in a CREATE statement. For example, create a view that uses a function:
CREATE VIEW high_value_orders AS SELECT order_id, total_amount, ROUND(total_amount, 2) AS rounded_total FROM orders; - Call a function with named arguments (for UDFs). Snowflake supports named arguments for SQL UDFs. Example:
SELECT calculate_discount(price => 100, discount_rate => 0.1);This improves readability. - Call a table function. Some functions return a table (e.g.,
FLATTEN). Use them in theFROMclause withTABLE():SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1,"b":2}'))); - Call a stored procedure (CALL). Stored procedures are invoked differently using the
CALLstatement:CALL my_procedure('arg1', 42);Note that a stored procedure does not return a value in a SELECT; it performs actions.
Common Mistakes
- Forgetting the parentheses. Even for functions with no arguments, you must use
CURRENT_DATE()notCURRENT_DATE(though some special functions likeCURRENT_DATEcan be used without parentheses, but it’s safer to include them). - Using the wrong data type. For example, passing a string to a numeric function like
ROUND('123')will fail. Convert withTO_NUMBER()first. - Mistaking function name case. Snowflake is case-insensitive, but if you quote the function name (e.g.,
"MyFunc"), it becomes case-sensitive and must match exactly. - Calling a UDF without schema qualification. If the UDF is not in the current schema, you get an error. Always use the fully qualified name or set the schema with
USE SCHEMA. - Using a stored procedure with SELECT. Stored procedures are called with
CALL, notSELECT. TryingSELECT my_proc()will fail.
Tips
- Use
SHOW FUNCTIONSto see all available functions and their signatures. RunSHOW FUNCTIONS LIKE 'DATE%';to filter. - Check the return type. Some functions return different types than you might expect. For example,
DIV0returns a number, butTO_VARCHARreturns a string. UseDESCRIBE FUNCTIONto verify. - Use aliases for readability. When calling functions in a SELECT, give the result an alias:
SELECT UPPER(last_name) AS last_name_upper FROM employees; - For UDFs, test with literal values first. Run
SELECT my_udf(1, 2);before applying to a table to ensure the logic works. - Remember that functions are deterministic by default unless declared otherwise. This affects query optimization and results caching.
Troubleshooting
- Error: “Function not found” – Check the function name and schema. Use
SHOW FUNCTIONSto confirm existence. If you are calling a UDF, ensure you haveEXECUTEprivilege. - Error: “Invalid argument types” – Verify that the arguments match the function signature. Use
DESCRIBE FUNCTIONto see expected types. Cast arguments explicitly withCAST()orTO_*functions. - Error: “Number of arguments does not match” – Count the commas. For example,
DATEADDrequires three arguments: datepart, interval, and date. If you pass two, it fails. - Query returns NULL unexpectedly – Some functions return NULL if any input is NULL. Use
IFNULL()orCOALESCE()to handle nulls before calling the function. - Performance issues when calling functions on large tables – Functions in WHERE clauses can prevent partition pruning. Consider creating a computed column or using a UDF that is declared as
IMMUTABLEto help the optimizer.
Frequently Asked Questions
Q: Can I call a function without a FROM clause?
A: Yes, you can call a function in a SELECT without a FROM clause, e.g., SELECT RANDOM(); It returns a single row.
Q: How do I call a function that returns a table?
A: Use the TABLE() keyword in the FROM clause, e.g., SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())); or FLATTEN.
Q: What is the difference between a function and a stored procedure?
A: A function returns a value and can be used in SQL expressions, while a stored procedure performs actions and is called with CALL. Functions cannot have side effects on data, but procedures can.
Q: Can I call a Python UDF?
A: Yes, Snowflake supports Python UDFs. You call them the same way as SQL UDFs, e.g., SELECT my_python_udf(col) FROM table;
Q: How do I find the signature of a function?
A: Use DESCRIBE FUNCTION function_name(argument_types); For example, DESCRIBE FUNCTION DATEADD(DATE, INT, DATE); Or use SHOW FUNCTIONS and look at the signature column.
Q: Can I call a function inside another function?
A: Yes, you can nest functions, e.g., SELECT ROUND(SUM(amount), 2) FROM orders; Just ensure the inner function returns a type compatible with the outer function.
Related
- Java String.split(-1): Keep Trailing Empty Fields
- Visual Basic String Split: Syntax, Examples, and Tips
- Is Oracle Stock Going to Split? What Investors Should Know
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS Function: What Happens If No Test Is TRUE?If every test in Excel’s IFS function is FALSE, the function returns #N/A; it does not have a…
- Excel Filter Shortcut: Turn Filters On and Open MenusIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for a…
- Excel SUBTOTAL Formula: How to Use It CorrectlyThe SUBTOTAL formula in Excel is =SUBTOTAL(function_num, ref1, [ref2], ...). For example, =SUBTOTAL(9, A2:A10) returns the sum of…
- Excel Formula for Calculating Overtime HoursTo calculate daily overtime in Excel, subtract unpaid break hours and the regular-hours threshold from the shift length,…
- How to Filter in Excel Using Keyboard Shortcuts and MenusTo filter a list in Excel for Microsoft 365 on Windows, select a cell in the data, press…
- Excel COUNTIF Range Examples: Text, Numbers, and DatesUse Excel’s COUNTIF function to count cells in a range that match one condition: for example, =COUNTIF(A2:A20,"East") counts…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.