SnowConvert AI - Redshift - SQL Statements¶

Translation reference for all the supported statements by SnowConvert AI for Redshift.

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).

Grammar Syntax¶

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

Sample Source Patterns¶

Base scenario¶

Input Code:¶

Redshift¶

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

Output Code:¶

Redshift¶

 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": "07/11/2025",  "domain": "no-domain-provided" }}'
AS
$$
BEGIN
    INSERT INTO event
    VALUES (:arg1, : arg2);
END;
$$;

CALL sp_insert_values(1, CURRENT_DATE());

Call using Output Parameters Mode (INOUT, OUT)¶

Input Code:¶

Redshift¶

 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();

Output Code:¶

Redshift¶

 CREATE OR REPLACE PROCEDURE sp_calculate_sum_product (a NUMERIC, b NUMERIC, sum_result OUT NUMERIC, product_result OUT NUMERIC)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
BEGIN
    sum_result := a + b;
    product_result := a * b;
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": "07/11/2025",  "domain": "no-domain-provided" }}'
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();

Known Issues¶

Output parameters from calls outside procedures won’t work.

SSC-EWI-0073: Pending Functional Equivalence Review

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>' } }

For more information please refer to Redshift CREATE DATABASE documentation.

Sample Source Patterns¶

Basic samples¶

Input Code:¶

Redshift¶

 CREATE DATABASE database_name;

Output Code:¶

Snowflake¶

 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" }}';

Collate Clause¶

Input Code:¶

Redshift¶

 CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;

Output Code:¶

Snowflake¶

 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" }}';

Connection Limit Clause¶

Input Code:¶

Redshift¶

 CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;

Output Code:¶

Snowflake¶

 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" }}';

Warning

The connection limit clause is removed since the connection concurrency in Snowflake is managed by warehouse. For more information, see the Snowflake MAX_CONCURRENCY_LEVEL parameter.

From ARN Clause¶

Input Code:¶

Redshift¶

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

Output Code:¶

Snowflake¶

 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" }}';

Warning

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

From Datashare Clause¶

Input Code¶

Redshift¶

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

Output Code¶

Snowflake¶

 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" }}';

Note

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

Owner Clause¶

Input Code¶

Redshift¶

 CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';

Output Code¶

Snowflake¶

 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" }}';

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¶

Redshift¶

 CREATE DATABASE database_Isolation
ISOLATION LEVEL SNAPSHOT;

Output Code¶

Snowflake¶

 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" }}';

Note

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

SSC-EWI-0073: Pending Functional Equivalence Review

CREATE EXTERNAL TABLE¶

Description ¶

Currently SnowConvert AI 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 }

See the Redshift CREATE EXTERNAL TABLE specification for this syntax.

Sample Source Patterns¶

Input Code:¶

Redshift¶

 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/';

Output Code:¶

Snowflake¶

 --** 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" }}'
;

Create External Table AS¶

Input Code:¶

Redshift¶

 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;

Output Code:¶

Snowflake¶

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

Recommendations¶

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

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

CREATE MATERIALIZED VIEW¶

Description¶

In SnowConvert AI, 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 AI will default to preassigned values during the conversion, as demonstrated in the example below.

For more information, see the Redshift CREATE MATERIALIZED VIEW documentation.

For details on the necessary parameters, see the Snowflake CREATE DYNAMIC TABLE documentation.

Grammar Syntax¶

The following is the SQL syntax to create a view in Amazon Redshift. See the Redshift CREATE MATERIALIZED VIEW specification for this syntax.

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

Sample Source Patterns¶

Input Code:¶

Redshift¶

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

Output Code:¶

Snowflake¶

 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;

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

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 [ ... ] ]

For more information please refer to Redshift CREATE SCHEMA documentation.

Sample Source Patterns¶

Basic samples¶

Input Code:¶

Redshift¶

 CREATE SCHEMA s1;

