SnowConvert: 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-TD0053is 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.