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).
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.
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.
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.
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.
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.
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.
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.
This command defines a user-defined function (UDF) within the database. These functions encapsulate reusable logic that can be invoked within SQL queries.
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.
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.
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.
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.
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.
This clause introduces a list of tables when additional tables are referenced in the WHERE clause condition. This clause is fully equivalent in Snowflake.
DELETEFROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name ='Sales';SELECT*FROM employees ORDERBY id;
DELETEFROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name ='Sales';SELECT*FROM employees ORDERBY id;
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.
WITH sales_employees AS(SELECT id
FROM employees
WHERE department ='Sales'), engineering_employees AS(SELECT id
FROM employees
WHERE department ='Engineering')DELETEFROM employees
WHERE id IN(SELECT id FROM sales_employees)OR id IN(SELECT id FROM engineering_employees);SELECT*FROM employees ORDERBY id;
CREATETEMPORARYTABLE sales_employees ASSELECT id
FROM employees
WHERE department ='Sales';CREATETEMPORARYTABLE engineering_employees ASSELECT id
FROM employees
WHERE department ='Engineering';DELETEFROM
employees
WHERE id IN(SELECT id FROM sales_employees)OR id IN(SELECT id FROM engineering_employees);DROPTABLE sales_employees;DROPTABLE engineering_employees;SELECT*FROM
employees
ORDERBY id;
WITHRECURSIVE subordinate_hierarchy(id,name, department, level)AS(SELECT id,name, department,0as level
FROM employees
WHERE department ='Marketing'UNIONALLSELECT e.id, e.name, e.department, sh.level +1FROM employees e
INNERJOIN subordinate_hierarchy sh ON e.manager_id = sh.id
)DELETEFROM employees
WHERE id IN(SELECT id FROM subordinate_hierarchy);
CREATETEMPORARYTABLE subordinate_hierarchy ASWITHRECURSIVE subordinate_hierarchy(id,name, department, level)AS(SELECT id,name, department,0as level
FROM
employees
WHERE department ='Marketing'UNIONALLSELECT e.id, e.name, e.department, sh.level +1FROM
employees e
INNERJOIN
subordinate_hierarchy sh ON e.manager_id = sh.id
)SELECT
id,name,
department,
level
FROM
subordinate_hierarchy;DELETEFROM
employees
WHERE id IN(SELECT id FROM
subordinate_hierarchy
);DROPTABLE subordinate_hierarchy;
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.
CREATEDYNAMICTABLE 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" }}'ASSELECT 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. ***/!!!DELETEFROM
emp_mv
WHERE id =2;
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.
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.
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.
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.
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.
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.
WITH avg_salary_cte AS(SELECTAVG(salary)AS avg_salary FROM employees
)UPDATE employees
SET salary =(SELECT avg_salary FROM avg_salary_cte)WHERE salary <500000;
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.
CREATETABLE target (
k INT,
v INT);CREATETABLE src (
k INT,
v INT);INSERTINTO target (k, v)VALUES(0,10);INSERTINTO src (k, v)VALUES(0,14),(0,15),(0,16);
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.