SnowConvert: Redshift Issues¶

SSC-EWI-RS0002¶

Set “configuration parameter” is not supported in Snowflake.

Severity¶

Medium

Description¶

The use of SET configuration parameter (https://docs.aws.amazon.com/es_es/redshift/latest/dg/r_CREATE_PROCEDURE.html) is not supported by Snowflake. For more information please refer to CREATE PROCEDURE documentation.

Code Examples¶

Input Code:¶

Redshift¶

 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

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE procedure2 (input_param INTEGER, output_param NUMERIC)
RETURNS VARIANT
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": "03/03/2025",  "domain": "test" }}'
AS $$
BEGIN
    output_param := input_param * 1.7;
    RETURN OBJECT_CONSTRUCT('output_param', :output_param);
END;
$$;

Copy

Best Practices¶

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

SSC-EWI-RS0003¶

View with no schema binding can not be removed due to missing references.

Severity¶

Medium

Description¶

Redshift documentation for CREATE VIEW includes an optional clause that specifies that the particular view is not bound to the database objects such as tables or functions, nor to those objects that it is referencing. The documentation also clarifies that in such cases that this clause is used, the referenced objects must be qualified with a schema name. This clause allows to create a view and reference objects that might not exist yet. Their existence will be verified once the view is queried, but not at its definition.

However, there is no equivalent command nor obvious workaround to implement this functionality in Snowflake, furthermore, the Snowflake documentation suggests that the views are linked to a specific schema and so are the referenced objects in the view.

If the references linked to the View are present in the input code, the statement will be removed without issue. However, if the necessary references are missing, a warning message will be added to inform the user that the statement cannot be removed due to the missing references.

SnowConvert performs analysis solely on the input code and does not account for objects already deployed in Snowflake. Therefore the output may have some issues pointing to missing references, if the references are already present in the Snowflake database, the user can safely remove the statement without any issues.

Code Examples¶

Input Code:¶

Redshift¶

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

Copy

Generated Code:¶

Snowflake¶

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

Best Practices¶

To resolve this issue, it is suggested to add the missing references to the input code, if the object is already deployed in the Snowflake database, the statement can be remove without issue.
If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-EWI-RS0004¶

HLLSKETCH data type not supported in Snowflake.

Severity¶

High

Description¶

This conversion issue is added because the HLLSKETCH data type is not supported in Snowflake.

Code Example¶
Input Code:¶ CREATE TABLE table1
(
    col_hllsketch HLLSKETCH
);
Copy

Generated 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

Best Practices¶

Please verify all aggregate functions provided by Snowflake to estimate cardinality using HyperLogLog.
If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-EWI-RS0005¶

Column aliases cannot be used in the IN clause.

Severity¶

High

Description¶

This conversion issue is added because the column aliases cannot be used in the IN clause of the PIVOT/UNPIVOT query in Snowflake.

Code Example¶

Input Code:¶

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

Copy

Generated 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

Best Practices¶

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

SSC-EWI-RS0006¶

The behavior of the SUBSTRING function may differ when applied to binary data.

Severity¶

Medium

Description¶

The behavior of the SUBSTRING function may differ when applied to binary data. Since Redshift and Snowflake handle binary data differently, the results may vary between the two platforms.

Code Example¶

Input Code:¶

 SELECT SUBSTRING('12345'::varbyte, 2, 4) AS substring_binary;
SELECT SUBSTRING('abc'::varbyte, 2, 4) AS substring_binary;

Copy

Generated Code:¶

 SELECT SUBSTRING('12345':: BINARY, 2, 4) !!!RESOLVE EWI!!! /*** SSC-EWI-RS0006 - THE BEHAVIOR OF THE SUBSTRING FUNCTION MAY DIFFER WHEN APPLIED TO BINARY DATA. ***/!!! AS substring_binary;
SELECT SUBSTRING('abc':: BINARY, 2, 4) !!!RESOLVE EWI!!! /*** SSC-EWI-RS0006 - THE BEHAVIOR OF THE SUBSTRING FUNCTION MAY DIFFER WHEN APPLIED TO BINARY DATA. ***/!!! AS substring_binary;

Copy

Best Practices¶

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

SSC-EWI-RS0007¶

Date literal is not supported in Snowflake.

Severity¶

High

Description¶

Some DATE, TIME, or TIMESTAMP formats are not supported in Snowflake. These formats may require manual manipulation.

Code Example¶

Input Code:¶

 select datediff(century, '2000-Jan-31', 'Jan-31-2000');

Copy

Generated Code:¶

  select
 DATEDIFF(YEAR,
                !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - '2000-Jan-31' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
                '2000-Jan-31',
                               !!!RESOLVE EWI!!! /*** SSC-EWI-RS0007 - 'Jan-31-2000' DATE LITERAL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!!
                               'Jan-31-2000') / 100;

Copy

Best Practices¶

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

SSC-EWI-RS0008¶

Date literal is not supported in Snowflake.

Severity¶

High

Description¶

This conversion issue is added since In Redshift you can apply the DELETE statement to materialized views used for streaming ingestion (https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-streaming-ingestion.html). In Snowflake, these views are transformed into dynamic tables, and the DELETE statement cannot be used on dynamic tables.

Code Example¶

Input Code:¶

 CREATE MATERIALIZED VIEW mv AS
SELECT id, name, department_id FROM employees WHERE department_id = 101;

DELETE FROM mv
WHERE id = 2;

Copy

Generated 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": "03/03/2025",  "domain": "test" }}'
AS
SELECT id, name, department_id FROM
employees
WHERE department_id = 101;


!!!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

Best Practices¶

Because dynamic tables cannot be directly deleted from, you can achieve the same result by replacing the dynamic table definition.
If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-EWI-RS0009¶

Semantic information not found for the source table.

Severity¶

Low

Description¶

In Snowflake, the MERGE with REMOVE DUPLICATES clause is not supported. To fully support the original code, a workaround is needed. An INSERT WHEN NOT MATCHED clause needs to be generated in the output code, and it requires the source table columns. If the source table was not found during migration, no columns are generated, and an error will be thrown.

Code Example¶

Input Code:¶

Redshift¶

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

Copy

Generated Code:¶

Snowflake¶

 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. ***/!!!
--** 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 ();
INSERT INTO target
SELECT
*
FROM
source_duplicates;
DROP TABLE IF EXISTS source_duplicates CASCADE;

Copy

Best Practices¶

Search to see if the source table is present in the migrated code and manually add the columns in the insert operation.
If you need more support, you can email us at snowconvert-support@snowflake.com

SSC-EWI-RS0010¶

Out parameters not supported on top-level CALL unit.

Severity¶

Low

Description¶

Redshift allows the use of CALL statements within out parameters as top-level code units without defining a variable that would be assigned as output. Snowflake enforces the usage of a variable to set the output value somewhere.

Code Example¶

Input Code:¶

Redshift¶

 CREATE OR REPLACE PROCEDURE get_total_sales_by_product(
    IN p_product_name VARCHAR(100),
    OUT p_total_sales DECIMAL(18, 2)
)
AS $$
BEGIN
    NULL;
END;
$$ LANGUAGE plpgsql;

CALL get_total_sales_by_product('Laptop');

Copy

Generated Code:¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE get_total_sales_by_product (p_product_name VARCHAR(100), p_total_sales OUT DECIMAL(18, 2))
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/10/2025",  "domain": "no-domain-provided" }}'
AS $$
BEGIN
NULL;
END;
$$;
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0010 - Top-level Procedure Call with out parameters is not supported. ***/!!!
CALL get_total_sales_by_product('Laptop');

Copy

Best Practices¶

Move the call into an anonymous block and declare a variable to pass as an output parameter.
If you need more support, you can email us at snowconvert-support@snowflake.com