Create Transactions in Snowflake Plus Useful Commands for SQL Devs

Problem

In the previous articles of this series, we discussed Snowflake transactions in contrast with SQL Server transactions. In this article, we will explore some essential commands for working with transactions in Snowflake. These commands are specific to Snowflake and can be helpful for SQL Server developers in starting with Snowflake development.

Solution

To begin with, let's create a test environment.

We will open a new worksheet in the Snowflake Web UI, copy and paste the code below:

CREATE OR REPLACE TABLE TESTDB.TESTSCHEMA.TableA ( 
    ID INT, 
    Val CHAR(1) 
);
 
CREATE OR REPLACE TABLE TESTDB.TESTSCHEMA.TableB( 
        ID INT IDENTITY, 
        Val CHAR(1) 
);

This code creates two tables:

Next, we will open a new worksheet and will use the following code:

--Session 1 
BEGIN TRANSACTION; 
 
INSERT INTO TESTDB.TESTSCHEMA.TableA (Val) 
VALUES ('A'), ('B'); 
 
SELECT CURRENT_TRANSACTION(); 
 
COMMIT; 
 
SELECT CURRENT_TRANSACTION(); 
 
SELECT LAST_TRANSACTION();

CURRENT_TRANSACTION()

In this step, we will run only the highlighted part of the code:

This code uses the first command in our list – CURRENT_TRANSACTION(), which returns the transaction ID of an open transaction in the current session. In the picture above, we can see that we got the current transaction ID, which we can easily copy from the Web UI.

SHOW TRANSACTIONS

The next command we are going to discuss is SHOW TRANSACTIONS. Let's run this command in a different worksheet:

SHOW TRANSACTIONS;

This command returns all running transactions for the current user:

We received the same transaction ID as in the previous query, and this transaction is in the running state. Please note that if we use the SHOW TRANSACTIONS IN ACCOUNT command, we will get all running transactions for all users in the account. Now, let's copy the code below into a new worksheet:

--Session 2 
BEGIN TRANSACTION; 
 
INSERT INTO TESTDB.TESTSCHEMA.TableB (Val) 
VALUES ('C'), ('D'); 
 
SELECT CURRENT_TRANSACTION(); 
 
ROLLBACK; 
 
SELECT CURRENT_TRANSACTION(); 
 
SELECT LAST_TRANSACTION();

After executing the selected part of the code, we will get the transaction ID for the second transaction:

If we run the command again, we can see that we now have two active transactions for the current user:

SHOW TRANSACTIONS;

DESCRIBE TRANSACTION

Our third command is DESCRIBE TRANSACTION (shortened - DESC TRANSACTION version can also be used) which receives the transaction ID and provides information about that transaction (start time, state, etc.). Let's copy the transaction IDs of both our transactions and use them in the DESCRIBE TRANSACTION command:

DESCRIBE TRANSACTION 1668991042603000000;
 
DESCRIBE TRANSACTION 1668991514777000000;

As expected, we can see the transaction-related information as the result of this command:

LAST_TRANSACTION()

In this step, we will roll back the second transaction:

We can see that the CURRENT_TRANSACTION() returns an empty result as there is no transaction in the session. However, if we run the LAST_TRANSACTION() command, we can get the ID of the rolled-back transaction:

The LAST_TRANSACTION() command returns the last transaction ID in the session (which was either committed or rolled back). If we run the SHOW TRANSACTION command again, we can see only one – the first transaction:

Nevertheless, we can receive information about the second-rolled-back transaction by issuing the DESCRIBE TRANSACTION command. We can see that now the "sate" or the transaction is "rolled back":

When we commit the first transaction, in this case as well, we will receive an empty result after running the CURRENT_TRANSACTION() command:

The LAST_TRANSACTION() command will return the committed transaction ID:

An empty result will be received after running the SHOW TRANSACTION command:

As in the previous example, we will be able to receive the transaction's description by using its ID. In this case, the "state" is "committed":

Conclusion

To sum up, Snowflake provides a set of useful commands for working with transactions. Understanding and mastering these commands can be beneficial for developers working with Snowflake transactions.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-12-28

Useful Commands for Snowflake Transactions