SnowConvert: Redshift SQL Statements¶

CALL¶

Description¶

Runs a stored procedure. The CALL command must include the procedure name and the input argument values. You must call a stored procedure by using the CALL statement. (Redshift SQL Language Reference CALL).

Warning

This syntax is partially supported in Snowflake. Procedure calls using output parameters will be tagged with the SSC-EWI-0073. This transformation will be delivered in the future.

Grammar Syntax¶

 CALL sp_name ( [ argument ] [, ...] )

Copy

Sample Source Patterns¶

Base scenario¶

Input Code:¶

 CREATE PROCEDURE sp_insert_values(IN arg1 INT, IN arg2 DATE)
LANGUAGE plpgsql
AS
$$
BEGIN
    INSERT INTO event VALUES (arg1, arg2);
END;
$$;

CALL sp_insert_values(1, CURRENT_DATE);

Copy

Output Code:¶

 CREATE PROCEDURE sp_insert_values (arg1 INT, arg2 DATE)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS
$$
BEGIN
    INSERT INTO event
    VALUES (:arg1, : arg2);
END;
$$;

CALL sp_insert_values(1, CURRENT_DATE());

Copy

Call using Output Parameters Mode (INOUT, OUT)¶

Input Code:¶

 CREATE OR REPLACE PROCEDURE sp_calculate_sum_product(IN a NUMERIC, IN b NUMERIC, INOUT sum_result NUMERIC, INOUT product_result NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    sum_result := a + b;
    product_result := a * b;
END;
$$;

CREATE OR REPLACE PROCEDURE call_sp_calculate_sum_product()
LANGUAGE plpgsql
AS $$
DECLARE
    sum_value NUMERIC DEFAULT null;
    product_value NUMERIC DEFAULT null;
BEGIN
    CALL sp_calculate_sum_product(FLOOR(20.5)::NUMERIC, CEIL(20.7)::NUMERIC, sum_value, product_value);
    INSERT INTO test VALUES (sum_value, product_value);
END;
$$;

CALL call_sp_calculate_sum_product();

Copy

Output Code:¶

 CREATE OR REPLACE PROCEDURE sp_calculate_sum_product (a NUMERIC, b NUMERIC, sum_result NUMERIC, product_result NUMERIC)
RETURNS VARIANT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS $$
BEGIN
    sum_result := a + b;
    product_result := a * b;
    RETURN OBJECT_CONSTRUCT('sum_result', :sum_result, 'product_result', :product_result);
END;
$$;

CREATE OR REPLACE PROCEDURE call_sp_calculate_sum_product ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/14/2025",  "domain": "test" }}'
AS $$
DECLARE
    sum_value NUMERIC DEFAULT NULL;
    product_value NUMERIC DEFAULT NULL;
BEGIN
    !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CALL' NODE ***/!!!
    CALL sp_calculate_sum_product(FLOOR(20.5), CEIL(20.7), sum_value, product_value);
    INSERT INTO test
    VALUES (:sum_value, : product_value);
END;
$$;

CALL call_sp_calculate_sum_product();

Copy

Known Issues¶

Output parameters are not currently supported in procedure calls.

CREATE DATABASE¶

Grammar Syntax¶

 CREATE DATABASE database_name 
[ { [ WITH ]
    [ OWNER [=] db_owner ]
    [ CONNECTION LIMIT { limit | UNLIMITED } ]
    [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ]
    [ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
  }
  | { [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid }
  | { FROM { { ARN '<arn>' } { WITH DATA CATALOG SCHEMA '<schema>' | WITH NO DATA CATALOG SCHEMA } }
             | { INTEGRATION '<integration_id>'} }
  | { IAM_ROLE  {default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>' } }

Copy

For more information please refer to Redshift CREATE DATABASE documentation.

Sample Source Patterns¶

Basic samples¶

Input Code:¶

 CREATE DATABASE database_name;

Copy

Output Code:¶

 CREATE DATABASE IF NOT EXISTS database_name
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/25/2024" }}';

Copy

Collate Clause¶

Input Code:¶

 CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;

Copy

Output Code:¶

 CREATE DATABASE IF NOT EXISTS database_collate
DEFAULT_DDL_COLLATION='en-ci'
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Connection Limit Clause¶

Input Code:¶

 CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;

Copy

Output Code:¶

 CREATE DATABASE IF NOT EXISTS database_connection
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Warning

The connection limit clause is removed since the connection concurrency in snowflake is managed by warehouse. More information here.

From ARN Clause¶

Input Code:¶

 CREATE DATABASE database_fromARN
FROM ARN 'arn' WITH NO DATA CATALOG SCHEMA IAM_ROLE 'arn:aws:iam::<account-id>:role/<role-name';

Copy

Output Code:¶

 CREATE DATABASE IF NOT EXISTS database_fromARN
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Warning

This clause is removed since it is used to reference Amazon Resources, not valid in Snowflake.

From Datashare Clause¶

Input Code¶

 CREATE DATABASE database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid';

Copy

Output Code¶

 CREATE DATABASE IF NOT EXISTS  database_fromDatashare
FROM DATASHARE datashare_name OF NAMESPACE 'namespace_guid' !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'FromDatashareAttribute' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Note

The transformation for Datashare is planned to be delivered in the future.

Owner Clause¶

Input Code¶

 CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';

Copy

Output Code¶

 CREATE DATABASE IF NOT EXISTS database_Owner
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Warning

Please be aware that for this case, the owner clause is removed from the code since Snowflake databases are owned by roles, not individual users. For more information please refer to Snowflake GRANT OWNERSHIP documentation.

Isolation Level Clause¶

Input Code¶

 CREATE DATABASE database_Isolation
ISOLATION LEVEL SNAPSHOT;

Copy

Output Code¶

 CREATE DATABASE IF NOT EXISTS database_Isolation
ISOLATION LEVEL SNAPSHOT !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'IsolationLevelAttribute' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/24/2024" }}';

Copy

Note

The transformation for Isolation Level is planned to be delivered in the future.

Related EWIs¶

SSC-EWI-0073: Pending Functional Equivalence Review

CREATE EXTERNAL TABLE¶

Description ¶

Currently Snowconvert is transforming CREATE EXTERNAL TABLES to regular tables, that implies additional effort because data stored in external RedShift tables must be transferred to the Snowflake database.

Grammar Syntax ¶

 CREATE EXTERNAL TABLE
external_schema.table_name
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )]
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name'
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

CREATE EXTERNAL TABLE
external_schema.table_name
[ PARTITIONED BY (col_name [, … ] ) ]
[ ROW FORMAT DELIMITED row_format ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]
 AS
 { select_statement }

Copy

Click here to go to the specification for this syntax.

Sample Source Patterns¶

Input Code:¶

 CREATE EXTERNAL TABLE
external_schema.sales_data
(
    sales_id INT,
    product_id INT,
    sales_amount DECIMAL(10, 2),
    sales_date DATE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://mybucket/sales_data/';

Copy

Output Code:¶

 --** SSC-FDM-0004 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
CREATE TABLE external_schema.sales_data
(
    sales_id INT,
    product_id INT,
    sales_amount DECIMAL(10, 2),
    sales_date DATE
)
--ROW FORMAT DELIMITED
--FIELDS TERMINATED BY ','
--STORED AS TEXTFILE
--LOCATION 's3://mybucket/sales_data/'
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
;

Copy

Create External Table AS¶

Input Code:¶

 CREATE EXTERNAL TABLE spectrum.partitioned_lineitem
PARTITIONED BY (l_shipdate, l_shipmode)
STORED AS parquet
LOCATION 'S3://amzn-s3-demo-bucket/cetas/partitioned_lineitem/'
AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;

Copy

Output Code:¶

 --** SSC-FDM-0004 - EXTERNAL TABLE TRANSLATED TO REGULAR TABLE **
CREATE TABLE spectrum.partitioned_lineitem
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
--PARTITIONED BY (l_shipdate, l_shipmode)
--STORED AS parquet
--LOCATION 'S3://amzn-s3-demo-bucket/cetas/partitioned_lineitem/'
AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM
local_table;

Copy

Recommendations¶

For the usage of Create External Table in Snowflake you may refer to Snowflake’s documentation.

Related EWIs¶

SSC-FDM-0004: External table translated to regular table

CREATE MATERIALIZED VIEW¶

Description¶

In Snowconvert, Redshift Materialized Views are transformed into Snowflake Dynamic Tables. To properly configure Dynamic Tables, two essential parameters must be defined: TARGET_LAG and WAREHOUSE. If these parameters are left unspecified in the configuration options, Snowconvert will default to preassigned values during the conversion, as demonstrated in the example below.

For more information on Materialized Views, click here.

For details on the necessary parameters for Dynamic Tables, click here.

Grammar Syntax¶

The following is the SQL syntax to create a view in Amazon Redshift. Click here to here to go to Redshifts specification for this syntax.

 CREATE MATERIALIZED VIEW mv_name
[ BACKUP { YES | NO } ]
[ table_attributes ]
[ AUTO REFRESH { YES | NO } ]
AS query          

Copy

Sample Source Patterns¶

Input Code:¶

 CREATE MATERIALIZED VIEW mv_baseball AS
SELECT ball AS baseball FROM baseball_table;

Copy

Output Code:¶

 CREATE DYNAMIC TABLE mv_baseball
--** 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": "11/26/2024",  "domain": "test" }}'
AS
    SELECT ball AS baseball FROM
        baseball_table;

Copy

Note

For the table attributes documentation you can check de following documentation:

Warning

The BACKUP and AUTO REFRESH clauses are deleted since they are not applicable in a Snowflake’s Dynamic Table

Related Ewis¶

SSC-FDM-0031: Dynamic Table required parameters set by default

CREATE SCHEMA¶

Grammar Syntax¶

 CREATE SCHEMA [ IF NOT EXISTS ] schema_name [ AUTHORIZATION username ]
           [ QUOTA {quota [MB | GB | TB] | UNLIMITED} ] [ schema_element [ ... ]

CREATE SCHEMA AUTHORIZATION username [ QUOTA {quota [MB | GB | TB] | UNLIMITED} ] 
[ schema_element [ ... ] ]

Copy

For more information please refer to Redshift CREATE SCHEMA documentation.

Sample Source Patterns¶

Basic samples¶

Input Code:¶

 CREATE SCHEMA s1;

CREATE SCHEMA IF NOT EXISTS s2;

CREATE SCHEMA s3
CREATE TABLE t1
(
    col1 INT
)
CREATE VIEW v1 AS SELECT * FROM t1;

Copy

Output Code:¶

 CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

CREATE SCHEMA IF NOT EXISTS s2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

CREATE SCHEMA IF NOT EXISTS s3
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;
CREATE TABLE t1
(
    col1 INT
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;
CREATE VIEW v1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
AS SELECT * FROM
    t1;

Copy

Authorization Clause¶

Input Code:¶

 CREATE SCHEMA s1 AUTHORIZATION miller;

Copy

Output Code:¶

 CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

Copy

Warning

Please be aware that for this case, the authorization clause is removed from the code since Snowflake schemas are owned by roles, not individual users. For more information please refer to Snowflake GRANT OWNERSHIP documentation.

Quota Clause¶

Input Code:¶

 CREATE SCHEMA s1 QUOTA UNLIMITED;

CREATE SCHEMA s2 QUOTA 10 TB;

Copy

Output Code:¶

 CREATE SCHEMA IF NOT EXISTS s1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

CREATE SCHEMA IF NOT EXISTS s2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

Copy

Note

In Snowflake is not allowed to define a quota per scheme. Storage management is done at the account and warehouse level, and Snowflake handles it automatically. For this reason it is removed from the code.

Create Schema Authorization¶

In Redshift when the schema name is not specified but the authorization clause is defined, a new schema is created with the owner’s name. For this reason this behavior is replicated in Snowflake.

Input Code:¶

 CREATE SCHEMA AUTHORIZATION miller;

Copy

Output Code:¶

 CREATE SCHEMA IF NOT EXISTS miller
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/23/2024" }}'
;

Copy

Related EWIs¶

There are no known issues.

CREATE VIEW¶

Description¶

This command creates a view in a database, which is run every time the view is referenced in a query. Using the WITH NO SCHEMA BINDING clause, you can create views to an external table or objects that don’t exist yet. This clause, however, requires you to specify the qualified name of the object or table that you are referencing.

Grammar Syntax¶

The following is the SQL syntax to create a view in Amazon Redshift. Click here to here to go to Redshifts specification for this syntax.

 CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query
[ WITH NO SCHEMA BINDING ]               

Copy

Sample Source Patterns¶

Considering the obligatory and optional clauses in Redshifts command, the output after migration to Snowflake is very similar.

Input Code:¶

 CREATE VIEW myuser
AS 
SELECT lastname FROM users;


CREATE VIEW myuser2
AS 
SELECT lastname FROM users2
WITH NO SCHEMA BINDING;

Copy

Output Code:¶

 CREATE VIEW myuser
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}'
AS
SELECT lastname FROM
users;

CREATE VIEW myuser2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "01/16/2025",  "domain": "test" }}'
AS
SELECT lastname FROM
users2
!!!RESOLVE EWI!!! /*** SSC-EWI-RS0003 - WITH NO SCHEMA BINDING STATEMENT CAN NOT BE REMOVED DUE TO MISSING REFERENCES. ***/!!!
WITH NO SCHEMA BINDING;

Copy

There are some exceptions, however, of one unsupported clause from Redshift, therefore an EWI was implemented to cover this case.

Related EWIs¶

SSC-EWI-RS0003: With no schema binding statement is not supported in Snowflake.

DELETE¶

Description¶

Deletes rows from tables. (Redshift SQL Language Reference Delete Statement).

This syntax is fully supported in Snowflake.

Grammar Syntax

 [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
DELETE [ FROM ] { table_name | materialized_view_name }
    [ USING table_name, ... ]
    [ WHERE condition ]

Copy

Sample Source Patterns

Setup data

 CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    department VARCHAR(255),
    manager_id INT REFERENCES employees(id)
);

INSERT INTO employees (id, name, department, manager_id) VALUES
(1, 'Alice', 'Sales', 2),
(2, 'Bob', 'Sales', 1),
(3, 'Charlie', 'Sales', 1),
(4, 'David', 'Marketing', 2),
(5, 'Eve', 'Marketing', 4),
(6, 'Frank', 'Marketing', 4),
(7, 'Grace', 'Engineering', 6),
(8, 'Helen', 'Engineering', 7),
(9, 'Ivy', 'Engineering', 7),
(10, 'John', 'Sales', 3),
(11, 'Joe', 'Engineering', 5);


CREATE TABLE departments (
    department_name VARCHAR(255)
);

INSERT INTO departments (department_name) VALUES
('Sales'), 
('Marketing'), 
('Engineering');

Copy

From Clause

Update a table by referencing information from other tables. In Redshift, the FROM keyword is optional, but in Snowflake, it is mandatory. Therefore, it will be added in cases where it’s missing.

Input Code:

 DELETE employees;

SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID

Output Code:

 DELETE FROM
    employees;
    
SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID

Where Clause

Restricts updates to rows that match a condition. When the condition returns true, the specified SET columns are updated. The condition can be a simple predicate on a column or a condition based on the result of a subquery. This clause is fully equivalent in Snowflake.

Input Code:

 DELETE FROM employees
WHERE department = 'Marketing';

SELECT * FROM employees
ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
1	Alice	Sales	2
2	Bob	Sales	1
3	Charlie	Sales	1
7	Grace	Engineering	6
8	Helen	Engineering	7
9	Ivy	Engineering	7
10	John	Sales	3
11	Joe	Engineering	5

Output Code:

 DELETE FROM
    employees
WHERE department = 'Marketing';

SELECT * FROM
    employees
ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
1	Alice	Sales	2
2	Bob	Sales	1
3	Charlie	Sales	1
7	Grace	Engineering	6
8	Helen	Engineering	7
9	Ivy	Engineering	7
10	John	Sales	3
11	Joe	Engineering	5

Using Clause

This clause introduces a list of tables when additional tables are referenced in the WHERE clause condition. This clause is fully equivalent in Snowflake.

Input Code:

 DELETE FROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name = 'Sales';

SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
4	David	Marketing	2
5	Eve	Marketing	4
6	Frank	Marketing	4
7	Grace	Engineering	6
8	Helen	Engineering	7
9	Ivy	Engineering	7
11	Joe	Engineering	5

Output Code:

 DELETE FROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name = 'Sales';

SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
4	David	Marketing	2
5	Eve	Marketing	4
6	Frank	Marketing	4
7	Grace	Engineering	6
8	Helen	Engineering	7
9	Ivy	Engineering	7
11	Joe	Engineering	5

WITH clause

This clause specifies one or more Common Table Expressions (CTE). The output column names are optional for non-recursive CTEs, but mandatory for recursive ones.

Since this clause cannot be used in an DELETE statement, it is transformed into temporary tables with their corresponding queries. After the DELETE statement is executed, these temporary tables are dropped to clean up, release resources, and avoid name collisions when creating tables within the same session. Additionally, if a regular table with the same name exists, it will take precedence again, since the temporary table has priority over any other table with the same name in the same session.

Non-Recursive CTE

Input Code:

 WITH sales_employees AS (
    SELECT id
    FROM employees
    WHERE department = 'Sales'
), engineering_employees AS (
    SELECT id
    FROM employees
    WHERE department = 'Engineering'
)
DELETE FROM employees
WHERE id IN (SELECT id FROM sales_employees)
   OR id IN (SELECT id FROM engineering_employees);

SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
4	David	Marketing	2
5	Eve	Marketing	4
6	Frank	Marketing	4

Output Code:

 CREATE TEMPORARY TABLE sales_employees AS
SELECT id
FROM employees
WHERE department = 'Sales';

CREATE TEMPORARY TABLE engineering_employees AS
SELECT id
FROM employees
WHERE department = 'Engineering';

DELETE FROM
    employees
WHERE id IN (SELECT id FROM sales_employees)
   OR id IN (SELECT id FROM engineering_employees);
   
DROP TABLE sales_employees;
DROP TABLE engineering_employees;

SELECT * FROM
    employees
ORDER BY id;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
4	David	Marketing	2
5	Eve	Marketing	4
6	Frank	Marketing	4

Recursive CTE

Input Code:

 WITH RECURSIVE subordinate_hierarchy(id, name, department, level) AS (
    SELECT id, name, department, 0 as level
    FROM employees
    WHERE department = 'Marketing'

    UNION ALL

    SELECT e.id, e.name, e.department, sh.level + 1
    FROM employees e
    INNER JOIN subordinate_hierarchy sh ON e.manager_id = sh.id
)
DELETE FROM employees
WHERE id IN (SELECT id FROM subordinate_hierarchy);

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
1	Alice	Sales	2
2	Bob	Sales	1
3	Charlie	Sales	1
10	John	Sales	3

Output Code:

 CREATE TEMPORARY TABLE subordinate_hierarchy AS
   WITH RECURSIVE subordinate_hierarchy(id, name, department, level) AS (
       SELECT id, name, department, 0 as level
       FROM
           employees
       WHERE department = 'Marketing'

       UNION ALL

       SELECT e.id, e.name, e.department, sh.level + 1
       FROM
           employees e
       INNER JOIN
               subordinate_hierarchy sh ON e.manager_id = sh.id
   )
   SELECT
       id,
       name,
       department,
       level
   FROM
       subordinate_hierarchy;
       
   DELETE FROM
   employees
   WHERE id IN (SELECT id FROM
           subordinate_hierarchy
   );
   
   DROP TABLE subordinate_hierarchy;

Copy

ID	NAME	DEPARTMENT	MANAGER_ID
1	Alice	Sales	2
2	Bob	Sales	1
3	Charlie	Sales	1
10	John	Sales	3

Delete Materialized View

In Redshift, you can apply the DELETE statement to materialized views used for streaming ingestion. In Snowflake, these views are transformed into dynamic tables, and the DELETE statement cannot be used on dynamic tables. For this reason, an EWI will be added.

Input Code:

 CREATE MATERIALIZED VIEW emp_mv AS
SELECT id, name, department FROM employees WHERE department = 'Engineering';

DELETE FROM emp_mv
WHERE id = 2;

Copy

Output Code:

 CREATE DYNAMIC TABLE emp_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 department = '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
emp_mv
WHERE id = 2;

Copy

Known Issues

Replicating the functionality of the WITH clause requires creating temporary tables mirroring each Common Table Expression (CTE). However, this approach fails if a temporary table with the same name already exists within the current session, causing an error.

Related EWIs

SSC-FDM-0031: Dynamic Table required parameters set by default.
SSC-EWI-RS0008: Delete statement cannot be used on dynamic tables.

INSERT

Description

Inserts new rows into a table. (Redshift SQL Language Reference Insert Statement).

Warning

This syntax is partially supported in Snowflake.

Grammar Syntax¶

 INSERT INTO table_name [ ( column [, ...] ) ]
{DEFAULT VALUES |
VALUES ( { expression | DEFAULT } [, ...] )
[, ( { expression | DEFAULT } [, ...] )
[, ...] ] |
query }

Copy

Sample Source Patterns¶

Setup data¶

 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

CREATE TABLE new_employees (
    name VARCHAR(100),
    salary INT,
    department VARCHAR(50)
);

INSERT INTO new_employees (name, salary, department)
VALUES
    ('Grace Lee', 32000, 'Operations'),
    ('Hannah Gray', 26000, 'Finance');

Copy

Default Values¶

It inserts a complete row with its default values. If any columns do not have default values, NULL values are inserted in those columns.

This clause cannot specify individual columns; it always inserts a complete row with its default values. Additionally, columns with the NOT NULL constraint cannot be included in the table definition. To replicate this behavior in Snowflake, SnowConvert insert a column with a DEFAULT value in the table. This action inserts a complete row, using the default value for every column.

Input Code:¶

 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

INSERT INTO employees
DEFAULT VALUES;

SELECT * FROM employees ORDER BY id;

Copy

ID	NAME	SALARY	DEPARTMENT
1	NULL	20000	Marketing

Output Code:¶

 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1) ORDER,
    name VARCHAR(100),
    salary INT DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';

INSERT INTO employees (id)
VALUES (DEFAULT);

SELECT * FROM
    employees
ORDER BY id;

Copy

ID	NAME	SALARY	DEPARTMENT
1	NULL	20000	Marketing

Query¶

Insert one or more rows into the table by using a query. All rows produced by the query will be inserted into the table. The query must return a column list that is compatible with the table’s columns, although the column names do not need to match. This functionality is fully equivalent in Snowflake.

Input Code:¶

 INSERT INTO employees (name, salary, department)
SELECT name, salary, department FROM new_employees;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Grace Lee	32000	Operations
2	Hannah Gray	26000	Finance

Output Code:¶

 INSERT INTO employees (name, salary, department)
SELECT name, salary, department FROM
    new_employees;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Grace Lee	32000	Operations
2	Hannah Gray	26000	Finance

Known Issues ¶

Certain expressions cannot be used in the VALUES clause in Snowflake. For example, in Redshift, the JSON_PARSE function can be used within the VALUES clause to insert a JSON value into a SUPER data type. In Snowflake, however, the PARSE_JSON function cannot be used in the VALUES clause to insert a JSON value into a VARIANT data type. Instead, a query can be used in place of the VALUES clause. For more details, please refer to the Snowflake documentation. You can also check the following article for further information.

Related EWIs¶

There are no known issues.

MERGE¶

Grammar Syntax¶

 MERGE INTO target_table 
USING source_table [ [ AS ] alias ] 
ON match_condition 
[ WHEN MATCHED THEN { UPDATE SET col_name = { expr } [,...] | DELETE }
WHEN NOT MATCHED THEN INSERT [ ( col_name [,...] ) ] VALUES ( { expr } [, ...] ) |
REMOVE DUPLICATES ]

Copy

For more information please refer to Redshift MERGE documentation.

Sample Source Patterns¶

UPDATE - INSERT¶

There are no differences between both languages. The code is kept in its original form.

Input Code:¶

 MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);

Copy

Output 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 UPDATE SET id = source.id, name = source.name
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);

Copy

DELETE - INSERT¶

There are no differences between both languages. The code is kept in its original form.

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

Output 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

REMOVE DUPLICATES¶

The REMOVE DUPLICATES clause is not supported in Snowflake, however, there is a workaround that could emulate the original behavior.

The output code will have three new statements:

A TEMPORARY TABLE with the duplicate values from the source and target table that matches the condition
An INSERT statement that adds the pending values to the target table after the merge
A DROP statement that drops the generated temporary table.

These are necessary since the DROP DUPLICATES behavior removes the duplicate values from the target table and then inserts the values that match the condition from the source table.

Input Code:¶

 CREATE TABLE target (id INT, name CHAR(10));
CREATE TABLE source (id INT, name CHAR(10));

INSERT INTO target VALUES (30, 'Tony'), (30, 'Daisy'), (11, 'Alice'), (23, 'Bill'), (23, 'Nikki');
INSERT INTO source VALUES (23, 'David'), (22, 'Clarence');

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

Copy

ID	NAME
30	Daisy
22	Clarence
30	Tony
11	Alice
23	David

Output Code:¶

 CREATE TABLE target (id INT, name CHAR(10))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';

CREATE TABLE source (id INT, name CHAR(10))
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "03/03/2025",  "domain": "test" }}';

