SnowConvert Extends Support to Azure Synapse and Enhances More Capabilities
At Snowflake, we are committed to simplifying any migration to the Snowflake AI Data Cloud. With SnowConvert’s proven track record of converting billions of lines of code from legacy data warehouses, with more on the horizon, we continue to expand our support for migrating data warehouse sources to Snowflake. In this blog post, we will share details about the additional support for Amazon Redshift stored procedures and the extension of T-SQL support to include Azure Synapse semantics.
Amazon Redshift-to-Snowflake migrations just got easier
Stored procedures are essential to many database applications, as they allow you to bundle complex business logic into a single unit that can be easily reused. From a migration standpoint, stored procedures present a unique challenge due to their ability to encapsulate a wide range of SQL commands. Unlike simple queries, stored procedures can contain data definition language (DDL) for creating and modifying database objects, data manipulation language (DML) for data operations, and various other control flow statements and complex logic. This rich grammar within stored procedures makes the conversion process to Snowflake more intricate, as it requires a comprehensive understanding of the source database's dialect and careful translation to Snowflake's SQL syntax and capabilities.
SnowConvert, Snowflake’s native code conversion solution for data warehouse migrations, is adding stored procedure support for Redshift migrations to Snowflake SQL. This significant development will make it even easier for customers to migrate their Redshift workloads to Snowflake.
Migration challenges between Amazon Redshift and Snowflake
Despite both being cloud-based data solutions, Redshift and Snowflake have several significant differences in their procedural languages, which, if gone unnoticed, can lead to undesired functionality gaps. In the next section, we will discuss some specific differences between Redshift and Snowflake that SnowConvert considers when migrating stored procedures and provide some tips for migrating stored procedures to Snowflake. We'll also discuss some examples.
Positional arguments and aliases
Redshift allows you to define stored procedure parameters positionally, meaning you reference them by their order instead of by name (e.g., $1, $2). Furthermore, Redshift allows for declaring aliases for arguments. Since there is no support for either of these features in Snowflake, SnowConvert generates a name for each nameless parameter and removes all redundant aliases.
Redshift
Notice only the first parameter has no name, and there are two aliases for the second parameter.
CREATE OR REPLACE PROCEDURE test_procedure (integer, PARAMETER2 integer)
LANGUAGE plpgsql
AS
$$
DECLARE
first_alias ALIAS FOR $1;
second_alias ALIAS FOR $2;
third_alias ALIAS FOR $2;
BEGIN
INSERT INTO t1
VALUES (first_alias + 1);
INSERT INTO t1
VALUES (second_alias + third_alias + 3);
END;
$$;Snowflake-generated code
CREATE OR REPLACE PROCEDURE test_procedure (SC_ARG1 integer, PARAMETER2 integer)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO t1
VALUES (:SC_ARG1 + 1);
INSERT INTO t1
VALUES (:PARAMETER2 + :PARAMETER2 + 3);
END;
$$;Transactions
In Redshift, when a stored procedure doesn't explicitly declare NONATOMIC, it operates with a default transaction behavior in which a call to a stored procedure is treated as a single SQL command, so all of its internal statements are committed atomically. Using a COMMIT or ROLLBACK in this context allows the user to perform internal transactions within the stored procedure’s scope. On the contrary, when the NONATOMIC option is used, each SQL command within the stored procedure is automatically committed.
On the other hand, Snowflake enables the session parameter AUTOCOMMIT, equivalent to having stored procedures in Redshift, using the NONATOMIC option. However, notice that this is a session-level parameter affecting all stored procedure calls within the session, while in Redshift, the option can be defined individually. When performing the translation, SnowConvert assumes that the AUTOCOMMIT parameter will be set to TRUE in Snowflake since this is the parameter's default value.
From a migration standpoint, one scenario in which it is essential to take all of this context into account is when the Redshift stored procedure doesn’t specify the NONATOMIC keyword, and internal COMMITs or ROLLBACKs are found within. This effectively creates internal implicit transactions. Since SnowConvert assumes that the AUTOCOMMIT parameter will be enabled, explicit transaction statements must be generated in the output code to replicate the original behavior.
Redshift
CREATE OR REPLACE PROCEDURE transaction_test(a INT)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO transaction_values_test VALUES (a);
INSERT INTO transaction_values_test VALUES (a+1);
COMMIT;
INSERT INTO transaction_values_test VALUES (a + 2);
INSERT INTO transaction_values_test VALUES (a + 2);
COMMIT;
END
$$;Snowflake-generated code
CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
BEGIN
BEGIN TRANSACTION;
INSERT INTO transaction_values_test
VALUES (:a);
INSERT INTO transaction_values_test
VALUES (:a+1);
COMMIT;
BEGIN TRANSACTION;
INSERT INTO transaction_values_test
VALUES (:a + 2);
INSERT INTO transaction_values_test
VALUES (:a + 2);
COMMIT;
END
$$;Without the autogenerated BEGIN TRANSACTION statements, each INSERT would have been committed individually because the AUTOCOMMIT session parameter is assumed to be enabled in Snowflake.
Complex scenario detection
In Redshift, when a COMMIT or ROLLBACK statement is specified in a nested procedure call, the command will commit or roll back all pending work from previous statements in the current and parent scopes. Modifying the current transaction from a nested scope is not supported in Snowflake. When these cases are detected, a functional difference message (FDM) will be generated.
Redshift
CREATE OR REPLACE PROCEDURE transaction_test(a INT)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO transaction_values_test VALUES (a);
COMMIT;
END
$$;
CREATE OR REPLACE PROCEDURE nested_transaction_test(a INT)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO transaction_values_test values (a);
INSERT INTO transaction_values_test values (a + 1);
INSERT INTO transaction_values_test values (a + 2);
CALL transaction_test(a + 3);
END
$$;Snowflake-generated code
CREATE OR REPLACE PROCEDURE transaction_test (a INT)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
BEGIN
BEGIN TRANSACTION;
INSERT INTO transaction_values_test
VALUES (:a);
COMMIT;
END
$$;
CREATE OR REPLACE PROCEDURE nested_transaction_test (a INT)
RETURNS VARCHAR
LANGUAGE SQL
AS $$
BEGIN
INSERT INTO transaction_values_test
values (:a);
INSERT INTO transaction_values_test
values (:a + 1);
INSERT INTO transaction_values_test
values (:a + 2);
--** SSC-FDM-RS0006 - CALLED PROCEDURE CONTAINS USAGES OF COMMIT/ROLLBACK, MODIFYING THE CURRENT TRANSACTION IN CHILD SCOPES IS NOT SUPPORTED IN SNOWFLAKE **
CALL transaction_test(:a + 3);
END
$$;WITH clause in some DMLs
As mentioned before, DML operations are often used in stored procedures. While both Redshift and Snowflake support the WITH clause, there's a slight difference in how they handle it, for example within DELETE or UPDATE statements. In Redshift, the WITH clause can be directly used within a DELETE statement to define a CTE that filters the rows to be deleted. Snowflake, while supporting CTEs, doesn't allow them to be directly referenced within the WHERE clause of a DELETE statement. This nuance requires SnowConvert to employ a workaround to help ensure functional equivalence.
SnowConvert's solution: Temporary tables
To overcome this limitation, SnowConvert employs the following strategy:
Create temporary table: SnowConvert generates a temporary table based on the CTE defined in the Redshift DELETE statement.
Delete using join: It then constructs a DELETE statement in Snowflake that joins the target table with the temporary table, effectively replicating the CTE's filtering behavior.
Drop temporary table: Finally, SnowConvert adds a command to drop the temporary table, ensuring cleanup.
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);Snowflake-generated 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;Azure Synapse support
SnowConvert has supported migrations from SQL Server T-SQL for a long time. We've now included additional elements to complete support for Azure Synapse. We've made changes in the documentation to include the specific differences between the basic SQL Server platform and Synapse.
Let's dive into some examples of how SnowConvert simplifies the transition from Azure Synapse to Snowflake.
Create table
One of the fundamental highlights is the DISTRIBUTION and PARTITION options. Snowflake's architecture is built on a shared-data, multi-cluster architecture. It separates storage and compute, automatically managing data distribution and optimization. This eliminates the need for manual distribution and partitioning definitions at the table creation level.
For that reason, SnowConvert removes these options in the transformation.
Azure Synapse
CREATE TABLE ProductSales
(
ProductID INT,
ProductName VARCHAR(255),
Category VARCHAR(100),
SaleDate DATE,
Quantity INT,
Price DECIMAL(18, 2)
)
WITH
(
DISTRIBUTION = HASH(ProductID),
PARTITION ( Category RANGE FOR VALUES (
'Electronics',
'Clothing',
'Home Goods',
'Books',
'Other'
))
);Snowflake-generated code
CREATE OR REPLACE TABLE ProductSales
(
ProductID INT,
ProductName VARCHAR(255),
Category VARCHAR(100),
SaleDate DATE,
Quantity INT,
Price DECIMAL(18, 2)
);Materialized views
A key distinction between SQL Server and the newly supported Azure Synapse is the implementation of materialized views, which are available in the latter platform. Materialized views (MVs) are specifically engineered to optimize performance for intricate analytical queries within data warehousing environments.
Azure Synapse
CREATE MATERIALIZED VIEW [SchemaY].MV_by_User2
AS
SELECT A.vendorID, sum(A.totalamount) AS S, Count_Big(*) AS T
FROM [SchemaX].[T1] A
INNER JOIN [SchemaX].[T2] B ON A.vendorID = B.vendorID GROUP BY A.vendorID ;Snowflake-generated code
CREATE OR REPLACE DYNAMIC TABLE SchemaY.MV_by_User2
--** 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": "azuresynapse", "convertedOn": "03/19/2025", "domain": "test" }}'
AS
SELECT
A.vendorID,
SUM(A.totalamount) AS S,
COUNT(*) AS T
FROM
SchemaX.T1 A
INNER JOIN
SchemaX.T2 B
ON A.vendorID = B.vendorID
GROUP BY
A.vendorID;Snowflake Dynamic Tables are an optimal alternative to complex materialized views, providing enhanced flexibility when dealing with multiple or joined tables and other patterns, detailed here.
Additional configuration needed
The migration of a materialized view to a Dynamic Table requires manual configuration. SnowConvert automatically assigns default values to the TARGET_LAG and WAREHOUSE parameters, but users are strongly advised to adjust these parameters before table deployment, specifically the option for specifying the WAREHOUSE that will keep the Dynamic Table updated. An FDM is generated to alert the user that the migration requires this additional configuration.
Conclusion
Migrating from Redshift or Synapse to Snowflake offers a wealth of benefits, from enhanced scalability and performance to reduced costs. By automating the conversion of intricate platform behaviors, SnowConvert reduces manual effort and risk of errors. Furthermore, SnowConvert goes beyond code conversion by highlighting potential differences between the platforms that may need to be addressed after the initial migration.
With SnowConvert, migrating your Redshift and Synapse workloads to Snowflake becomes streamlined and efficient. You can confidently embrace the Snowflake AI Data Cloud, knowing that your critical database logic is reliably translated and ready to power your data-driven initiatives in Snowflake's dynamic environment.
Ready to embark on your Snowflake migration journey? Explore SnowConvert and discover how it can simplify your transition. If you have any questions reach out to SnowConvert-support@snowflake.com, or visit SnowConvert documentation.
Authors
