Data Engineering · Topic 14 of 16
Stored Procedures and Data Processing Options
Choose how to process data in Snowflake: procedure languages, owner and caller rights, procedures versus UDFs, and when SQL, Snowpark or tasks fit.
- 5 min
- A stored procedure is run with CALL, can do DDL and DML, supports SQL, JavaScript, Python, Java and Scala.
- Default is
EXECUTE AS OWNER;EXECUTE AS CALLERuses the caller's privileges and session context. - Use a procedure for multi step, procedural or administrative work and a UDF for calculations inside queries.
What it is
Data processing in Snowflake means running logic next to the data instead of pulling it out. The main building blocks are SQL statements, stored procedures for procedural logic, functions for per row or per table calculations, and tasks and streams for scheduling and change capture.
A stored procedure is a named routine that runs statements and logic such as loops, branches and error handling. It is called with CALL, can run DDL and DML, and is the usual unit of work that a task runs.
How it works
Languages and handlers
| Language | Notes |
|---|---|
| SQL (Snowflake Scripting) | Blocks with DECLARE, BEGIN, END; loops, cursors, RESULTSET, exceptions |
| JavaScript | Uses the built in snowflake object to run statements |
| Python, Java, Scala | Use the Snowpark API; need a runtime version and packages |
| Python with Snowpark | Dataframe code runs as SQL pushed down to the warehouse |
CREATE OR REPLACE PROCEDURE load_daily(run_date DATE)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
DELETE FROM sales_daily WHERE sale_date = :run_date;
INSERT INTO sales_daily
SELECT * FROM sales_stage WHERE sale_date = :run_date;
RETURN 'Loaded ' || :run_date;
END;
$$;
CALL load_daily('2026-11-13');
A Python procedure names its handler and packages, for example RUNTIME_VERSION = '3.11', PACKAGES = ('snowflake-snowpark-python') and HANDLER = 'main'. The first argument to the handler is the Snowpark session.
Owner's rights and caller's rights
| Setting | Runs with | Typical use |
|---|---|---|
EXECUTE AS OWNER (default) | Privileges of the role that owns the procedure | Let users do a controlled action without giving them table privileges |
EXECUTE AS CALLER | Privileges of the role that calls it | Utility procedures that act on the caller's own objects |
An owner's rights procedure is a way to expose a limited operation. The caller needs only USAGE on the procedure, not on the tables it touches. A caller's rights procedure uses the caller's current database and schema and can see the caller's session variables; an owner's rights procedure uses the database and schema it was created in and cannot see the caller's session variables.
The default is owner's rights. A procedure that must work on objects the caller owns, or read session state, needs EXECUTE AS CALLER set explicitly.
Procedures compared with UDFs
| Feature | Stored procedure | UDF |
|---|---|---|
| Called with | CALL as its own statement | Inside a query, such as SELECT my_fn(col) |
| Return | A value, or a table with RETURNS TABLE | A value per row, or rows with a UDTF |
| DDL and DML | Yes | No, a UDF is for calculation |
| Used in SELECT or WHERE | No | Yes |
| Typical caller | Tasks, pipelines, admins | Queries, views |
If the question is "run a nightly job that deletes, inserts and grants", the answer is a procedure. If it is "apply a custom calculation to every row in a query", it is a UDF.
Choosing a processing approach
- Plain SQL, views, dynamic tables: best when the work is a query. They are optimized by Snowflake and need little code.
- Stored procedures: when you need control flow, several dependent statements, dynamic SQL, error handling or administrative actions.
- Snowpark procedures: when the team writes Python, Java or Scala and wants dataframes or third party packages; the heavy work still runs in the warehouse.
- Tasks: schedule a procedure or SQL statement. Streams add change capture.
- External processing (Spark, Kafka consumers): only when logic cannot run in Snowflake or already exists elsewhere.
Keep processing set based. A procedure that loops over rows one at a time and runs a statement per row is slow, because each statement has its own overhead. One MERGE over the whole set is far faster.
Transactions and errors
A stored procedure that runs in AUTOCOMMIT mode commits each statement as it goes. To make several statements succeed or fail together, use BEGIN TRANSACTION, COMMIT and ROLLBACK inside the procedure, with an EXCEPTION block in Snowflake Scripting to roll back on failure. A procedure called inside an open transaction joins that transaction.
Procedures can return a single value or a table. They are limited to one return value per call, so return a status string, a count or a result set rather than several separate values.
Common mistakes
Calling a procedure inside a SELECT. Procedures run through CALL only; use a UDF for per row logic in a query.
Expecting owner's rights to see the caller's session. Use caller's rights when the procedure depends on the caller's context.
Row by row processing in a loop. Use set based SQL or MERGE.
Leaving a multi step load without a transaction. A failure part way leaves half the work committed.
What to remember for the exam
- A stored procedure is run with CALL, can do DDL and DML, supports SQL, JavaScript, Python, Java and Scala.
- Default is
EXECUTE AS OWNER;EXECUTE AS CALLERuses the caller's privileges and session context. - Use a procedure for multi step, procedural or administrative work and a UDF for calculations inside queries.
- Tasks run procedures on a schedule; Snowpark code is pushed down to the warehouse.
- Prefer set based SQL over loops, and wrap multi step work in a transaction.
Check yourself
Users must run a controlled cleanup on a table they cannot touch directly. What do you create?
A stored procedure with
EXECUTE AS OWNERandUSAGEgranted to those users.Can a stored procedure be used in a SELECT list?
No. It is invoked with CALL; a UDF is the object that works inside a query.
Which setting lets a procedure use the caller's current schema?
EXECUTE AS CALLER.
Topic 14 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 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.