Built-in functions
Last updated
Last updated
Aggregate functions compute a single result value from a set of input values. ().
Notes: Snowflake supports the grammar, though ALL is disallowed. DISTINCT has no effect in either.
Notes: Redshift and Snowflake may show different precision/decimals due to data type rounding/formatting.
Notes: Snowflake supports all the grammar, though ALL is disallowed.
Notes: Snowflake supports all the grammar, though ALL is disallowed.
Creates an array of the SUPER data type. ().
( [ <expr1> ] [ , <expr2> [ , ... ] ] )
( super_expr1,super_expr2,.. )
Notes: Redshift allows missing delimiters; Snowflake requires them, defaulting to comma
Notes: Function names and the second argument differ; adjust arguments for equivalence.
Notes: Redshift's NVL accepts multiple arguments; Snowflake's NVL accepts only two. To match Redshift behavior, NVL with more than two arguments is converted to COALESCE.
Notes: Redshift's NULLIF ignores trailing spaces in some string comparisons, unlike Snowflake. Therefore, the transformation adds RTRIM for equivalence.
Notes: Redshift defaults to UTC; the Snowflake function requires explicit UTC specification. Therefore, it will be added as the target timezone.
Notes: Invalid date part formats are translated to Snowflake-compatible formats.
Notes: Invalid date part formats are translated to Snowflake-compatible formats.
Notes: Invalid date part formats are translated to Snowflake-compatible formats.
Notes: In Redshift's FNV_HASH function, the seed parameter is optional, but it is not used in the Snowflake's Hash function since the seed parameter is only used on FNV algorithm.
Notes:
Redshift treats newline, tab, and carriage return characters literally; Snowflake interprets them.
A JSON literal and dot-separated path are required to access nested objects in the Snowflake function.
Paths with spaces in variables must be quoted.
Notes: For negative lengths in LEFT/RIGHT, Snowflake returns an empty string; Redshift raises an error.
Notes: Snowflake and Redshift handle SPLIT_PART differently with case-insensitive collations.
Notes: Redshift uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.
Notes: AVG rounding/formatting can vary by data type between Redshift and Snowflake.
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.
Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.
Notes: Redshift allows constant or expression offsets; Snowflake allows only constant offsets.
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
Notes: Rounding varies between platforms.
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
( [ ALL ] expression )
OVER ( [ PARTITION BY expr_list ]
[ ORDER BY order_list frame_clause ] )
( [ DISTINCT ] expr1) OVER ( [ PARTITION BY expr2 ] [ ORDER BY expr3 [ ASC | DESC ] [ <window_frame> ] ] )
Notes: Snowflake supports all the grammar, though ALL is disallowed.
( [ ALL ] expression )
OVER ( [ PARTITION BY expr_list ]
[ ORDER BY order_list frame_clause ] )
( [ DISTINCT ] expr1) OVER ( [ PARTITION BY expr2 ] [ ORDER BY expr3 [ ASC | DESC ] [ <window_frame> ] ] )
Notes: Snowflake supports all the grammar, though ALL is disallowed.
( [ DISTINCT | ALL ] expression )
( [ DISTINCT ]expression )
( [ DISTINCT | ALL ] expression )
( [ DISTINCT ] expression)
Notes: Redshift's DISTINCT ignores trailing spaces ('a ' = 'a'); Snowflake's does not. (See ).
Notes: Snowflake does not allow the use of date types, while Redshift does. (See ).
( [ DISTINCT | ALL ] expression)
( [ DISTINCT | ALL ] expression)
( [ DISTINCT ] expression)
( [ DISTINCT ] expression)
( [ DISTINCT | ALL ] expression)
( [ DISTINCT | ALL ] expression)
( [ DISTINCT ] expression)
( [ DISTINCT ] expression)
( [ expr1 ] [ , expr2 [ , ... ] ] )
( super_expr1, super_expr2 )
( <array1> , <array2> )
( <array> )
Notes: the results may vary between platforms (See ).
( super_expr )
( <array> | <variant>)
( string,delimiter )
(<string>, <separator>)
( super_expr, start_position, length )
( <array> , <from> , <to> )
Notes: the results may vary between platforms (See ).
( expression, expression, ... )
( expression, expression, ... )
( value [, ...] )
( <expr1> [, <expr2> ... ] )
( value [, ...] )
( <expr1> [, <expr2> ... ])
( expression, expression, ... )
( expression, expression )
Data type formatting functions provide an easy way to convert values from one data type to another. For each of these functions, the first argument is always the value to be formatted and the second argument contains the template for the new format. ().
Notes: Snowflake's support for this function is partial (see ).
Notes: Snowflake's TO_DATE fails on invalid dates like '20010631' (June has 30 days), unlike Redshift's lenient TO_DATE. Use TRY_TO_DATE in Snowflake to handle these cases by returning NULL. (see , , ).
Notes: the results may vary between platforms (See ).
( <source_tz> , <target_tz> , <source_timestamp_ntz> )
( <target_tz> , <source_timestamp> )
( datepart, interval, {date | time | timetz | timestamp} )
( <date_or_time_part>, <value>, <date_or_time_expr> )
Notes: this function is partially supported by Snowflake. (See ).
(date)
( <date_or_timestamp_expr> )
Notes: the results may vary between platforms (See ).
()
()
Notes: the results may vary between platforms (See ).
Notes: the results may vary between platforms (See ).
()
Notes: Part-time or Date time supported: DAY, DOW, DOY, EPOCH, HOUR, MINUTE, MONTH, QUARTER, SECOND, WEEK, YEAR.
Redshift timestamps default to microsecond precision (6 digits); Snowflake defaults to nanosecond precision (9 digits). Adjust precision as needed using ALTER SESSION (e.g., ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF2';). Precision loss may occur depending on the data type used.
Since some formats are incompatible with Snowflake, adjusting the account parameters might maintain functional equivalence between platforms.
A hash function is a mathematical function that converts a numerical input value into another value. ().
(value [, seed])
( <expr> [ , <expr> ... ] | *)
(number)
(10, number)
String functions process and manipulate character strings or expressions that evaluate to character strings. ().
/
Notes: the results may vary between platforms (See ).
(string)
('"', string, '"')
Notes: This function includes a parameters argument that enables the user to interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect, represented by the p value, this is removed to avoid any issues. (See , , ).
Notes: Certain special characters, the results may vary between platforms (See ).
(string, substring )
( <expr1> IN <expr> )
Notes: Snowflake partially supports this function. Redshift's SUBSTRING, with a non-positive start_position, calculates start_position + number_characters (returning '' if the result is non-positive). Snowflake's behavior differs. (See ).
Notes: the results may vary between platforms (See ).
Notes: the results may vary between platforms (See ).
Notes: Redshift's DISTINCT ignores trailing spaces ('a ' = 'a'); Snowflake's does not. (See ).
Notes: Snowflake does not allow the use of date types, while Redshift does. (See ).
Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1. (See ).
Notes: the results may vary between platforms (See ).
/
/
For more information about quoted identifiers in functions, click .
: Date or time format is not supported in Snowflake.
: Parameter is not a literal value, transformation could not be fully applied
: Function syntactically supported by Snowflake but may have functional differences.
: Invalid dates will cause errors in Snowflake.