INSERT INTO target
VALUES (30, 'Tony'), (30, 'Daisy'), (11, 'Alice'), (23, 'Bill'), (23, 'Nikki');

INSERT INTO source
VALUES (23, 'David'), (22, 'Clarence');

CREATE TEMPORARY TABLE source_duplicates AS
SELECT DISTINCT
source.*
FROM
source
INNER JOIN
target
ON target.id = source.id;
--** 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);
INSERT INTO target

SELECT
*
FROM
source_duplicates;

DROP TABLE IF EXISTS source_duplicates CASCADE;

Copy

ID	NAME
22	Clarence
30	Tony
30	Daisy
11	Alice
23	David

Known Issues¶

There are no known issues.

Related EWIs¶

SSC-EWI-RS0009: Semantic information not found for the source table.
SSC-FDM-RS0005: Duplicates not allowed in source table.

UPDATE¶

Description¶

Updates values in one or more table columns when a condition is satisfied. (Redshift SQL Language Reference Update Statement).

This syntax is fully supported in Snowflake.

Grammar Syntax

 [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
            UPDATE table_name [ [ AS ] alias ] SET column = { expression | DEFAULT } [,...]

[ FROM fromlist ]
[ WHERE condition ]

Copy

Sample Source Patterns

Setup data

 CREATE TABLE employees (
    id INTEGER IDENTITY(1,1),
    name VARCHAR(100),
    salary DECIMAL DEFAULT 20000,
    department VARCHAR(50) DEFAULT 'Marketing'
);

INSERT INTO employees (name, salary, department)
VALUES
    ('Alice', 500000, 'HR'),
    ('Bob', 600000, 'Engineering'),
    ('Charlie', 700000, 'Engineering'),
    ('David', 400000, 'Marketing'),
    ('Eve', 450000, 'HR'),
    ('Frank', 750000, 'Engineering'),
    ('Grace', 650000, 'Engineering'),
    ('Helen', 390000, 'Marketing'),
    ('Ivy', 480000, 'HR'),
    ('Jack', 420000, 'Engineering'),
    ('Ken', 700000, 'Marketing'),
    ('Liam', 600000, 'Engineering'),
    ('Mona', 470000, 'HR');

CREATE TABLE department_bonus (
    department VARCHAR(100),
    bonus DECIMAL
);

INSERT INTO department_bonus (department, bonus)
VALUES
    ('HR', 10000),
    ('Engineering', 50000),
    ('Marketing', 20000),
    ('Sales', 5000);

Copy

Alias

Although Snowflake’s grammar does not specify that a table alias can be used, it’s valid code in Snowflake.

Input Code:

 UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	505000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	405000	Marketing
5	Eve	455000	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	395000	Marketing
9	Ivy	485000	HR
10	Jack	425000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	475000	HR

Output Code:

 UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	505000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	405000	Marketing
5	Eve	455000	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	395000	Marketing
9	Ivy	485000	HR
10	Jack	425000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	475000	HR

WITH clause

This clause specifies one or more Common Table Expressions (CTE). The output column names are optional for non-recursive CTEs, but mandatory for recursive ones.

Since this clause cannot be used in an UPDATE statement, it is transformed into temporary tables with their corresponding queries. After the UPDATE statement is executed, these temporary tables are dropped to clean up, release resources, and avoid name collisions when creating tables within the same session. Additionally, if a regular table with the same name exists, it will take precedence again, since the temporary table has priority over any other table with the same name in the same session.

Non-Recursive CTE

Input Code:

 WITH avg_salary_cte AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
UPDATE employees
SET salary = (SELECT avg_salary FROM avg_salary_cte)
WHERE salary < 500000;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	500000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	546923	Marketing
5	Eve	546923	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	546923	Marketing
9	Ivy	546923	HR
10	Jack	546923	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	546923	HR

Output Code:

 CREATE TEMPORARY TABLE avg_salary_cte AS
SELECT AVG(salary) AS avg_salary FROM
employees;

UPDATE employees
SET salary = (SELECT avg_salary FROM
      avg_salary_cte
)
WHERE salary < 500000;

DROP TABLE avg_salary_cte;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	500000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	546923	Marketing
5	Eve	546923	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	546923	Marketing
9	Ivy	546923	HR
10	Jack	546923	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	546923	HR

Recursive CTE

Input Code:

 WITH RECURSIVE bonus_updates(id, name, department, salary, level) AS (
    SELECT e.id,
           e.name,
           e.department,
           e.salary + CASE
                          WHEN db.bonus IS NOT NULL THEN db.bonus
                          ELSE 0
               END AS new_salary,
           1 AS level
    FROM employees e
    LEFT JOIN department_bonus db ON e.department = db.department
    UNION ALL
    SELECT e.id,
           e.name,
           e.department,
           e.salary + CASE
                          WHEN db.bonus IS NOT NULL THEN db.bonus
                          ELSE 0
               END + (e.salary * 0.05) AS new_salary,
           bu.level + 1
    FROM employees e
    JOIN department_bonus db ON e.department = db.department
    JOIN bonus_updates bu ON e.id = bu.id
    WHERE bu.level < 3
)
UPDATE employees
SET salary = bu.new_salary
FROM (SELECT id, AVG(salary) as new_salary FROM bonus_updates GROUP BY id) as bu
WHERE employees.id = bu.id
  AND bu.new_salary > employees.salary;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	526666	HR
2	Bob	670000	Engineering
3	Charlie	773333	Engineering
4	David	433333	Marketing
5	Eve	475000	HR
6	Frank	825000	Engineering
7	Grace	721666	Engineering
8	Helen	423000	Marketing
9	Ivy	506000	HR
10	Jack	484000	Engineering
11	Ken	743333	Marketing
12	Liam	670000	Engineering
13	Mona	495668	HR

Output Code:

 CREATE TEMPORARY TABLE bonus_updates AS
  --** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "employees", "department_bonus" **
 WITH RECURSIVE bonus_updates(id, name, department, salary, level) AS (
     SELECT e.id,
            e.name,
            e.department,
            e.salary + CASE
                           WHEN db.bonus IS NOT NULL THEN db.bonus
                           ELSE 0
                END AS new_salary,
            1 AS level
     FROM
            employees e
     LEFT JOIN
                           department_bonus db ON e.department = db.department
     UNION ALL
     SELECT e.id,
            e.name,
            e.department,
            e.salary + CASE
                           WHEN db.bonus IS NOT NULL THEN db.bonus
                           ELSE 0
                END + (e.salary * 0.05) AS new_salary,
            bu.level + 1
     FROM
            employees e
     JOIN
                           department_bonus db ON e.department = db.department
     JOIN
                           bonus_updates bu ON e.id = bu.id
     WHERE bu.level < 3
 )
 SELECT
     id,
     name,
     department,
     salary,
     level
 FROM
     bonus_updates;
     
UPDATE employees
SET salary = bu.new_salary
FROM (SELECT id, AVG(salary) as new_salary 
FROM bonus_updates 
GROUP BY id) as bu
WHERE employees.id = bu.id
  AND bu.new_salary > employees.salary;
 
DROP TABLE bonus_updates;

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	526667	HR
2	Bob	670000	Engineering
3	Charlie	773333	Engineering
4	David	433333	Marketing
5	Eve	475000	HR
6	Frank	825000	Engineering
7	Grace	721667	Engineering
8	Helen	423000	Marketing
9	Ivy	506000	HR
10	Jack	484000	Engineering
11	Ken	743333	Marketing
12	Liam	670000	Engineering
13	Mona	495667	HR

SET DEFAULT values

Input Code:

 UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE department = 'HR';

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	20000	Sales
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	400000	Marketing
5	Eve	20000	Sales
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	390000	Marketing
9	Ivy	20000	Sales
10	Jack	420000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	20000	Sales

Output Code:

 UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE
    department = 'HR';

Copy

ID	NAME	SALARY	DEPARTMENT
1	Alice	20000	Sales
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	400000	Marketing
5	Eve	20000	Sales
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	390000	Marketing
9	Ivy	20000	Sales
10	Jack	420000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	20000	Sales

SET clause

It is responsible for modifying values in the columns. Similar to Snowflake, update queries with multiple matches per row will throw an error when the configuration parameter ERROR_ON_NONDETERMINISTIC_UPDATE is set to true. This flag works the same way in Snowflake, and it even uses the same name, ERROR_ON_NONDETERMINISTIC_UPDATE.

However, when this flag is turned off, no error is returned, and one of the matched rows is used to update the target row. The selected joined row is nondeterministic and arbitrary in both languages; the behavior may not be consistent across executions, which could lead to data inconsistencies.

Setup data:

 CREATE TABLE target (
  k INT,
  v INT
);

CREATE TABLE src (
  k INT,
  v INT
);

INSERT INTO target (k, v) VALUES (0, 10);

INSERT INTO src (k, v) VALUES
  (0, 14),
  (0, 15),
  (0, 16);

Copy

Input Code:

 UPDATE target
  SET v = src.v
  FROM src
  WHERE target.k = src.k;


SELECT * FROM target;

Copy

K	V
0	16

Output Code:

 UPDATE target
  SET v = src.v
  FROM src
  WHERE target.k = src.k;


SELECT * FROM target;

Copy

K	V
0	14

Known Issues

Update queries with multiple matches per row may cause data inconsistencies. Although both platforms have the flag ERROR_ON_NONDETERMINISTIC_UPDATE, these values will always be nondeterministic. Snowflake offers recommendations for handling these scenarios. Click here for more details.
Replicating the functionality of the WITH clause requires creating temporary tables mirroring each Common Table Expression (CTE). However, this approach fails if a temporary table with the same name already exists within the current session, causing an error.

Related EWIs

There are no known issues.

CREATE TABLE AS

Description

Creates a new table based on a query. The owner of this table is the user that issues the command.

For more information please refer to CREATE TABLE AS documentation.

Grammar Syntax

 CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ]
