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 (https://docs.aws.amazon.com/redshift/latest/dg/r_CALL_procedure.html)).
CREATE OR REPLACEPROCEDURE 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 REPLACEPROCEDURE call_sp_calculate_sum_product()LANGUAGE plpgsql
AS $$
DECLARE
sum_value NUMERICDEFAULTnull;
product_value NUMERICDEFAULTnull;BEGINCALL sp_calculate_sum_product(FLOOR(20.5)::NUMERIC,CEIL(20.7)::NUMERIC, sum_value, product_value);INSERTINTOtestVALUES(sum_value, product_value);END;
$$;CALL call_sp_calculate_sum_product();
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 ORDER BY id;
DELETEFROM employees
USING departments d
WHERE employees.department = d.department_name
AND d.department_name ='Sales';SELECT*FROM employees ORDER BY 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 ORDER BY 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
ORDER BY 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
INNER JOIN 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
INNER JOIN
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;
CREATEDYNAMICTABLE emp_mv
--** SSC-FDM-0031 - DYNAMIC TABLE REQUIRED PARAMETERS SET BY DEFAULT **TARGET_LAG='1 day'WAREHOUSE=UPDATE_DUMMY_WAREHOUSECOMMENT='{ "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';!!!RESOLVEEWI!!!/*** 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.
CREATE OR REPLACEPROCEDURE create_dynamic_table(table_nameVARCHAR)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;
CREATE OR REPLACEPROCEDURE bad_statement (table_nameVARCHAR)RETURNSVARCHARLANGUAGESQLCOMMENT='{ "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';!!!RESOLVEEWI!!!/*** SSC-EWI-0030 - THE STATEMENT BELOW HAS USAGES OF DYNAMIC SQL. ***/!!!!!!RESOLVEEWI!!!/*** SSC-EWI-0027 - THE FOLLOWING STATEMENT USES A VARIABLE/LITERAL WITH AN INVALID QUERY AND IT WILL NOT BE EXECUTED ***/!!!EXECUTEIMMEDIATE sql_statement;END;
$$;
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.
CREATETABLE employees (
id INTEGERIDENTITY(1,1),nameVARCHAR(100),
salary INTDEFAULT20000,
department VARCHAR(50)DEFAULT'Marketing');INSERTINTO employees
DEFAULTVALUES;SELECT*FROM employees ORDER BY id;
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.
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;
WITHRECURSIVE bonus_updates(id,name, department, salary, level)AS(SELECT e.id,
e.name,
e.department,
e.salary +CASEWHEN db.bonus IS NOT NULLTHEN db.bonus
ELSE0ENDAS new_salary,1AS level
FROM employees e
LEFT JOIN department_bonus db ON e.department = db.department
UNIONALLSELECT e.id,
e.name,
e.department,
e.salary +CASEWHEN db.bonus IS NOT NULLTHEN db.bonus
ELSE0END+(e.salary *0.05)AS new_salary,
bu.level +1FROM 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;
CREATETEMPORARYTABLE bonus_updates AS--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "employees", "department_bonus" **WITHRECURSIVE bonus_updates(id,name, department, salary, level)AS(SELECT e.id,
e.name,
e.department,
e.salary +CASEWHEN db.bonus IS NOT NULLTHEN db.bonus
ELSE0ENDAS new_salary,1AS level
FROM
employees e
LEFT JOIN
department_bonus db ON e.department = db.department
UNIONALLSELECT e.id,
e.name,
e.department,
e.salary +CASEWHEN db.bonus IS NOT NULLTHEN db.bonus
ELSE0END+(e.salary *0.05)AS new_salary,
bu.level +1FROM
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;DROPTABLE bonus_updates;
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);
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.