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
In a nutshell
  • 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 CALLER uses 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.

Triggertask, user or toolCALLStored procedurelogic and control flowrunsSQL, DML, DDLinside SnowflakewritesTables

How it works

Languages and handlers

LanguageNotes
SQL (Snowflake Scripting)Blocks with DECLARE, BEGIN, END; loops, cursors, RESULTSET, exceptions
JavaScriptUses the built in snowflake object to run statements
Python, Java, ScalaUse the Snowpark API; need a runtime version and packages
Python with SnowparkDataframe 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

SettingRuns withTypical use
EXECUTE AS OWNER (default)Privileges of the role that owns the procedureLet users do a controlled action without giving them table privileges
EXECUTE AS CALLERPrivileges of the role that calls itUtility 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.

Watch out

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

FeatureStored procedureUDF
Called withCALL as its own statementInside a query, such as SELECT my_fn(col)
ReturnA value, or a table with RETURNS TABLEA value per row, or rows with a UDTF
DDL and DMLYesNo, a UDF is for calculation
Used in SELECT or WHERENoYes
Typical callerTasks, pipelines, adminsQueries, 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

Needhow complex is the logic?one SELECTView or dynamic tabledeclarativemulti step logicProcedure plus taskproceduralper row calculationUDFused inside SQL
  • 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 CALLER uses 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 OWNER and USAGE granted 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.