CREATE SCHEMA IF NOT EXISTS s2;

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

Output Code:¶

Snowflake¶

 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;

Authorization Clause¶

Input Code:¶

Redshift¶

 CREATE SCHEMA s1 AUTHORIZATION miller;

Output Code:¶

Snowflake¶

 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" }}'
;

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

Redshift¶

 CREATE SCHEMA s1 QUOTA UNLIMITED;

CREATE SCHEMA s2 QUOTA 10 TB;

Output Code:¶

Snowflake¶

 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" }}'
;

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

Redshift¶

 CREATE SCHEMA AUTHORIZATION miller;

Output Code:¶

Snowflake¶

 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" }}'
;

There are no known issues.

CREATE FUNCTION¶

Description¶

This command defines a user-defined function (UDF) within the database. These functions encapsulate reusable logic that can be invoked within SQL queries.

Grammar Syntax¶

The following is the SQL syntax to create a view in Amazon Redshift. See the Redshift CREATE VIEW specification for this syntax.

 CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

SQL Language¶

Volatility category¶

In Snowflake, VOLATILE and IMMUTABLE function volatility are functionally equivalent. Given that STABLE is inherently transformed to the default VOLATILE behavior, explicit use of STABLE will be deleted.

Input Code:¶

Redshift¶

 CREATE OR REPLACE FUNCTION get_sale(INTEGER)
RETURNS FLOAT
STABLE
AS $$
SELECT price FROM sales where id = $1
$$ LANGUAGE SQL;

Output Code:¶

Snowflake¶

 CREATE OR REPLACE FUNCTION get_sale (SC_ARG1 INTEGER)
RETURNS FLOAT
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
SELECT price FROM
sales
where id = SC_ARG1
$$
;

Python Language¶

Within the SnowConvert AI scope, the Python language for CREATE FUNCTION statements is not supported. Consequently, the language plpythonu will be flagged with an EWI (SSC-EWI-0073), and its body could appear with parsing errors.

Input Code:¶

Redshift¶

 create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

Output Code:¶

Snowflake¶

 create function f_py_greater (a float, b float)
returns float
language plpythonu !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'LANGUAGE PLPythonU' NODE ***/!!!
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
as $$
-- ** SSC-EWI-0001 - UNRECOGNIZED TOKEN ON LINE '5' COLUMN '3' OF THE SOURCE CODE STARTING AT 'if'. EXPECTED 'STATEMENT' GRAMMAR. LAST MATCHING TOKEN WAS 'if' ON LINE '5' COLUMN '3'. **
--  if a > b:
--    return a
--  return b
$$
;

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. See the Redshift CREATE VIEW specification for this syntax.

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

Sample Source Patterns¶

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

Input Code:¶

Redshift¶

 CREATE VIEW myuser
AS
SELECT lastname FROM users;


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

Output Code:¶

Snowflake¶

 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;

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

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).

Note

:class: tip 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 ]

Sample Source Patterns¶

Setup data¶

Redshift¶

 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');

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

Redshift¶

 DELETE employees;

SELECT * FROM employees ORDER BY id;

Result¶

ID	NAME	DEPARTMENT	MANAGER_ID

Output Code:¶

Snowflake¶

 DELETE FROM
    employees;

SELECT * FROM employees ORDER BY id;

Result¶

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

Redshift¶

 DELETE FROM employees
WHERE department = 'Marketing';

SELECT * FROM employees
ORDER BY id;

Result¶

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

Snowflake¶

 DELETE FROM
    employees
WHERE department = 'Marketing';

SELECT * FROM
    employees
ORDER BY id;

Result¶

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

Redshift¶

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

SELECT * FROM employees ORDER BY id;

Result¶

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

Snowflake¶

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

SELECT * FROM employees ORDER BY id;

Result¶

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

Redshift¶

 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;

Result¶

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

Output Code:¶

Snowflake¶

 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;

