Data Transformation · Topic 5 of 14

Snowpark Stored Procedures and Transaction Management

Write Snowpark stored procedures in Python, Java or Scala and manage transactions: autocommit, BEGIN, COMMIT, ROLLBACK, DDL commits and procedure scope.

  • 5 min
In a nutshell
  • Snowpark procedures: handler takes Session first, PACKAGES = ('snowflake-snowpark-python'), return a value or a table.
  • DataFrame operations are pushed down to run as SQL; use a Snowpark optimized warehouse for memory heavy work.
  • Autocommit is on by default; READ COMMITTED is the only isolation level.

What it is

A Snowpark stored procedure is a procedure written in Python, Java or Scala whose handler receives a Snowpark Session and uses DataFrames to transform data. It is the right choice for multi step pipelines that need libraries, DataFrame code or complex logic, and that you want to run from a task or a CALL.

Transaction management controls when a group of DML statements becomes permanent. Procedures often wrap their work in one transaction so the changes succeed or fail together.

Task or CALLstartsSnowpark procedurehandler gets a SessionDataFrame operationsSQL pushed downrun on the warehousecommit or rollbackTables

How it works

Snowpark procedure structure

The handler's first argument is the session. The remaining arguments match the procedure's declared parameters.

CREATE PROCEDURE big_orders(min_amount FLOAT)
  RETURNS TABLE (id INT, amount FLOAT)
  LANGUAGE PYTHON
  RUNTIME_VERSION = '3.11'
  PACKAGES = ('snowflake-snowpark-python')
  HANDLER = 'run'
AS $$
from snowflake.snowpark.functions import col
def run(session, min_amount):
    return session.table('orders').filter(col('amount') > min_amount).select('id', 'amount')
$$;

CALL big_orders(1000);
  • PACKAGES must include snowflake-snowpark-python for Python. Java and Scala procedures import the Snowpark JAR and name the handler as Class.method.
  • The procedure can return a value, or a table when it is declared RETURNS TABLE(...) and the handler returns a DataFrame.
  • You can also register one from code with the @sproc decorator or session.sproc.register, and call it with session.call.
  • A UDF cannot do this, because a UDF has no session and cannot run DML.
PointDetail
Where it runsOn the warehouse that runs the CALL, inside a secure sandbox
PushdownDataFrame operations are translated to SQL and run in Snowflake; only your own Python logic runs in the handler
Heavy memory workUse a Snowpark optimized warehouse, which has more memory per node
Privilege modelSame EXECUTE AS OWNER default, or CALLER, as other procedures
Network accessOnly through an external access integration
Exam tip

Keep work as DataFrame operations so it is pushed down. Pulling rows into the handler with collect() or to_pandas() moves data into one process and is slow for large tables.

Transaction basics

FactValue
Default modeAutocommit is on: each statement commits when it succeeds and rolls back if it fails
Explicit transactionBEGIN TRANSACTION, then DML, then COMMIT or ROLLBACK
Isolation levelREAD COMMITTED is the only one supported for tables; a statement sees only data committed before that statement began
Turning autocommit offALTER SESSION SET AUTOCOMMIT = FALSE; the first DML then starts a transaction that you must end yourself
ScopeA transaction belongs to one session
LockingUPDATE, DELETE and MERGE lock the target table; a waiting statement fails after LOCK_TIMEOUT (default 43200 seconds)
VisibilitySHOW TRANSACTIONS lists running transactions

DDL commits the transaction

A DDL statement such as CREATE TABLE or ALTER TABLE first commits the open transaction, then runs as its own transaction. It cannot be rolled back, so putting DDL in the middle of a transaction ends the transaction early.

BEGIN TRANSACTIONDMLINSERT, UPDATEthen DDLImplicit COMMITDDL runs in its own transactionlater ROLLBACKNothing left to undo

Transactions inside procedures

These scoping rules are exam favorites.

  • Transactions are never nested. A procedure called inside a transaction does not create a nested one.
  • A transaction cannot span a procedure boundary. A transaction begun outside a procedure must end outside it, and one begun inside must end inside.
  • If a transaction started in a procedure is still open when the procedure ends, it is rolled back and an error is raised.
  • A procedure with no BEGIN of its own simply takes part in the caller's transaction.
  • A failed DML or CALL statement undoes its own changes, but the surrounding transaction stays active until you end it.
CREATE PROCEDURE move_funds(src INT, dst INT, amt NUMBER)
  RETURNS VARCHAR
  LANGUAGE SQL
AS
BEGIN
  BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - :amt WHERE id = :src;
  UPDATE accounts SET balance = balance + :amt WHERE id = :dst;
  COMMIT;
  RETURN 'done';
EXCEPTION
  WHEN OTHER THEN
    ROLLBACK;
    RAISE;
END;

The EXCEPTION handler rolls back and re-raises, which is the standard pattern for all or nothing work. In Snowpark you issue the same commands with session.sql("BEGIN TRANSACTION").collect(), then COMMIT or ROLLBACK in a try and except block.

Watch out

A transaction left active at the end of a session is rolled back, and so is one left open at the end of the procedure that started it. Always end it explicitly.

Snowflake can also abort idle transactions: one that blocks another transaction on the same table is aborted after 5 minutes idle, and a non blocking transaction older than 4 hours is aborted.

Common mistakes

Doing DDL inside a transaction and expecting a rollback. The DDL committed everything before it.

Starting a transaction in one procedure and ending it in another. Transactions cannot cross procedure boundaries.

Forgetting the session argument. A Snowpark handler's first parameter is the Session, not a declared argument.

Collecting a huge DataFrame in the handler. Keep operations pushed down.

What to remember for the exam

  • Snowpark procedures: handler takes Session first, PACKAGES = ('snowflake-snowpark-python'), return a value or a table.
  • DataFrame operations are pushed down to run as SQL; use a Snowpark optimized warehouse for memory heavy work.
  • Autocommit is on by default; READ COMMITTED is the only isolation level.
  • DDL commits the current transaction first and cannot be rolled back.
  • Transactions are not nested and cannot cross a procedure boundary; an unfinished one is rolled back with an error.

Check yourself

  • What must the first parameter of a Python Snowpark procedure handler be?

    The Snowpark Session object.

  • A procedure runs INSERT, then CREATE TABLE, then ROLLBACK. Is the INSERT undone?

    No. The CREATE TABLE committed the transaction before it ran.

  • What happens if a procedure ends with its own transaction still open?

    Snowflake rolls the transaction back and raises an error.

Topic 5 of 14 in Data Transformation

Test yourself on Data Transformation

The full Snowflake DEA-C02 question bank has 709 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.