Brolly Academy | Snowflake

Stored Procedures in Snowflake

Syntax, Examples, Caller vs Owner Rights, Errors and Tasks (2026)

A plain-English guide to stored procedures in Snowflake for October 2026: what they are, which languages you can use, the CREATE PROCEDURE syntax, short examples in Snowflake Scripting, JavaScript and Python, arguments and return values, caller's vs owner's rights, error handling, scheduling with tasks, how procedures differ from UDFs, and the mistakes beginners make most.

Stored procedures in Snowflake for beginners: CREATE PROCEDURE syntax, Snowflake Scripting, JavaScript and Python examples, caller vs owner rights.

SQL Scripting Python Owner vs Caller Updated 9 Nov 2026
Share

By Brolly Academy Team · Brolly Academy · Updated 24 October 2026

Stored procedures in Snowflake guide - Brolly Academy

Quick answer

A stored procedure in Snowflake is a named, reusable block of code that you create once with CREATE PROCEDURE and run with CALL, so you can execute many SQL statements, with loops, conditions and error handling, as a single step. You can write the logic (Snowflake calls it the handler) in Snowflake Scripting (SQL), JavaScript, Python, Java or Scala, according to the Snowflake stored procedures overview (Snowflake documentation, October 2026). Procedures are best for admin and pipeline work such as loading, merging, purging and auditing data. If you need a value inside a SELECT, use a UDF (user-defined function) instead.

  • Create: CREATE OR REPLACE PROCEDURE name(args) RETURNS type LANGUAGE ... AS $$ ... $$
  • Run: CALL name(values);
  • Languages: Snowflake Scripting, JavaScript, Python, Java, Scala
  • Security: runs with owner's rights by default; add EXECUTE AS CALLER for caller's rights
  • Automation: call a procedure from a task to run it on a schedule

Key takeaways

  1. A stored procedure groups many SQL statements and procedural logic into one object that you run with CALL, which makes repeated multi-step jobs easy to reuse and automate.
  2. Snowflake Scripting and JavaScript handlers must be written in-line; Python, Java and Scala handlers can be in-line or staged as files.
  3. Procedures run with owner's rights unless you write EXECUTE AS CALLER, and that one choice changes which privileges, session variables and context the code can see.
  4. Snowflake Scripting has DECLARE, BEGIN, EXCEPTION blocks, loops and named exceptions, so you can catch and report errors cleanly instead of failing silently.
  5. Use a procedure for DDL and DML work; use a UDF when you need a value inside a SQL statement. Schedule procedures with tasks rather than running them by hand.

1. Stored procedures in Snowflake at a glance

Short answer: This table gives you the whole topic on one screen, so you can jump to the part you need.

Every row below comes from Snowflake's own documentation, read in October 2026. Each later section explains one row in detail with a simplified example.

Topic What you need to know What it is A named object that runs procedural code (SQL plus loops, IF, error handling) when you CALL it Languages Snowflake Scripting (SQL), JavaScript, Python, Java, Scala Where the code lives In-line for Snowflake Scripting and JavaScript; in-line or staged for Python, Java and Scala How you run it CALL proc_name(args); one procedure per CALL statement Return value Optional in the body (returns NULL if you return nothing), but the RETURNS clause is required Return a table RETURNS TABLE(...) works for Snowflake Scripting, Python, Java and Scala, not JavaScript Security model Owner's rights by default; EXECUTE AS CALLER for caller's rights Errors EXCEPTION blocks in Snowflake Scripting; try/catch in JavaScript; Python exceptions Scheduling A task can run CALL proc_name() on a schedule or when a stream has data Size limit The procedure definition is limited to 1 MB

If you are new to Snowflake itself, read our guide on what Snowflake does first, then come back here.

2. What is a stored procedure in Snowflake?

Short answer: A stored procedure is saved code inside a Snowflake database that runs a sequence of steps, such as several INSERT, MERGE, DELETE or CREATE statements, with branching and looping, whenever you call it.

Real data work rarely fits in one statement. A nightly load might copy data, merge it into a target table, delete old rows and write an audit record. A stored procedure puts all of that in one named object that you run with a single CALL.

The Snowflake overview page (October 2026) lists the main reasons to use stored procedures:

  • Procedural logic: add IF, CASE and loops on top of SQL
  • Reuse: call the same logic from scripts, tasks or other procedures
  • Automation: wrap repeated multi-step database work into one object
  • Dynamic SQL: build a SQL string at run time and execute it
  • Controlled access: let users run a job with the owner's privileges instead of their own

A few terms you will see throughout this post:

  • Handler: the code that does the work inside the procedure
  • Snowflake Scripting: Snowflake's procedural extension to SQL, with variables, blocks, loops and exceptions
  • UDF (user-defined function): a function you write that returns a value inside a SQL statement
  • Task: a Snowflake object that runs SQL or calls a procedure on a schedule or when a stream has data
  • Stream: an object that tracks inserts, updates and deletes on a table so you can process only the changes
  • Virtual warehouse: the compute cluster that runs your queries; it uses credits, the unit Snowflake bills compute in
  • RBAC (role-based access control): privileges are granted to roles, and roles are granted to users

If you came from Oracle PL/SQL, SQL Server T-SQL or Teradata, the idea is familiar. The syntax and the security model are different, which is why most of this guide focuses on those two areas.

3. Which languages can you use for Snowflake stored procedures?

Short answer: You can write the handler in Snowflake Scripting (SQL), JavaScript, Python, Java or Scala; the language you pick decides where the code lives and whether you can return a table.

The CREATE PROCEDURE reference (Snowflake documentation, October 2026) sets out these differences.

