SnowConvert: Built-in Functions¶
Note
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.
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 |
|
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 |
VAR_POP |
VAR_POP |
|
VAR_SAMP |
VAR_SAMP |
See Aggregate functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/Aggregate-Functions)
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 (https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/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 |
See Attribute functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/Attribute-Functions)
Bit/Byte Manipulation Functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
BITAND |
BITAND |
|
BITNOT |
BITNOT |
|
BITOR |
BITOR |
|
BITXOR |
BITXOR |
|
GETBIT |
GETBIT |
See Bit/Byte functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/Bit/Byte-Manipulation-Functions)
Built-In (System Functions)¶
Teradata |
Snowflake |
Note |
|---|---|---|
ACCOUNT |
CURRENT_ACCOUNT |
|
CURRENT_DATE CURDATE |
CURRENT_DATE |
|
CURRENT_ROLE |
CURRENT_ROLE |
|
CURRENT_TIME |
CURRENT_TIME |
|
CURRENT_TIMESTAMP |
CURRENT_TIMESTAMP |
|
DATABASE |
CURRENT_DATABASE |
|
DATE |
CURRENT_DATE |
|
NOW |
CURRENT_TIMESTAMP |
|
PROFILE |
CURRENT_ROLE |
|
SESSION |
CURRENT_SESSION |
|
TIME |
CURRENT_TIME |
|
USER |
CURRENT_USER |
See Built-In Functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/Built-In-Functions)
Business Calendars¶
Teradata |
Snowflake |
Note |
|---|---|---|
DAYNUMBER_OF_MONTH(DatetimeValue, ‘COMPATIBLE’) |
DAYOFMONTH |
|
DAYNUMBER_OF_MONTH(DatetimeValue, ‘ISO’) |
DAYNUMBER_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’) |
TD_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 |
See Business Calendars (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Date-and-Time-Functions-and-Expressions-17.20/Business-Calendars)
Calendar Functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
DAYNUMBER_OF_WEEK(DatetimeValue) |
TD_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 |
TD_DAY_OF_CALENDAR_UDF |
|
TD_DAY_OF_MONTH |
DAYOFMONTH |
|
TD_DAY_OF_WEEK |
TD_DAY_OF_WEEK_UDF |
|
TD_DAY_OF_YEAR |
DAYOFYEAR |
|
TD_MONTH_OF_CALENDAR(DateTimeValue) |
TD_MONTH_OF_CALENDAR_UDF(DateTimeValue) |
|
TD_WEEK_OF_CALENDAR(DateTimeValue) |
TD_WEEK_OF_CALENDAR_UDF(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) |
See Calendar Functions (https://docs.teradata.com/r/WX0vkeB8F3JQXZ0HTR~d0Q/~8TzAjUr3AFwohWtu8ndxQ)
Case Functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
COALESCE |
COALESCE |
|
NULLIF |
NULLIF |
See case functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/CASE-Expressions)
Comparison Functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
DECODE |
DECODE |
|
GREATEST |
GREATEST |
|
LEAST |
LEAST |
See comparison functions (https://docs.teradata.com/r/Teradata-VantageTM-SQL-Functions-Expressions-and-Predicates-17.20/Comparison-Operators-and-Functions)
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 |
with ASCII parameter not supported in Snowflake. |
See Data Type Conversions (https://docs.teradata.com/reader/~_sY_PYVxZzTnqKq45UXkQ/iZ57TG_CtznEu1JdSbFNsQ)
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 |
|
TO_DATE |
TO_DATE |
|
TO_DATE(input, ‘YYYYDDD’) |
JULIAN_TO_DATE_UDF |
See Data Type Conversion Functions (https://docs.teradata.com/r/Teradata-VantageTM-Data-Types-and-Literals/March-2019/Data-Type-Conversion-Functions)
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 |
See DateTime and Interval Functions and Expressions (https://docs.teradata.com/r/kmuOwjp1zEYg98JsB8fu_A/JhmMJqd9vWURvHYeTRgQLQ)
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 (https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/jslafnqlE8bGpg~wXQiEFw)
JSON functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
NEW JSON |
TO_JSON(PARSE_JSON()) |
|
JSON_CHECK |
CHECK_JSON |
Check JSON_CHECK |
JSON_TABLE |
Equivalent query |
|
JSONExtract JSONExtractValue |
JSON_EXTRACT_UDF |
See JSON documentation (https://docs.teradata.com/r/C8cVEJ54PO4~YXWXeXGvsA/_aeoMCG0XgMNegNj0oy5cg)
Null-Handling functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
NVL |
NVL |
|
NVL2 |
NVL2 |
See Null-Handling functions (https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/4di35TY_6SqRNEGk4vv0ww)
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 (https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/qbFqalW6IF5Fryz47~iqJQ)
Period functions and operators¶
Teradata |
Snowflake |
Note |
|---|---|---|
BEGIN |
PERIOD_BEGIN_UDF |
|
END |
PERIOD_END_UDF |
|
INTERVAL |
TIMESTAMPDIFF |
|
LAST |
PERIOD_LAST_UDF |
|
LDIFF |
PERIOD_LDIFF_UDF |
|
OVERLAPS |
PUBLIC.PERIOD_OVERLAPS_UDF |
|
PERIOD |
PERIOD_UDF |
|
PERIOD(datetimeValue, UNTIL_CHANGED) |
PERIOD_UDF(datetimeValue, ‘9999-12-31 23:59:59.999999’) |
|
RDIFF |
PERIOD_RDIFF_UDF |
See Period Functions and Operators (https://docs.teradata.com/r/Teradata-Database-SQL-Functions-Operators-Expressions-and-Predicates/June-2017/Period-Functions-and-Operators)
Query band functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
GETQUERYBANDVALUE |
GETQUERYBANDVALUE_UDF |
See Query band functions (https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Application-Programming-Reference-17.20/Workload-Management-Query-Band-APIs/Open-APIs-SQL-Interfaces)
Regex functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
REGEXP_INSTR |
REGEXP_INSTR |
|
REGEXP_REPLACE |
REGEXP_REPLACE |
|
REGEXP_SIMILAR |
REGEXP_LIKE |
|
REGEXP_SUBSTR |
REGEXP_SUBSTR |
See Regex functions (https://docs.teradata.com/r/756LNiPSFdY~4JcCCcR5Cw/yL2xT~elOTehmwVmwVBRHA)
String operators and functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
ASCII |
ASCII |
|
CHAR2HEXINT |
CHAR2HEXINT_UDF |
|
CHR |
CHR/CHAR |
|
CHAR_LENGTH |
LEN |
|
CONCAT |
CONCAT |
|
EDITDISTANCE |
EDITDISTANCE |
|
INDEX |
CHARINDEX |
|
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 |
|
REVERSE |
REVERSE |
|
RIGHT |
RIGHT |
|
RPAD |
RPAD |
|
RTRIM |
RTRIM |
|
SOUNDEX |
SOUNDEX_P123 |
|
STRTOK |
STRTOK |
|
STRTOK_SPLIT_TO_TABLE |
STRTOK_SPLIT_TO_TABLE |
|
SUBSTRING |
SUBSTR/SUBSTR_UDF |
|
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 |
See String operators and functions (https://docs.teradata.com/reader/756LNiPSFdY~4JcCCcR5Cw/5nyfztBE7gDQVCVU2MFTnA)
St_Point functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
ST_SPHERICALDISTANCE |
HAVERSINE |
See St_Point functions (https://docs.teradata.com/r/W1AEeHO2cxTi3Sn7dtj8hg/JDVMx04qe~mo1mIm2h7NWQ)
Table operators¶
Teradata |
Snowflake |
Note |
|---|---|---|
TD_UNPIVOT |
Equivalent query |
See Table Operators (https://docs.teradata.com/r/Teradata-Database-SQL-Functions-Operators-Expressions-and-Predicates/June-2017/Table-Operators)
XML functions¶
Teradata |
Snowflake |
Note |
|---|---|---|
XMLAGG |
LISTAGG |
|
XMLQUERY |
Not Supported |
See XML functions (https://docs.teradata.com/r/JTydkOYDksSy26sxlEtMvg/GhlIYri~mxyncdX5BV3jWA)
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 (https://downloads.teradata.com/download/extensibility/isnumeric-udf) |
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:
SELECT INDEX(35, '5');
Returns 4, while the CHARINDEX equivalent in Snowflake:
SELECT CHARINDEX('5', 35);
Returns 2, this happens because Teradata has its own default formats (https://docs.teradata.com/r/S0Fw2AVH8ff3MDA0wDOHlQ/Xh8u4~A7KI46wOdMG9DSHQ) which are used during implicit conversion. In the above example, Teradata interprets the numeric constant (https://docs.teradata.com/r/T5QsmcznbJo1bHmZT2KnFw/TEOJhlyP6az05SdTK9JHMg) 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_VARCHARfunction 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_VARCHARfunction 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:
SELECT CHARINDEX( '5', TO_VARCHAR(35, 'MI999'));
Which returns 4, the same as the Teradata code.
Known Issues¶
No issues were found.