Writing and choosing between SQL, JavaScript, and Snowpark UDFs

User-Defined Functions (UDFs) let you extend SQL with custom scalar or tabular logic written in SQL, JavaScript, Python, Java, or Scala, returning either a single value or a set of rows. They are called inline within SQL statements like built-in functions and are ideal for encapsulating reusable business logic or complex computations.

1 · Learn the must-know

• 1UDFs can be scalar (return one value per call) or tabular/UDTFs (return a set of rows, used with LATERAL or in the FROM clause).
• 2Snowflake supports SQL, JavaScript, Python, Java, and Scala as UDF languages, each defined with CREATE FUNCTION and a LANGUAGE clause.
• 3UDFs can be created as SECURE to hide the function definition and logic from unauthorized users, at the cost of some query optimization visibility.
• 4Unlike stored procedures, UDFs cannot execute DDL/DML or control transactions—they are meant for computation, not side effects, and are used purely as expressions within SQL.
• 5Python, Java, and Scala UDFs run in a sandboxed environment and can reference external packages via PACKAGES clause or staged files via IMPORTS.
• 6UDFs can be overloaded (same name, different argument signatures) and support default values, but each overload is a distinct function object internally.

2 · Check your understanding