All topics
53 / 66
Snowflake

Stored Procedures & UDFs (JavaScript, SQL, Python)

Encapsulate reusable logic and procedural workflows directly inside Snowflake.

Snowflake supports both stored procedures and user-defined functions (UDFs), but they serve different purposes. Stored procedures encapsulate procedural logic — control flow, loops, multiple DML statements, error handling — and are invoked with CALL. UDFs, by contrast, are scalar or tabular functions invoked inline within a SQL query (like SELECT my_func(col) FROM table), and must return a value without side effects beyond that computation (for scalar/table UDFs).

A UDF is like a calculator function you plug into a spreadsheet formula — cell in, value out; a stored procedure is more like a macro that can open dialogs, make decisions, and touch several sheets in sequence.

Key Concepts

1
Both support multiple languages: SQL for simple logic, JavaScript for more complex procedural code with native JSON handling, and increasingly Python (and Java) via Snowpark, which allows importing common data science and utility libraries. Python UDFs and procedures run in a secure sandboxed environment managed by Snowflake.
SQLJavaScriptPython
2
UDTFs (user-defined table functions) are a special case that can return multiple rows and columns per call, useful for tasks like parsing a complex string into multiple output rows — something a scalar UDF cannot do.
UDTFs
3
Stored procedures can also execute with either the caller's rights (running with the invoking user's privileges) or owner's rights (running with the privileges of whoever created the procedure), which matters significantly for security design — owner's rights procedures can be used to grant controlled, elevated access to specific operations without giving users broad underlying permissions.
caller's rightsowner's rights
Use cases:stored-proceduresudfjavascriptpythonsnowpark