22 October 2018        Add to Favorites   Report

Converting Teradata data types to Snowflake data types

While migrating from Teradata to Snowflake, few of the Teradata data types aren't supported in Snowflake, you might want to re-engineer/rewrite your existing queries/reports. Below table has all the Teradata datatypes and their equivalent in Snowflake

Teradata data types and the equivalent Snowflake data types

Teradata Column Type  

Teradata Data Type

Snowflake Data Type

++
TD_ANYTYPENot supported in Snowflake
A1
ARRAY
ARRAY
AN
ARRAY
ARRAY
AT
TIME
TIME
BF
BYTE
BINARY
BO

BLOB

BLOB data type isn't directly supported but can be replaced with BINARY (limited to 8MB)

BV
VARBYTE
BINARY
CF
CHAR
VARCHAR
CO
CLOB

CLOB data type isn't directly supported but can be replaced with VARCHAR (limited to 16MB)

CV
VARCHAR
VARCHAR
DDECIMAL
NUMBER
DADATE
DATE
DHINTERVAL DAY TO HOUR

INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)

DMINTERVAL DAY TO MINUTE
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
DSINTERVAL DAY TO SECOND
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
DTDATASET
DATASET data type isn't supported in Snowflake.
DYINTERVAL DAY
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
FFLOAT
FLOAT
HMINTERVAL HOUR TO MINUTE
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
HRINTERVAL HOURINTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
HSINTERVAL HOUR TO SECONDINTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
I1
BYTEINT
NUMBER
I2
SMALLINT
NUMBER
I8
BIGINT
NUMBER
I
INTEGER
NUMBER
JNJSON
VARIANT
LFCHAR

This data type is in DBC only and can't be converted to Snowflake

LVVARCHAR

This data type is in DBC only and can't be converted to Snowflake

MIINTERVAL MINUTE
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
MOINTERVAL MONTH
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
MSINTERVAL MINUTE TO SECOND
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
NNUMBERNUMBER
PDPERIOD(DATE)

Can be converted to VARCHAR or split into 2 separate dates

PM

PERIOD(TIMESTAMP WITH TIME ZONE)

Can be converted to VARCHAR or split into 2 separate timestamps (TIMESTAMP_TZ).

PSPERIOD(TIMESTAMP)

Can be converted to VARCHAR or split into 2 separate timestamps (TIMESTAMP_NTZ).

PTPERIOD(TIME)

Can be converted to VARCHAR or split into 2 separate times.

PZ
PERIOD(TIME WITH TIME ZONE)

Can be converted to VARCHAR or split into 2 separate times but WITH TIME ZONE isn't supported for TIME.

SCINTERVAL SECOND

INTERVAL data types aren't supported in Snowflake but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)

SZTIMESTAMP WITH TIME ZONE
TIMESTAMP_TZ
TS
TIMESTAMP
TIMESTAMP_NTZ
TZTIME WITH TIME ZONE

TIME WITH TIME ZONE isn't supported because TIME is stored using "wall clock" time only without a time zone offset

UFCHAR

This data type is in DBC only and can't be converted to Snowflake.

UTUDTUDT data type isn't supported in Snowflake.
UVVARCHAR

This data type is in DBC only and can't be converted to Snowflake

XMXMLVARIANT
YMINTERVAL YEAR TO MONTH
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)
YRINTERVAL YEAR
INTERVAL data types aren't supported in Snowflake. but date calculations can be done with the date comparison functions (e.g. DATEDIFF and DATEADD)


To analyze Teradata data type usage:

Use the below query to evaluate the number of datatypes conversions needed while migrating to Snowflake. You can update the WHERE clause of the query to include the specific list of databases migrated from Teradata to Snowflake. Use the results of this query, along with the list of Teradata and Snowflake data types, to identify the prevalence of data types that may pose challenges during the migration

SELECT
ColumnType,
CASE
WHEN ColumnType = '++' THEN 'TD_ANYTYPE'
WHEN ColumnType = 'A1' THEN 'ARRAY'
WHEN ColumnType = 'AN' THEN 'ARRAY'
WHEN ColumnType = 'AT' THEN 'TIME'
WHEN ColumnType = 'BF' THEN 'BYTE'
WHEN ColumnType = 'BO' THEN 'BLOB'
WHEN ColumnType = 'BV' THEN 'VARBYTE'
WHEN ColumnType = 'CF' THEN 'CHAR'
WHEN ColumnType = 'CO' THEN 'CLOB'
WHEN ColumnType = 'CV' THEN 'VARCHAR'
WHEN ColumnType = 'D' THEN 'DECIMAL'
WHEN ColumnType = 'DA' THEN 'DATE'
WHEN ColumnType = 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN ColumnType = 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN ColumnType = 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN ColumnType = 'DT' THEN 'DATASET'
WHEN ColumnType = 'DY' THEN 'INTERVAL DAY'
WHEN ColumnType = 'F' THEN 'FLOAT'
WHEN ColumnType = 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN ColumnType = 'HR' THEN 'INTERVAL HOUR'
WHEN ColumnType = 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN ColumnType = 'I1' THEN 'BYTEINT'
WHEN ColumnType = 'I2' THEN 'SMALLINT'
WHEN ColumnType = 'I8' THEN 'BIGINT'
WHEN ColumnType = 'I' THEN 'INTEGER'
WHEN ColumnType = 'JN' THEN 'JSON'
WHEN ColumnType = 'MI' THEN 'INTERVAL MINUTE'
WHEN ColumnType = 'MO' THEN 'INTERVAL MONTH'
WHEN ColumnType = 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN ColumnType = 'N' THEN 'NUMBER'
WHEN ColumnType = 'PD' THEN 'PERIOD(DATE)'
WHEN ColumnType = 'PM' THEN 'PERIOD(TIMESTAMP WITH TIME ZONE)'
WHEN ColumnType = 'PS' THEN 'PERIOD(TIMESTAMP)'
WHEN ColumnType = 'PT' THEN 'PERIOD(TIME)'
WHEN ColumnType = 'PZ' THEN 'PERIOD(TIME WITH TIME ZONE)'
WHEN ColumnType = 'SC' THEN 'INTERVAL SECOND'
WHEN ColumnType = 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN ColumnType = 'TS' THEN 'TIMESTAMP'
WHEN ColumnType = 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN ColumnType = 'UT' THEN 'UDT'
WHEN ColumnType = 'XM' THEN 'XML'
WHEN ColumnType = 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN ColumnType = 'YR' THEN 'INTERVAL YEAR'
END AS Data_Type,
COUNT(*) AS Data_Type_Count
FROM DBC.ColumnsV
WHERE DatabaseName NOT IN ('DBC', 'Crashdumps', 'dbcmngr', 'External_AP', 'EXTUSER', 'LockLogShredder',
'QCD', 'SQLJ', 'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB', 'SYSSPATIAL', 'SystemFE', 'SYSUDTLIB',
'SYSUIF', 'TD_SERVER_DB', 'TD_SYSFNLIB', 'TD_SYSGPL', 'TD_SYSXML', 'TDPUSER', 'TDQCD', 'TDStats', 'tdwm')
GROUP BY 1,2
ORDER BY 1;



Read great educational content like this and a lot more ! Create my free account now 🎁

Copied