SnowConvert for Redshift: A Translation Specification

SnowConvert for Redshift is currently in preview, offering assessment and translation capabilities for TABLES and VIEWS. While SnowConvert can recognize other statement types, full support is still under development.

This document outlines the translation specification for migrating Redshift SQL to Snowflake using SnowConvert, detailing how various elements are transformed and providing examples for clarity.

Variable Declaration

  • Redshift’s CONSTANT keyword, preventing variable reassignment, is removed during translation as Snowflake does not support it.

  • The NOT NULL constraint is also removed, but the default value is retained to maintain functionality.

Transactions

  • TRUNCATE, ROLLBACK, and COMMIT statements are handled with specific considerations for Redshift’s behavior and potential issues in Snowflake.

  • Nested procedure calls with COMMIT or ROLLBACK may result in functional differences due to Snowflake’s transaction model.

Cursors

  • DECLARE CURSOR, OPEN CURSOR, FETCH CURSOR, and CLOSE CURSOR are fully supported in Snowflake.

Data Types

  • Most basic SQL data types are supported with some restrictions.

  • Numeric, character, binary, date/time, and other data types are mapped with detailed notes on potential issues.

  • INTERVAL data types are currently transformed to VARCHAR due to limitations in Snowflake.

Numeric Format Models

  • Various numeric format models are supported with equivalents in Snowflake.

  • Some formats like CC, PR, RN, and TH do not have direct equivalents and may require manual adjustments.

SQL Statements and Functions

  • CREATE MATERIALIZED VIEW: Transformed into Snowflake Dynamic Tables with specific parameters.

  • CREATE EXTERNAL TABLE: Currently converted to regular tables, requiring data transfer.

  • CREATE VIEW: Generally translated directly, but the WITH NO SCHEMA BINDING clause may present issues.

  • CREATE DATABASE: Various clauses are handled with specific considerations for Snowflake’s architecture.

  • Other statements like SELECT INTO, WITH, HAVING, WHERE, TOP, JOIN, GROUP BY, DISTINCT, UNION, INTERSECT, EXCEPT, and various DDL and DML statements are generally supported with potential nuances.

  • Mathematical and string functions are mostly translated directly, with some exceptions and potential behavioral differences.

  • Window functions like RANK, DENSE_RANK, and ROW_NUMBER are fully supported.

  • System functions like LOWER, SUBSTRING, REPLACE, SPLIT_PART, and others are generally translated with notes on potential issues.

  • IF: SnowConvert will add the parenthesis in the conditions and change the keyword ELSIF by ELSEIF since Redshift does not require the parenthesis in the conditions and ELSIF is the keyword.

  • SELECT INTO:Redshift also allow to SELECT INTO variables when the statement is executed inside stored procedures, Snowflake doesn’t support this grammar for SELECT INTO, the expressions are moved to the left of the INTO.

  • Declare Refcursor: Since Snowflake does not support the REFCURSOR data type, its functionality is replicated by converting the REFCURSOR variable into a RESULTSET type.

  • RAISE:In Snowflake, this functionality can be emulated using a user-defined function (UDF) that makes a call to the console depending on the specified level.

  • Open Cursor: Cursor arguments have to be binded per each one of its uses, SnowConvert will generate the bindings, was well as reorder and repeat the passed values to the OPEN statement as needed to satisfy the bindings.

  • RETURN:The conversion of the return statement from Amazon Redshift to Snowflake is straightforward, only considering adding a NULL to the return statement on Snowflake.

Key Considerations

  • This document is a work in progress, and full support for all Redshift elements is still under development.

  • Thoroughly test converted code to ensure accuracy and address any functional differences.

  • Consider performance implications and optimize queries after conversion.

This translation specification provides a comprehensive overview of how SnowConvert handles the migration of Redshift SQL to Snowflake. Understanding these transformations is crucial for a successful migration and can help you anticipate and address potential issues during the process.

Language: English