A Snowflake stored procedure lets you wrap procedural logic, branching, loops, and multiple SQL statements into one named object you can call on demand. It is how teams automate cleanup jobs, run multi-step transformations, and delegate privileged operations safely inside the platform.
The catch is that Snowflake gives you several ways to write one. You can use Snowflake Scripting in pure SQL, the Snowpark API in Python, Java, or Scala, or older JavaScript procedures. Each path has its own syntax, its own strengths, and its own failure modes.
This guide walks through every option with real code, then covers parameters, return types, transactions, error handling, owner versus caller rights, and how to schedule procedures with Tasks. It is written for engineers who build on Snowflake every day, not for a feature tour. If you are weighing platforms first, our Snowflake vs Redshift comparison and our overview of the cloud data warehouse market are useful background.
If you are still mapping the wider platform, our primers on Snowflake architecture and the Snowflake data warehouse set the context for where procedures fit. Procedures are the automation layer that sits on top of that storage and compute design.
Key Takeaways A Snowflake stored procedure wraps procedural logic, branching, loops, and multiple SQL statements into one named object you call with CALL. You can write the handler in Snowflake Scripting (SQL), Snowpark (Python, Java, Scala), or legacy JavaScript, and the choice hinges on team skills and library needs. Procedures differ from UDFs: they run as standalone statements, may return nothing, and can perform DML and DDL that functions cannot. Owner’s rights, the default, runs with the owner’s privileges for safe delegation, while caller’s rights runs with the caller’s privileges and session context. Wrap multi-write logic in explicit transactions with exception handling, and schedule procedures with Snowflake Tasks for automated pipelines. Kanerika, a Snowflake Select Tier Partner, replaced manual reconciliation with governed Snowflake procedures and pipelines, cutting reconciliation effort by 60% for a distributed enterprise. What Is a Snowflake Stored Procedure? A stored procedure is a named database object that holds procedural code. Unlike a single SQL statement, it can branch, loop, declare variables, and run many operations in sequence before returning a result.
According to Snowflake’s official documentation , procedures let you automate tasks that need multiple database operations, dynamically build and execute SQL, and run code with the privileges of the owning role rather than the caller. That last point is what makes them a safe delegation tool.
Picture a routine that deletes data older than a cutoff date across many tables. Instead of remembering every table name, a team calls one procedure and passes the cutoff as a parameter. The logic lives in one place and stays consistent.
People often confuse procedures with user-defined functions. The difference is simple but important, and getting it wrong forces teams to buy peak capacity and watch it idle.
Stored Procedure vs UDF A user-defined function must return a value and is called inside a query, often once per row. A stored procedure runs as a standalone statement with the CALL keyword and may return nothing at all.
Use a UDF when you need to compute and return a value as part of a SELECT, like a tax rate or a formatted string. Use a stored procedure for administrative actions: creating objects, cleaning up tables, or orchestrating a multi-step load that feeds your business intelligence tools . This distinction shapes how the platform actually works under the hood.
Kanerika Service
Snowflake Consulting and Implementation
Kanerika is a Snowflake Select Tier Partner that designs, migrates, and operates Snowflake environments end to end, from architecture and stored-procedure libraries to AI-ready pipelines.
Explore Snowflake Services Aspect Stored Procedure User-Defined Function How it is called CALL as its own statementInside a SELECT expression Return value Optional, can return nothing Mandatory, must return a value Typical use Admin tasks, multi-step jobs, DDL Row-level calculations DML inside Allowed Not allowed
Languages You Can Use for Snowflake Stored Procedures Snowflake supports five handler languages for procedures: SQL Scripting, Python, Java, Scala, and JavaScript. The right one depends on team skills, library needs, and whether the logic is mostly SQL or mostly application code, the same trade-off that shapes any ETL vs ELT design.
SQL Scripting is the native choice for SQL-heavy workflows. Snowpark covers Python, Java, and Scala for procedures that need real programming constructs or external libraries. JavaScript is the legacy path that predates the others, which Kanerika has covered in a dedicated practical guide.
Handler code can live in-line inside the CREATE PROCEDURE statement, or on a stage as a compiled artifact. Not every language supports both, so location matters when you pick.
SQL Scripting : in-line only, best for SQL-centric logic and quick administrative jobs.Python (Snowpark) : in-line or staged, ideal for data science and complex transformations.Java and Scala (Snowpark) : in-line or staged, suited to existing JVM codebases.JavaScript : in-line only, a legacy option you should avoid for new work.Snowflake Scripting Procedures (SQL) Snowflake Scripting is the SQL dialect for procedural logic. You declare variables, open cursors, branch, and loop, all in familiar SQL syntax wrapped in a BEGIN ... END block.
Here is a minimal procedure that returns the message passed in. The LANGUAGE SQL clause tells Snowflake to treat the body as Scripting.
CREATE OR REPLACE PROCEDURE hello_world(message VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
RETURN message;
END;A more useful pattern declares variables and binds an argument into a query. The colon prefix marks a bind variable, and INTO captures the result.
CREATE OR REPLACE PROCEDURE get_value(id NUMBER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
result VARCHAR;
BEGIN
SELECT value INTO :result FROM sp_test WHERE id = :id;
RETURN result;
END;Cursors let you iterate over query results and act on each row. This procedure finds every empty table in a database and drops it, building each statement dynamically. It is the kind of cleanup job procedures were designed for.
Talk to Kanerika
Automating Multi-Step Jobs on Snowflake?
Kanerika scopes which jobs belong in stored procedures, how to schedule them with Tasks, and how to govern them safely. A short working session turns your backlog into a plan.
Schedule a Demo → CREATE OR REPLACE PROCEDURE clean_empty_tables(db_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
table_list RESULTSET DEFAULT (
SELECT table_name, table_schema FROM information_schema.tables
WHERE table_catalog = :db_name AND row_count = 0);
c1 CURSOR FOR table_list;
drop_stmt VARCHAR;
BEGIN
FOR rec IN c1 DO
drop_stmt := 'DROP TABLE ' || :db_name || '.' || rec.table_schema || '.' || rec.table_name;
EXECUTE IMMEDIATE :drop_stmt;
END FOR;
RETURN 'cleaning done';
END;Snowflake Scripting also supports IF, CASE, and three loop forms (FOR, WHILE, and REPEAT), as documented in the Snowflake Scripting reference . For teams comfortable in SQL, this is the lowest-friction option and it pairs naturally with Snowflake dynamic tables for declarative pipelines.
Snowpark Procedures (Python, Java, Scala) Snowpark procedures run application code that pushes work down to Snowflake’s compute. They shine when logic outgrows SQL: DataFrame transformations, machine learning steps, the kind of data transformation work that needs a real library set, or AI features built on Snowflake Cortex .
A Python procedure names a runtime version, declares its packages, and points a HANDLER at the function to run. The function receives a session object as its first argument, which is your entry point to Snowpark DataFrames.
CREATE OR REPLACE PROCEDURE copy_rows(from_table STRING, to_table STRING, count INT)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'run'
AS
$$
def run(session, from_table, to_table, count):
session.table(from_table).limit(count).write.save_as_table(to_table)
return "SUCCESS"
$$;The double-dollar delimiters mark the code body, and the session argument carries the security context of the procedure. Everything you do through that session respects owner or caller rights, just like a Scripting procedure.
Python procedures can be in-line, as above, or staged for larger codebases. Snowpark is also the natural home for AI-driven data analytics workflows that read from governed tables inside the Snowflake data warehouse . Java and Scala follow the same pattern with their own handler signatures.
Listen on Spotify
7 Ways Predictive Analytics Complements Business Intelligence
Decision factor Choose Snowflake Scripting (SQL) Choose Snowpark (Python/Java/Scala) Primary logic SQL statements and DDL DataFrame and application code External libraries None needed Pandas, scikit-learn, custom packages Team skill set SQL analysts and engineers Python or JVM developers Handler location In-line only In-line or staged Best fit Cleanup, orchestration, dynamic SQL ML pipelines, heavy transformations
JavaScript Procedures (Legacy) JavaScript was the original procedural language for Snowflake procedures, and a lot of older code still uses it. It works, but it is the option you should plan to migrate away from for new development.
Case Study
60% Less Manual Reconciliation via Snowflake Migration
A global technology consulting firm replaced manual reconciliation across regional systems with governed, centralized Snowflake data and automated procedures, cutting reconciliation effort by 60% and giving distributed teams real-time visibility.
Read the Case Study → A JavaScript procedure encloses its body in $$ delimiters and uses the snowflake object to run SQL through createStatement and execute. The syntax is verbose compared to Scripting.
CREATE OR REPLACE PROCEDURE row_count(table_name STRING)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
var stmt = snowflake.createStatement(
{sqlText: "SELECT COUNT(*) FROM " + TABLE_NAME});
var rs = stmt.execute();
rs.next();
return rs.getColumnValue(1);
$$;Note that JavaScript binds arguments as uppercase identifiers, so table_name becomes TABLE_NAME in the body. This casing rule trips up many engineers and is a frequent source of bugs. Reddit threads on why JavaScript procedures persist reflect how much teams want to leave them behind.
For most new procedures, Snowflake Scripting or Snowpark deliver the same result with cleaner syntax and better tooling. Treat JavaScript as maintenance-only, and convert it during your next platform refresh or data migration project.
Parameters, Arguments, and Return Types Procedures accept typed arguments you pass at call time. Arguments can carry default values, which makes them optional, and Snowflake requires that required arguments come before optional ones in the signature.
CREATE OR REPLACE PROCEDURE greet(
name VARCHAR,
greeting VARCHAR DEFAULT 'Hello')
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
RETURN :greeting || ', ' || :name;
END;The RETURNS clause is mandatory even when a procedure returns nothing useful. To build a procedure with no real output, declare a return type and simply omit the RETURN value, which yields NULL to the caller.
Procedures can also return a table. You declare RETURNS TABLE(...) with column names and types, capture rows in a RESULTSET, and return it with the TABLE() function.
CREATE OR REPLACE PROCEDURE recent_orders()
RETURNS TABLE(id NUMBER, customer VARCHAR)
LANGUAGE SQL
AS
DECLARE
res RESULTSET DEFAULT (SELECT id, customer FROM orders LIMIT 10);
BEGIN
RETURN TABLE(res);
END;Scalar return types include STRING, NUMBER, BOOLEAN, and the semi-structured VARIANT, OBJECT, and ARRAY types. Tabular returns are powerful for procedures that feed downstream queries or refresh Snowflake dynamic tables and reports.
Calling Stored Procedures You invoke a procedure with the CALL statement, passing arguments by position. A procedure call cannot be embedded inside a SELECT, which is one of the clearest lines between procedures and functions.
CALL clean_empty_tables('ANALYTICS_DB');
CALL greet('Asha'); -- uses the default greeting
CALL greet('Asha', 'Hi'); -- overrides the defaultSnowflake also supports anonymous procedures with CALL ... WITH, which create and run a procedure in a single statement without persisting it. This is handy for one-off logic that should not clutter the schema, and it sidesteps the need for the CREATE PROCEDURE privilege.
To run a procedure you need the USAGE privilege on it, plus whatever privileges its execution mode demands. That mode is the next thing to understand, because it decides who can do what, and it has become a board-level topic rather than an engineering detail.
Owner’s Rights vs Caller’s Rights Every procedure runs in one of two modes. Owner’s rights, the default, executes with the privileges of the role that owns the procedure. Caller’s rights executes with the privileges of whoever calls it.
Owner’s rights is how you delegate safely. You can give analysts a procedure that deletes old records without granting them the DELETE privilege on the tables directly. The procedure does what the owner permits, not what the caller permits, which is a cornerstone of any sound data governance framework .
Caller’s rights is the choice when the procedure must see the caller’s session context, session variables, or only objects the caller already owns. The trade-off is that the caller needs every privilege the procedure touches.
Behavior Owner’s Rights (default) Caller’s Rights Runs with privileges of The procedure owner The caller Caller session access Limited, cannot change session state Full, changes persist after the call Best for Delegating privileged tasks Session-aware, self-owned logic
You set the mode in the CREATE PROCEDURE statement with EXECUTE AS OWNER or EXECUTE AS CALLER. The Snowflake security documentation spells out the access boundaries for each, and getting this choice right is central to a sound governance posture.
Transactions in Stored Procedures A procedure can group several writes into one transaction so they commit or roll back together. By default Snowflake runs in autocommit mode, where each statement commits on its own, so you use explicit BEGIN TRANSACTION and COMMIT to control the boundary.
CREATE OR REPLACE PROCEDURE transfer(amount NUMBER, from_acct NUMBER, to_acct NUMBER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - :amount WHERE id = :from_acct;
UPDATE accounts SET balance = balance + :amount WHERE id = :to_acct;
COMMIT;
RETURN 'transfer complete';
EXCEPTION
WHEN OTHER THEN
ROLLBACK;
RETURN 'transfer failed: ' || SQLERRM;
END;Pair every explicit transaction with exception handling so a mid-procedure failure triggers a ROLLBACK instead of leaving half-applied writes. Snowflake does not nest transactions, so a procedure called inside another transaction joins the outer one rather than starting its own.
Keep transactions short. Long-running transactions hold locks and can block other writers, a pattern that hurts concurrency and inflates compute cost across busy warehouses. Strong data observability helps you catch these stalls before they cascade.
Error Handling and Exceptions Snowflake Scripting provides structured exception handling through an EXCEPTION block inside BEGIN ... END. You can catch the built-in OTHER condition or declare your own named exceptions for specific error codes.
CREATE OR REPLACE PROCEDURE safe_divide(a NUMBER, b NUMBER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
div_by_zero EXCEPTION (-20001, 'Cannot divide by zero');
BEGIN
IF (b = 0) THEN
RAISE div_by_zero;
END IF;
RETURN (:a / :b)::VARCHAR;
EXCEPTION
WHEN div_by_zero THEN
RETURN 'Error: ' || SQLERRM;
WHEN OTHER THEN
RETURN 'Unexpected: ' || SQLSTATE || ' ' || SQLERRM;
END;The built-in variables SQLCODE, SQLERRM, and SQLSTATE give you the error number, message, and state inside a handler. Use them to log context before you return or re-raise.
Good error handling turns silent failures into signals you can act on. Log the failure, return a clear status, and let the orchestration layer decide whether to retry, which is far better than a procedure that swallows errors and reports success.
Scheduling Procedures with Snowflake Tasks A procedure on its own runs only when something calls it. Snowflake Tasks turn a procedure into a scheduled job that fires on a cron expression or whenever an upstream task finishes.
CREATE OR REPLACE TASK nightly_cleanup
WAREHOUSE = ops_wh
SCHEDULE = 'USING CRON 0 2 * * * America/New_York'
AS
CALL clean_empty_tables('ANALYTICS_DB');
ALTER TASK nightly_cleanup RESUME;Tasks are created in a suspended state, so you must RESUME them before they run. You can chain tasks into a dependency tree with the AFTER clause, which is how teams build the multi-step pipelines that move data through your data integration tools where each procedure runs only after its predecessor succeeds.
The Snowflake Tasks documentation covers serverless versus warehouse-backed execution and monitoring. Scheduling is where stored procedures graduate from manual helpers to the backbone of automated workflows, and Kanerika builds exactly these multi-source pipelines for enterprise clients.
Kanerika Service
Data Integration and Pipeline Engineering
Kanerika builds the governed, multi-source pipelines that stored procedures and Tasks orchestrate, with quality, lineage, and monitoring built in.
Explore Data Integration Best Practices for Snowflake Stored Procedures Procedures are code, so treat them like code. The habits below keep them maintainable, secure, and cheap to run as your estate grows.
Version control the source. Procedures are not covered by Snowflake Time Travel , so keep the definitions in Git to recover prior versions.Choose the execution mode deliberately. Default to owner’s rights for delegation and switch to caller’s rights only when session context demands it.Wrap multi-write logic in transactions. Commit or roll back as a unit, and always pair it with an exception handler so a partial load never reaches downstream consumers.Capitalize and document parameters. Consistent naming and inline comments save the next engineer hours of guessing.Prefer Scripting or Snowpark over JavaScript. New procedures should use the modern languages for cleaner syntax and better tooling.Following these consistently is what keeps unit costs flat as adoption grows. A library of well-governed procedures becomes an asset; a pile of undocumented ones becomes technical debt.
Common Mistakes to Avoid Most procedure problems trace back to a handful of recurring errors. Watch for these, because they are easy to ship and painful to debug later.
Mixing up owner and caller rights. The wrong mode either leaks privileges or blocks legitimate callers with permission errors.Forgetting JavaScript’s uppercase binding. Arguments become uppercase identifiers in the body, so lowercase references silently fail.Skipping exception handling. A procedure without an EXCEPTION block reports failures unpredictably and can leave partial writes.Building unsafe dynamic SQL. Concatenating raw input into EXECUTE IMMEDIATE invites injection, so bind variables wherever possible.Letting transactions run long. Big transactions hold locks and raise costs, so keep the boundary tight.How Kanerika Builds Production-Grade Snowflake Automation Kanerika is a Snowflake Select Tier Partner that designs, migrates, and operates Snowflake environments end to end. Stored procedures are rarely the headline of an engagement, but they are the connective tissue that makes automated pipelines reliable, and we treat them with the same rigor as the data model itself.
Our delivery for procedure-heavy workloads follows four stages. We assess the existing procedure estate, flag legacy JavaScript and unsafe dynamic SQL, and map execution-mode risk. We design a standard library with consistent naming, exception handling, and version control in Git. We build the procedures in Snowflake Scripting or Snowpark and wire them into Tasks for scheduling. We govern them with owner-rights delegation, resource monitors, and lineage through our data governance practice so the automation stays auditable.
Case Study
Real-Time Insights Across Distributed Operations
See how Kanerika migrated a distributed enterprise onto governed Snowflake data with automated procedures and pipelines, cutting manual reconciliation effort by 60% and delivering real-time operational visibility.
Read the Case Study → That work sits inside our broader data integration and data analytics practices, and our FLIP platform accelerates the ingestion pipelines that procedures often orchestrate. As a CMMI Level 3, ISO 27001, and SOC 2 Type II certified partner, governance and security are built into how we ship, not bolted on after.
One global technology consulting firm shows the payoff. Working across distributed regional systems, the client had teams reconciling data by hand every month. Kanerika migrated them onto governed, centralized Snowflake data with automated procedures and pipelines replacing the manual steps, cutting reconciliation effort by 60% and giving distributed teams real-time operational visibility into operations that previously surfaced in month-end reports.
The practitioner lesson we carry into every engagement is that procedures fail quietly. A swallowed exception, a wrong execution mode, or a procedure outside version control will not break a demo, but it will break a 2 a.m. scheduled run. Building for that night is the difference between automation you trust and automation you babysit.
Frequently Asked Questions What is a Snowflake stored procedure? A Snowflake stored procedure is a named database object that holds procedural code such as branching, loops, variables, and multiple SQL statements. You call it with the CALL statement to automate tasks like data cleanup, multi-step transformations, or privileged operations. Unlike a single query, it can run many operations in sequence and may return a scalar value, a table, or nothing at all.
What language can I use to write a Snowflake stored procedure? Snowflake supports five handler languages: SQL Scripting, Python, Java, Scala, and JavaScript. SQL Scripting is the native choice for SQL-heavy logic, while Snowpark covers Python, Java, and Scala for procedures that need real programming constructs or external libraries. JavaScript is the original, legacy option that you should avoid for new development in favor of Scripting or Snowpark.
What is the difference between a stored procedure and a UDF in Snowflake? A user-defined function must return a value and is called inside a SELECT, often once per row, while a stored procedure runs as a standalone CALL statement and may return nothing. Procedures are used for administrative actions, DDL, and multi-step jobs, and they can perform DML that functions cannot. Use a UDF for row-level calculations and a procedure for orchestration and cleanup.
What is the difference between owner's rights and caller's rights? Owner’s rights, the default, runs a procedure with the privileges of the role that owns it, which is how you delegate privileged tasks without granting those privileges directly. Caller’s rights runs with the privileges and session context of whoever calls the procedure. You set the mode with EXECUTE AS OWNER or EXECUTE AS CALLER in the CREATE PROCEDURE statement.
How do you call a stored procedure in Snowflake? You invoke a procedure with the CALL statement, passing arguments by position, for example CALL my_proc(‘value’). A procedure call cannot be embedded inside a SELECT, which is one of the clearest differences from a function. Snowflake also supports anonymous procedures with CALL … WITH, which create and run a procedure in a single statement without persisting it.
Can a Snowflake stored procedure return a table? Yes. You declare the return type as RETURNS TABLE with column names and types, capture rows in a RESULTSET variable, and return it with the TABLE() function. Tabular returns are useful for procedures that feed downstream queries, dynamic tables, or reports. Procedures can also return scalar types like STRING, NUMBER, and BOOLEAN, or the semi-structured VARIANT, OBJECT, and ARRAY types.
How do you handle errors in a Snowflake stored procedure? Snowflake Scripting provides an EXCEPTION block inside BEGIN … END where you catch the built-in OTHER condition or declare named exceptions for specific error codes. Inside a handler, the variables SQLCODE, SQLERRM, and SQLSTATE give you the error number, message, and state. Good practice is to log context, return a clear status, and roll back any open transaction so failures never leave partial writes.
Can you schedule a Snowflake stored procedure? Yes. Snowflake Tasks turn a procedure into a scheduled job that fires on a cron expression or after an upstream task finishes. A task is created suspended, so you must run ALTER TASK … RESUME before it executes, and you can chain tasks with the AFTER clause to build multi-step pipelines. This is how teams move stored procedures from manual helpers to automated workflows.
