Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Create UDF in Snowflake: Simple Steps and Example

To create a User Defined Function (UDF) in Snowflake, use the CREATE FUNCTION statement with the function name, input parameters, return type, and function logic. The function logic can be written in SQL or JavaScript. Once created, you can call the UDF in your queries like any built-in function.
📐

Syntax

The basic syntax to create a UDF in Snowflake includes the function name, input parameters with their data types, the return data type, and the function body which contains the logic. You specify the language as SQL or JavaScript.

  • CREATE FUNCTION: Starts the function creation.
  • function_name: The name you give your function.
  • (parameters): Input parameters with their types.
  • RETURNS: The data type the function returns.
  • LANGUAGE: The language used (SQL or JAVASCRIPT).
  • AS: The function logic or code.
sql
CREATE FUNCTION function_name(parameter_name DATA_TYPE, ...) 
RETURNS RETURN_DATA_TYPE
LANGUAGE SQL | JAVASCRIPT
AS
'function_body';
💻

Example

This example creates a simple SQL UDF named add_one that takes a number and returns the number plus one. It shows how to define the function and then how to use it in a query.

sql
CREATE OR REPLACE FUNCTION add_one(x INT)
RETURNS INT
LANGUAGE SQL
AS
' x + 1 ';

-- Using the UDF
SELECT add_one(5) AS result;
Output
RESULT 6
⚠️

Common Pitfalls

Common mistakes when creating UDFs in Snowflake include:

  • Not specifying the correct LANGUAGE (SQL or JAVASCRIPT).
  • Using incorrect data types for parameters or return type.
  • Forgetting to use CREATE OR REPLACE FUNCTION to update existing functions.
  • Writing function logic that does not match the declared return type.

Here is an example of a wrong and right way to create a UDF:

sql
-- Wrong: Missing LANGUAGE keyword
CREATE FUNCTION bad_udf(x INT) RETURNS INT AS 'x + 1';

-- Right:
CREATE OR REPLACE FUNCTION good_udf(x INT) RETURNS INT LANGUAGE SQL AS 'x + 1';
📊

Quick Reference

ClauseDescription
CREATE FUNCTIONStarts the function creation statement
function_name(parameters)Name and input parameters with types
RETURNS data_typeSpecifies the return type of the function
LANGUAGE SQL | JAVASCRIPTDefines the language used for the function logic
AS 'function_body'Contains the SQL or JavaScript code for the function
✅

Key Takeaways

Use CREATE FUNCTION with parameters, return type, and language to define a UDF in Snowflake.
Specify LANGUAGE as SQL or JAVASCRIPT to match your function logic.
Use CREATE OR REPLACE FUNCTION to update existing UDFs safely.
Ensure your function logic matches the declared return data type.
Call your UDF in queries just like built-in functions.