ROLLBACK

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Stops the current transaction and discards all updates made by that transaction. (Redshift SQL Language Reference ROLLBACK)

Grammar Syntax

ROLLBACK [WORK | TRANSACTION]

Sample Source Patterns

Setup data

Redshift

Query

CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

Snowflake

Query

CREATE TABLE transaction_values_test
(
    col1 INTEGER
);

ROLLBACK with TRANSACTION keyword

The TRANSACTION keyword is not supported in Snowflake. However, since it does not have an impact on functionality it will just be removed.

Redshift

Query

IN -> Redshift_01.sql

ROLLBACK TRANSACTION;

Snowflake

Query

OUT -> Redshift_01.sql

ROLLBACK;

ROLLBACK in a default transaction behavior procedure (without NONATOMIC clause)

In order to avoid out of scope transaction exceptions in Snowflake, the usages of ROLLBACK will be matched with BEGIN TRANSACTION.

When multiple transaction control statements are present in the procedure, multiple BEGIN TRANSACTION statements will be generated after every each one of them to emulate the Redshift transaction behavior.

Redshift

Query

IN -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE transaction_test(a INT)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    COMMIT;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM transaction_values_test;

Result

Snowflake

Query

OUT -> Redshift_02.sql

CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
    LANGUAGE SQL
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test values (:a);
    COMMIT;
    BEGIN TRANSACTION;
    insert into transaction_values_test values (80);
    insert into transaction_values_test values (55);
    ROLLBACK;
END
$$;

CALL transaction_test(120);

SELECT * FROM
    transaction_values_test;

Result

ROLLBACK in a procedure with NONATOMIC behavior

The NONATOMIC behavior from Redshift is emulated in Snowflake by using the session parameter AUTOCOMMIT set to true.

Since the AUTOCOMMIT session parameter is assumed to be true by SnowConvert, the ROLLBACK statement inside NONATOMIC procedures is left as is.

Redshift

Query

IN -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE nonatomic_procedure(a int)
    NONATOMIC
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    INSERT INTO transaction_values_test values (a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 2);
    INSERT INTO transaction_values_test values (a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM transaction_values_test;

Result

Snowflake

Query

OUT -> Redshift_03.sql

CREATE OR REPLACE PROCEDURE nonatomic_procedure (a int)
RETURNS VARCHAR
--    --** SSC-FDM-RS0008 - SNOWFLAKE USES AUTOCOMMIT BY DEFAULT. **
--    NONATOMIC
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    INSERT INTO transaction_values_test
    values (:a);
    INSERT INTO transaction_values_test
    values (:a + 1);
    ROLLBACK;
    INSERT INTO transaction_values_test
    values (:a + 2);
    INSERT INTO transaction_values_test
    values (:a + 3);
    COMMIT;
END
$$;

CALL nonatomic_procedure(10);

SELECT * FROM
transaction_values_test;

Result

Known Issues

1. ROLLBACK inside a nested procedure call

In Redshift, when a ROLLBACK statement is specified in a nested procedure call, the command will commit all pending work from previous statements in the current and parent scopes. Committing the parent scope actions is not supported in Snowflake, when this case is detected an FDM will be generated.

Redshift

Query

IN -> Redshift_04.sql

CREATE OR REPLACE PROCEDURE transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    ROLLBACK;
    INSERT INTO transaction_values_test values (a + 1);
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CALL transaction_test(a + 3);
    COMMIT;
END
$$;

Snowflake

Query

OUT -> Redshift_04.sql

CREATE OR REPLACE PROCEDURE transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    ROLLBACK;
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a + 1);
    COMMIT;
END
$$;

CREATE OR REPLACE PROCEDURE nested_transaction_test (a int)
RETURNS VARCHAR
    LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    --** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
    CALL transaction_test(:a + 3);
    COMMIT;
END
$$;

2. ROLLBACK of DDL statements

In Snowflake, DDL statements perform an implicit commit whenever they are executed inside a procedure, making effective all the work prior to executing the DDL as well as the DDL itself. This causes the ROLLBACK statement to not be able to discard any changes before that point, this issue will be informed using an FDM.

Redshift

Query

IN -> Redshift_05.sql

CREATE OR REPLACE PROCEDURE rollback_ddl(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO transaction_values_test values (a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );

    INSERT INTO someRollbackTable values (a);
    ROLLBACK;
END
$$;

Snowflake

Query

OUT -> Redshift_05.sql

CREATE OR REPLACE PROCEDURE rollback_ddl (a int)
RETURNS VARCHAR
    LANGUAGE SQL
    COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}'
    AS $$
BEGIN
    BEGIN TRANSACTION;
    INSERT INTO transaction_values_test
    values (:a);
    CREATE TABLE someRollbackTable
    (
        col1 INTEGER
    );
    BEGIN TRANSACTION;
    INSERT INTO someRollbackTable
    values (:a);
    --** SSC-FDM-RS0007 - DDL STATEMENTS PERFORM AN AUTOMATIC COMMIT, ROLLBACK WILL NOT WORK AS EXPECTED **
    ROLLBACK;
END
$$;

Known Issues

There are no known issues.

Related EWIs

1. SSC-FDM-RS0006: Called procedure contains usages of COMMIT/ROLLBACK, modifying the current transaction in child scopes is not supported in Snowflake.

2. SSC-FDM-RS0007: DDL statements perform an automatic COMMIT, ROLLBACK will not work as expected.