Data Transformation · Topic 1 of 14

User-Defined Functions - SQL, JavaScript, Snowpark and Secure UDFs

Pick the right scalar UDF language in Snowflake: SQL, JavaScript, Python, Java and Scala, Snowpark registration, vectorized batches and secure UDFs.

  • 4 min
In a nutshell
  • A scalar UDF returns one value per row; languages are SQL, JavaScript, Python, Java and Scala.
  • SQL UDFs are simplest and best optimized; JavaScript arguments are uppercase.
  • Python adds PACKAGES, IMPORTS and HANDLER; vectorized Python UDFs process batches.

What it is

A user-defined function (UDF) is a function you write yourself and call inside a query, like a built-in function. A scalar UDF returns one value for each input row, and it can be used in the SELECT list, WHERE, JOIN and other expressions.

Snowflake lets you write UDF logic in SQL, JavaScript, Python, Java and Scala. The code runs inside Snowflake, next to the data, on the warehouse that runs the query. A UDF cannot run DDL or DML; for that you need a stored procedure.

QuerySELECT my_udf(col)calls per rowUDFSQL, JavaScript, Python, Java or Scalareturns one valueResult column

How it works

Choosing the language

LanguageCode locationTypical use
SQLInline expression or queryReusable business rule, no extra runtime
JavaScriptInline onlySmall custom logic, string and JSON handling
PythonInline, or a staged file through IMPORTSLibraries from the Snowflake Anaconda channel, ML scoring
Java and ScalaInline, or a staged JAR through IMPORTSExisting JVM code and libraries

SQL UDFs are the simplest and usually the fastest, because the optimizer can see through them as an expression.

CREATE FUNCTION net_price(price NUMBER, tax_rate NUMBER)
  RETURNS NUMBER
AS 'price * (1 + tax_rate)';

JavaScript UDFs receive arguments as uppercase variable names inside the body, a classic exam trap. The body is a JavaScript function body and must return a value.

CREATE FUNCTION clean_name(name VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
AS $$
  return NAME.trim().toUpperCase();
$$;

Python, Java and Scala UDFs name a HANDLER (a function, or Class.method for JVM languages) and a runtime version. Python adds PACKAGES for third party libraries, and IMPORTS pulls files from a stage.

CREATE FUNCTION score(x FLOAT)
  RETURNS FLOAT
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('numpy')
  HANDLER = 'run'
AS $$
import numpy as np
def run(x):
    return float(np.tanh(x))
$$;

Snowpark UDFs

A Snowpark UDF is a UDF you register from Snowpark code instead of writing CREATE FUNCTION by hand. In Python you use the @udf decorator or session.udf.register; Snowpark uploads the code and its dependencies to a stage and creates the function.

  • A temporary UDF lives only for the session.
  • A permanent UDF needs is_permanent=True and a stage_location, and other sessions can call it.
  • The result is a normal UDF that SQL can call too.

For speed, a Python UDF can be vectorized: the handler receives a batch of rows as a pandas DataFrame or Series instead of one row at a time. This suits numeric and ML work where per row calls are slow.

Snowpark code@udf decoratoruploads code toStagedependenciescreatesPermanent UDFcalled fromSQL or DataFrame

Secure UDFs

Add the SECURE keyword to hide the function definition and protect data from the optimizer.

CREATE SECURE FUNCTION region_label(code VARCHAR)
  RETURNS VARCHAR
AS 'CASE code WHEN ''E'' THEN ''East'' ELSE ''Other'' END';
AspectRegular UDFSecure UDF
Definition visible to other rolesYes, through SHOW and GET_DDLNo, only to the owning role
OptimizerCan push filters and reorder freelySkips optimizations that might leak data through the function
SpeedFasterCan be slower
Needed for sharingNot allowedRequired to share a UDF with a consumer account

You can also convert one with ALTER FUNCTION ... SET SECURE.

Exam tip

A scenario that says the logic is proprietary and must be shared with another account, or that rows must never leak through the optimizer, points to a secure UDF. Accept the performance cost.

Calling and managing UDFs

  • Call a UDF by name in a query, with a schema qualifier if needed.
  • Overloading is allowed: the same name with different argument types or counts.
  • NULL handling is set with CALLED ON NULL INPUT (default) or RETURNS NULL ON NULL INPUT.
  • IMMUTABLE tells Snowflake the same input always gives the same output; VOLATILE is the default.
  • Calling the function needs the USAGE privilege on it.

Python, Java and Scala UDFs can reach the network only through an external access integration, and are otherwise isolated in a sandbox.

Common mistakes

Using a UDF for DDL or DML. UDFs cannot change objects or tables. Use a stored procedure.

Lowercase arguments in JavaScript. Inside the JavaScript body the argument price must be written PRICE.

Choosing JavaScript for library work. JavaScript has no package support; use Python or Java for libraries.

Forgetting SECURE before sharing. A plain UDF cannot be shared, and exposing a definition through SHOW or GET_DDL reveals your logic.

What to remember for the exam

  • A scalar UDF returns one value per row; languages are SQL, JavaScript, Python, Java and Scala.
  • SQL UDFs are simplest and best optimized; JavaScript arguments are uppercase.
  • Python adds PACKAGES, IMPORTS and HANDLER; vectorized Python UDFs process batches.
  • Snowpark registers UDFs from code; permanent ones need stage_location.
  • SECURE hides the definition, limits optimizer pushdown, and is required for sharing.
  • UDFs cannot run DDL or DML; stored procedures can.

Check yourself

  • A Data Engineer must reuse a simple tax calculation in many queries with the best optimizer visibility. Which UDF language?

    A SQL UDF, because the optimizer treats it like an inline expression.

  • How are arguments named inside a JavaScript UDF body?

    In uppercase, even if they were declared in lowercase.

  • Why make a UDF secure?

    To hide its definition from other roles, prevent optimizer based data leaks, and allow it to be shared.

Topic 1 of 14 in Data Transformation

Test yourself on Data Transformation

The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.