What you will be able to do
- Distinguish explicit and implicit transactions, and predict how DDL and AUTOCOMMIT end them
- Predict which rows survive when one statement inside a transaction fails
- Apply the scope rule that a transaction cannot cross a stored procedure boundary
- Write a try/catch procedure that commits on success and rolls back on error
1.Explicit and implicit transactions, and how DDL ends them
A transaction is a sequence of SQL statements that Snowflake processes as one atomic unit: either all are committed or all are rolled back, with ACID guarantees. You start one explicitly with BEGIN (Snowflake recommends the form BEGIN TRANSACTION) and end it with COMMIT or ROLLBACK. A transaction belongs to a single session, and transactions are never nested. If a transaction is already active, another BEGIN TRANSACTION is generally ignored.
DDL behaves differently. Each DDL statement, including CREATE TABLE AS SELECT, runs as its own transaction. If you issue DDL while a transaction is open, Snowflake first commits the open transaction implicitly, then runs the DDL separately. That is why explicit transactions should contain only DML statements (INSERT, UPDATE, DELETE, MERGE, TRUNCATE) and query statements (SELECT, CALL).
AUTOCOMMIT, which defaults to TRUE, controls statements outside an explicit transaction. With it on, each such statement is its own transaction: committed if it succeeds, rolled back if it fails. Statements inside an explicit BEGIN TRANSACTION are unaffected. With AUTOCOMMIT off, the first DML statement after a transaction ends implicitly starts a new one. Do not change AUTOCOMMIT inside a stored procedure, because doing so raises an error.
Checkpoint 1 of 5· Check yourself
Inside an explicit transaction, a procedure runs an INSERT, then a CREATE TABLE, then a second INSERT, and finally issues ROLLBACK. What is undone?
The DDL implicitly commits the open transaction, which includes the first INSERT, and then commits itself as a separate transaction. The second INSERT starts a new transaction, and that is all ROLLBACK can undo.
“Because a DDL statement is its own transaction, you cannot roll back a DDL statement”Source: docs.snowflake.com
Sources1
2.When one statement in a transaction fails
CREATE TABLE table1 (i int);
BEGIN TRANSACTION;
INSERT INTO table1 (i) VALUES (1);
INSERT INTO table1 (i) VALUES ('This is not a valid integer.'); -- FAILS!
INSERT INTO table1 (i) VALUES (2);
COMMIT;
SELECT i FROM table1 ORDER BY i;Committing a transaction as a unit is not the same as it succeeding or failing as a unit. A failed statement's own changes are rolled back, but the transaction stays open until something commits or rolls it back. If that turns out to be COMMIT, the successful statements are kept.
Whether COMMIT is ever reached depends on where the code runs. Snowsight stops at the first error, while SnowSQL with -f keeps executing. Inside a Snowflake Scripting procedure, the failed INSERT raises an exception. If nothing handles the exception, the procedure never reaches COMMIT, the open transaction is rolled back, and the table ends up empty. If a handler commits the work done before the failure and skips the remaining statements, only row 1 is kept.
Checkpoint 2 of 5· Check yourself
The three INSERTs above are inside a Snowflake Scripting procedure that has no exception handler. What does table1 contain after the CALL?
The unhandled exception stops the procedure before COMMIT, so Snowflake rolls back the open transaction, including row 1.
“the stored procedure never completes, and the COMMIT is never executed, so the open transaction is implicitly rolled back.”Source: docs.snowflake.com
Sources1
3.A transaction cannot cross a procedure boundary
Stored procedures follow the general transaction rules plus one extra constraint on scope. A transaction can sit entirely inside a procedure, or a CALL can sit entirely inside a transaction. A transaction cannot start on one side of the procedure boundary and finish on the other.
In practice, this rules out three patterns:
- Starting a transaction before the CALL and committing it inside the procedure. Snowflake reports an error saying a transaction started at a different scope can't be modified. - Starting a transaction inside the procedure and leaving it open on return. When the procedure ends, Snowflake raises an error and rolls the transaction back, and this is true whether the transaction was started explicitly or implicitly. - Splitting a transaction across nested calls. If procedure A calls procedure B, every BEGIN TRANSACTION in A needs its COMMIT or ROLLBACK in A, and the same holds for B.
Inside one procedure, an explicit transaction can still cover just part of the body, for example a middle statement wrapped in BEGIN TRANSACTION ... COMMIT between two statements outside it.
Checkpoint 3 of 5· Check yourself
A script runs BEGIN TRANSACTION, then CALL load_orders(), and load_orders() ends with COMMIT. What happens?
A procedure cannot complete a transaction its caller started. Snowflake rejects the attempt with an error instead of committing or nesting.
“Modifying a transaction that has started at a different scope is not allowed.”Source: docs.snowflake.com
Sources1
4.Committing or rolling back with try/catch
Inside a single procedure, the usual pattern is to open a transaction, run the related statements in a try block, commit at the end of the try, and roll back in the catch. JavaScript handlers have try/catch built in. If an error occurs, the catch block can undo every statement, but only if those statements were run inside a transaction. The documentation's cleanup procedure deletes from a child table and then its parent table. Its force_failure argument lets the caller trigger a deliberate error to watch the rollback happen.
var result = "";
snowflake.execute( {sqlText: "BEGIN WORK;"} );
try {
snowflake.execute( {sqlText: "DELETE FROM child;"} );
snowflake.execute( {sqlText: "DELETE FROM parent;"} );
if (FORCE_FAILURE === "fail") {
// To see what happens if there is a failure/rollback,
snowflake.execute( {sqlText: "DELETE FROM no_such_table;"} );
}
snowflake.execute( {sqlText: "COMMIT WORK;"} );
result = "Succeeded";
}
catch (err) {
snowflake.execute( {sqlText: "ROLLBACK WORK;"} );
return "Failed: " + err; // Return a success/error indicator.
}
return result;The code uses FORCE_FAILURE in uppercase, which matches the argument-case rule for JavaScript procedures. The transaction starts and ends in the same procedure, so it satisfies the scope rule from the previous section. Every path either commits or rolls back, so the transaction is never left open when the procedure returns. CALL cleanup('fail') deletes from both tables, fails on no_such_table, and rolls back both deletes. CALL cleanup('do not fail') commits them.
Checkpoint 4 of 5· Put it in order
Put the successful run of the cleanup procedure in execution order
- 1.Set result to "Succeeded" and return it
- 2.Delete from the child table inside the try block
- 3.Delete from the parent table
- 4.Execute COMMIT WORK
- 5.Execute BEGIN WORK to open the transaction
The transaction opens before the try block, the DML runs inside the try, COMMIT WORK is the last statement in the try, and the result is returned after the try/catch. On an error, the catch block runs ROLLBACK WORK instead.
“The following example wraps multiple related statements in a transaction, and uses try/catch to commit or roll back.”Source: docs.snowflake.com
Sources2
5.Owner's rights and caller's rights
Separate from transaction scope, a stored procedure can run with the privileges of the role that owns it rather than the role that calls it. This lets the owner delegate the power to perform specified operations to users who otherwise could not do so. Whether a procedure uses caller's rights or owner's rights affects what information it can access and which tasks it may perform, and you choose this when you write the procedure (EXECUTE AS OWNER or EXECUTE AS CALLER).
Checkpoint 5 of 5· Exam question
A platform team wants a role with no direct DELETE privilege on a staging table to be able to run a cleanup routine that purges rows older than 90 days, without granting that role broader table privileges. Which stored procedure design achieves this safely?
Correct answer: A — Create the procedure with `EXECUTE AS OWNER`, granted to the limited role, so it deletes rows under the owning role's privileges rather than the caller's
- A. Correct — an owner's rights procedure runs mostly under the privileges of the role that owns it, so granting EXECUTE on the procedure to the limited role lets that role trigger the purge without receiving DELETE on the table itself.
- B. This defeats the goal: granting DELETE directly on the staging table gives the limited role the very privilege the team wanted to avoid granting broadly.
- C. Owner's rights procedures face restrictions on certain built-in functions such as GET_DDL when invoked by a role other than the owner, so this design does not reliably let the caller inspect the table through the procedure.
- D. Running the procedure under ACCOUNTADMIN would grant every caller administrator-level delete capability through the procedure, which is a far broader privilege footprint than the least-privilege purge the team wants.
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A CREATE TABLE inside BEGIN TRANSACTION ... ROLLBACK is undone along with the DML.Why is that wrong?
DDL implicitly commits the open transaction and then runs as its own transaction, so it can't be rolled back, and neither can the DML it committed.
Covered in Explicit and implicit transactions, and how DDL ends them
2.If any statement in a transaction fails, Snowflake automatically rolls back the whole transaction.Why is that wrong?
Only the failed statement's changes are rolled back. The transaction stays open, and a later COMMIT keeps the statements that succeeded.
Covered in When one statement in a transaction fails
3.A procedure can leave a transaction open so the caller can commit it later.Why is that wrong?
If a transaction started inside a procedure is still open when the procedure returns, Snowflake raises an error and rolls the transaction back.
Practise it for real
Use the documentation's cleanup stored procedure to watch a failure roll back a whole transaction, then a success commit it
1.Run CREATE TABLE child (i int); CREATE TABLE parent (i int); then INSERT INTO child VALUES (1); INSERT INTO parent VALUES (1);
Why: DDL runs as its own transaction, so the tables are created before any explicit transaction, and each needs a row for the deletes to remove
You should see: Both tables exist and each holds one row
2.Create the cleanup procedure with the JavaScript body shown in the lesson, wrapped in CREATE OR REPLACE PROCEDURE cleanup(force_failure VARCHAR) RETURNS VARCHAR NOT NULL LANGUAGE JAVASCRIPT AS $$ ... $$;
Why: The procedure opens its own transaction with BEGIN WORK and ends it with COMMIT WORK or ROLLBACK WORK, so it respects the scope rule
You should see: The procedure is created
3.Run CALL cleanup('fail'); then SELECT COUNT(*) FROM child; and SELECT COUNT(*) FROM parent;
Why: The deletes succeed, the DELETE on no_such_table throws, and the catch block rolls back both deletes
You should see: The call returns a string starting with Failed:, and both tables still hold their one row
4.Run CALL cleanup('do not fail'); then SELECT COUNT(*) FROM child; and SELECT COUNT(*) FROM parent;
Why: With no error, COMMIT WORK runs and the deletes are kept
You should see: The call returns Succeeded, and both tables are now empty
Stuck? Get a nudge
If the second CALL returns Succeeded but the tables still have rows, check that the first CALL really rolled back and that your tables were not emptied before you ran it.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Explicit transactions should contain only DML statements and query statements.”
↩︎ Explicit and implicit transactions, and how DDL ends them“Do not change AUTOCOMMIT settings inside a stored procedure.”
↩︎ Explicit and implicit transactions, and how DDL ends them“The default setting for AUTOCOMMIT is TRUE (enabled).”
↩︎ Explicit and implicit transactions, and how DDL ends them“However, the transaction stays active until the entire transaction is committed or rolled back.”
↩︎ When one statement in a transaction fails“a transaction cannot be partly inside and partly outside a stored procedure”
↩︎ A transaction cannot cross a procedure boundary“If procedure A calls procedure B, procedure B cannot complete a transaction that was started in procedure A or vice versa.”
↩︎ A transaction cannot cross a procedure boundary“Regardless of whether the stored procedure’s active transaction was started explicitly or implicitly, Snowflake rolls back the active transaction and issues an error message.”
↩︎ A transaction cannot cross a procedure boundary“Because a DDL statement is its own transaction, you cannot roll back a DDL statement”
↩︎ Exam trap 1“However, the transaction stays active until the entire transaction is committed or rolled back.”
↩︎ Exam trap 2“is still active when the stored procedure finishes, an error occurs and the transaction is rolled back.”
↩︎ Exam trap 3“Because a DDL statement is its own transaction, you cannot roll back a DDL statement”
↩︎ Checkpoint“When a DML statement or CALL statement in a transaction fails, the changes made by that failed statement are rolled back.”
↩︎ Prediction“the stored procedure never completes, and the COMMIT is never executed, so the open transaction is implicitly rolled back.”
↩︎ Checkpoint“Modifying a transaction that has started at a different scope is not allowed.”
↩︎ Checkpoint - 2.https://docs.snowflake.com/en/developer-guide/stored-procedure/stored-procedures-javascriptOfficial docs
“If an error occurs, then your catch block can roll back all of the statements (if you put the statements in a transaction).”
↩︎ Committing or rolling back with try/catch“The following example wraps multiple related statements in a transaction, and uses try/catch to commit or roll back.”
↩︎ Checkpoint - 3.https://docs.snowflake.com/en/developer-guide/stored-procedure/stored-procedures-overviewOfficial docs
“Execute code with the privileges of the role that owns the procedure, rather than with the privileges of the role that runs the procedure.”
↩︎ Owner's rights and caller's rights