TABLE table_name
[ ( column_name [, ... ] ) ]
[ BACKUP { YES | NO } ]
[ table_attributes ]
AS query

where table_attributes are:
[ DISTSTYLE { AUTO | EVEN | ALL | KEY } ]
[ DISTKEY( distkey_identifier ) ]
[ [ COMPOUND | INTERLEAVED ] SORTKEY( column_name [, ...] ) ]   

Copy

Table Start: BACKUP

Description

Enables Amazon Redshift to automatically adjust the encoding type for all columns in the table to optimize query performance. In Snowflake, the concept of BACKUP as seen in other databases is not directly applicable. Snowflake automatically handles data backup and recovery through its built-in features like Time Travel and Fail-safe, eliminating the need for manual backup operations. For these reasons, the statement BACKUP is removed during the transformation process

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 BACKUP { YES | NO }

Copy

Sample Source Patterns

NO option

An FDM is added since Snowflake, by default, always creates a backup of the created table.

Input Code:

 CREATE TABLE table1
BACKUP NO
AS SELECT * FROM table_test;

Copy

Output Code:

 CREATE TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
----** SSC-FDM-RS0001 - BACKUP NO OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--BACKUP NO
AS SELECT * FROM
table_test;

Copy

YES option

The option is removed since Snowflake, by default, applies a backup to the created table.

