Data Types
This section shows equivalents between data types in Teradata and in Snowflake.
Conversion Table
Teradata | Snowflake | Notes |
---|---|---|
|
| |
|
|
|
|
| Limited to 8MB. |
|
| |
|
| |
|
| |
|
| Limited to 16MB. |
|
| |
|
| |
|
| |
|
| |
|
|
|
|
| Intervals are stored as |
|
| Intervals are stored as |
|
| Intervals are stored as |
|
| Intervals are stored as |
|
| Intervals are stored as |
|
| Elements inside a JSON are ordered by their keys when inserted in a table. [Check out note]. |
|
| Not supported |
|
| |
|
| Periods are stored as |
|
| Periods are stored as |
|
| Periods are stored as |
|
| Periods are stored as |
|
| Periods are stored as |
|
| |
|
|
|
|
| |
|
| |
|
| Warning SSC-EWI-0096 is generated. |
|
| |
|
| |
|
| |
|
| |
|
| |
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,647Teradata
SMALLINT
: -32768 to 32767Teradata
BIGINT
: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807Snowflake
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:
Output code:
Cases where the interval is being multiplied/divided by a numerical expression are transformed to equivalent DATEADD
function calls instead:
Input code:
Output code:
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: