User Defined Functions (UDFs) in Snowflake — Part 1

3 min readSep 11, 2021

User-defined functions (UDFs) let you extend the system to perform operations that are not available through the built-in, system-defined functions provided by Snowflake. Snowflake currently supports the following languages for writing UDFs:

SQL: A SQL UDF evaluates an arbitrary SQL expression and returns either scalar or tabular results.

JavaScript: A JavaScript UDF lets you use the JavaScript programming language to manipulate data and return either scalar or tabular results.

Java: A Java UDF lets you use the Java programming language to manipulate data and return scalar results.

Introduction to SQL UDFs:

A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression.

The function definition can be a SQL expression that returns either a scalar (i.e. single) value or, if defined as a table function, a set of rows.

We start with SQL syntax,

Example 1: (Scalar)

Write an UDF to add ‘@@’ sign beginning and end of First name and Last name.

Input:
First Name: Tata

Last Name: Harrier

Expected Output:

@@Tata Harrier@@

Script:

CREATE OR REPLACE FUNCTION String_Concat(Firstname varchar(100),Lastname varchar(100))
returns VARCHAR
as
$$
SELECT ‘@@’||Firstname||’ ‘||Lastname||’@@’ FROM DUAL
$$
;

Output:

SELECT String_Concat(‘TATA’,’HARRIER’) as FULLNAME;

Example 2: (Scalar)

How to find biggest of two numbers ?

Input:
100,500

Expected Output:
500

Script:

CREATE OR REPLACE FUNCTION Biggest_of_Two_Numbers (Num1 float, Num2 float)
returns float
as
$$
SELECT CASE WHEN Num1 > Num2 THEN Num1 ELSE Num2 END
$$
;

Output:

SELECT Biggest_of_Two_Numbers (100,500) as Biggest_Number;

Note: When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.

You can include only one query expression. The expression can include UNION [ALL].

Tabular SQL UDFs (UDTFs)

Snowflake supports SQL UDFs that return a set of rows, consisting of 0, 1, or multiple rows, each of which has 1 or more columns. Such UDFs are called tabular UDFs, table UDFs, or, most frequently, UDTFs (user-defined table functions).

Syntax:

Example 1: (Tabular)

Fetch TRIPDURATION, BIKEID, BIRTH_YEAR from Trips table by passing parameters START_STATION_ID, END_STATION_ID AND TRIPDURATION.

Below tables considered for this example,

Script:

CREATE OR REPLACE FUNCTION GetTripsDetail (START_STATION_IN number, END_STATION_IN number, TRIPDURATION_IN number)
returns table (TRIPDURATION number, BIKEID number, BIRTH_YEAR number)
as
$$
SELECT TRIPDURATION,BIKEID,BIRTH_YEAR FROM TRIPS
WHERE
TRIPS.START_STATION_ID = START_STATION_IN AND
TRIPS.END_STATION_ID = END_STATION_IN AND
TRIPS.TRIPDURATION < TRIPDURATION_IN
$$
;