Input Code:

 CREATE TABLE table1
BACKUP YES
AS SELECT * FROM table_test;

Copy

Output Code:

 CREATE TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
AS SELECT * FROM
table_test;

Copy

Related EWIs

SSC-FDM-RS0001: “Option” not supported. Data storage is automatically handled by Snowflake.

Table Start: COLUMNS

Description

The name of a column in the new table. If no column names are provided, the column names are taken from the output column names of the query.

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 ( column_name [, ... ] )

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE table1 
(
    col1, col2, col3
)
AS SELECT col1, col2, col3 FROM table_test;

Copy

Output Code:

 CREATE TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
(
    col1, col2, col3
)
AS SELECT col1, col2, col3 FROM
        table_test;

Copy

Related EWIs

There are no known issues.

Table Start: LOCAL

Description

In Amazon Redshift, LOCAL TEMPORARY or TEMP are used to create temporary tables that exist only for the duration of the session. These tables are session-specific and automatically deleted when the session ends. They are useful for storing intermediate results or working data without affecting the permanent database schema.

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 LOCAL { TEMPORARY | TEMP }

Copy

Sample Source Patterns

Input Code:

 CREATE LOCAL TEMP TABLE table1
AS SELECT FROM table_test;

Copy

Output Code:

 CREATE LOCAL TEMP TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
