SnowConvert: Redshift Functional Differences¶

SSC-FDM-RS0001¶

Description¶

Snowflake automatically manages data distribution and optimization, unlike Redshift. When migrating from Redshift to Snowflake, you don’t need to specify SORTKEY and DISTSTYLE parameters because Snowflake’s architecture handles data partitioning and indexing automatically to optimize query performance.

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

CREATE TABLE table2 (
    col1 INTEGER
)
SORTKEY AUTO;
Copy

Generated Code:¶ 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¶

Consider using CLUSTER BY in Snowflake when migrating from Redshift. This feature can enhance query performance by organizing data based on commonly accessed columns.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0002¶

Description¶

The Amazon Redshift SORTKEY command (except for SORTKEY AUTO) serves a similar purpose to Snowflake’s CLUSTER BY command. Keep in mind that because Redshift and Snowflake have different architectures, you may see different performance results when using these commands.

SORTKEY enhances query performance by storing data in a sorted sequence according to specified columns. This is especially useful when performing range-based queries and sorting operations.
CLUSTER BY in Snowflake groups related data into micro-partitions based on selected columns, which improves filtering and aggregation operations. Unlike SORTKEY, it does not maintain strict row ordering.

Understanding how these mechanisms work is essential for achieving the best performance in each platform.

Code Example¶

Input Code:¶

 CREATE TABLE table1 (
    col1 INTEGER
)
SORTKEY (col1);

CREATE TABLE table2 (
    col1 INTEGER SORTKEY
);

Copy

Generated Code:¶

 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¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0003¶

Description¶

Foreign key translation will be supported in a future release. For information about Snowflake’s Foreign Key functionality, please refer to the Snowflake documentation.

Note

Snowflake does not enforce Foreign Key Constraints. They are used only for documenting referential relationships between tables.

Code Example¶

Input Code:¶

 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:¶

 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¶

You can manually add Foreign Keys to tables using the alter tables command.

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

Copy

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0004¶

Description¶

Snowflake and Redshift handle invalid dates differently in their TO_DATE functions:

Snowflake’s TO_DATE strictly validates dates and throws an error when given invalid dates (like ‘20010631’). It does not attempt to fix incorrect dates.
Redshift’s TO_DATE can automatically adjust invalid dates to the nearest valid date (for example, converting June 31 to July 1) when the is_strict parameter is set to false.

This difference shows that Snowflake emphasizes data accuracy by rejecting invalid dates, while Redshift offers more flexible date handling options.

Code Example¶

Input Code:¶

 SELECT TO_DATE('20010631', 'YYYYMMDD', FALSE);

Copy

Generated Code:¶

 SELECT
TRY_TO_DATE(/*** SSC-FDM-RS0004 - INVALID DATES WILL CAUSE ERRORS IN SNOWFLAKE ***/ '20010631', 'YYYYMMDD');

Copy

Best Practices¶

Verify that the date format is correct by using the TRY_TO_DATE() function.
For additional assistance, please contact our support team at snowconvert-support@snowflake.com

SSC-FDM-RS0005¶

Description¶

When executing a MERGE statement with duplicate values in the source table, Redshift and Snowflake handle the situation differently. While Redshift raises an error, Snowflake continues the execution. The Feature Difference Manager (FDM) alerts users about this behavioral difference, as it may lead to unexpected results in the converted MERGE statement.

Code Example¶

Input Code:¶

 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:¶

 --** 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¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0006¶

Description¶

In Redshift, procedures can include COMMIT and ROLLBACK statements to either save or cancel changes made by a transaction that was started outside the procedure.

Snowflake uses scoped transactions, which means that each procedure call operates as an independent transaction. As a result, COMMIT and ROLLBACK statements only affect operations within their specific procedure scope.

SnowConvert will generate this FDM warning when it detects calls to a procedure containing COMMIT or ROLLBACK statements.

Code Example¶

Input Code:¶

 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:¶

 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¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0007¶

Description¶

In Snowflake, DDL statements automatically commit after they execute. This means all changes in the current transaction become permanent and cannot be undone using a ROLLBACK command.

When SnowConvert detects a ROLLBACK statement in a procedure that includes a DDL (Data Definition Language) statement, it will generate a Feature Difference Message (FDM) to alert users about the automatic commit behavior of DDL statements.

Code Example¶

Input Code:¶

 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:¶

 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¶

For additional support, please contact us at snowconvert-support@snowflake.com

SSC-FDM-RS0008¶

Description¶

Each Snowflake statement within a stored procedure automatically commits its changes. This behavior is known as autocommit. For more details, see the Snowflake Documentation.

Code Example¶

Input Code:¶

 CREATE OR REPLACE PROCEDURE SP_NONATOMIC()
NONATOMIC 
AS 
$$
    BEGIN
        NULL;
    END;
$$ 
LANGUAGE plpgsql;

Copy

Generated Code:¶

 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¶

For additional support, please contact us at snowconvert-support@snowflake.com