SnowConvert Functional Differences¶

SnowConvert is a powerful tool designed to assist in migrating your database code to Snowflake SQL, with support for various SQL dialects such as PostgreSQL, Teradata, SQL Server, and Oracle. However, due to the inherent differences between these dialects and Snowflake SQL, there might be instances where the converted code might not function exactly the same way as the original code. When SnowConvert encounters such scenarios, it generates an FDM (Functional Difference Message) to highlight the potential discrepancies.

FDMs serve as indicators of areas in your code where the functionality in Snowflake SQL might deviate from the original SQL dialect. They provide detailed explanations of the functional difference, illustrate the issue with code examples, and offer recommendations for addressing the discrepancy.

This document provides a comprehensive list of all FDMs identified when using SnowConvert, categorized by the SQL dialect they pertain to. Understanding these functional differences can help you anticipate and address potential challenges during the migration process, ensuring a smoother and more successful transition to Snowflake SQL.

Teradata FDMs¶

  • SSC-FDM-TD0001 (Low): The data type BLOB is not supported in Snowflake and is changed to BINARY.

  • SSC-FDM-TD0002 (Low): The data type CLOB is not supported in Snowflake and is changed to VARCHAR.

  • SSC-FDM-TD0003 (Low): Bash variables found in the source code are transformed into SnowSQL variables, requiring the use of SnowSQL with variable substitution enabled to run the script.

  • SSC-FDM-TD0004 (Low): The PERIOD data type is not supported in Snowflake and is handled as two data fields.

  • SSC-FDM-TD0005 (Low): Non-standard time zone offsets are not supported in Snowflake and are rounded to the nearest valid time zone.

  • SSC-FDM-TD0006 (Low): The WITH CHECK OPTION clause in views is not supported in Snowflake and is commented out.

  • SSC-FDM-TD0010 (Low): The Teradata table DBC.COLUMNSV is converted to INFORMATION_SCHEMA.COLUMNS in Snowflake, but some columns might not have an exact match.

  • SSC-FDM-TD0011 (Low): Unicode BMP is not supported in Snowflake.

  • SSC-FDM-TD0012 (Low): The DEFAULT TIME, DEFAULT DATE, DEFAULT CURRENT_DATE, DEFAULT CURRENT_TIME, and DEFAULT CURRENT_TIMESTAMP column specifications are not supported for the FLOAT data type in Snowflake.

  • SSC-FDM-TD0013 (Low): The Snowflake error code might not match the original Teradata error code.

  • SSC-FDM-TD0014 (Low): The execution of a file with SQL statements stops when an error occurs in Snowflake, unlike in Teradata where it continues.

  • SSC-FDM-TD0015 (Low): The REGEXP_SUBSTR function in Snowflake only supports POSIX regular expressions.

  • SSC-FDM-TD0016 (Low): The value ‘l’ for the parameter ‘match_arg’ in REGEXP_SUBSTR is not supported in Snowflake.

  • SSC-FDM-TD0020 (Low): Unrecognized JSON literals in Snowflake.

  • SSC-FDM-TD0021 (Low): Queries referencing the DBC.DATABASES table in Teradata might have columns that have no equivalence in Snowflake.

  • SSC-FDM-TD0022 (Low): Shell variables found in the source code, requiring the converted code to be run in a shell script.

  • SSC-FDM-TD0024 (Low): The SET TABLE functionality is not supported in Snowflake.

  • SSC-FDM-TD0025 (Low): Temporal forms are not supported in Snowflake.

  • SSC-FDM-TD0026 (Low): GOTO statements are removed due to if statement inversion.

  • SSC-FDM-TD0027 (Low): The TD_UNPIVOT transformation requires column information that could not be found, resulting in missing columns.

  • SSC-FDM-TD0030 (Low): A return statement was added at the end of the label section to ensure the same execution flow.

  • SSC-FDM-TD0031 (Low): The ST_DISTANCE results in Snowflake are slightly different from ST_SPHERICALDISTANCE in Teradata.

  • SSC-FDM-TD0032 (Low): The NOT CASESPECIFIC clause in LIKE expressions was removed.

  • SSC-FDM-TD0033 (Low): The ACTIVITY_COUNT status variable in Teradata is emulated in Snowflake with some limitations.