AS SELECT FROM
table_test;

Copy

Related EWIs

There are no known issues.

Tabla Attributes: DISTKEY

Description

In Amazon Redshift, DISTKEY is used to distribute data across cluster nodes to optimize query performance. Snowflake, however, automatically handles data distribution and storage without needing explicit distribution keys. Due to differences in architecture and data management approaches, Snowflake does not have a direct equivalent to Redshift’s DISTKEY. For these reasons, the statement DISTKEY is removed during the transformation process

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 DISTKEY ( column_name )

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE table1
DISTKEY (col1)
AS SELECT * FROM table_test;

Copy

Output Code:

 CREATE TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "02/10/2025",  "domain": "test" }}'
----** SSC-FDM-RS0001 - DISTKEY OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTKEY (col1)
AS SELECT * FROM
table_test;

Copy

Related EWIs

SSC-FDM-RS0001: “Option” not supported. Data storage is automatically handled by Snowflake.

Tabla Attributes: DISTSTYLE

Description

Keyword that defines the data distribution style for the whole table.

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 DISTSTYLE { AUTO | EVEN | KEY | ALL }

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE table1 
DISTSTYLE AUTO
AS SELECT * FROM table_test;

CREATE TABLE table2
DISTSTYLE EVEN
AS SELECT * FROM table_test;

