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 ] [, ...] )
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);
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());
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();
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();
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>' } }
For more information please refer to Redshift CREATE DATABASE
documentation.
Sample Source Patterns¶
Basic samples¶
Input Code:¶
CREATE DATABASE database_name;
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" }}';
Collate Clause¶
Input Code:¶
CREATE DATABASE database_collate
COLLATE CASE_INSENSITIVE;
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" }}';
Connection Limit Clause¶
Input Code:¶
CREATE DATABASE database_connection
CONNECTION LIMIT UNLIMITED;
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" }}';
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';
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" }}';
Warning
This clause is removed since it is used to reference Amazon Resources, not valid in Snowflake.
Owner Clause¶
Input Code¶
CREATE DATABASE database_Owner
OWNER db_owner
ENCODING 'encoding';
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" }}';
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;
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" }}';
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 }
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/';
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" }}'
;
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;
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;
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
Sample Source Patterns¶
Input Code:¶
CREATE MATERIALIZED VIEW mv_baseball AS
SELECT ball AS baseball FROM baseball_table;
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;
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 [ ... ] ]
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;
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;
Quota Clause¶
Input Code:¶
CREATE SCHEMA s1 QUOTA UNLIMITED;
CREATE SCHEMA s2 QUOTA 10 TB;
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" }}'
;
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.
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 ]
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;
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;
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 ]
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');
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;
ID |
NAME |
DEPARTMENT |
MANAGER_ID |
---|---|---|---|
Output Code:
DELETE FROM
employees;
SELECT * FROM employees ORDER BY id;
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;
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;
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;
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;
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;
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;
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);
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;
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;
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;
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 }
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');
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;
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;
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;
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;
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 ]
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);
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);
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);
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);
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;
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;
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 ]
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);
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;
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;
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;
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;
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;
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;
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';
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';
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);
Input Code:
UPDATE target
SET v = src.v
FROM src
WHERE target.k = src.k;
SELECT * FROM target;
K |
V |
---|---|
0 |
16 |
Output Code:
UPDATE target
SET v = src.v
FROM src
WHERE target.k = src.k;
SELECT * FROM target;
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 [, ...] ) ]
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 }
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;
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;
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;
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;
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 [, ... ] )
Sample Source Patterns
Input Code:
CREATE TABLE table1
(
col1, col2, col3
)
AS SELECT col1, col2, col3 FROM table_test;
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;
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 }
Sample Source Patterns
Input Code:
CREATE LOCAL TEMP TABLE table1
AS SELECT FROM table_test;
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;
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 )
Sample Source Patterns
Input Code:
CREATE TABLE table1
DISTKEY (col1)
AS SELECT * FROM table_test;
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;
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 }
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;
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;
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 [, ...] )
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;
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;
Related EWIs
SSC-FDM-RS0002: The performance of the CLUSTER BY may vary compared to the performance of Sortkey.