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
- 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,IMPORTSandHANDLER; 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.
How it works
Choosing the language
| Language | Code location | Typical use |
|---|---|---|
| SQL | Inline expression or query | Reusable business rule, no extra runtime |
| JavaScript | Inline only | Small custom logic, string and JSON handling |
| Python | Inline, or a staged file through IMPORTS | Libraries from the Snowflake Anaconda channel, ML scoring |
| Java and Scala | Inline, or a staged JAR through IMPORTS | Existing 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=Trueand astage_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.
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';
| Aspect | Regular UDF | Secure UDF |
|---|---|---|
| Definition visible to other roles | Yes, through SHOW and GET_DDL | No, only to the owning role |
| Optimizer | Can push filters and reorder freely | Skips optimizations that might leak data through the function |
| Speed | Faster | Can be slower |
| Needed for sharing | Not allowed | Required to share a UDF with a consumer account |
You can also convert one with ALTER FUNCTION ... SET SECURE.
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.
NULLhandling is set withCALLED ON NULL INPUT(default) orRETURNS NULL ON NULL INPUT.IMMUTABLEtells Snowflake the same input always gives the same output;VOLATILEis the default.- Calling the function needs the
USAGEprivilege 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,IMPORTSandHANDLER; vectorized Python UDFs process batches. - Snowpark registers UDFs from code; permanent ones need
stage_location. SECUREhides 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.