SnowConvert: Teradata SQL Translation Guide for Snowflake

This page provides a guide to translating Teradata SQL statements to Snowflake, leveraging SnowConvert’s capabilities. It covers a variety of functions, data types, and SQL syntax elements, highlighting key differences and translation strategies. This guide is intended to supplement the official SnowConvert documentation and provide a high-level overview. Refer to the official documentation for the most detailed and up-to-date information.

Data Type Mapping:

Teradata and Snowflake have similar but not identical data types. SnowConvert handles the mapping automatically in most cases, but understanding the underlying conversions is beneficial. Common mappings include:

Data Type Mapping:

Teradata Data Type

Snowflake Equivalent

Notes

BYTEINT

SMALLINT

SMALLINT

SMALLINT

INTEGER

INTEGER

BIGINT

BIGINT

DECIMAL(p,s)

NUMBER(p,s)

Precision and scale should be reviewed.

FLOAT

FLOAT / DOUBLE

May have platform-specific nuances.

REAL

FLOAT

CHAR(n)

CHAR(n)

Character set differences should be considered.

VARCHAR(n)

VARCHAR(n)

Character set differences should be considered.

CLOB

VARCHAR(16777216) / TEXT

Requires careful handling and may involve different storage mechanisms.

BLOB

VARBINARY

Requires careful handling and may involve different storage mechanisms.

DATE

DATE

TIME

TIME

TIMESTAMP

TIMESTAMP_NTZ / TIMESTAMP_TZ

Time zone considerations should be reviewed.

INTERVAL

INTERVAL

SQL Syntax and Functions:

SnowConvert automates the translation of many SQL constructs. However, some areas require special attention:

  • DDL Statements: CREATE TABLE, ALTER TABLE, DROP TABLE are generally translated directly. Review constraints and indexes for compatibility.

  • DML Statements: SELECT, INSERT, UPDATE, DELETE are mostly compatible. Pay attention to specific function usage within these statements.

  • Aggregate Functions: SUM, AVG, COUNT, MIN, MAX are typically translated seamlessly.

  • String Functions: While many string functions have equivalents (e.g., SUBSTRING, UPPER, LOWER), some may have different names or behavior.

  • Date/Time Functions: Similar to string functions, review date and time function equivalents for any discrepancies.

  • Analytic Functions: Window functions (e.g., ROW_NUMBER, RANK, LAG, LEAD) are generally supported in both platforms, but specific syntax or behavior may differ.

  • Stored Procedures and UDFs: Teradata stored procedures and user-defined functions (UDFs) require conversion. SnowConvert can assist with this process, but manual review and adjustments are often necessary.

  • Macros: Teradata macros will need to be rewritten as Snowflake stored procedures or UDFs.

  • Conditional Logic: CASE statements are generally compatible.

  • Joins: Inner, outer, and cross joins are usually translated without issues.

  • Transactions: Snowflake’s transaction model might differ from Teradata’s, so review transaction logic.

  • Specific Teradata Features: Features unique to Teradata (e.g., specific table partitioning schemes, and multi-value compressions) may not have direct equivalents in Snowflake and require alternative approaches.

Language: English