Built-in functions
Aggregate Functions
Aggregate functions compute a single result value from a set of input values. (Greenplum Language Reference Aggregate Functions).
Notes: Greenplum and Snowflake may show different precision/decimals due to data type rounding/formatting.
Notes: Snowflake does not allow the use of date types, while Greenplum does. (See SSC-FDM-PG0013).
STDDEV/STDDEV_SAMP (expression)
STDDEV_POP (expression)
STDDEV/STDDEV_SAMP (expression)
STDDEV_POP (expression)
VARIANCE/VAR_SAMP (expression)
VAR_POP (expression)
VARIANCE/VAR_SAMP (expression)
VAR_POP (expression)
Conditional expressions
GREATEST ( value [, ...] )
GREATEST_IGNORE_NULLS ( <expr1> [, <expr2> ... ] )
LEAST ( value [, ...] )
LEAST_IGNORE_NULLS ( <expr1> [, <expr2> ... ])
Data type formatting functions
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. (Greenplum Language Reference Data type formatting functions).
Notes: Snowflake's support for this function is partial (see SSC-EWI-PG0005).
Notes: Snowflake's TO_DATE
fails on invalid dates like '20010631' (June has 30 days), unlike Greenplum's lenient TO_DATE
. Use TRY_TO_DATE
in Snowflake to handle these cases by returning NULL. (see SSC-EWI-PG0005, SSC-FDM-0032).
Date and time functions
CONVERT_TIMEZONE ( <source_tz> , <target_tz> , <source_timestamp_ntz> )
CONVERT_TIMEZONE ( <target_tz> , <source_timestamp> )
Notes: Greenplum defaults to UTC; the Snowflake function requires explicit UTC specification. Therefore, it will be added as the target timezone.
Notes: this function is partially supported by Snowflake. (See SSC-EWI-PGOOO5).
Notes: Invalid date part formats are translated to Snowflake-compatible formats.
JSON Functions
Notes:
Greenplum 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.
Math functions
String functions
String functions process and manipulate character strings or expressions that evaluate to character strings. (Greenplum Language Reference String functions).
Notes: the results may vary between platforms (See SSC-FDM-PG0013).
QUOTE_IDENT (string)
CONCAT ('"', 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 SSC-EWI-0009, SC-FDM-0032, SSC-FDM- PG0011).
Notes: Snowflake and Greenplum handle SPLIT_PART differently with case-insensitive collations.
Notes: Snowflake partially supports this function. Greenplum'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 SSC-EWI-RS0005).
Notes: Greenplum uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.
Window functions
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: Greenplum 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
. (See SSC-FDM-PG0013).
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
.
Related EWIs
SSC-EWI-PG0005: Date or time format is not supported in Snowflake.
SSC-FDM-0032: Parameter is not a literal value, transformation could not be fully applied
SSC-FDM-PG0013: Function syntactically supported by Snowflake but may have functional differences.
SSC-FDM-RS0004: Invalid dates will cause errors in Snowflake.
Last updated