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
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

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

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’)

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’)
DAYNUMBER_OF_WEEK(DatetimeValue)

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

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

DAYOFMONTH

TD_DAY_OF_WEEK
DAYOFWEEK

TD_DAY_OF_WEEK_UDF

TD_DAY_OF_YEAR

DAYOFYEAR

TD_MONTH_OF_CALENDAR(DateTimeValue)
MONTH_CALENDAR(DateTimeValue)

TD_MONTH_OF_CALENDAR_UDF(DateTimeValue)

TD_WEEK_OF_CALENDAR(DateTimeValue)
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)

Case Functions

Teradata

Snowflake

Note

COALESCE

COALESCE

NULLIF

NULLIF

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

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

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

JSON functions

Teradata

Snowflake

Note

NEW JSON

TO_JSON(PARSE_JSON())

JSON_CHECK

CHECK_JSON

Check JSON_CHECK

JSON_TABLE

Equivalent query

JSONExtract

JSONExtractValue
JSONExtractLargeValue

JSON_EXTRACT_UDF

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

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(datetimeValue, UNTIL_CLOSED)

PERIOD_UDF(datetimeValue, ‘9999-12-31 23:59:59.999999’)

RDIFF

PERIOD_RDIFF_UDF

Query band functions

Teradata

Snowflake

Note

GETQUERYBANDVALUE

GETQUERYBANDVALUE_UDF

Regex functions

Teradata

Snowflake

Note

REGEXP_INSTR

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_REPLACE

REGEXP_SIMILAR

REGEXP_LIKE

REGEXP_SUBSTR

REGEXP_SUBSTR

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

St_Point functions

Teradata

Snowflake

Note

ST_SPHERICALDISTANCE

HAVERSINE
ST_DISTANCE

See St_Point functions

Table operators

Teradata

Snowflake

Note

TD_UNPIVOT

Equivalent query

XML functions

Teradata

Snowflake

Note

XMLAGG

LISTAGG

XMLQUERY

Not Supported

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:

SELECT INDEX(35, '5');
Copy

Returns 4, while the CHARINDEX equivalent in Snowflake:

SELECT CHARINDEX('5', 35);
Copy

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 snowflakeTO_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 theTO_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'));
Copy

Which returns 4, the same as the Teradata code.

Known Issues

No issues were found.

Language: English