Snowflake Cheatsheet: User Defined Functions

User-defined functions (UDFs) are a powerful way to extend the functionality of Snowflake. They allow you to encapsulate complex logic in a reusable function that can be called from SQL. This can save you time and effort, and it can also make your queries more efficient.

There are two types of UDFs in Snowflake: scalar and tabular. A scalar UDF returns a single value, while a tabular UDF returns a table. Scalar UDFs are the most common type of UDF, and they are used for a variety of tasks, such as:

  • Calculating complex mathematical expressions
  • Formatting data
  • Validating data
  • Performing string manipulation

Tabular UDFs are less common, but they can be very powerful. They can be used to:

  • Join data from different tables
  • Aggregate data
  • Apply complex business logic

UDFs can be written in a variety of languages, including SQL, Python, Java, and JavaScript. The language you choose will depend on the specific task you are trying to accomplish.

To create a UDF, you use the CREATE FUNCTION statement. The syntax for this statement is as follows:

CREATE FUNCTION function_name ( [parameter_name data_type] [, ...] )
RETURNS data_type
AS
BEGIN
  -- function body
END;

The function_name is the name of the function. The parameter_name and data_type clauses define the parameters of the function. The RETURNS clause specifies the data type of the value that the function returns. The function body is the code that implements the function’s logic.

Once you have created a UDF, you can call it from SQL. The syntax for calling a UDF is as follows:

SELECT function_name ( [parameter_value] [, ...] );

The parameter_value clauses are the values that you want to pass to the function.

Here are some examples of how UDFs can be used in Snowflake:

  • You can use a UDF to calculate the area of a circle.
  • You can use a UDF to format data in a specific way.
  • You can use a UDF to validate data before it is inserted into a table.
  • You can use a UDF to join data from different tables.
  • You can use a UDF to aggregate data.
  • You can use a UDF to apply complex business logic.


The below shows a simple scalar UDF, which simply takes an input value and multiplies it by 100.

CREATE OR REPLACE FUNCTION simplecalc(inputval int)
RETURNS INT
  AS
  $$
    select inputval*100
  $$; 
SELECT simplecalc(100);

Here is another SQL example, which returns the output of a query.

CREATE OR REPLACE FUNCTION get_total_articles()
RETURNS INT
LANGUAGE SQL
AS
$$
    SELECT COUNT(*) FROM KODEY.KODEY_CO_UK.ARTICLES
$$;
SELECT get_total_articles();

We can implement a Python example too. This function returns a given value, depending on the input value.

CREATE OR REPLACE FUNCTION py_simplecalc(inputval int)
RETURNS INT
LANGUAGE PYTHON
handler = 'calc'
runtime_version = '3.8'
AS
$$
def calc(inputval):
  if inputval < 100:
      return inputval*100
  else:
      return 0
$$;
SELECT py_simplecalc(100);

We can call UDF’s directly in our SQL:

SELECT * FROM KODEY.KODEY_CO_UK.ARTICLES
WHERE py_simplecalc(length(TITLE)) != 0

Much of the base text of this article was generated with the help of Google Bard

Share the Post:

Related Posts