Language Handler location Can return a table? Good fit for Snowflake Scripting (SQL) In-line only Yes, with RETURNS TABLE SQL developers, DBAs, ETL logic, migrations from PL/SQL or T-SQL JavaScript In-line only No; returns one value (use VARIANT or a delimited string for more) Older Snowflake code bases; dynamic SQL with binds Python In-line or staged Yes Data engineers using Snowpark and Python libraries Java In-line or staged Yes (tabular Java procedures are in preview) Teams with Java skills and existing JAR files Scala In-line or staged Yes (tabular Scala procedures are in preview) Teams with Scala or Spark backgrounds

How to choose, in practice:

  1. Start with Snowflake Scripting if your job is mostly SQL. It reads like the SQL you already know and needs no extra packages.
  2. Choose Python when you need DataFrame logic or Python packages. Python procedures use Snowpark, Snowflake's library for running Python, Java and Scala code inside Snowflake. Our post on Snowpark in Snowflake covers the API itself.
  3. Read JavaScript confidently, because many existing Snowflake projects still use it, even if you write new procedures in SQL or Python.
  4. Pick Java or Scala only when your team already works in them.

Job descriptions back up this mix. Of 14 Snowflake job descriptions we read on Naukri and LinkedIn on 25 October 2026, TCS, DTCC and Capgemini named stored procedures or UDFs, and an EPAM Systems role asked for Snowflake Scripting and Snowpark for Python. Our guide to the Snowflake developer role covers the rest of those skills.

4. CREATE PROCEDURE syntax explained

Short answer: Every procedure has a name with typed arguments, a RETURNS clause, a LANGUAGE clause and a body between $$ markers; optional clauses control security, null handling and packages.

Here is the anatomy of a Snowflake Scripting procedure, taken from the docs pattern.

-- Simplified example: the parts of a procedure
CREATE OR REPLACE PROCEDURE output_message(message VARCHAR)
  RETURNS VARCHAR NOT NULL
  LANGUAGE SQL
  AS
  $$
  BEGIN
    RETURN message;
  END;
  $$;

CALL output_message('Hello World');

What each clause does, from the CREATE PROCEDURE reference (October 2026):

Clause What it does Notes CREATE OR REPLACE Drops and recreates the procedure in one atomic step Add COPY GRANTS to keep existing privileges except OWNERSHIP CREATE OR ALTER Creates it, or changes the existing one in place Can change the body, return type, comment, SECURE and some language properties name(arg type, ...) The procedure name and typed arguments Snowflake identifies a procedure by name plus argument types RETURNS type The data type returned Required, even if the body returns nothing; add NOT NULL to forbid null results RETURNS TABLE(...) Returns rows and columns Not for JavaScript LANGUAGE SQL, JAVASCRIPT, PYTHON, JAVA or SCALA LANGUAGE SQL means Snowflake Scripting EXECUTE AS OWNER (default), CALLER, or RESTRICTED CALLER (preview) Section 11 explains the difference CALLED ON NULL INPUT / STRICT What happens when an argument is NULL CALLED ON NULL INPUT is the default; STRICT returns NULL without running the body RUNTIME_VERSION, PACKAGES, HANDLER Python, Java and Scala settings Python needs the snowflake-snowpark-python package AS $$ ... $$ The handler code Dollar quoting avoids escaping single quotes

Two more points from the reference that save beginners time:

  • Snowflake does not fully validate the code when you create the procedure. A typo in a table name may only show up as an error the first time you CALL it. Always run a test CALL right after creating it.
  • Procedures are not atomic. If statement five fails, statements one to four are not automatically rolled back. Plan your own transaction handling and error logging.

You can also create a TEMPORARY procedure that lasts only for your session. The overview page notes that temporary and anonymous procedures do not need the CREATE PROCEDURE privilege on the schema.

5. Example 1: a Snowflake Scripting procedure with arguments

Short answer: In Snowflake Scripting, you read an argument directly in procedural code, but inside a SQL statement you put a colon in front of it, as in :job_name.

This first real example writes an audit row every time a job runs. Audit logging is one of the most common procedures in Indian GCC and IT services projects, because support teams need to know what ran and when.

-- Simplified example: log a job run
CREATE OR REPLACE PROCEDURE log_run(job_name VARCHAR)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  BEGIN
    INSERT INTO job_audit (job_name, run_at)
      VALUES (:job_name, CURRENT_TIMESTAMP());
    RETURN 'Logged ' || job_name;
  END;
  $$;

CALL log_run('daily_sales_load');

What to notice:

  1. :job_name inside INSERT. Snowflake's Snowflake Scripting procedure docs (October 2026) say you prefix an argument with a colon when you use it inside a SQL statement.
  2. job_name without a colon in RETURN. Outside SQL statements, in expressions and assignments, you use the plain name.
  3. BEGIN ... END is required. DECLARE and EXCEPTION are optional parts of the block.

The structure of every Snowflake Scripting block is the same:

Part Required? What goes in it DECLARE Optional Variables, cursors, RESULTSETs and named exceptions BEGIN ... END Required Your statements; you can also declare variables here with LET EXCEPTION Optional Handlers for errors raised in the block

If you only want to test some logic without creating an object, you can run an anonymous block. In Snowsight you can run the block directly; in Snowflake CLI, the Classic Console or the Python connector, wrap it in EXECUTE IMMEDIATE $$ ... $$, as the Snowflake Scripting blocks page explains (October 2026).

6. Example 2: loops, variables and returning a table

Short answer: Snowflake Scripting supports FOR, WHILE, REPEAT and LOOP, and a procedure can return a full result set with RETURNS TABLE and RETURN TABLE(res).