CREATE TABLE table3
DISTSTYLE ALL
AS SELECT * FROM table_test;

CREATE TABLE table4
DISTSTYLE KEY
DISTKEY (col1)
AS SELECT * FROM table_test;

Copy

Output Code:

 CREATE TABLE table1
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
----** SSC-FDM-RS0001 - DISTSTYLE AUTO OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE AUTO
AS SELECT * FROM
table_test;

CREATE TABLE table2
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
----** SSC-FDM-RS0001 - DISTSTYLE EVEN OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE EVEN
AS SELECT * FROM
table_test;

CREATE TABLE table3
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
----** SSC-FDM-RS0001 - DISTSTYLE ALL OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE ALL
AS SELECT * FROM
table_test;

CREATE TABLE table4
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "09/17/2024" }}'
----** SSC-FDM-RS0001 - DISTSTYLE KEY OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTSTYLE KEY
----** SSC-FDM-RS0001 - DISTKEY OPTION NOT SUPPORTED. DATA STORAGE IS AUTOMATICALLY HANDLED BY SNOWFLAKE. **
--DISTKEY (col1)
AS SELECT * FROM
table_test;

Copy

Related EWIs

SSC-FDM-RS0001: “Option” not supported. Data storage is automatically handled by Snowflake.

Tabla Attributes: SORTKEY