SQL Server FDMs¶

  • SSC-FDM-TS0001 (Low): The xp_cmdshell stored procedure is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0002 (Low): The GOTO statement is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0003 (Low): The xp_logininfo stored procedure is mapped to a custom UDF in Snowflake.

  • SSC-FDM-TS0004 (Low): The SET ANSI_NULLS statement is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0005 (Low): The SET QUOTED_IDENTIFIER statement is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0006 (Low): The EXECUTE AS clause in stored procedures is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0007 (Low): The FOR REPLICATION clause in stored procedures is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0008 (Low): The sp_rename stored procedure is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0009 (Low): The ENCRYPTED WITH clause in column definitions is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0010 (Low): The TEXTIMAGE_ON clause in table definitions is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0011 (Low): The ROWGUIDCOL property in column definitions is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0012 (Low): The IDENTITY property in column definitions is handled differently in Snowflake, and the functional equivalence needs verification.

  • SSC-FDM-TS0013 (Low): Snowflake Scripting cursors are not modifiable, unlike SQL Server cursors.

  • SSC-FDM-TS0015 (Low): The @@ROWCOUNT variable is mapped to ROW_COUNT in Snowflake, but the behavior might differ in certain scenarios.

  • SSC-FDM-TS0016 (Low): The @@NESTLEVEL variable is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0017 (Low): The @@ERROR variable is mapped to SQLCODE in Snowflake, but the behavior might differ in certain scenarios.

  • SSC-FDM-TS0018 (Low): The DBCC statements are not supported in Snowflake and are replaced with comments.

  • SSC-FDM-TS0019 (Low): The OPENROWSET function is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0020 (Low): The OPENDATASOURCE function is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0021 (Low): The MASKED WITH clause in column definitions is replaced with a MASKING POLICY in Snowflake, but the behavior might differ.

  • SSC-FDM-TS0022 (Low): The COMPRESSION clause in table definitions is not supported in Snowflake and is replaced with a comment.

  • SSC-FDM-TS0023 (Low): The OUTPUT clause in INSERT, UPDATE, and DELETE statements is handled differently in Snowflake, and the functional equivalence needs verification.

