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_ANYTYPE | Not 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 |
| D | DECIMAL | NUMBER |
| DA | DATE | DATE |
| DH | INTERVAL 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) |
| DM | INTERVAL 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) |
| DS | INTERVAL 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) |
| DT | DATASET | DATASET data type isn't supported in Snowflake. |
| DY | INTERVAL 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) |
| F | FLOAT | FLOAT |
| HM | INTERVAL 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) |
| HR | INTERVAL 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) |
| HS | INTERVAL HOUR 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) |
| I1 | BYTEINT | NUMBER |
| I2 | SMALLINT | NUMBER |
| I8 | BIGINT | NUMBER |
| I | INTEGER | NUMBER |
| JN | JSON | VARIANT |
| LF | CHAR | This data type is in DBC only and can't be converted to Snowflake |
| LV | VARCHAR | This data type is in DBC only and can't be converted to Snowflake |
| MI | INTERVAL 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) |
| MO | INTERVAL 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) |
| MS | INTERVAL 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) |
| N | NUMBER | NUMBER |
| PD | PERIOD(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). |
| PS | PERIOD(TIMESTAMP) | Can be converted to VARCHAR or split into 2 separate timestamps (TIMESTAMP_NTZ). |
| PT | PERIOD(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. |
| SC | INTERVAL 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) |
| SZ | TIMESTAMP WITH TIME ZONE | TIMESTAMP_TZ |
| TS | TIMESTAMP | TIMESTAMP_NTZ |
| TZ | TIME WITH TIME ZONE | TIME WITH TIME ZONE isn't supported because TIME is stored using "wall clock" time only without a time zone offset |
| UF | CHAR | This data type is in DBC only and can't be converted to Snowflake. |
| UT | UDT | UDT data type isn't supported in Snowflake. |
| UV | VARCHAR | This data type is in DBC only and can't be converted to Snowflake |
| XM | XML | VARIANT |
| YM | INTERVAL 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) |
| YR | INTERVAL 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;
Bozhack-miller
posted on 22 Oct 18Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds
Post Comment