Description

The keyword that specifies that the column is the sort key for the table. In Snowflake, SORTKEY from Redshift can be migrated to CLUSTER BY because both optimize data storage for query performance. CLUSTER BY in Snowflake organizes data on specified columns, similar to how SORTKEY orders data in Redshift.

Click here to navigate to the Amazon Redshift docs page for this syntax.

Grammar Syntax

 [ COMPOUND | INTERLEAVED ] SORTKEY( column_name [, ...] )

Copy

Sample Source Patterns

Input Code:

 CREATE TABLE table1 (
    col1,
    col2,
    col3,
    col4
)
COMPOUND SORTKEY (col1, col3)
AS SELECT * FROM table_test;

CREATE TABLE table2 (
    col1
)
INTERLEAVED SORTKEY (col1)
AS SELECT * FROM table_test;

CREATE TABLE table3 (
    col1
)
SORTKEY (col1)
AS SELECT * FROM table_test;

Copy

Output Code:

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

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

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

Copy

Related EWIs

SSC-FDM-RS0002: The performance of the CLUSTER BY may vary compared to the performance of Sortkey.

SnowConvert: Redshift SQL Statements¶

CALL¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Base scenario¶

Input Code:¶

Output Code:¶

Call using Output Parameters Mode (INOUT, OUT)¶

