How to Call a Function in Snowflake (Step-by-Step)

Formulas & functions · Updated November 22, 2026

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: USAGE on the database, schema, and warehouse, plus EXECUTE on 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

  1. 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 using SHOW FUNCTIONS or the DESCRIBE FUNCTION command.
  2. Write a basic SELECT call. The simplest way to call a function is in a SELECT list. For example: SELECT UPPER('hello'); returns HELLO. For functions with multiple arguments, separate them with commas: SELECT DATEADD('day', 5, CURRENT_DATE());
  3. Call a function on a column. Use the column name as the argument. For example, if you have a table employees with a column last_name, run: SELECT UPPER(last_name) FROM employees;
  4. Use a function in a WHERE clause. Functions can filter rows. Example: SELECT * FROM orders WHERE YEAR(order_date) = 2024; Here YEAR() is called on each row.
  5. Call a user-defined function (UDF). If you have a UDF named calculate_discount that 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;
  6. 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;
  7. 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.
  8. Call a table function. Some functions return a table (e.g., FLATTEN). Use them in the FROM clause with TABLE(): SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1,"b":2}')));
  9. Call a stored procedure (CALL). Stored procedures are invoked differently using the CALL statement: 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() not CURRENT_DATE (though some special functions like CURRENT_DATE can 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 with TO_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, not SELECT. Trying SELECT my_proc() will fail.

Tips

  • Use SHOW FUNCTIONS to see all available functions and their signatures. Run SHOW FUNCTIONS LIKE 'DATE%'; to filter.
  • Check the return type. Some functions return different types than you might expect. For example, DIV0 returns a number, but TO_VARCHAR returns a string. Use DESCRIBE FUNCTION to 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 FUNCTIONS to confirm existence. If you are calling a UDF, ensure you have EXECUTE privilege.
  • Error: “Invalid argument types” – Verify that the arguments match the function signature. Use DESCRIBE FUNCTION to see expected types. Cast arguments explicitly with CAST() or TO_* functions.
  • Error: “Number of arguments does not match” – Count the commas. For example, DATEADD requires 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() or COALESCE() 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 IMMUTABLE to 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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.