Learning Snowflake SQL and Scripting

Chapter 11. Transactions

This chapter explores transactions, which are the mechanism used to group a set of SQL statements together such that either all or none of the statements succeed.

What Is a Transaction?

A transaction is a series of SQL statements within a single database session, with the goal of having all of the changes either applied or undone as a unit. In other words, when using a transaction, you will never face a situation where some of your changes succeed and others fail. Consider the classic scenario of a bank transfer, where money is withdrawn from one account and deposited into another; if your money was withdrawn from your savings account but the deposit to your checking account fails, your money would disappear. Here’s a pseudocode description of how a transaction would eliminate this possibility:

Begin Transaction
Update Savings_Account (remove $100 as long as balance >= $100)
Update Checking_Account (add $100)
If errors then
  Rollback Transaction
Else
  Commit Transaction
End If

The next section describes the ways that Snowflake handles transactions.

Explicit and Implicit Transactions

You can choose to start a transaction by issuing the begin transaction statement, after which all following SQL statements will be considered part of the transaction until you issue a commit or rollback statement. This is known as an explicit transaction because you are instructing Snowflake to start a transaction. Here’s an example with two insert statements inside a single ...