Result¶

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

Recursive CTE¶

Input Code:¶

Redshift¶

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

Result¶

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

Output Code:¶

Snowflake¶

 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;

Result¶

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

Redshift¶

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

DELETE FROM emp_mv
WHERE id = 2;

Output Code:¶

Snowflake¶

 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 IN SNOWFLAKE. ***/!!!
DELETE FROM
emp_mv
WHERE id = 2;

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.

SSC-FDM-0031: Dynamic Table required parameters set by default.
SSC-EWI-RS0008: Materialized view is transformed into a dynamic table, and the DELETE statement cannot be used on dynamic tables in Snowflake.

EXECUTE¶

Description¶

The EXECUTE IMMEDIATE statement builds and runs a dynamic SQL statement in a single operation.

Native dynamic SQL uses the EXECUTE IMMEDIATE statement to process most dynamic SQL statements. (Redshift Language Reference EXECUTE Statement)

Grammar Syntax¶

 EXECUTE command-string [ INTO target ];

Sample Source Patterns¶

Concated Example

Input Code

Redshift¶

 CREATE OR REPLACE PROCEDURE create_dynamic_table(table_name VARCHAR)
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'CREATE TABLE IF NOT EXISTS ' || table_name || ' (id INT, value VARCHAR);';
EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;

Output Code