First, a loop. This procedure adds the numbers from 1 to n. It is a toy, but the same FOR pattern drives real jobs, such as processing a list of regions or months one by one.

-- Simplified example: a counter loop
CREATE OR REPLACE PROCEDURE sum_to_n(n INTEGER)
  RETURNS INTEGER
  LANGUAGE SQL
  AS
  $$
  DECLARE
    total INTEGER DEFAULT 0;
  BEGIN
    FOR i IN 1 TO n DO
      total := total + i;
    END FOR;
    RETURN total;
  END;
  $$;

CALL sum_to_n(5);

The loop types, from the Snowflake Scripting loops page (October 2026):

Loop When to use it FOR (counter) A fixed number of passes, such as FOR i IN 1 TO 5; add REVERSE to count down FOR (cursor or RESULTSET) One pass per row of a query result WHILE Repeat while a condition is true REPEAT ... UNTIL Run at least once, then repeat until a condition is true LOOP Repeat until you hit BREAK

BREAK (or EXIT) leaves a loop, and CONTINUE (or ITERATE) skips to the next pass.

Now a procedure that returns a table. Searchers often ask "can a Snowflake stored procedure return a table?" The answer is yes for Snowflake Scripting, Python, Java and Scala.

-- Simplified example: return the top 10 sales days
CREATE OR REPLACE PROCEDURE top_sales_days()
  RETURNS TABLE (sales_date DATE, quantity NUMBER)
  LANGUAGE SQL
  AS
  $$
  DECLARE
    res RESULTSET DEFAULT (SELECT sales_date, quantity
      FROM sales ORDER BY quantity DESC LIMIT 10);
  BEGIN
    RETURN TABLE(res);
  END;
  $$;

CALL top_sales_days();

A RESULTSET is a variable that holds the result of a query. You wrap it in TABLE() when you return it. The CREATE PROCEDURE reference (October 2026) adds that you can leave the column list empty, RETURNS TABLE (), when the columns are only known at run time.

7. Example 3: a JavaScript stored procedure

Short answer: A JavaScript procedure runs SQL through the snowflake object, using createStatement with sqlText and binds, and it must refer to arguments in uppercase inside the JavaScript code.

You will meet JavaScript procedures in many existing projects, so you should be able to read and fix them.

-- Simplified example: count orders with a given status
CREATE OR REPLACE PROCEDURE count_orders(status VARCHAR)
  RETURNS FLOAT
  LANGUAGE JAVASCRIPT
  AS
  $$
    var stmt = snowflake.createStatement({
      sqlText: "SELECT COUNT(*) FROM orders WHERE status = ?",
      binds: [STATUS]
    });
    var rs = stmt.execute();
    rs.next();
    return rs.getColumnValue(1);
  $$;

CALL count_orders('SHIPPED');

Rules from Snowflake's JavaScript stored procedures guide (Snowflake documentation, October 2026):

  • Uppercase arguments. SQL is case-insensitive, but JavaScript is case-sensitive. An argument declared as status in SQL is STATUS inside the JavaScript code. Writing status in lowercase is the most common JavaScript procedure bug. The docs' other fix is to double-quote the argument name in SQL so it keeps its case.
  • Use binds, not string joins. The ? placeholder with binds passes values safely. Only JavaScript numbers, strings and SfDate values can be bound.
  • Read results row by row. Call next() to move to a row and getColumnValue(1) to read the first column.
  • One return value only. A JavaScript procedure returns a single value. For more data, return a VARIANT or a delimited string, or switch to Snowflake Scripting or Python if you need RETURNS TABLE.

Should you write new procedures in JavaScript or Snowflake Scripting? For SQL-heavy work, Snowflake Scripting is usually easier to read and lets you return tables. JavaScript remains fully supported, so there is no need to rewrite working code just for the sake of it.

8. Example 4: a Python stored procedure

Short answer: A Python procedure needs RUNTIME_VERSION, the snowflake-snowpark-python package and a HANDLER function whose first argument is a Snowpark Session that Snowflake passes in for you.

This example is the in-line Python handler pattern from the CREATE PROCEDURE reference. It copies the first N rows of one table into another.

-- Simplified example: copy N rows with Snowpark
CREATE OR REPLACE PROCEDURE copy_top_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"
  $$;

CALL copy_top_rows('orders', 'orders_sample', 100);

Key rules from Snowflake's Python procedure pages (October 2026):

  1. Session comes first. The handler's first parameter is the Snowpark Session. Snowflake creates it when you call the procedure; you cannot create it yourself, and you do not list it as a SQL argument.
  2. Runtime versions. Python 3.10 to 3.14 are generally available; 3.9 is deprecated.
  3. Snowpark package is required. Include snowflake-snowpark-python in PACKAGES.
  4. HANDLER names the function. For in-line code, give just the function name; for staged code, give module_name.function_name.
  5. Uncaught exceptions fail the call with an error that includes a Python stack trace.
  6. Async child jobs. If you start child jobs with asynchronous calls such as collect_nowait, they are cancelled when the parent job finishes first.

For DataFrames, lazy evaluation and the wider Snowpark API, see our guide to Snowpark in Snowflake. This post stays on procedure syntax.

9. Arguments and return values

Short answer: Arguments are typed in the CREATE statement, a procedure is identified by its name plus argument types, and RETURNS can be a single value or a table.

Here are the rules that cause the most confusion, all from the CREATE PROCEDURE reference (October 2026):

