SnowConvert: Redshift Functional Differences

SSC-FDM-RS0001

Option not supported. Data storage is automatically handled by Snowflake.

Description

In Snowflake, it is not necessary to explicitly define SORTKEY and DISTSTYLE when migrating from Redshift because Snowflake’s architecture inherently manages data distribution and optimization. Snowflake automatically handles data partitioning and indexing, optimizing query performance without requiring manual configuration of these parameters.

Code Example

Input Code:
Redshift
 CREATE TABLE table1 (
    col1 INTEGER
)
DISTSTYLE AUTO;

CREATE TABLE table2 (
    col1 INTEGER
)
SORTKEY AUTO;
Copy
Generated Code:
Snowflake
 CREATE TABLE table1 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - DISTSTYLE AUTO OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE AUTO
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

CREATE TABLE table2 (
    col1 INTEGER
)
----** SSC-FDM-RS0001 - SORTKEY AUTO OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--SORTKEY AUTO
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';
Copy

Best Practices

  • It is advisable to assess the use of CLUSTER BY in Snowflake during migration from Redshift, as it may improve query performance by optimizing data locality for frequently queried columns.

  • If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-FDM-RS0002

The performance of the CLUSTER BY may vary compared to the performance of Sortkey.

Description

The SORTKEY (excluding SORTKEY AUTO) in Amazon Redshift are analogous to CLUSTER BY in Snowflake. However, performance implications may vary due to architectural differences between Redshift and Snowflake.

  • SORTKEY improves performance by maintaining data in a sorted order based on specified columns. This is particularly beneficial for range queries and ordering operations.

  • CLUSTER BY in Snowflake organizes data into blocks based on designated columns, aiding in filtering and aggregation tasks. However, it is less stringent about ordering compared to SORTKEY.

Understanding these mechanisms is crucial for optimizing performance in each respective platform.

Code Example

Input Code:
Redshift
 CREATE TABLE table1 (
    col1 INTEGER
)
SORTKEY (col1);

