The TRANSACTION keyword is not supported in Snowflake. However, since it does not have an impact on functionality it will just be removed.
Redshift
IN -> Redshift_01.sql
ROLLBACK TRANSACTION;
Snowflake
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
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;
+------+
| col1 |
+------+
| 120 |
+------+
Snowflake
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;
+------+
| col1 |
+------+
| 120 |
+------+
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
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;
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
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
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
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
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
SSC-FDM-RS0006: Called procedure contains usages of COMMIT/ROLLBACK, modifying the current transaction in child scopes is not supported in Snowflake.
SSC-FDM-RS0007: DDL statements perform an automatic COMMIT, ROLLBACK will not work as expected.