Rule What it means for you Overloading You can create two procedures with the same name if their argument count or types differ Optional arguments If a procedure has optional arguments, you cannot create another one with the same name and a different signature Argument names Must start with a letter, with no spaces or special characters unless double-quoted; double-quoted names are case-sensitive NULL inputs CALLED ON NULL INPUT (default) runs the body; RETURNS NULL ON NULL INPUT (or STRICT) skips it and returns NULL NOT NULL Add NOT NULL to RETURNS to forbid null results No return statement The procedure returns NULL, but you still need a RETURNS clause Dynamic SQL Inside EXECUTE IMMEDIATE strings, join the argument without a colon

Dynamic SQL deserves a short example, because the colon rule flips. When you build a SQL string, you join the plain argument name into the string, then run it with EXECUTE IMMEDIATE. The Snowflake Scripting docs show this pattern:

-- Simplified example: dynamic SQL inside a procedure body
select_statement := 'SELECT * FROM invoices WHERE id = ' || id;
res := (EXECUTE IMMEDIATE :select_statement);

Be careful with dynamic SQL built from user input. Joining raw text into SQL can open the door to SQL injection. Validate inputs, or use bind variables where the language supports them, as in the JavaScript example.

Overloading also explains a common error. Because a procedure is identified by name and argument types, ALTER, DROP and GRANT statements must include the argument types, for example DROP PROCEDURE log_run(VARCHAR). Leave the types out and Snowflake says the procedure does not exist.

10. How to call a stored procedure in Snowflake

Short answer: You run a procedure with CALL proc_name(args); you can also call one procedure from another, capture its result in a variable, or create and call a one-time anonymous procedure.

The ways to call a procedure, from the Snowflake overview and Snowflake Scripting pages (October 2026):

  1. From a worksheet or script: CALL myproc('table_a', 'table_b', 5);
  2. From another Snowflake Scripting procedure: pass variables with a colon, such as CALL insert_value(:value1);
  3. Capture the result: assign a call to a RESULTSET, res1 := (CALL my_procedure());, then loop over it with a cursor
  4. From a task: a task body can be CALL my_proc(); on a schedule (section 13)
  5. Anonymous procedure: create and call a procedure in one statement; Snowflake drops it right after
  6. From any client: Snowflake CLI, drivers and connectors send the CALL statement like any other SQL

Remember one limit from the stored procedures vs UDFs page (Snowflake documentation, October 2026): you can call only one stored procedure per SQL statement. A procedure can call other procedures, and can even call itself recursively.

You cannot use a procedure's return value directly inside an expression like SELECT myproc(). The same page lists workarounds: capture it in a Snowflake Scripting variable, call it in a FROM clause if it returns a table, read it with RESULT_SCAN, or store the results in a table.

To see who can call your procedure, remember that calling needs the USAGE privilege on the procedure, plus access to its database and schema. Grant it to a role, never to individual users, in line with RBAC.

11. Caller's rights vs owner's rights

Short answer: An owner's rights procedure (the default) runs with the privileges of the role that owns it, while a caller's rights procedure (EXECUTE AS CALLER) runs with the privileges, context and session variables of the person calling it.

This is the most important design choice in a Snowflake procedure, and a favourite interview question. The caller's and owner's rights page (Snowflake documentation, October 2026) sets out the differences.

Point Owner's rights (default) Caller's rights (EXECUTE AS CALLER) Whose privileges The owning role's The caller's Session variables and context Limited access to the caller's session Uses the caller's session variables and context Can change session state No Yes Source code visibility Hidden from non-owners (GET_DDL blocked) Visible as normal Nested calls Any procedure called from it runs with owner's rights Follows each procedure's own setting Typical use Let a support role run a controlled admin job without granting table access Utility code that should never do more than the user could do

A simplified example of switching between the two:

-- Simplified example: caller's rights, then switch to owner's
CREATE OR REPLACE PROCEDURE show_my_role()
  RETURNS VARCHAR
  LANGUAGE SQL
  EXECUTE AS CALLER
  AS
  $$
  BEGIN
    RETURN CURRENT_ROLE();
  END;
  $$;

ALTER PROCEDURE show_my_role() EXECUTE AS OWNER;

How to choose:

  • Use owner's rights when you want people to run a fixed, safe job (for example, purging rows older than 90 days) without giving them DELETE on the table. The procedure is the only door.
  • Use caller's rights for helper procedures that should respect each user's own access, or that need to read or set session variables.
  • Keep owner's rights procedures small and reviewed. They act with the owner's power, so avoid dynamic SQL built from unchecked input inside them.

There is also a third option, EXECUTE AS RESTRICTED CALLER. The CREATE PROCEDURE reference lists it as a preview feature: it runs like caller's rights but may not have all of the caller's privileges. Do not rely on it for production work until Snowflake marks it generally available.

12. Error handling in Snowflake stored procedures

Short answer: In Snowflake Scripting you declare named exceptions, RAISE them, and catch errors in an EXCEPTION block using SQLCODE, SQLERRM and SQLSTATE; JavaScript uses try/catch and Python uses normal exceptions.

Error handling is what separates a demo procedure from a production one. Without it, a failed nightly job may leave half-loaded data and no clear message.

-- Simplified example: named exception and handlers
CREATE OR REPLACE PROCEDURE check_stock(qty INTEGER)
  RETURNS VARCHAR
  LANGUAGE SQL
  AS
  $$
  DECLARE
    low_stock EXCEPTION (-20010, 'Stock too low.');
  BEGIN
    IF (qty < 10) THEN
      RAISE low_stock;
    END IF;
    RETURN 'Stock OK';
  EXCEPTION
    WHEN low_stock THEN RETURN 'Handled: ' || SQLERRM;
    WHEN OTHER THEN RETURN 'Error ' || SQLCODE || ': ' || SQLERRM;
  END;
  $$;

