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

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

OUT -> Teradata_01.sql
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "table2" **
CREATE 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-TD0001 - 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

IN -> Teradata_02.sql
-- Set semantics
CREATE SET TABLE table1 (
    column1 INTEGER
);

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

Snowflake

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

--Multiset semantics
CREATE 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

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

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

--Volatile Table
CREATE 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

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

OUT -> Teradata_04.sql
-- With data
CREATE 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 TABLE table1 LIKE table2
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

Snowflake's Reserved & Limited Keywords

Snowflake SQL reserves all ANSI keywords (with the exception of type keywords such as CHAR, DATE, DECIMAL, etc.), as well as some additional keywords (ASC, DESC, MINUS, etc.) that are reserved by other popular databases. Additionally, Snowflake reserves keywords REGEXP and RLIKE (which function like the ANSI reserved keyword LIKE) and SOME (which is a synonym for the ANSI reserved keyword ANY).

For this reason, when SnowConvert converts a column with a reserved name it changes its format to XXX_<name>_XXX. Next, you will find the list of reserved and limited keywords.

KeywordComment

A

ACCOUNT

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).

ALL

Reserved by ANSI.

ALTER

Reserved by ANSI.

AND

Reserved by ANSI.

ANY

Reserved by ANSI.

AS

Reserved by ANSI.

B

BETWEEN

Reserved by ANSI.

BY

Reserved by ANSI.

C

CASE

Cannot be used as column reference in a scalar expression.

CAST

Cannot be used as column reference in a scalar expression.

CHECK

Reserved by ANSI.

COLUMN

Reserved by ANSI.

CONNECT

Reserved by ANSI.

CONNECTION

Cannot be used as an identifier in a SHOW command (e.g. ‘SHOW … IN <identifier>’).