Using Stored Procedures inside Snowflake Transactions

By: Sergey Gigoyan   |   Updated: 2023-04-06   |   Comments   |   Related: More > Snowflake

Problem

In Snowflake, stored procedures and transactions are two key concepts used for managing and processing data. Understanding how they work together can be helpful for database developers who want to program optimal data processing logic more flexibly and ensure data consistency. In this article, we will discuss how stored procedures and transactions can be used together in Snowflake and provide examples illustrating their combined functioning.

Solution

Snowflake stored procedures are used to encapsulate a series of SQL statements and control the flow of data processing. They are created using the CREATE PROCEDURE statement and can be executed using the CALL statement. Stored procedures can have input and output parameters and can return result sets. They can also be used to perform complex calculations, data validation, and other business logic operations.

Transactions, on the other hand, are used to manage the changes made to the data. They are used to ensure that data changes are atomic, consistent, isolated, and durable (ACID). In Snowflake, transactions are managed using the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements.

In Snowflake, it is possible to run stored procedures within a transaction, which means that the changes made by the stored procedure are committed or rolled back as a single unit of work. This allows you to ensure that the data changes made by the stored procedure are consistent and atomic. To run a stored procedure inside a transaction, you can use the BEGIN TRANSACTION and COMMIT or ROLLBACK commands. (It is assumed that the stored procedure does not contain BEGIN TRANSACTION, COMMIT, or ROLLBACK commands, neither explicit nor implicit.)

Let's create a test environment and illustrate this behavior:

CREATE DATABASE TestDB;
 
CREATE SCHEMA TestSchema;
 
USE DATABASE TestDB;
 
CREATE OR REPLACE TABLE TestDB.TestSchema.TestTable (
   ID INT NOT NULL,
   Val INT
);

Then, create a sample stored procedure that inserts rows into the TestTable:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN
    INSERT INTO TestTable (ID, Val) VALUES (1, 100);
    INSERT INTO TestTable (ID, Val) VALUES (2, 200);
END;

Now, let's call this stored procedure within a transaction:

BEGIN TRANSACTION;
    CALL TestDB.TestSchema.InsertData();
COMMIT;

Notice that two rows are successfully inserted into the table:

SELECT * FROM TestDB.TestSchema.TestTable;

In contrast, if we would like to roll back the changes made by the stored procedure, we can use the ROLLBACK command:

BEGIN TRANSACTION;
    CALL TestDB.TestSchema.InsertData();
ROLLBACK;

As expected, the result shows that nothing is inserted after the second run:

It is important to note that DDL statements, such as CREATE TABLE, DROP TABLE, etc., are always executed as separate transactions and cannot be included in other transactions. This means that if a stored procedure contains a DDL statement, the stored procedure will be executed as a separate transaction, and the changes made by the stored procedure will not be committed or rolled back together with the changes made by the other statements in the transaction.

To demonstrate this, we will change the code of the stored procedure above to include a DDL statement – CREATE TABLE:

CREATE OR REPLACE PROCEDURE TestSchema.InsertData()
RETURNS INTEGER
LANGUAGE SQL
AS
BEGIN 
    CREATE OR REPLACE TABLE TestDB.TestSchema.TestTable (
      ID INT NOT NULL,
      Val INT
    );
    INSERT INTO TestTable (ID, Val) VALUES (1, 100);
    INSERT INTO TestTable (ID, Val) VALUES (2, 200);
END;

If we call the stored procedure without transaction, it will work fine:

When we call this stored procedure within a transaction, we will receive an error message:

BEGIN TRANSACTION;
    CALL TestDB.TestSchema.InsertData();
COMMIT;

Thus, when designing stored procedures, it's essential to consider the transactional behavior of the statements inside it and the implications of using DDL statements within a transaction.

Conclusion

To sum up, Snowflake stored procedures and transactions provide a robust solution for data management and processing. By executing stored procedures within a transaction, developers can ensure the consistency and atomicity of the data changes made by the stored procedures. This integration enhances the flexibility and efficiency of data management. However, it's important to keep in mind that stored procedures with DDL statements cannot be included in transactions.

Next Steps

For more information, please follow the links below:

• Writing Stored Procedures in Snowflake Scripting — Snowflake Documentation
• Transactions — Snowflake Documentation
• Transactions — Snowflake Documentation
• DDL and DML Statements in transactions for SQL Server and Snowflake (mssqltips.com)

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