Built-in Functions
This page provides a description of the translation for the built-in functions in Teradata to Snowflake
This page only lists the functions that are already transformed by SnowConvert, if a function from the Teradata documentation is not listed there then it should be taken as unsupported.
Some Teradata functions do not have a direct equivalent in Snowflake so they are transformed into a functional equivalent UDF, these can be easily spotted by the _UDF postfix in the name of the function. For more information on the UDFs SnowConvert uses check this git repository.
Aggregate Functions
Teradata | Snowflake | Note |
---|---|---|
AVG | AVG | |
CORR | CORR | |
COUNT | COUNT | |
COVAR_POP | COVAR_POP | |
COVAR_SAMP | COVAR_SAMP | |
GROUPING | GROUPING | |
KURTOSIS | KURTOSIS | |
MAXIMUM MAX | MAX | |
MINIMUM MIN | MIN | |
PIVOT | PIVOT | Check PIVOT. |
REGR_AVGX | REGR_AVGX | |
REGR_AVGY | REGR_AVGY | |
REGR_COUNT | REGR_COUNT | |
REGR_INTERCEPT | REGR_INTERCEPT | |
REGR_R2 | REGR_R2 | |
REGR_SLOPE | REGR_SLOPE | |
REGR_SXX | REGR_SXX | |
REGR_SXY | REGR_SXY | |
REGR_SYY | REGR_SYY | |
SKEW | SKEW | |
STDDEV_POP | STDDEV_POP | |
STDDEV_SAMP | STDDEV_SAMP | |
SUM | SUM | |
UNPIVOT | UNPIVOT | Unpivot with multiple functions not supported in Snowflake |
VAR_POP | VAR_POP | |
VAR_SAMP | VAR_SAMP |
Arithmetic, Trigonometric, Hyperbolic Operators/Functions
Teradata | Snowflake | Note |
---|---|---|
ABS | ABS | |
CEILING | CEIL | |
DEGREES | DEGREES | |
EXP | EXP | |
FLOOR | FLOOR | |
HYPERBOLIC ACOSH ASINH ATANH COSH SINH TANH | HYPERBOLIC ACOSH ASINH ATANH COSH SINH TANH | |
LOG | LOG | |
LN | LN | |
MOD | MOD | |
NULLIFZERO(param) | CASE WHEN param=0 THEN null ELSE param END | |
POWER | POWER | |
RANDOM | RANDOM | |
RADIANS | RADIANS | |
ROUND | ROUND | |
SIGN | SIGN | |
SQRT | SQRT | |
TRUNC | TRUNC_UDF | |
TRIGONOMETRIC ACOS ASIN ATAN ATAN2 COS SIN TAN | TRIGONOMETRIC ACOS ASIN ATAN ATAN2 COS SIN TAN | |
ZEROIFNULL | ZEROIFNULL |
See Arithmetic, Trigonometric, Hyperbolic Operators/Functions
Attribute Functions
Teradata | Snowflake | Note |
---|---|---|
BIT_LENGTH | BIT_LENGTH | |
BYTE BYTES | LENGTH | |
CHAR CHARS CHARACTERS | LEN | |
CHAR_LENGTH CHARACTER_LENGTH | LEN | |
MCHARACTERS | LENGTH | |
OCTECT_LENGTH | OCTECT_LENGTH |
Bit/Byte Manipulation Functions
Teradata | Snowflake | Note |
---|---|---|
BITAND | BITAND | |
BITNOT | BITNOT | |
BITOR | BITOR | |
BITXOR | BITXOR | |
GETBIT | GETBIT |
Built-In (System Functions)
Teradata | Snowflake | Note |
---|---|---|
ACCOUNT | CURRENT_ACCOUNT | |
CURRENT_DATE CURDATE | CURRENT_DATE | |
CURRENT_ROLE | CURRENT_ROLE | |
CURRENT_TIME CURTIME | CURRENT_TIME | |
CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | |
DATABASE | CURRENT_DATABASE | |
DATE | CURRENT_DATE | |
NOW | CURRENT_TIMESTAMP | |
PROFILE | CURRENT_ROLE | Check MSCEWI2068 for more details on this transformation |
SESSION | CURRENT_SESSION | |
TIME | CURRENT_TIME | |
USER | CURRENT_USER |
Business Calendars
Teradata | Snowflake | Note |
---|---|---|
DAYNUMBER_OF_MONTH(DatetimeValue, 'COMPATIBLE') | DAYOFMONTH | |
DAYNUMBER_OF_MONTH(DatetimeValue, 'ISO') | DAY_NUMBER_OF_MONTH_ISO_UDF | |
DAYNUMBER_OF_MONTH(DatetimeValue, 'TERADATA') | DAYOFMONTH | |
DAYNUMBER_OF_WEEK(DatetimeValue, 'ISO') | DAYOFWEEKISO | |
DAYNUMBER_OF_WEEK(DatetimeValue, 'COMPATIBLE') | DAY_OF_WEEK_COMPATIBLE_UDF | |
DAYNUMBER_OF_WEEK(DatetimeValue, 'TERADATA') DAYNUMBER_OF_WEEK(DatetimeValue) | DAY_OF_WEEK_UDF | |
DAYNUMBER_OF_YEAR(DatetimeValue, 'ISO') | PUBLIC.DAY_OF_YEAR_ISO_UDF | |
DAYNUMBER_OF_YEAR(DatetimeValue) | DAYOFYEAR | |
QUARTERNUMBER_OF_YEAR | QUARTER | |
TD_SUNDAY(DateTimeValue) | PREVIOUS_DAY(DateTimeValue, 'Sunday') | |
WEEKNUMBER_OF_MONTH | WEEKNUMBER_OF_MONTH_UDF | |
WEEKNUMBER_OF_QUARTER(dateTimeValue) | WEEKNUMBER_OF_QUARTER_UDF | |
WEEKNUMBER_OF_QUARTER(dateTimeValue, 'ISO') | WEEKNUMBER_OF_QUARTER_ISO_UDF | |
WEEKNUMBER_OF_QUARTER(dateTimeValue, 'COMPATIBLE') | WEEKNUMBER_OF_QUARTER_COMPATIBLE_UDF | |
WEEKNUMBER_OF_YEAR(DateTimeValue, 'ISO') | WEEKISO | |
YEARNUMBER_OF_CALENDAR(DATETIMEVALUE, 'COMPATIBLE') | YEAR | |
YEARNUMBER_OF_CALENDAR(DATETIMEVALUE, 'ISO') | YEAROFWEEKISO |
Calendar Functions
Teradata | Snowflake | Note |
---|---|---|
DAYNUMBER_OF_WEEK(DatetimeValue) | DAY_OF_WEEK_UDF | |
DAYNUMBER_OF_WEEK(DatetimeValue, 'COMPATIBLE') | DAY_OF_WEEK_COMPATIBLE_UDF | |
QuarterNumber_Of_Year(DatetimeValue, 'ISO') | QUARTER_OF_YEAR_ISO_UDF(DatetimeValue) | |
TD_DAY_OF_CALENDAR | DAY_OF_CALENDAR_UDF | |
TD_DAY_OF_MONTH | DAYOFMONTH | |
TD_DAY_OF_WEEK DAYOFWEEK | DAY_OF_WEEK_UDF | |
TD_DAY_OF_YEAR | DAYOFYEAR | |
TD_WEEK_OF_CALENDAR(DateTimeValue) | DATEDIFF('WEEK', '1900-01-01', DateTimeValue) | |
TD_WEEK_OF_YEAR | WEEK_OF_YEAR_UDF | |
TD_YEAR_BEGIN(DateTimeValue) | YEAR_BEGIN_UDF(DateTimeValue) | |
TD_YEAR_BEGIN(DateTimeValue, 'ISO') | YEAR_BEGIN_ISO_UDF(DateTimeValue) | |
TD_YEAR_END(DateTimeValue) | YEAR_END_UDF(DateTimeValue) | |
TD_YEAR_END(DateTimeValue, 'ISO') | YEAR_END_ISO_UDF(DateTimeValue) | |
WEEKNUMBER_OF_MONTH(DateTimeValue) | WEEKNUMBER_OF_MONTH_UDF(DateTimeValue) | |
WEEKNUMBER_OF_QUARTER(DateTimeValue) | WEEKNUMBER_OF_QUARTER_UDF(DateTimeValue) | |
WEEKNUMBER_OF_QUARTER(DateTimeValue, 'ISO') | WEEKNUMBER_OF_QUARTER_ISO_UDF(DateTimeValue) | |
WEEKNUMBER_OF_QUARTER(DateTimeValue, 'COMPATIBLE') | WEEKNUMBER_OF_QUARTER_COMPATIBLE_UDF(DateTimeValue) | |
WEEKNUMBER_OF_YEAR(DateTimeValue) | WEEK_OF_YEAR_UDF(DateTimeValue) | |
WEEKNUMBER_OF_YEAR(DateTimeValue, 'COMPATIBLE') | WEEK_OF_YEAR_COMPATIBLE_UDF(DateTimeValue) |
Case Functions
Teradata | Snowflake | Note |
---|---|---|
COALESCE | COALESCE | Check Coalesce. |
NULLIF | NULLIF |
See case functions
Comparison Functions
Teradata | Snowflake | Note |
---|---|---|
DECODE | DECODE | |
GREATEST | GREATEST | |
LEAST | LEAST |
Data type conversions
Teradata | Snowflake | Note |
---|---|---|
CAST | CAST | |
CAST(DatetimeValue AS INT) | DATE_TO_INT_UDF | |
CAST (VarcharValue AS INTERVAL) | INTERVAL_UDF | |
TRYCAST | TRY_CAST | |
FROM_BYTES | TO_NUMBER TO_BINARY | FROM_BYTES with ASCII parameter not supported in Snowflake. |
Data Type Conversion Functions
Teradata | Snowflake | Note |
---|---|---|
TO_BYTES(Input, 'Base10') | INT2HEX_UDF(Input) | |
TO_NUMBER | TO_NUMBER | |
TO_CHAR | TO_CHAR or equivalent expression | Check TO_CHAR. |
TO_DATE | TO_DATE | |
TO_DATE(input, 'YYYYDDD') | JULIAN_TO_DATE_UDF |
DateTime and Interval functions
Teradata | Snowflake | Note |
---|---|---|
ADD_MONTHS | ADD_MONTHS | |
EXTRACT | EXTRACT | |
LAST_DAY | LAST_DAY | |
MONTH | MONTH | |
MONTHS_BETWEEN | MONTHS_BETWEEN_UDF | |
NEXT_DAY | NEXT_DAY | |
OADD_MONTHS | ADD_MONTHS | |
ROUND(Numeric) | ROUND | |
ROUND(Date) | ROUND_DATE_UDF | |
TRUNC(Date) | TRUNC_UDF | |
YEAR | YEAR |
Hash functions
Teradata | Snowflake | Note |
---|---|---|
HASH_MD5 | MD5 | |
HASHAMP HASHBACKAM HASHBUCKET HASHROW | Not supported | Check notes on the architecture differences between Teradata and Snowflake |
See Hash functions
JSON functions
Teradata | Snowflake | Note |
---|---|---|
NEW JSON | TO_JSON(PARSE_JSON()) | Check NEW JSON |
JSON_CHECK | CHECK_JSON | Check JSON_CHECK |
JSON_TABLE | Equivalent query | Check JSON_TABLE |
JSONExtract JSONExtractValue JSONExtractLargeValue | JSON_EXTRACT_UDF | Check JSON_EXTRACT |
Null-Handling functions
Teradata | Snowflake | Note |
---|---|---|
NVL | NVL | |
NVL2 | NVL2 |
Ordered Analytical/Window Aggregate functions
Teradata | Snowflake | Note |
---|---|---|
CSUM(col1, col2) | SUM(col_1) OVER (PARTITION BY null ORDER BY col_2 ROWS UNBOUNDED PRECEDING) | |
CUME_DIST | CUME_DIST | |
DENSE_RANK | DENSE_RANK | |
FIRST_VALUE | FIRST_VALUE | |
LAG | LAG | |
LAST_VALUE | LAST_VALUE | |
LEAD | LEAD | |
MAVG(csales, 2, cdate, csales) | AVG(csales) OVER ( ORDER BY cdate, csales ROWS 1 PRECEDING) | |
MEDIAN | MEDIAN | |
MSUM(csales, 2, cdate, csales) | SUM(csales) OVER(ORDER BY cdate, csales ROWS 1 PRECEDING) | |
PERCENT_RANK | PERCENT_RANK | |
PERCENTILE_CONT | PERCENTILE_CONT | |
PERCENTILE_DISC | PERCENTILE_DISC | |
QUANTILE | QUANTILE | |
RANK | RANK | |
ROW_NUMBER | ROW_NUMBER |
See Window functions
Period functions and operators
Teradata | Snowflake | Note |
---|---|---|
BEGIN | PERIOD_BEGIN_UDF | |
END | PERIOD_END_UDF | |
INTERVAL | TIMESTAMPDIFF | |
LAST | PERIOD_LAST_UDF | |
LDIFF | PERIOD_L_DIFF_UDF | |
OVERLAPS | PUBLIC.PERIOD_OVERLAPS_UDF | |
PERIOD | PERIOD_UDF | |
PERIOD(datetimeValue, UNTIL_CHANGED) PERIOD(datetimeValue, UNTIL_CLOSED) | PERIOD_UDF(datetimeValue, '9999-12-31 23:59:59.999999') | See notes about ending bound constants |
RDIFF | PERIOD_R_DIFF_UDF |
Query band functions
Teradata | Snowflake | Note |
---|---|---|
GETQUERYBANDVALUE | GET_QUERY_BAND_VALUE_UDF | Check GETQUERYBANDVALUE |
Regex functions
Teradata | Snowflake | Note |
---|---|---|
REGEXP_INSTR | REGEXP_INSTR | Check Regex functions |
REGEXP_REPLACE | REGEXP_REPLACE | Check Regex functions |
REGEXP_SIMILAR | REGEXP_LIKE | Check Regex functions |
REGEXP_SUBSTR | REGEXP_SUBSTR | Check Regex functions |
See Regex functions
String operators and functions
Teradata | Snowflake | Note |
---|---|---|
ASCII | ASCII | |
CHAR2HEXINT | CHAR_TO_HEX_INT_UDF | |
CHR | CHR/CHAR | |
CHAR_LENGTH | LEN | |
CONCAT | CONCAT | |
EDITDISTANCE | EDITDISTANCE | |
INDEX | CHARINDEX | Check notes about implicit conversion |
INITCAP | INITCAP | |
INSTR | REGEXP_INSTR | |
INSTR(StringValue, StringValue ,NumericNegativeValue, NumericValue) | INSTR_UDF(StringValue, StringValue ,NumericNegativeValue, NumericValue) | |
LEFT | LEFT | |
LENGTH | LENGTH | |
LOWER | LOWER | |
LPAD | LPAD | |
LTRIM | LTRIM | |
OREPLACE | REPLACE | |
OTRANSLATE | TRANSLATE | |
POSITION | POSITION | Check notes about implicit conversion |
REVERSE | REVERSE | |
RIGHT | RIGHT | |
RPAD | RPAD | |
RTRIM | RTRIM | |
SOUNDEX | SOUNDEX_P123 | |
STRTOK | STRTOK | |
STRTOK_SPLIT_TO_TABLE | STRTOK_SPLIT_TO_TABLE | Check Strtok_split_to_table |
SUBSTRING | SUBSTR/SUBSTR_UDF | Check Substring |
TRANSLATE_CHK | TRANSLATE_CHK_UDF | |
TRIM(LEADING '0' FROM aTABLE) | LTRIM(aTABLE, '0') | |
TRIM(TRAILING '0' FROM aTABLE) | RTRIM(aTABLE, '0') | |
TRIM(BOTH '0' FROM aTABLE) | TRIM(aTABLE, '0') | |
TRIM(CAST(numericValue AS FORMAT '999')) | LPAD(numericValue, 3, 0) | |
UPPER | UPPER |
St_Point functions
Teradata | Snowflake | Note |
---|---|---|
ST_SPHERICALDISTANCE | HAVERSINE ST_DISTANCE |
Table operators
Teradata | Snowflake | Note |
---|---|---|
TD_UNPIVOT | Equivalent query | Check Td_unpivot |
See Table Operators
XML functions
Teradata | Snowflake | Note |
---|---|---|
XMLAGG | LISTAGG | Check Xmlagg |
XMLQUERY | Not Supported |
See XML functions
Extensibility UDFs
This section contains UDFs and other extensibility functions that are not offered as system built-in functions by Teradata but are transformed by SnowConvert
Teradata | Snowflake | Note |
---|---|---|
CHKNUM | CHKNUM_UDF | Check this UDF download page |
Notes
Architecture differences between Teradata and Snowflake
Teradata has a shared-nothing architecture with Access Module Processors (AMP) where each AMP manages their own share of disk storage and is accessed through hashing when doing queries. To take advantage of parallelism the stored information should be evenly distributed among AMPs and to do this Teradata offers a group of hash-related functions that can be used to determine how good the actual primary indexes are.
On the other hand, Snowflake architecture is different, and it manages how the data is stored on its own, meaning users do not need to worry about optimizing their data distribution.
Ending bound constants (UNTIL_CHANGED and UNTIL_CLOSED)
Both UNTIL_CHANGED and UNTIL_CLOSED are Teradata constants that represent an undefined ending bound for periods. Internally, these constants are represented as the maximum value a timestamp can have i.e '9999-12-31 23:59:59.999999'. During the migration of the PERIOD function, the ending bound is checked if present to determine if it is one of these constants and to replace it with varchar of value '9999-12-31 23:59:59.999999' in case it is, Snowflake then casts the varchar to date or timestamp depending on the type of the beginning bound when calling PERIOD_UDF.
Implicit conversion
Some Teradata string functions like INDEX or POSITION accept non-string data types and implicitly convert them to string, this can cause inconsistencies in the results of those functions between Teradata and Snowflake. For example, the following Teradata code:
Returns 4, while the CHARINDEX equivalent in Snowflake:
Returns 2, this happens because Teradata has its own default formats which are used during implicit conversion. In the above example, Teradata interprets the numeric constant 35 as BYTEINT and uses BYTEINT default format'-999'
for the implicit conversion to string, causing the converted value to be ' 35'
. On the other hand, Snowflake uses its own default formats, creating inconsistencies in the result.
To solve this, the following changes are done to those function parameters:
If the parameter does not have a cast with format, then a snowflake
TO_VARCHAR
function with the default Teradata format equivalent in Snowflake is added instead.If the parameter does have a cast with format, then the format is converted to its Snowflake equivalent and the
TO_VARCHAR
function is added.As a side note, Teradata ignores the sign of a number if it is not explicitly put inside a format, while Snowflake always adds spaces to insert the sign even when not specified, for those cases a check is done to see if the sign was specified and to remove it from the Snowflake string in case it was not.
After these changes, the resulting code would be:
Which returns 4, the same as the Teradata code.
Last updated