How to Use Snowflake User Defined Functions (UDF)

Snowflake User Defined Functions (UDF) are supported in Coalesce similarly to other transforms within a Node. Currently, UDFs need to be created outside of Coalesce.

Simple Example

Here is an example of a simple function in Snowflake that returns pi to 9-digits and doesn't accept any argument.

create function mydb.myschema.pi_udf()
returns float
as '3.141592654::FLOAT';

To use the above UDF as a transform in a Node, take the following steps:

1. Run the above statement in Snowflake and make note of the database and schema it's in.
2. Go to Build Settings >vStorage Locations.
3. Create a Storage Location - named FUNC in the following examples, but feel free to choose any name.
4. Map that Storage Location to mydb.myschema where the UDF was created in Step 1.
5. Use ref_no_link to specify the storage location from Step 1, with name of function as 2nd parameter. It is very important to use ref_no_link as we do not want to attempt to create a relationship to another Coalesce metadata object. In this example, {{red_no_link('FUNC', 'PI_UDF')}}().

General format - {{ref_no_link('storage_loc', 'func_name')}} (udf_argument1, udf_argument2, ...)

6. Run the node and you'll notice that all the entries for the S_ACCTBAL column will be 3.14.

Example with UDF arguments

For this example, we'll use a function that accepts one argument and carries out a simple calculation.

CREATE FUNCTION mydb.myschema.area_of_circle(radius FLOAT)
RETURNS FLOAT
AS
$$
  pi() * radius * radius
$$;

To use the above UDF as a transform in a Node, take the following steps:

1. Run the above statement in Snowflake and make note of the database and schema it's in.
2. If you went through the previous example, skip to the next step. Otherwise, follow Steps 2 to 4.
3. Use ref_no_link to specify the storage location from Step 1, with the name of the function as the 2nd parameter, and the UDF argument will be in parenthesis () afterward.

General format - {{ref_no_link('storage_loc', 'func_name')}} (udf_argument1, udf_argument2, ...)

4. Run the node and you'll notice that all the entries for the S_ACCTBAL column will be much larger numbers, as they were multiplied times themselves and pi.