Snowflake Transaction Management

In a Snowflake, a transaction is a set of SQL statements, both reads and writes on database objects, that are processed as a unit. All the statements in the transaction are either committed or undone using rolled back as a single unit. Similar to other databases such as Redshift, Netezza, etc, Snowflake transactions guarantees ACID properties. In this article, we will discuss about the Snowflake transaction management using BEGIN, COMMIT and ROLLBACK.

The default automatic commit behavior of the Snowflake database causes each SQL command that runs separately to commit individually. By default, a DML statement executed without explicitly starting a transaction is automatically committed on query success or rolled back on failure at the end of the statement. The AUTOCOMMIT parameter will control the auto commit behavior in the Snowflake cloud data warehouse.

Snowflake BEGIN Command

The BEGIN command begins a transaction block in the current session. Both BEGIN and START TRANSACTION are same in Snowflake. You do not need any special privileges to use the BEGIN command in Snowflake.

Snowflake BEGIN Syntax and Example

Following code block is the BEGIN syntax that you can use:

BEGIN [ { WORK | TRANSACTION } ] [ NAME <name> ];

Similarly, start session syntax.

START TRANSACTION [ NAME <name> ]

Where, WORK and TRANSACTION are optional keywords that you can provide while creating transaction block. And NAME is an optional string that assigns a name to the transaction.

Following are some of the different ways that you can use BEGIN command.

BEGIN;
select 1;

BEGIN WORK;
select 1;

BEGIN TRANSACTION;
select 1;

BEGIN NAME t1;
select 1;

You can use any of the above syntax.

Snowflake COMMIT Command

A COMMIT command in Snowflake commits an open transaction in the current session. If there are any statements such as update or insert commands, this COMMIT makes those commands from the transaction permanent.

Snowflake COMMIT Syntax and Example

Following command is COMMIT statement syntax.

COMMIT [ {WORK} ];

For example, each of the following examples commits the current transaction to the database.

COMMIT;

COMMIT WORK;

You can use any of the above commit statements.

Snowflake ROLLBACK Command

The Snowflake ROLLBACK command aborts the current transaction and discards all updates made by that transaction.

Snowflake ROLLBACK Command Syntax and Example

Following is the ROLLBACK syntax that you can use.

ROLLBACK [ {WORK} ];

Where, WORK keyword is optional.

For examples, the below example creates a table then starts a transaction where data is inserted into the table. The ROLLBACK command at the end then rolls back the data insertion.

Following example demonstrates Redshift BEGIN, COMMIT and ROLLBACK transaction control management commands.

create table sn_rollback_test( id int );

BEGIN;

insert into sn_rollback_test values (1);
insert into sn_rollback_test values (2);

COMMIT;

select * from sn_rollback_test;
select * from sn_rollback_test;
+----+
| ID |
|----|
|  1 |
|  2 |
+----+

insert into sn_rollback_test values (3);

select * from sn_rollback_test;
+----+
| ID |
|----|
|  1 |
|  2 |
|  3 |
+----+

ROLLBACK;

select * from sn_rollback_test;
id 
+----+
| ID |
|----|
|  1 |
|  2 |
+----+

Note that, you should execute all statement in single session to use ROLLBACK.