Data Types

This section shows equivalents between data types in Teradata and in Snowflake.

Conversion Table

Teradata	Snowflake	Notes
ARRAY	ARRAY
BIGINT	BIGINT	BIGINTin Snowflake is an alias for NUMBER(38,0).[Check out note]
BLOB	BINARY	Limited to 8MB. BLOBis not supported, warning MSCEWI2013 is generated
BYTE	BINARY
BYTEINT	BYTEINT
CHAR	CHAR
CLOB	VARCHAR	​Limited to 16MB. CLOBis not supported, warning MSCEWI2014 is generated
DATE	DATE
DECIMAL	DECIMAL
DOUBLE PRECISION	DOUBLE PRECISION
FLOAT	FLOAT
INTEGER	INTEGER	INTEGERin Snowflake is an alias for NUMBER(38,0). [Check out note]
INTERVAL DAY [TO HOUR | MINUTE | SECOND]	VARCHAR(20)	​Intervals are stored asVARCHARin Snowflake except when used in addition/subtraction. [Check out note].
INTERVAL HOUR [TO MINUTE | SECOND]	VARCHAR(20)	​Intervals are stored asVARCHARin Snowflake except when used in addition/subtraction. [Check out note].
INTERVAL MINUTE [TO SECOND]	VARCHAR(20)	​Intervals are stored asVARCHARin Snowflake except when used in addition/subtraction. [Check out note].
INTERVAL SECOND	VARCHAR(20)	​Intervals are stored asVARCHARin Snowflake except when used in addition/subtraction. [Check out note].
INTERVAL YEAR [TO SECOND]	VARCHAR(20)	​Intervals are stored asVARCHARin Snowflake except when used in addition/subtraction. [Check out note].
JSON	VARIANT	Elements inside a JSON are ordered by their keys when inserted in a table. [Check out note].
MBR	---	Not supported
NUMBER	NUMBER(38, 18)
PERIOD(DATE)	VARCHAR(24)	Periods are stored asVARCHARin Snowflake. [Check out note].
PERIOD(TIME)	VARCHAR(34)	Periods are stored asVARCHARin Snowflake. [Check out note].
PERIOD(TIME WITH TIME ZONE)	VARCHAR(46)	Periods are stored asVARCHARin Snowflake. [Check out note].
PERIOD(TIMESTAMP)	VARCHAR(58)	Periods are stored asVARCHARin Snowflake. [Check out note].
PERIOD(TIMESTAMP WITH TIME ZONE)	VARCHAR(58)	Periods are stored asVARCHARin Snowflake. [Check out note].
REAL	REAL
SMALLINT	​SMALLINT​	SMALLINT in Snowflake is an alias for NUMBER(38,0). [Check out note]
ST_GEOMETRY	---	​Not supported
TIME	TIME
TIME WITH TIME ZONE	TIME	Warning MSCEWI2011 is generated.
TIMESTAMP	TIMESTAMP
TIMESTAMP WITH TIME ZONE	TIMESTAMP_TZ
VARBYTE	BINARY
VARCHAR	VARCHAR
XML	VARIANT	​

Notes

See the documentation on Teradata data types

Integer Data Types

For the conversion of integer data types (INTEGER, SMALLINT, and BIGINT), each one is converted to the alias in Snowflake with the same name. Each of those aliases converts to NUMBER(38,0), a data type that is considerably larger than the integer datatype. Below is a comparison of the range of values that can be present in each data type:

• Teradata INTEGER: -2,147,483,648 to 2,147,483,647

• Teradata SMALLINT: -32768 to 32767

• Teradata BIGINT: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

• Snowflake NUMBER(38,0): -99999999999999999999999999999999999999 to +99999999999999999999999999999999999999

Warning MSCEWI1036 is generated.

Interval/Period Data Types

Intervals and Periods are stored as a string (VARCHAR) in Snowflake. When converting, SnowConvert creates a UDF that recreates the same expression as a string. Warning MSCEWI2053 is generated.

You can see more of the UDF's in the public repository of UDF's currently created by Snowflake SnowConvert.

These UDF's assume that periods are stored in a VARCHAR where the data/time parts are separated by an *. For example for a Teradata period like PERIOD('2018-01-01','2018-01-20') it should be stored in Snowflake as a VARCHAR like '2018-01-01*2018-01-20'.

The only exception to the VARCHAR transformation for intervals are interval literals used to add/subtract values from a Datetime expression, Snowflake does not have an INTERVAL datatype but interval constants exist for the specific purpose mentioned. Examples:

Input code:

SELECT TIMESTAMP '2018-05-13 10:30:45' + INTERVAL '10 05:30' DAY TO MINUTE;

Output code:

SELECT
TIMESTAMP '2018-05-13 10:30:45' + INTERVAL '10 DAY, 05 HOUR, 30 MINUTE';

Cases where the interval is being multiplied/divided by a numerical expression are transformed to equivalent DATEADD function calls instead:

Input code:

SELECT TIME '03:45:15' - INTERVAL '15:32:01' HOUR TO SECOND * 10;

Output code:

SELECT
DATEADD('SECOND', 10 * -1, DATEADD('MINUTE', 10 * -32, DATEADD('HOUR', 10 * -15, TIME '03:45:15')));

JSON Data Type

Elements inside a JSON are ordered by their keys when inserted in a table. Thus, the query results might differ. However, this does not affect the order of arrays inside the JSON.

For example, if the original JSON is:

{ 
   "firstName":"Peter",
   "lastName":"Andre",
   "age":31,
   "cities": ["Los Angeles", "Lima", "Buenos Aires"]
}

Using the Snowflake PARSE_JSON() that interprets an input string as a JSON document, producing a VARIANT value. The inserted JSON will be:

{ 
   "age": 31,
   "cities": ["Los Angeles", "Lima", "Buenos Aires"],
   "firstName": "Peter",
   "lastName": "Andre" 
}

Note how "age" is now the first element. However, the array of "cities" maintains its original order.