Snowflake¶

 CREATE OR REPLACE PROCEDURE create_dynamic_table (table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'CREATE TABLE IF NOT EXISTS ' || table_name || ' (id INT, value VARCHAR)';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE sql_statement;
END;
$$;

Function Transformation¶

Input Code¶

Redshift¶

 CREATE OR REPLACE PROCEDURE insert_with_dynamic()
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'insert into orders(order_date) values ("getdate"());';
EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;

Output Code¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE insert_with_dynamic ()
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'insert into orders (order_date) values (GETDATE())';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
EXECUTE IMMEDIATE sql_statement;
END;
$$;

Error In Query Parsing¶

Input Code¶

Redshift¶

 CREATE OR REPLACE PROCEDURE bad_statement(table_name VARCHAR)
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'bad statement goes here';
EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;

Output Code¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE bad_statement (table_name VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
DECLARE
sql_statement VARCHAR;
BEGIN
sql_statement := 'bad statement goes here';
!!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVE EWI!!! /*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!
EXECUTE IMMEDIATE sql_statement;
END;
$$;

INTO Clause¶

Input Code¶

Redshift¶

 CREATE OR REPLACE PROCEDURE get_max_id(table_name VARCHAR, OUT max_id INTEGER)
AS $$
DECLARE
    sql_statement VARCHAR;
BEGIN
    sql_statement := 'SELECT MAX(id) FROM ' || table_name || ';';
    EXECUTE sql_statement INTO max_id;
END;
$$ LANGUAGE plpgsql;

Output Code¶

Snowflake¶

 CREATE OR REPLACE PROCEDURE get_max_id (table_name VARCHAR, max_id OUT INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "redshift",  "convertedOn": "07/11/2025",  "domain": "no-domain-provided" }}'
AS $$
        DECLARE
            sql_statement VARCHAR;
BEGIN
    sql_statement := 'SELECT
   MAX(id) FROM
   ' || table_name;
            !!!RESOLVE EWI!!! /*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!
            EXECUTE IMMEDIATE sql_statement
                                            !!!RESOLVE EWI!!! /*** SSC-EWI-PG0007 - INTO CLAUSE IN DYNAMIC SQL IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! INTO max_id;
END;
$$;

Known Issues¶

1. Execution results cannot be stored in variables.¶

SnowScripting does not support INTO nor BULK COLLECT INTO clauses. For this reason, results will need to be passed through other means.

2. Dynamic SQL Execution queries may be marked incorrectly as non-runnable.¶

In some scenarios there an execute statement may be commented regardless of being safe or non-safe to run so please take this into account:

SSC-EWI-0027: Variable with invalid query.
SSC-EWI-0030: The statement below has usages of dynamic SQL.

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 }

Sample Source Patterns¶

Setup data¶

Redshift¶

 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');

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

Redshift¶

 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;

Result¶

ID	NAME	SALARY	DEPARTMENT
1	NULL	20000	Marketing

Output Code:¶

Snowflake¶

 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;

Result¶

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

Redshift¶

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

Result¶

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

Output Code:¶

Snowflake¶

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

Result¶

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.

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 ]

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

Redshift¶

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

Output Code:¶

Snowflake¶

 --** SSC-FDM-RS0005 - REDSHIFT MERGE STATEMENT REJECTS DUPLICATE SOURCE ROWS. SNOWFLAKE ALLOWS DUPLICATES, WHICH MAY PRODUCE NON-DETERMINISTIC RESULTS. **
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);

DELETE - INSERT¶

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

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

Output Code:¶

Snowflake¶

 --** SSC-FDM-RS0005 - REDSHIFT MERGE STATEMENT REJECTS DUPLICATE SOURCE ROWS. SNOWFLAKE ALLOWS DUPLICATES, WHICH MAY PRODUCE NON-DETERMINISTIC RESULTS. **
MERGE INTO target USING source ON target.id = source.id
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name);

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

Redshift¶

 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;

Results¶

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

Output Code:¶

Snowflake¶

 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 REJECTS DUPLICATE SOURCE ROWS. SNOWFLAKE ALLOWS DUPLICATES, WHICH MAY PRODUCE NON-DETERMINISTIC RESULTS. **
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;

Results¶

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

Known Issues¶

There are no known issues.

SSC-EWI-RS0009: Semantic information not found for the source table.
SSC-FDM-RS0005: Redshift MERGE rejects duplicate source rows. Snowflake allows them, which may produce different results.

UPDATE¶

Description¶

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

Note

:class: tip 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 ]

Sample Source Patterns¶

Setup data¶

Redshift¶

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

Alias¶

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

Input Code:¶

Redshift¶

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

Result¶

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

Snowflake¶

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

Result¶

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

Redshift¶

 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;

Result¶

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

Snowflake¶

 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;

Result¶

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

Redshift¶

 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;

Result¶

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

Snowflake¶

 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;

Result¶

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

Redshift¶

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

Result¶

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

Snowflake¶

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

Result¶

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

Redshift¶

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

Input Code:¶

Redshift¶

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


SELECT * FROM target;

Result¶

K	V
0	16

Output Code:¶

Snowflake¶

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


SELECT * FROM target;

Result¶

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. See the Snowflake UPDATE examples 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.

There are no known issues.

SnowConvert AI - Redshift - SQL Statements¶

CALL¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Base scenario¶

Input Code:¶

Redshift¶

Output Code:¶

Redshift¶

Call using Output Parameters Mode (INOUT, OUT)¶

Input Code:¶

Redshift¶

Output Code:¶

Redshift¶

Known Issues¶

Related EWIs.¶

CREATE DATABASE¶

Grammar Syntax¶

Sample Source Patterns¶

Basic samples¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

Collate Clause¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

Connection Limit Clause¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

From ARN Clause¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

From Datashare Clause¶

Input Code¶

Redshift¶

Output Code¶

Snowflake¶

Owner Clause¶

Input Code¶

Redshift¶

Output Code¶

Snowflake¶

Isolation Level Clause¶

Input Code¶

Redshift¶

Output Code¶

Snowflake¶

Related EWIs¶

CREATE EXTERNAL TABLE¶

Description ¶

Grammar Syntax ¶

Sample Source Patterns¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

Create External Table AS¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

Recommendations¶

Related EWIs¶

CREATE MATERIALIZED VIEW¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Input Code:¶

Redshift¶

Output Code:¶

Snowflake¶

Related Ewis¶