Input Code:¶

Output Code:¶

Known Issues¶

Related EWIs.¶

CREATE DATABASE¶

Grammar Syntax¶

Sample Source Patterns¶

Basic samples¶

Input Code:¶

Output Code:¶

Collate Clause¶

Input Code:¶

Output Code:¶

Connection Limit Clause¶

Input Code:¶

Output Code:¶

From ARN Clause¶

Input Code:¶

Output Code:¶

From Datashare Clause¶

Input Code¶

Output Code¶

Owner Clause¶

Input Code¶

Output Code¶

Isolation Level Clause¶

Input Code¶

Output Code¶

Related EWIs¶

CREATE EXTERNAL TABLE¶

Description ¶

Grammar Syntax ¶

Sample Source Patterns¶

Input Code:¶

Output Code:¶

Create External Table AS¶

Input Code:¶

Output Code:¶

Recommendations¶

Related EWIs¶

CREATE MATERIALIZED VIEW¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Input Code:¶

Output Code:¶

Related Ewis¶

CREATE SCHEMA¶

Grammar Syntax¶

Sample Source Patterns¶

Basic samples¶

Input Code:¶

Output Code:¶

Authorization Clause¶

Input Code:¶

Output Code:¶

Quota Clause¶

Input Code:¶

Output Code:¶

Create Schema Authorization¶

Input Code:¶

Output Code:¶

Related EWIs¶

CREATE VIEW¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Input Code:¶

Output Code:¶

Related EWIs¶

DELETE¶