Tables

Translation reference to convert Teradata TABLE statement to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

Teradata's TABLE statement is translated to Snowflake TABLE syntax.

For more information on Teradata TABLEs, check here.

Sample Source Patterns

Simple Create​ Table

Teradata

Table

IN -> Teradata_01.sql

CREATE TABLE table1, no fallback,
no before journal,
no after journal (
  c1 INTEGER NOT NULL,
	f1 INTEGER NOT NULL,
	p1 INTEGER NOT NULL,
  DATE,
  TIME,
	FOREIGN KEY(f1) REFERENCES WITH CHECK OPTION table2 (d1)
)
UNIQUE PRIMARY INDEX(c1)
PARTITION BY COLUMN(p1);

Snowflake

Table

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE table1 (
	c1 INTEGER NOT NULL,
	f1 INTEGER NOT NULL,
	p1 INTEGER NOT NULL,
	DATE,
	TIME,
	FOREIGN KEY(f1) REFERENCES table2 (d1) ,
	UNIQUE (c1)
)
--** SSC-PRF-0007 - PERFORMANCE REVIEW - CLUSTER BY **
CLUSTER BY (p1)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Table Kind Clause - SET and MULTISET

Teradata's kind clause determines whether duplicate rows are permitted (MULTISET) or not (SET).

Teradata

Table

IN -> Teradata_02.sql

-- Set semantics
CREATE SET TABLE table1 (
    column1 INTEGER
);

--Multiset semantics
CREATE MULTISET TABLE table2(
    column1 INTEGER
);

Snowflake

Table

OUT -> Teradata_02.sql

-- Set semantics
--** SSC-FDM-TD0024 - SET TABLE FUNCTIONALITY NOT SUPPORTED. TABLE MIGHT HAVE DUPLICATE ROWS **
CREATE OR REPLACE TABLE table1 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

--Multiset semantics
CREATE OR REPLACE TABLE table2 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Volatile and Global Temporary Tables

Teradata's Volatile and Global Temporary tables are used for the temporary storage of data. Their difference lies in that the table definition (DDL) of Global Temporary tables is persisted in the Data Dictionary, while Volatile tables definition is not stored.

Teradata

Table

IN -> Teradata_03.sql

--Global Temporary Table
CREATE MULTISET GLOBAL TEMPORARY TABLE table1 (
    column1 INTEGER
);

--Volatile Table
CREATE MULTISET VOLATILE TABLE table3 (
    column1 INTEGER
);

Snowflake

Table

OUT -> Teradata_03.sql

--Global Temporary Table
--** SSC-FDM-0009 - GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED. **
CREATE OR REPLACE TABLE table1 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

--Volatile Table
CREATE OR REPLACE TEMPORARY TABLE table3 (
    column1 INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

With data and with no data option

Teradata

Table

IN -> Teradata_04.sql

-- With data
CREATE TABLE table1 AS table2 WITH DATA

-- With no data
CREATE TABLE table1 AS table2 WITH NO DATA 

Snowflake

Table

OUT -> Teradata_04.sql

-- With data
CREATE OR REPLACE TABLE table1 CLONE table2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

-- With no data
--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR table1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **
CREATE OR REPLACE TABLE table1 LIKE table2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Snowflake's Reserved & Limited Keywords

SnowConvert facilitates seamless SQL migrations to Snowflake by addressing challenges associated with reserved keywords. As per Snowflake's reserved and limited keyword documentation, certain keywords cannot be used as column names, table names, or aliases without special handling. SnowConvert includes functionality to ensure SQL code compatibility in such cases.

Reserved ANSI Keywords as Column Names

For column names that match ANSI or Snowflake reserved keywords, SnowConvert automatically wraps the column name in double quotes (") to comply with Snowflake's syntax rules. This adjustment ensures that queries with these column names compile correctly in Snowflake without requiring manual intervention.

Example:

Table

IN -> Teradata_05.sql

CREATE TABLE ReservedKeywords (
  "CREATE" VARCHAR(50),
  FOLLOWING VARCHAR(50),
  "ILIKE" VARCHAR(50),
  RLIKE VARCHAR(50)
);

Snowflake

Table

OUT -> Teradata_05.sql

CREATE OR REPLACE TABLE ReservedKeywords (
    "CREATE" VARCHAR(50),
    "FOLLOWING" VARCHAR(50),
    "ILIKE" VARCHAR(50),
    "RLIKE" VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/28/2024",  "domain": "test" }}'
;

Snowflake-Specific Reserved Keywords

Columns that match Snowflake-specific reserved keywords (e.g., CONSTRAINT, CURRENT_DATE, CURRENT_TIME) may still cause compilation issues even when wrapped in quotes. SnowConvert detects these instances and generates a warning with code SSC-EWI-0045, prompting users to review and potentially rename these columns for compatibility.

Example:

Table

IN -> Teradata_06.sql

CREATE TABLE ColumnReservedNames (
  "CONSTRAINT" VARCHAR(50),
  "CURRENT_DATE" VARCHAR(50),
  "CURRENT_TIME" VARCHAR(50)
);

Snowflake

Table

OUT -> Teradata_06.sql

CREATE OR REPLACE TABLE ColumnReservedNames (
    !!!RESOLVE EWI!!! /*** SSC-EWI-0045 - COLUMN NAME 'CONSTRAINT' IS A SNOWFLAKE RESERVED KEYWORD ***/!!!
    "CONSTRAINT" VARCHAR(50),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0045 - COLUMN NAME 'CURRENT_DATE' IS A SNOWFLAKE RESERVED KEYWORD ***/!!!
    "CURRENT_DATE" VARCHAR(50),
    !!!RESOLVE EWI!!! /*** SSC-EWI-0045 - COLUMN NAME 'CURRENT_TIME' IS A SNOWFLAKE RESERVED KEYWORD ***/!!!
    "CURRENT_TIME" VARCHAR(50)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/28/2024",  "domain": "test" }}'
;

Known Issues

1. Create table options not supported

As shown in the example "Simple Create Table", Snowflake does not support Teradata create table options. They are removed.

2. Partition by performance issues

In the example "Simple Create Table", the partition by statement is removed due to performance considerations.

3. Primary Index moved

In Teradata, the primary index constraint is declared outside of the create table statement, but in Snowflake it is required to be inside, as shown in the example "Simple Create Table".

4. SET semantics not supported

As shown in the example "Table Kind Clause - SET and MULTISET", Snowflake does not support Teradata's SET semantics. They are removed.

5. Global Temporary table option not supported

As shown in the example "Volatile and Global Temporary Table", Snowflake does not support Teradata's Global Temporary table option. It will be removed.

6. Compress unsupported

COMPRESS (value1. value2, value3) is removed due to being unsupported.

7. On commit unsupported

On commit is removed due to being unsupported.

8. Block compression unsupported

Block compression is removed due to being unsupported.

9. Normalize unsupported

Normalize is removed due to being unsupported.

Related EWIs

1. SSC-FDM-0009: GLOBAL TEMPORARY TABLE functionality not supported.
2. SSC-FDM-0019: Sematic information could not be loaded.
3. SSC-FDM-TD0024: Set table functionality not supported.
4. SSC-PRF-0007: CLUSTER BY performance review.
5. SSC-EWI-0045: Column Name is Snowflake Reserved Keyword.