SSC-FDM-RS0007

DDL statements perform an automatic COMMIT, ROLLBACK will not work as expected

Description

In Snowflake, DDL statements perform an automatic commit after their execution, making permanent all the changes in the current transaction, meaning they can not be discarded by a ROLLBACK.

When a ROLLBACK statement is found in a procedure that also contains a DDL statement, SnowConvert will generate this FDM to inform about the DDL autocommit behavior.

Code Example

Input Code:

Redshift

IN -> Redshift_01.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
$$;

CALL rollback_ddl(10);

Output Code:

Snowflake

OUT -> Redshift_01.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
$$;

CALL rollback_ddl(10);

Recommendations

• If you need more support, you can email us at [email protected]