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
- Snowpark procedures: handler takes
Sessionfirst,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 COMMITTEDis 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.
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);
PACKAGESmust includesnowflake-snowpark-pythonfor Python. Java and Scala procedures import the Snowpark JAR and name the handler asClass.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
@sprocdecorator orsession.sproc.register, and call it withsession.call. - A UDF cannot do this, because a UDF has no session and cannot run DML.
| Point | Detail |
|---|---|
| Where it runs | On the warehouse that runs the CALL, inside a secure sandbox |
| Pushdown | DataFrame operations are translated to SQL and run in Snowflake; only your own Python logic runs in the handler |
| Heavy memory work | Use a Snowpark optimized warehouse, which has more memory per node |
| Privilege model | Same EXECUTE AS OWNER default, or CALLER, as other procedures |
| Network access | Only through an external access integration |
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
| Fact | Value |
|---|---|
| Default mode | Autocommit is on: each statement commits when it succeeds and rolls back if it fails |
| Explicit transaction | BEGIN TRANSACTION, then DML, then COMMIT or ROLLBACK |
| Isolation level | READ COMMITTED is the only one supported for tables; a statement sees only data committed before that statement began |
| Turning autocommit off | ALTER SESSION SET AUTOCOMMIT = FALSE; the first DML then starts a transaction that you must end yourself |
| Scope | A transaction belongs to one session |
| Locking | UPDATE, DELETE and MERGE lock the target table; a waiting statement fails after LOCK_TIMEOUT (default 43200 seconds) |
| Visibility | SHOW 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.
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
BEGINof its own simply takes part in the caller's transaction. - A failed DML or
CALLstatement 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.
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
Sessionfirst,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 COMMITTEDis 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.