What the Snowflake Scripting exceptions page (Snowflake documentation, October 2026) tells you:

Item Meaning Named exception Declared in DECLARE with a number and message, such as low_stock EXCEPTION (-20010, 'Stock too low.') RAISE Throws a named exception STATEMENT_ERROR Catches errors from running a SQL statement EXPRESSION_ERROR Catches errors from evaluating an expression OTHER Catches anything not handled above SQLCODE A 5-digit signed integer error code SQLERRM The error message text SQLSTATE A 5-character error state code EXIT vs CONTINUE Handlers exit the block by default; a CONTINUE handler resumes after the failed statement

In JavaScript, wrap your snowflake calls in try/catch. The error object carries code, state, message and stackTraceTxt, so you can return or log a full description:

// Simplified example: inside a JavaScript procedure body
try {
  snowflake.execute({sqlText: "DELETE FROM stage_orders"});
  return "Done";
} catch (err) {
  return "Failed: " + err.code + " " + err.state + " " + err.message;
}

In Python, an unhandled exception fails the CALL with a stack trace. Catch the errors you expect and return or log a clear message.

A practical pattern: catch errors in the EXCEPTION block, insert a row into an error log table with SQLCODE, SQLERRM and the job name, and return a clear failure message, so support engineers can see what failed without reading the code.

13. Calling stored procedures from tasks

Short answer: A task can run CALL my_proc(); on a fixed schedule, on a cron schedule with a time zone, or when a stream has new data, which is how most production procedures run.

The CREATE TASK reference (Snowflake documentation, October 2026) includes a task whose body is a CALL. Here is the same pattern scheduled for 2 a.m. India time:

-- Simplified example: run a procedure every night at 2 a.m. IST
CREATE TASK nightly_audit_task
  WAREHOUSE = mywh
  SCHEDULE = 'USING CRON 0 2 * * * Asia/Kolkata'
  AS
    CALL log_run('nightly_load');

ALTER TASK nightly_audit_task RESUME;

What you need to know, from Snowflake's tasks documentation (October 2026):

  1. A new task starts suspended. Run ALTER TASK ... RESUME, or EXECUTE TASK for a single run.
  2. Schedules can be an interval such as '60 MINUTES' or 'USING CRON ...' with a time zone.
  3. Stream triggers. A task can run only when a stream has data, using WHEN SYSTEM$STREAM_HAS_DATA('stream_name').
  4. Compute choice. A user-managed task names a WAREHOUSE. A serverless task leaves WAREHOUSE out and needs the EXECUTE MANAGED TASK privilege.
  5. Task graphs. You can chain tasks in series or in parallel, so one procedure runs after another.
  6. Supported handlers. Tasks can call procedures written in JavaScript, Python, Java, Scala or Snowflake Scripting.

The classic pipeline is a stream on a raw table, a task that fires when the stream has data, and a stored procedure that merges the changes into a target table and logs the run. We cover streams, tasks, MERGE and recovery in depth in our Snowflake streams and tasks lab, so this post does not repeat it.

Cost note: a task that calls a procedure uses compute each time it runs, either your warehouse credits or serverless compute. Credit prices vary by region and edition, so check your own account's rates before you schedule anything to run every few minutes.

14. Stored procedures vs UDFs in Snowflake

Short answer: Use a stored procedure to do work (DDL, DML, multi-step jobs) with CALL; use a UDF to compute a value inside a SQL statement such as SELECT.

This is one of the most searched comparisons, and the stored procedures vs UDFs page (Snowflake documentation, October 2026) lays out the differences.

Point Stored procedure UDF Main purpose Run SQL statements, often admin or pipeline work Compute and return a value Return value Optional in the body (NULL if none) Required How you run it A standalone CALL statement Inside a SQL statement or expression Use in SELECT No (use workarounds) Yes, anywhere an expression is allowed Per SQL statement One procedure per statement Many UDFs per statement Database access DDL, DML and queries Simple queries only; no DDL or DML Languages Java, JavaScript, Python, Scala, Snowflake Scripting Java, JavaScript, Python, Scala, SQL, Snowflake Scripting

Choose a stored procedure when:

  • you are migrating a procedure from Oracle, SQL Server or Teradata
  • you need to create, update, delete or purge data, add users or drop temporary tables
  • the job has several steps that must run in order

Choose a UDF when:

  • you need a value for every row or group, such as a tax calculation or a cleaned-up phone number
  • you want to call it inside SELECT, WHERE or JOIN
  • you only need simple queries

The Snowflake UDF overview (October 2026) adds one more difference for data sharing: JavaScript and SQL UDFs can be shared through Secure Data Sharing, while Java, Python and Scala UDFs cannot.

15. Real use cases for stored procedures

Short answer: Most real procedures in Snowflake projects do loading, merging, cleanup, auditing or controlled admin work, usually triggered by a task.

These are the jobs you are most likely to build in a Hyderabad, Bengaluru, Pune or Chennai data team, whether at an IT services firm or a GCC:

Use case What the procedure does Language usually chosen Incremental load Reads changes from a stream and MERGEs them into a target table Snowflake Scripting SCD Type 2 load Closes old dimension rows and inserts new versions with dates Snowflake Scripting Audit and error logging Writes job name, row counts, start and end times, and errors to a log table Snowflake Scripting Data purge Deletes rows older than a retention period, run with owner's rights Snowflake Scripting Dynamic table copy Loops over a list of table names and builds SQL for each Snowflake Scripting or JavaScript DataFrame transformation Uses Snowpark to filter, join and save tables in Python Python Admin automation Creates schemas or roles for a new project from a template Snowflake Scripting (owner's rights) Legacy migration Rewrites Oracle PL/SQL or SQL Server T-SQL procedures for Snowflake Snowflake Scripting

