Built-in functions

Aggregate Functions

Aggregate functions compute a single result value from a set of input values. (Greenplum Language Reference Aggregate Functions).

Greenplum
Snowflake

AVG

Notes: Greenplum and Snowflake may show different precision/decimals due to data type rounding/formatting.

MEDIAN

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

Greenplum
Snowflake

COALESCE ( value [, ...] )

COALESCE ( expression, expression, ... )

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

Greenplum
Snowflake

TO_CHAR

Notes: Snowflake's support for this function is partial (see SSC-EWI-PG0005).

TO_DATE

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

Greenplum
Snowflake

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.

DATE_PART

Notes: this function is partially supported by Snowflake. (See SSC-EWI-PGOOO5).

DATE_TRUNC

Notes: Invalid date part formats are translated to Snowflake-compatible formats.

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

JSON Functions

Greenplum
Snowflake

JSON_EXTRACT_PATH_TEXT

Notes:

  1. Greenplum treats newline, tab, and carriage return characters literally; Snowflake interprets them.

  2. A JSON literal and dot-separated path are required to access nested objects in the Snowflake function.

  3. Paths with spaces in variables must be quoted.

Math functions

Greenplum and Snowflake results may differ in scale.

String functions

String functions process and manipulate character strings or expressions that evaluate to character strings. (Greenplum Language Reference String functions).

Greenplum
Snowflake

OCTET_LENGTH

Notes: the results may vary between platforms (See SSC-FDM-PG0013).

QUOTE_IDENT (string)

CONCAT ('"', string, '"')

REGEXP_REPLACE

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

SPLIT_PART

Notes: Snowflake and Greenplum handle SPLIT_PART differently with case-insensitive collations.

STRPOS (string, substring )

POSITION ( <expr1> IN <expr> )

SUBSTRING

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

TRIM

Notes: Greenplum uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.

Window functions

Greenplum
Snowflake

AVG

Notes: AVG rounding/formatting can vary by data type between Greenplum and Snowflake.

DENSE_RANK

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

FIRST_VALUE

Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.

LAST_VALUE

Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.

LEAD

Notes: Greenplum allows constant or expression offsets; Snowflake allows only constant offsets.

NTH_VALUE

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

NTILE

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1. (See SSC-FDM-PG0013).

PERCENT_RANK

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

PERCENTILE_CONT

Notes: Rounding varies between platforms.

ROW_NUMBER

Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

  • 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