SSC-EWI-RS0001

"NONATOMIC" option is not supported in Snowflake.

Severity

Medium

Description

Redshift CREATE PROCEDURE includes an optional clause that specifies the NONATOMIC transaction mode. Snowflake does not have an equivalent keyword to reproduce the behavior as it is defined, when possible, the functionality is emulated in Snowflake by doing changes in the final code.

The only behavior that must be manually checked is that a procedure defined as NONATOMIC automatically commits the statements inside the procedure. To reproduce this behavior, Snowflake supports "autocommit" as an account parameter, more information about it here.

Code Examples

Input Code:

Redshift

IN -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE procedure1(parameter int)
Nonatomic
AS
   $$
      SELECT * from my_table;
   $$
LANGUAGE plpgsql;

Output Code:

Snowflake

OUT -> Redshift_01.sql

CREATE OR REPLACE PROCEDURE procedure1 (parameter int)
RETURNS VARCHAR
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0001 - THE NONATOMIC OPTION IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
Nonatomic
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
AS
   $$
      SELECT * from
         my_table;
   $$;

Recommendations

• If you need more support, you can email us at [e-mail]