SnowConvert: Redshift Issuses¶

SSC-EWI-RS0008¶

Severity¶

High

Description¶

This issue arises because Redshift allows DELETE statements on materialized views that are used for streaming ingestion. When converting to Snowflake, these materialized views become dynamic tables, which do not support DELETE operations.

Code Example¶
Input Code:¶CREATE MATERIALIZED VIEW mv AS
SELECT id, name, department FROM employees WHERE department = 'Engineering';

DELETE FROM mv
WHERE id = 2;
Copy

Output Code:¶CREATE DYNAMIC TABLE mv
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **
TARGET_LAG='1 day'
WAREHOUSE=UPDATE_DUMMY_WAREHOUSE
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/11/2025",  "domain": "test" }}'
AS
SELECT id, name, department FROM
        employees
WHERE
        RTRIM( department) = RTRIM( 'Engineering');


!!!RESOLVE EWI!!! /*** SSC-EWI-RS0008 - MATERIALIZED VIEW IS TRANSFORMED INTO A DYNAMIC TABLE, AND THE DELETE STATEMENT CANNOT BE USED ON DYNAMIC TABLES. ***/!!!
DELETE FROM mv
WHERE id = 2;
Copy

Recommendations¶

To delete records from a dynamic table, you must replace the entire dynamic table definition with a new one.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-RS0009¶

Severity¶

Low

Description¶

In Snowflake, when using the MERGE statement, the REMOVE DUPLICATES clause is not available. To achieve the same functionality, you’ll need to implement a workaround. This involves adding an INSERT WHEN NOT MATCHED clause to your code. This clause requires column information from the source table. If the migration process cannot locate the source table, the required columns cannot be generated, which will result in an error.

Code Example¶

Input Code:¶

MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES;

Copy

Output Code:¶

CREATE TEMPORARY TABLE source_duplicates AS
  SELECT DISTINCT
    source.*
  FROM
    source
    INNER JOIN
      target
      ON target.id = source.id;

!!!RESOLVE EWI!!! /*** SSC-EWI-RS0009 - SEMANTIC INFORMATION NOT FOUND FOR THE SOURCE TABLE. COLUMNS TO BE INSERTED MAY BE ADDED MANUALLY. ***/!!!
MERGE INTO target
USING source ON target.id = source.id
WHEN MATCHED THEN
  DELETE
WHEN NOT MATCHED THEN
  INSERT
  VALUES ();

INSERT INTO target
SELECT
  *
FROM
  source_duplicates;

DROP TABLE IF EXISTS source_duplicates CASCADE;

Copy

Recommendations¶

Check if the source table exists in the migrated code, then manually add any missing columns to the insert statement.
For additional assistance, please contact us at snowconvert-support@snowflake.com

SSC-EWI-RS0002¶

Severity¶

Medium

Description¶

The SET configuration parameter feature, which is available in Redshift, is not supported in Snowflake. For more details, please consult the CREATE PROCEDURE documentation.

Code Examples¶

Input Code:¶

CREATE OR REPLACE PROCEDURE procedure2(
    IN input_param INTEGER,
    OUT output_param NUMERIC
)
AS $$
BEGIN
    output_param := input_param * 1.7;
END;
$$
LANGUAGE plpgsql
SET enable_numeric_rounding to ON;

Copy

Output Code:¶

CREATE OR REPLACE PROCEDURE procedure2 (
    IN !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'InParameterMode' NODE ***/!!! input_param INTEGER,
    OUT output_param NUMERIC
)
RETURNS VARCHAR
LANGUAGE SQL
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0002 - SET CONFIGURATION PARAMETER 'enable_numeric_rounding' IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
SET enable_numeric_rounding to ON
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
AS $$
BEGIN
    output_param := input_param * 1.7;
END;
$$;

Copy

Recommendations¶

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

SSC-EWI-RS0003¶

Severity¶

Medium

Description¶