Oracle FDMs¶

  • SSC-FDM-OR0001 (Low): The NLS_NUMERIC_CHARACTERS session parameter is not supported in Snowflake. The decimal separator in Snowflake is always the dot character.

  • SSC-FDM-OR0002 (Low): The NLS_TERRITORY session parameter is not supported in Snowflake.

  • SSC-FDM-OR0003 (Low): The NLS_LANGUAGE session parameter is not supported in Snowflake.

  • SSC-FDM-OR0004 (Low): The ORDER BY SIBLINGS clause is not supported in Snowflake.

  • SSC-FDM-OR0005 (Low): Synonyms are not supported in Snowflake, but references to the synonym were changed to the original object name.

  • SSC-FDM-OR0006 (Low): The OCTET_LENGTH function might have different results in Snowflake compared to Oracle when used with NVARCHAR2 or NCHAR data types.

  • SSC-FDM-OR0007 (Low): Snowflake does not support versioning of objects. Developers should consider alternate approaches for code versioning.

  • SSC-FDM-OR0008 (Low): The ON COMMIT clause for global temporary tables is not supported in Snowflake.

  • SSC-FDM-OR0009 (Low): SQL implicit cursor values may differ between Oracle and Snowflake.

  • SSC-FDM-OR0010 (Low): The RANGE clause with physical offsets in analytic functions may produce non-deterministic results in Snowflake.

  • SSC-FDM-OR0011 (Low): The boolean argument for RAISE_APPLICATION_ERROR (add to stack option) is not supported in Snowflake.

  • SSC-FDM-OR0012 (Low): The COMMIT and ROLLBACK statements require appropriate setup to work as intended in Snowflake.

  • SSC-FDM-OR0013 (Low): The CYCLE clause in hierarchical queries (CONNECT BY) is not supported in Snowflake.

  • SSC-FDM-OR0014 (Low): Foreign key data type mismatch.

  • SSC-FDM-OR0015 (Low): Referenced custom type is not supported in Snowflake.

  • SSC-FDM-OR0016 (Low): The TYPE function is not supported in Snowflake.

  • SSC-FDM-OR0017 (Low): The AUTHID CURRENT_USER clause in CREATE FUNCTION is not supported in Snowflake.

  • SSC-FDM-OR0018 (Low): The Snowflake MERGE statement may have some functional differences compared to Oracle.

  • SSC-FDM-OR0019 (Low): The COMPILE clause in ALTER statements is not supported in Snowflake.

  • SSC-FDM-OR0020 (Low): The PRAGMA EXCEPTION_INIT statement is not supported in Snowflake.

  • SSC-FDM-OR0021 (Low): The RESTRICT_REFERENCES pragma is not supported in Snowflake.

  • SSC-FDM-OR0022 (Low): Oracle allows multiple conditions in a single FOR LOOP, but Snowflake Scripting only allows one condition per FOR LOOP.

  • SSC-FDM-OR0023 (Low): Exception code number exceeds Snowflake Scripting limits.

  • SSC-FDM-OR0024 (Low): The DEFAULT clause in CREATE SEQUENCE is not supported in Snowflake.

  • SSC-FDM-OR0025 (Low): The CREATE FORCE VIEW statement is not supported in Snowflake.

  • SSC-FDM-OR0026 (Low): The REPLACE clause in CREATE VIEW is not supported in Snowflake.

  • SSC-FDM-OR0027 (Low): The CREATE OR REPLACE PACKAGE statement is not supported in Snowflake.

  • SSC-FDM-OR0028 (Low): Subquery factoring with ANALYTIC VIEW is not supported in Snowflake.

  • SSC-FDM-OR0029 (Low): The DEFAULT ON CONVERSION ERROR clause is not supported in Snowflake.

  • SSC-FDM-OR0030 (Low): Identifiers with invalid characters were replaced with their UTF-8 codes.

  • SSC-FDM-OR0031 (Low): The INDEXTYPE IS ISAM clause is not supported in Snowflake.

  • SSC-FDM-OR0032 (Low): The USING INDEX clause is not supported in Snowflake.

  • SSC-FDM-OR0033 (Low): The USING INDEX TABLESPACE clause is not supported in Snowflake.

  • SSC-FDM-OR0034 (Low): The DISABLE and ENABLE clauses in ALTER INDEX are not supported in Snowflake.

  • SSC-FDM-OR0035 (Low): The VISIBLE and INVISIBLE clauses in ALTER INDEX are not supported in Snowflake.

  • SSC-FDM-OR0036 (Low): The PARALLEL clause in CREATE INDEX is not supported in Snowflake.

  • SSC-FDM-OR0037 (Low): The CONTAINERS, SUBQUERY RESTRICTION, HIERARCHIES, EXTERNAL MODIFY, DBLINK, SHARDS, PARTITION, SUBPARTITION, and HIERARCHICAL clauses in SELECT statements are not supported in Snowflake.

  • SSC-FDM-OR0038 (Low): The VPD POLICY clause is not supported in Snowflake.

  • SSC-FDM-OR0039 (Low): The REF function is not supported in Snowflake.

  • SSC-FDM-OR0040 (Low): The DEREF function is not supported in Snowflake.

  • SSC-FDM-OR0041 (Low): The MAKE_REF function is not supported in Snowflake.

  • SSC-FDM-OR0042 (Low): The VALUE function is not supported in Snowflake.

  • SSC-FDM-OR0043 (Low): The TREAT function is not supported in Snowflake.

  • SSC-FDM-OR0044 (Low): The REGEXP_LIKE_UDF match parameter may not behave correctly in Snowflake.

  • SSC-FDM-OR0045 (Low): The FOR UPDATE clause is not supported in Snowflake.

  • SSC-FDM-OR0046 (Low): The SKIP LOCKED clause is not supported in Snowflake.

  • SSC-FDM-OR0047 (Low): The NOWAIT clause is not supported in Snowflake.

  • SSC-FDM-OR0048 (Low): The WAIT clause is not supported in Snowflake.

  • SSC-FDM-OR0049 (Low): The OF clause in FOR UPDATE is not supported in Snowflake.

  • SSC-FDM-OR0050 (Low): The ROW SHARE clause in LOCK TABLE is not supported in Snowflake.

  • SSC-FDM-OR0051 (Low): The ROW EXCLUSIVE clause in LOCK TABLE is not supported in Snowflake.

  • SSC-FDM-OR0052 (Low): The SHARE ROW EXCLUSIVE clause in LOCK TABLE is not supported in Snowflake.

  • SSC-FDM-OR0053 (Low): The EXCLUSIVE clause in LOCK TABLE is not supported in Snowflake.

  • SSC-FDM-OR0054 (Low): The SHARE UPDATE clause in LOCK TABLE is not supported in Snowflake.

  • SSC-FDM-OR0055 (Low): The SHARE clause in LOCK TABLE is not supported in Snowflake.