If you work with dbt, you will also see procedures used alongside dbt models for steps that do not fit a model, such as admin or cleanup work. Many job titles now pair Snowflake with dbt: Naukri showed about 1,729 results for "dbt snowflake" on 25 October 2026 (keyword matches, not a count of open jobs).

16. Best practices for Snowflake stored procedures

Short answer: Keep procedures small and single-purpose, choose the rights model on purpose, handle and log errors, use binds or validated inputs for dynamic SQL, and keep the code in Git.

A checklist you can use on every procedure:

  1. One job per procedure. A procedure that loads, merges and purges is hard to test. Split it and call the parts in order.
  2. Decide rights deliberately. Owner's rights is the default, so write EXECUTE AS CALLER when you mean it, and add a comment explaining why.
  3. Test right after you create it. Snowflake does not fully validate code at creation, so run a CALL with sample inputs immediately.
  4. Handle errors in every production procedure. Use an EXCEPTION block or try/catch, and log the error to a table.
  5. Plan for partial failure. Procedures are not atomic, so design steps that can be re-run safely, for example with MERGE rather than plain INSERT.
  6. Avoid unsafe dynamic SQL. Use binds in JavaScript and validate any input you join into SQL strings, especially in owner's rights procedures.
  7. Use CREATE OR REPLACE with COPY GRANTS, or CREATE OR ALTER, so redeploying does not silently remove privileges.
  8. Include argument types in GRANT, ALTER and DROP. For example, GRANT USAGE ON PROCEDURE log_run(VARCHAR) TO ROLE etl_role.
  9. Grant to roles, not users. Follow RBAC and keep a small set of roles with USAGE on each procedure.
  10. Prefer Snowflake Scripting for SQL-heavy logic and Python for DataFrame or library logic; keep JavaScript for existing code.
  11. Keep the code in Git and deploy through scripts or Snowflake CLI rather than editing in production worksheets.
  12. Watch compute. A procedure scheduled every few minutes on a large warehouse can burn credits; size the warehouse for the job and let it auto-suspend.
  13. Stay under the 1 MB definition limit. If a procedure is that large, it should be several procedures or staged Python code.

17. Common errors and how to fix them

Short answer: Most stored procedure errors come from argument references, missing argument types, privileges, rights model surprises and code that was never tested after creation.

Error or symptom Likely cause Fix "Procedure does not exist or not authorized" on DROP, ALTER or GRANT Argument types left out of the name Write proc_name(VARCHAR, INTEGER) with the exact types Same error on CALL Role lacks USAGE on the procedure, or the database or schema Grant USAGE to the calling role; check your current role and schema SQL compilation error on an argument inside a query Missing colon in Snowflake Scripting Use :arg_name inside SQL statements Dynamic SQL fails or matches nothing Colon used inside an EXECUTE IMMEDIATE string, or wrong quoting Join the plain argument into the string; quote text values JavaScript says a variable is not defined Argument used in lowercase Use the uppercase name inside the JavaScript code RETURNS TABLE rejected JavaScript handler Use Snowflake Scripting, Python, Java or Scala for tabular results Python handler error about arguments Session not the first parameter, or listed as a SQL argument Make session the first handler argument and leave it out of the SQL signature Cannot set a session variable or see the caller's context Procedure runs with owner's rights Use EXECUTE AS CALLER if the design allows it Users cannot see the procedure code Owner's rights hides the source (GET_DDL blocked) Expected behaviour; share code through Git instead Half the data changed after a failure Procedures are not atomic Add error handling, make steps re-runnable, use MERGE Scheduled task never runs New tasks start suspended Run ALTER TASK ... RESUME Errors only on first CALL, not on CREATE Code is not fully validated at creation Test every procedure with a CALL after each change

For debugging, run a test CALL right after creating it, return intermediate values while you test, write progress rows to a log table, and read the error message with SQLCODE and SQLERRM. Then remove the extra output once the procedure is stable.

18. Stored procedure interview questions to prepare

Short answer: Interviewers usually test the language options, the CALL syntax, owner's vs caller's rights, error handling, tasks and the procedure vs UDF difference.

Practise answering these out loud in two or three sentences each:

  1. Which languages can you use for a Snowflake stored procedure, and which must be in-line?
  2. What is the difference between owner's rights and caller's rights?
  3. When would you choose a UDF instead of a stored procedure?
  4. How do you return a table from a procedure?
  5. What happens if statement four of six fails inside a procedure?
  6. How would you migrate an Oracle PL/SQL procedure to Snowflake?

For the full concept bank, see our Snowflake interview questions. For realistic project situations, such as a failed nightly MERGE, see Snowflake scenario-based interview questions.

19. How Brolly Academy helps

Short answer: Brolly Academy's Snowflake course in Hyderabad covers tasks, streams and calling procedures from tasks, plus error handling and validation, inside a 45-day programme with projects and interview preparation.

If you want to learn procedures as part of a full Snowflake skill set, the Snowflake Training in Hyderabad course page (checked 25 October 2026) lists:

  • Duration and modes: 45 days, in classroom or live online batches, plus a self-paced video plan
  • Fees: Video ₹8,999, Online ₹17,999, Classroom ₹22,999, with EMI available
  • Trainer: Mr. Bala Krishna, with 13+ years in data warehousing, SQL and Snowflake
  • Syllabus: 15 modules, including a scheduling module on tasks, streams and calling procedures from tasks, and an advanced module covering error handling and validation, resource monitoring and best practices
  • Projects: 6 projects, including ETL pipeline development and a financial data warehouse
  • Certificate: a Brolly Academy completion certificate; SnowPro exams are run by Snowflake and their fees are separate
  • Support: a free demo class, resume help and mock interviews

