Snowflake UDFs: Scalar, SQL, JavaScript and Secure Functions

1.What a UDF is, and its variations: scalar, UDTF, UDAF

A user-defined function (UDF) adds an operation that Snowflake's built-in functions don't provide. It can also wrap a calculation your organization repeats everywhere. After you create it, you call it the same way you call a built-in function, and you can reuse it as often as you like. A function always returns a value from an expression, so it fits work that computes something and hands it back.

The word "UDF" covers several variations. They differ in how many rows go in and how many come out. Exam questions often describe what goes in and what comes out, and expect you to name the variation.

Sources1

2.Handler languages, Snowpark (Java, Python, Scala) and sharability

There are two main ways to create a UDF. In SQL, you run CREATE FUNCTION with a handler written in Java, JavaScript, Python, Scala or SQL. In the Snowpark API, you write Java, Python or Scala code on the client, and Snowflake runs that code where the data is. The Snowflake CLI, the Snowflake Python API and the REST API can also create and manage functions.

The language you choose limits what you can build. Python is the only language that supports every variation. Through SQL, it covers UDF, UDAF, UDTF and both vectorized types. JavaScript and Java support UDFs and UDTFs. Scala supports scalar UDFs only when created through SQL, but both UDFs and UDTFs through Snowpark. SQL handlers support UDFs and UDTFs. No other language offers a UDAF.

Below is a Python handler written in-line in a CREATE FUNCTION statement. LANGUAGE names the handler language. HANDLER names the Python function to call. RUNTIME_VERSION pins the Python version. You call the result like any other function, for example SELECT addone(3);.

CREATE OR REPLACE FUNCTION addone(i INT)
  RETURNS INT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  HANDLER = 'addone_py'
AS $$
def addone_py(i):
 return i+1
$$;

Two runtime details often come up in exam questions:

- If the handler calls CURRENT_DATABASE or CURRENT_SCHEMA, the result is the database or schema that contains the UDF, not the session's current one. - When reading staged files, UDTFs can process several files in parallel, but scalar UDFs currently process them one at a time.

Sources1

3.SQL UDFs

A SQL UDF evaluates any SQL expression and returns the result. A plain definition returns one scalar value. A definition declared as a table function returns a set of rows. A SQL UDF needs no LANGUAGE or HANDLER clause, because the body between the $$ delimiters is the logic.

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

The privilege model makes SQL UDFs useful for access control. If the definition names a table without a schema, Snowflake looks for it in the schema that contains the function. The function's owner must have privileges on every object the definition uses. The caller only needs privileges on the function itself. In the example below, an administrator exposes a user count without giving analysts access to the sensitive users table.

CREATE FUNCTION total_user_count() RETURNS NUMBER AS 'select count(*) from users';

GRANT USAGE ON FUNCTION total_user_count() TO ROLE analyst;

When the analyst role runs SELECT * FROM users, the query fails because the role has no access to the table. When the same role runs SELECT total_user_count(), the query succeeds.

Sources2

4.JavaScript UDFs

A JavaScript UDF is created with SQL and adds LANGUAGE JAVASCRIPT. Snowflake converts arguments and return values between SQL types and JavaScript types. One rule catches many people: inside the JavaScript body, you must refer to parameter names in all uppercase, even if you declared them in lowercase in SQL. In the example, the parameter is declared as a but read as A.

-- Create the UDF.
CREATE OR REPLACE FUNCTION my_array_reverse(a ARRAY)
  RETURNS ARRAY
  LANGUAGE JAVASCRIPT
AS
$$
  return A.reverse();
$$
;

JavaScript handlers are always written in-line; they cannot live on a stage. Like SQL UDFs, JavaScript UDFs can be shared through Secure Data Sharing. Handler code can also write log and trace data while it runs.

-- Create the UDF.
CREATE OR REPLACE FUNCTION my_array_reverse(a ARRAY)
  RETURNS ARRAY
  LANGUAGE JAVASCRIPT
AS
$$
  return  ? .reverse();
$$
;

Sources3

5.Secure UDFs

To hide a function's internals from users who shouldn't see them, add the SECURE keyword when you create or alter the UDF. A secure UDF protects two things.

First, it protects the definition. Only users granted a role that owns the function can see its body (the handler code), its list of imports, its handler name and its packages list. Other users can still see its parameter types, return type, handler language, null handling and volatility.

Second, it protects the data. Some internal optimizations for regular UDFs, such as pushdown, need access to the underlying base tables, and that access could reveal data hidden from the function's users. A secure UDF prevents this. For handlers written in Java, Python or Scala, SECURE also runs each function in its own sandbox, so no resources are shared between functions.

That cost is why the guidance is narrow. Make a UDF secure when its purpose is data privacy, meaning it limits access to sensitive data that not every user of the underlying tables should see. Don't make a UDF secure when it only exists to make queries more convenient.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Declaring every UDF SECURE is a free security improvement.Why is that wrong?

   Secure UDFs skip the optimizer shortcuts that regular UDFs get, so they can run slower. Use SECURE only for functions meant to protect data privacy, not for convenience functions.

   Covered in Secure UDFs

2. 2.A Python UDF can be shared with a consumer account through Secure Data Sharing.Why is that wrong?

   Only UDFs with JavaScript or SQL handlers can be shared. Java, Python and Scala UDFs cannot.

   Covered in Handler languages, Snowpark (Java, Python, Scala) and sharability

3. 3.To call a SQL UDF that reads a table, the caller needs SELECT on that table.Why is that wrong?

   Objects in the definition are checked against the function owner's privileges. The caller only needs privileges on the function.

   Covered in SQL UDFs