Snowflake UDFs, UDTFs and Stored Procedures

1.Scalar UDFs and UDTFs: one row in, how much out?

Snowflake's built-in functions don't cover everything. When you need logic they lack, or a calculation your organization defines in its own standard way, you write a user-defined function. You call a UDF just as you would call a built-in function, and once it exists you can reuse it in any query. The function's logic is called its *handler*, and you write it in one of the supported languages.

The first thing to decide is the shape of the output. A plain UDF is a scalar function: each input row produces exactly one value. A user-defined table function (UDTF) produces a whole table for each input row, so it fits jobs like exploding one record into many. The documentation lists further variations. A user-defined aggregate function (UDAF) works across many rows. Vectorized UDFs and UDTFs receive their input in batches as Pandas DataFrames.

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
$$;

You call it with SELECT addone(3);. The choice of shape has practical consequences too. When functions read staged files, UDTFs can process several files in parallel, but scalar UDFs currently handle files one after another. There is also a scoping detail to remember: if the handler calls CURRENT_DATABASE or CURRENT_SCHEMA, it gets back the database or schema that contains the UDF, not the one the session is currently using.

Sources1

2.Handler languages, handler location and sharing

You can write a handler in Java, JavaScript, Python, Scala or SQL, but the languages don't all support the same features. Python is the only language that supports every variation (UDF, UDAF, UDTF and both vectorized forms). Scala supports only scalar UDFs when you create them with SQL. Two other properties also depend on the language. The first is where the handler code can live: in-line in the CREATE FUNCTION statement, or staged as a file. The second is whether the finished UDF can be shared, meaning used with Snowflake Secure Data Sharing.

Sources1

3.Stored procedures: procedural code you CALL

A UDF calculates a value. A stored procedure, by contrast, can automate work that takes several database operations, and it can build and run SQL statements dynamically. The documentation's example is a cleanup job that deletes old data from several tables using one cut-off date parameter. Users run one procedure instead of remembering every table name.

The workflow is: write the handler (Java, JavaScript, Python, Scala or Snowflake Scripting), create the procedure with CREATE PROCEDURE, then run it with CALL. A procedure can return a single value or, where the handler language supports it, tabular data. If you only need a procedure once, you can create a temporary procedure that lasts for the current session, or an anonymous procedure that runs and is dropped in a single statement. Neither of these requires the CREATE PROCEDURE privilege.

CREATE OR REPLACE PROCEDURE myproc(from_table STRING, to_table STRING, count INT)
  RETURNS STRING
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.12'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
as
$$
def run(session, from_table, to_table, count):
  session.table(from_table).limit(count).write.save_as_table(to_table)
  return "SUCCESS"
$$;

Sources2

4.Owner's rights versus caller's rights

A procedure can run with the privileges of the role that owns it. That lets the owner delegate a specific power, such as the cleanup job above, to users who couldn't otherwise perform it. This is also the default: when you don't specify EXECUTE AS, the procedure runs as an owner's rights procedure, because Snowflake chooses the setting that gives it less access to the caller's environment.

An owner's rights procedure follows these rules within a session:

- It runs with the owner's privileges, not the caller's. - It inherits the caller's current warehouse. - It uses the database and schema it was created in, not the ones the caller has active. - It cannot view, set or unset the caller's session variables, and it can use only a subset of the caller's session parameters.

The last rule explains a common symptom: a procedure that reads $region from the caller's session always finds it empty. To give the procedure the caller's privileges and environment, declare it EXECUTE AS CALLER. You can also switch an existing procedure with ALTER PROCEDURE … EXECUTE AS CALLER. A third option, EXECUTE AS RESTRICTED CALLER, runs with caller's rights but might not have all of the caller's privileges.

Sources234

5.Handling exceptions in Snowflake Scripting

A Snowflake Scripting procedure handles errors in an EXCEPTION section at the end of a block. Each block can have only one exception handler. That handler can still catch several kinds of exception, because it can contain several WHEN clauses, each naming a declared exception, plus a final WHEN OTHER THEN clause that catches anything not already listed. Each clause can be marked EXIT or CONTINUE.

The handler's scope is precise. It covers only the statements between BEGIN and EXCEPTION in the block where it is declared. It does not cover the DECLARE section. Inside the handler you can run SQL statements (including CALL), control-flow statements or nested blocks, so writing the error to an audit table is just an INSERT inside a WHEN clause. If the procedure is meant to return a value, it should return one on every exit path, including each EXIT-type WHEN clause. The EXCEPTION reference points to RAISE for raising exceptions. These sources do not describe how RAISE behaves, so this lesson doesn't either.

Sources5

6.Asynchronous child jobs in procedures

By default, a Snowflake Scripting block runs its child jobs one after another, and each waits for the previous one to finish. If you put the ASYNC keyword before a query, that query runs in the background while the block moves on. The query can be a SELECT or a DML statement such as INSERT or UPDATE. Independent statements can then run concurrently, which can shorten the total run time.

There are two ways to use ASYNC. You can attach it to a query whose result goes into a RESULTSET, then run AWAIT res1; before reading that result. You can also apply it to a standalone statement and finish with AWAIT ALL;, which waits for every running child job. CANCEL stops a child job that is running for a RESULTSET, and SYSTEM$GET_RESULTSET_STATUS reports its status. Up to 4,000 child jobs can currently run concurrently. Because they can finish in any order, using LAST_QUERY_ID with them is non-deterministic.

CREATE OR REPLACE PROCEDURE test_async_child_job_inserts()
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  CREATE OR REPLACE TABLE test_child_job_queries1 (col1 INT);
  ASYNC (INSERT INTO test_child_job_queries1(col1) VALUES(1));
  ASYNC (INSERT INTO test_child_job_queries1(col1) VALUES(2));
  ASYNC (INSERT INTO test_child_job_queries1(col1) VALUES(3));
  AWAIT ALL;
END;

  BEGIN
     ?  res1;
    LET cur1 CURSOR FOR res1;
    OPEN cur1;

Sources6

Exam traps

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

1. 1.A procedure created with default settings can read the calling user's session variables.Why is that wrong?

   The default is owner's rights, and an owner's rights procedure cannot see the caller's session variables. Declare EXECUTE AS CALLER when the procedure needs the caller's environment.

   Covered in Owner's rights versus caller's rights

2. 2.Any UDF can go into a Secure Data Sharing share, whatever its handler language.Why is that wrong?

   Only SQL and JavaScript UDFs are sharable. Java, Python and Scala UDFs are not.

   Covered in Handler languages, handler location and sharing