Data Types

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

Conversion Table

Teradata	Snowflake	Notes

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 SSC-EWI-0036 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 SSC-EWI-TD0053 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:

IN -> Teradata_01.sql

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

Output code:

OUT -> Teradata_01.sql

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:

IN -> Teradata_02.sql

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

Output code:

OUT -> Teradata_02.sql

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.

Known Issues

No issues were found.

Related EWIs

No related EWIs.