Redshift FDMs¶

  • SSC-FDM-RS0001 (Low): The SORTKEY AUTO option is not supported in Snowflake. Data storage is automatically handled by Snowflake.

  • SSC-FDM-RS0002 (Low): The performance of CLUSTER BY in Snowflake may vary compared to the performance of SORTKEY in Redshift.

  • SSC-FDM-RS0003 (Low): The WITH NO SCHEMA BINDING clause in CREATE VIEW is not supported in Snowflake.

  • SSC-FDM-RS0004 (Low): Invalid dates will cause errors in Snowflake, whereas Redshift allows for more flexibility in date handling.

  • SSC-FDM-RS0005 (Low): Duplicates are not allowed in the source table in Snowflake, whereas Redshift allows duplicates.

  • SSC-FDM-RS0006 (Low): Called procedures containing COMMIT or ROLLBACK are not supported in Snowflake, as modifying the current transaction in child scopes is not allowed.

  • SSC-FDM-RS0007 (Low): The TRUNCATE statement in Redshift acquires a commit lock on the table, which could cause contention issues in Snowflake.

General FDMs¶

  • SSC-FDM-0001 (Low): Views selecting all columns from a single table are not required in Snowflake and may impact performance.

  • SSC-FDM-0012 (Low): The FOR XML clause is transformed to its Snowflake equivalent. It is added because columns in XML could be different.

  • SSC-FDM-0013 (Low): The IDENTITY property in column definitions is handled differently in Snowflake, and the functional equivalence needs verification.

  • SSC-FDM-0016 (Low): Constants are not supported by Snowflake Scripting. It was transformed to a variable.

  • SSC-FDM-0017 (Low): The WITH SYSTEM VERSIONING clause is not supported by Snowflake.

  • SSC-FDM-0018 (Low): The FOR SYSTEM_TIME clause is not supported by Snowflake.

  • SSC-FDM-0019 (Low): Semantic information could not be loaded.

  • SSC-FDM-0027 (Low): Removed next statement, not applicable in Snowflake.

  • SSC-FDM-0030 (Low): Identifier has invalid characters. Characters were replaced with their UTF-8 codes.

  • SSC-FDM-0031 (Low): Dynamic Table required parameters set by default.

  • SSC-FDM-0032 (Low): Unsupported Window Frame Unit was changed into Rows, leading to output differences.

Language: English