CREATE TABLE table2 (
    col1 INTEGER SORTKEY
);
Copy
Generated Code:
Snowflake
 CREATE TABLE table1 (
    col1 INTEGER
)
--** SSC-FDM-RS0002 - THE PERFORMANCE OF THE CLUSTER BY MAY VARY COMPARED TO THE PERFORMANCE OF SORTKEY **
CLUSTER BY (col1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

CREATE TABLE table2 (
    col1 INTEGER
)
--** SSC-FDM-RS0002 - THE PERFORMANCE OF THE CLUSTER BY MAY VARY COMPARED TO THE PERFORMANCE OF SORTKEY **
CLUSTER BY (col1)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';
Copy

Best Practices

SSC-FDM-RS0003

Foreign Key translation will be supported in the future.

Description

The foreign key translation will be transformed in the future. Documentation for Snowflake’s Foreign Key can be accessed here.

Note

In Snowflake, the Foreign Key Constraint is not enforced, and it is used for referential integrity.

Code Example

Input Code:
Redshift
 CREATE TABLE TABLE1 (
    id INTEGER,
    PRIMARY KEY (id)
);

CREATE TABLE TABLE2 (
	id INTEGER,
	id_table1 INTEGER,
	FOREIGN KEY (id_table1) REFERENCES TABLE1 (col1)
);
Copy
Generated Code:
Snowflake
 CREATE TABLE TABLE1 (
    id INTEGER,
    PRIMARY KEY (id)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/26/2024" }}';

CREATE TABLE TABLE2 (
	id INTEGER,
	id_table1 INTEGER
--	                 ,
--    --** SSC-FDM-RS0003 - THE TRANSLATION FOR FOREIGN KEY IS NOT AVAILABLE, IT WILL BE PROVIDED IN THE FUTURE. **
--	FOREIGN KEY (id_table1) REFERENCES TABLE1 (col1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/26/2024" }}';
Copy

Best Practices

 ALTER TABLE TABLE2 ADD CONSTRAINT
FOREIGN KEY (id_table1) REFERENCES TABLE1 (col1)
Copy

SSC-FDM-RS0004

It is possible that the date is wrong and Snowflake does not accept wrong dates

Description

In Snowflake, using TO_DATE with an invalid date string (like ‘20010631’) results in an error because it enforces strict validation, rejecting any non-existent dates. In contrast, Redshift’s TO_DATE can adjust such invalid dates to the nearest valid date (e.g., rolling June 31 to July 1) if the is_strict parameter is set to false. This difference highlights how Snowflake prioritizes data integrity by not automatically correcting invalid dates, while Redshift allows for more flexibility in date handling.

Code Example

Input Code:
Redshift
 SELECT TO_DATE('20010631', 'YYYYMMDD', FALSE);
Copy
Generated Code:
Snowflake
 SELECT
TRY_TO_DATE(/*** SSC-FDM-RS0004 - INVALID DATES WILL CAUSE ERRORS IN SNOWFLAKE ***/ '20010631', 'YYYYMMDD');
Copy

Best Practices

SSC-FDM-RS0005

Duplicates not allowed in source table

Description

In Redshift, the MERGE statement throws an error when the source table has duplicate values. Snowflake doesn’t throw the error and instead, it allows the query execution. The FDM warns about this behavior, that it could change the result in the converted MERGE.

Code Example

Input Code:
Redshift
 MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);
Copy
Generated Code:
Snowflake
 --** SSC-FDM-RS0005 - REDSHIFT MERGE STATEMENT DOESN'T ALLOW DUPLICATES IN THE SOURCE TABLE. SNOWFLAKE BEHAVIOR MAY DIFFER IF THERE ARE DUPLICATE VALUES. **
MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);
Copy

Best Practices

SSC-FDM-RS0006

Called procedure contains usages of COMMIT/ROLLBACK, modifying the current transaction in child scopes is not supported in Snowflake

Description

In Redshift, it is allowed to use the statements COMMIT and ROLLBACK inside a procedure to make permanent or discard the changes on a transaction that was opened on an outer scope.

Snowflake works with the concept of scoped transactions, which treats each procedure call as a separate transaction, this limits the effects of the COMMIT and ROLLBACK statements to the scope of the procedure they are declared in.

The aforementioned functional difference will be warned with this FDM when calls to a procedure with COMMIT or ROLLBACK are detected by SnowConvert.

Code Example

Input Code:
Redshift
 CREATE OR REPLACE PROCEDURE inner_transaction_procedure(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 outer_transaction_procedure(a int)
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- This insert is also affected by the ROLLBACK in inner_transaction_procedure
    INSERT INTO transaction_values_test values (a);
    CALL inner_transaction_procedure(a + 3);
    COMMIT;
END
$$;

CALL outer_transaction_procedure(10);
Copy
Generated Code:
Snowflake
 CREATE OR REPLACE PROCEDURE inner_transaction_procedure (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 outer_transaction_procedure (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;
    -- This insert is also affected by the ROLLBACK in inner_transaction_procedure
    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 inner_transaction_procedure(:a + 3);
    COMMIT;
END
$$;

CALL outer_transaction_procedure(10);
Copy

Best Practices

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
 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);
Copy
Generated Code:
Snowflake
 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);
Copy

Best Practices

SSC-FDM-RS0008

SNOWFLAKE USES AUTOCOMMIT BY DEFAULT.

Description

Snowflake statements inside procedures are treated as autocommit. Snowflake Documentation

Code Example

Input Code:
Redshift
 CREATE OR REPLACE PROCEDURE SP_NONATOMIC()
NONATOMIC 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;
Copy
Generated Code:
Snowflake
 CREATE OR REPLACE PROCEDURE SP_NONATOMIC ()
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": "02/10/2025",  "domain": "test" }}'
AS
$$
    BEGIN
        NULL;
    END;
$$;
Copy

Best Practices

Language: English