The course teaches Python integration through the Snowflake Python connector. Snowpark is not a syllabus module, so learn Python procedures through Snowflake's own documentation and our Snowpark guide.

Write your first Snowflake stored procedure with a trainer beside you

Reading syntax is one thing; debugging a failed MERGE at 2 a.m. is another. Brolly Academy's 45-day Snowflake course in Hyderabad, available as classroom, live online or video, walks you through stages, loading, streams, tasks and calling procedures from tasks, with 6 projects and mock interviews. Book a free demo class to see how it fits your schedule.

Frequently asked questions

What is a stored procedure in Snowflake?

A stored procedure in Snowflake is a named object that runs a sequence of steps, such as several SQL statements with loops, conditions and error handling, whenever you call it with CALL. You create it once with CREATE PROCEDURE and reuse it from worksheets, scripts, tasks or other procedures. Teams use procedures mainly for loading, merging, purging, auditing and controlled admin jobs that need more than one SQL statement.

Which languages can I use to write Snowflake stored procedures?

You can write the handler in Snowflake Scripting, which is SQL, or in JavaScript, Python, Java or Scala. Snowflake Scripting and JavaScript handlers must be written in-line inside the CREATE PROCEDURE statement. Python, Java and Scala handlers can be in-line or staged as files. Most SQL developers start with Snowflake Scripting, while data engineers who work with DataFrames often choose Python with Snowpark.

How do I create a stored procedure in Snowflake?

Use CREATE OR REPLACE PROCEDURE with a name, typed arguments, a RETURNS clause, a LANGUAGE clause and the code between double dollar signs. For Snowflake Scripting, write LANGUAGE SQL and put your statements inside BEGIN and END. Then run a test CALL straight away, because Snowflake does not fully validate the code when you create it, so some mistakes only appear on the first call.

How do I call a stored procedure in Snowflake?

Run CALL followed by the procedure name and its arguments, for example CALL log_run('daily_sales_load'). You can also call a procedure from another procedure, capture its result in a RESULTSET variable, or call it from a task on a schedule. Only one stored procedure can be called per SQL statement, but a procedure can call other procedures and even itself recursively.

What is Snowflake Scripting?

Snowflake Scripting is Snowflake's procedural extension to SQL. It adds variables, DECLARE, BEGIN and EXCEPTION blocks, IF and CASE logic, loops, cursors, RESULTSETs and named exceptions. You use it by writing LANGUAGE SQL in a stored procedure, or by running an anonymous block. In Snowflake CLI, the Classic Console or the Python connector, you wrap anonymous blocks in EXECUTE IMMEDIATE with double dollar signs.

Why do I need a colon before arguments in Snowflake Scripting?

Inside a SQL statement, such as an INSERT or a SELECT in a RESULTSET, Snowflake Scripting needs a colon before a variable or argument name, like :job_name, so it knows you mean the variable and not a column. Outside SQL statements, in assignments, conditions and RETURN, you use the plain name. When you build a string for EXECUTE IMMEDIATE, you also join the plain name without a colon.

Can a Snowflake stored procedure return a table?

Yes. Write RETURNS TABLE with the column names and types, put a query into a RESULTSET variable, and return it with RETURN TABLE(res). Tabular procedures work with Snowflake Scripting and Python, and with Java and Scala, where tabular procedures are in preview. JavaScript procedures cannot return a table and return only a single value, so they use a VARIANT or a delimited string for richer output.

What is the difference between a stored procedure and a UDF in Snowflake?

A stored procedure does work: it runs DDL, DML and multi-step jobs, and you run it with a standalone CALL. A UDF computes and returns a value, and you use it inside a SQL statement such as SELECT. A procedure's return value is optional, while a UDF must return a value. You can use many UDFs in one statement, but only one procedure per CALL.

When should I use a UDF instead of a stored procedure?

Use a UDF when you need a value for every row or group inside a query, such as a tax calculation, a cleaned-up code or a category label used in SELECT, WHERE or JOIN. UDFs only run simple queries and cannot run DDL or DML. If your logic needs to create, update, delete or purge data, or run several steps in order, a stored procedure is the right choice.

What is EXECUTE AS CALLER vs EXECUTE AS OWNER in Snowflake?

EXECUTE AS OWNER, the default, runs the procedure with the privileges of the role that owns it, hides the source from non-owners and cannot change session state. EXECUTE AS CALLER runs with the caller's privileges, context and session variables. Owner's rights suits controlled admin jobs that users run without direct table access, while caller's rights suits helper code that should respect each user's own access.

Can I change a procedure from owner's rights to caller's rights?

Yes. You can switch an existing procedure with ALTER PROCEDURE, including the argument types in the name, followed by EXECUTE AS CALLER or EXECUTE AS OWNER. You can also recreate it with CREATE OR REPLACE and the new clause. Remember that any procedure called from inside an owner's rights procedure also runs with owner's rights, so test nested calls after you change the setting.

How do I handle errors in a Snowflake stored procedure?

In Snowflake Scripting, add an EXCEPTION block with handlers such as WHEN STATEMENT_ERROR, WHEN EXPRESSION_ERROR and WHEN OTHER, and read SQLCODE, SQLERRM and SQLSTATE to describe the error. You can declare named exceptions with a number and message and throw them with RAISE. In JavaScript use try and catch, and in Python catch the exceptions you expect and log a clear message.