The CREATE VIEW command in Redshift includes an optional clause that makes views independent from their referenced database objects (such as tables or functions). When using this clause, you must include schema names for all referenced objects. This feature allows you to create views that reference objects that don’t exist yet. The system only checks if these referenced objects exist when the view is queried, not when it is created.

Currently, Snowflake does not provide an equivalent command or clear alternative to implement this functionality. According to Snowflake’s documentation, views are specifically tied to a schema, as are the objects referenced within the view.

When attempting to remove a View statement, the system checks for all referenced objects in the input code. If all references are found, the View statement will be successfully removed. If any references are missing, the system will display a warning message indicating that the View cannot be removed due to missing dependencies.

SnowConvert analyzes only the provided source code and doesn’t check objects that already exist in your Snowflake environment. As a result, you might see error messages about missing references. If these objects already exist in your Snowflake database, you can safely ignore these errors and remove the corresponding statements.

Code Examples¶

Input Code:¶

CREATE VIEW myView AS SELECT col1 FROM public.missingTable
WITH NO SCHEMA BINDING;

Copy

Output Code:¶

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "public.missingTable" **
CREATE VIEW myView
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
AS SELECT col1 FROM
public.missingTable
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0003 - WITH NO SCHEMA BINDING STATEMENT CAN NOT BE REMOVED DUE TO MISSING REFERENCES. ***/!!!
WITH NO SCHEMA BINDING;

Copy

Recommendations¶

To fix this issue, add any missing references in your code. If the object already exists in the Snowflake database, you can safely remove the statement.
For additional help, contact us at snowconvert-support@snowflake.com

SSC-EWI-RS0004¶

Severity¶

High

Description¶

This issue occurs because Snowflake does not support the HLLSKETCH data type.

Code Example¶

Input Code:¶

CREATE TABLE table1
(
    col_hllsketch HLLSKETCH
);

Copy

Output Code:¶

CREATE TABLE table1
(
    col_hllsketch HLLSKETCH !!!RESOLVE EWI!!! /*** SSC-EWI-RS0004 - HLLSKETCH DATA TYPE NOT SUPPORTED IN SNOWFLAKE. ***/!!!
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}';

Copy

Recommendations¶

Review all aggregate functions that Snowflake offers for estimating cardinality using HyperLogLog.
For additional assistance, contact us at snowconvert-support@snowflake.com

SSC-EWI-RS0005¶

Severity¶

High

Description¶

This issue occurs because Snowflake does not allow column aliases to be used in the IN clause of PIVOT/UNPIVOT operations. For more details, see the Snowflake documentation.

Code Example¶

Input Code:¶

SELECT *
FROM count_by_color UNPIVOT (
    cnt FOR color IN (red AS r, green AS g, blue AS b)
);

Copy

Output Code:¶

SELECT *
FROM
    count_by_color UNPIVOT (
    cnt FOR color IN (red
                          !!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!! AS r, green
                                                                                                                                                                              !!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!! AS g, blue
                                                                                                                                                                                                                                                                                                                                 !!!RESOLVE EWI!!! /*** SSC-EWI-RS0005 - COLUMN ALIASES CANNOT BE USED IN THE IN CLAUSE OF THE PIVOT/UNPIVOT QUERY IN SNOWFLAKE. ***/!!! AS b)
);

Copy

Recommendations¶

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

SSC-EWI-RS0001¶

Severity¶

Medium

Description¶

Redshift’s CREATE PROCEDURE statement includes an optional NONATOMIC transaction mode clause. Since Snowflake doesn’t have a direct equivalent for this feature, SMA attempts to replicate similar functionality by modifying the converted code where possible.

The NONATOMIC procedure behavior, which automatically commits statements within the procedure, needs to be manually verified. Snowflake provides an “autocommit” account parameter to replicate this functionality. For more details about autocommit, refer to the Snowflake documentation.

Code Examples¶

Input Code:¶

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

Copy

Output Code:¶

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;
   $$;

Copy

Recommendations¶

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