What do SQLCODE, SQLERRM and SQLSTATE mean?

They are built-in variables you can read inside a Snowflake Scripting exception handler. SQLCODE is a 5-digit signed integer error code, SQLERRM is the error message text, and SQLSTATE is a 5-character error state code. Many teams insert all three, along with the job name and time, into an error log table so support engineers can see exactly what failed.

Are Snowflake stored procedures transactional?

No, not automatically. Snowflake's CREATE PROCEDURE reference says procedures are not atomic, so if one statement fails, the statements before it are not necessarily rolled back. Design your procedures so they can be re-run safely, for example by using MERGE instead of plain INSERT, and add error handling that logs what happened so you can fix the data and run the job again.

How do I schedule a stored procedure in Snowflake?

Create a task whose body is a CALL statement, give it a schedule such as '60 MINUTES' or a cron expression with a time zone, and name a warehouse or make it serverless. A new task starts suspended, so run ALTER TASK with RESUME to start it, or EXECUTE TASK for a single run. A task can also run only when a stream has new data.

Can a task call a Python or JavaScript stored procedure?

Yes. Snowflake's tasks documentation says tasks can call stored procedures written in JavaScript, Python, Java, Scala or Snowflake Scripting. The task body is simply a CALL statement. You can also chain several tasks into a task graph so that procedures run in series or in parallel, which is how many teams build nightly load pipelines with streams, tasks and procedures.

How do I write a stored procedure in Python?

Write LANGUAGE PYTHON, choose a RUNTIME_VERSION such as 3.11, add the snowflake-snowpark-python package in PACKAGES, and set HANDLER to your function name. The function's first argument must be the Snowpark Session, which Snowflake creates and passes in when you call the procedure. Python 3.10 to 3.14 are generally available for procedures, and Python 3.9 is deprecated.

Should I use Snowflake Scripting or JavaScript for new procedures?

For SQL-heavy logic, Snowflake Scripting is usually easier to read and maintain, and it can return a table with RETURNS TABLE, which JavaScript cannot. JavaScript is still fully supported and is common in older Snowflake projects, so you should be able to read and fix it. Choose Python when you need DataFrames or Python libraries, and keep Java or Scala for teams already using them.

Why does my JavaScript procedure say an argument is undefined?

JavaScript is case-sensitive, while SQL is not. An argument declared in lowercase in the CREATE PROCEDURE statement must be written in uppercase inside the JavaScript code, so status becomes STATUS. Using the lowercase name is the most common JavaScript procedure bug. You can also double-quote the argument name in SQL if you want to keep a specific case.

Why does DROP PROCEDURE say the procedure does not exist?

Snowflake identifies a procedure by its name plus its argument types, because you can overload procedures with different signatures. So DROP, ALTER and GRANT statements must include the argument types, for example DROP PROCEDURE log_run(VARCHAR). If you leave the types out, or your role lacks the right privileges on the procedure, Snowflake reports that the procedure does not exist or is not authorised.

Can I use a stored procedure inside a SELECT statement?

No, not directly. A stored procedure runs as a standalone CALL, and its return value cannot be used in an expression the way a UDF can. Snowflake lists workarounds: capture the value in a Snowflake Scripting variable, call a table-returning procedure in a FROM clause, read the result with RESULT_SCAN, or store results in a table. If you need a value per row, write a UDF.

What are the limitations of Snowflake stored procedures?

The main limits are: only one procedure per CALL statement, the return value cannot be used directly in expressions, the definition is limited to 1 MB, procedures are not atomic, and JavaScript procedures return only one value. Snowflake also does not fully validate code at creation, so errors may only appear on the first call. Owner's rights procedures also cannot change session state.

How do I debug a Snowflake stored procedure?

Run a test CALL right after creating it, because some errors only appear at run time. While testing, return intermediate values, write progress rows to a log table and catch errors with an EXCEPTION block that returns SQLCODE and SQLERRM. In JavaScript, return the error code, state, message and stack trace from the catch block. Remove the extra output once the procedure is stable.

Are stored procedures asked in Snowflake interviews and job descriptions?

Yes. Of 14 Snowflake job descriptions we read on Naukri and LinkedIn on 25 October 2026, roles at TCS, DTCC and Capgemini named stored procedures or UDFs, and an EPAM Systems role asked for Snowflake Scripting and Snowpark for Python. Interviewers commonly ask about languages, owner's vs caller's rights, error handling, scheduling with tasks and the difference between procedures and UDFs.

Does Brolly Academy's Snowflake course cover stored procedures?

Brolly Academy's 45-day Snowflake course in Hyderabad includes a scheduling module on tasks, streams and calling procedures from tasks, and an advanced module covering error handling and validation. Fees are ₹8,999 for video, ₹17,999 for online and ₹22,999 for classroom, with EMI available. Snowpark is not a syllabus module, so learn Python procedures from Snowflake's documentation alongside the course.

Learn with Brolly Academy

Review the syllabus, practical projects and batch timings, and book a free demo before you enrol.

Related Articles

Continue Your Learning

Snowflake Training in Hyderabad

45-day course covering Snowflake architecture, warehouses, data loading, security, performance and real-time projects, in classroom, live online and recorded plans.

View Course

dbt Course

Learn dbt, the transformation tool most Snowflake job descriptions ask for, to build tested, documented data models.

View Course

Databricks Course

Add the lakehouse side of modern data engineering with Databricks, Spark and Delta Lake, often used alongside Snowflake.

View Course