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	AVG Notes: Redshift and Snowflake may show different precision/decimals due to data type rounding/formatting.
COUNT	COUNT
MAX	MAX
MEDIAN	MEDIAN Notes: Snowflake does not allow the use of date types, while Greenplum does. (See SSC-FDM-PG0013).
MIN	MIN
PERCENTILE_CONT	PERCENTILE_CONT
STDDEV/STDDEV_SAMP (expression) STDDEV_POP (expression)	STDDEV/STDDEV_SAMP (expression) STDDEV_POP (expression)
SUM	SUM
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> ... ])
NULLIF	NULLIF Notes: Redshift's NULLIF ignores trailing spaces in some string comparisons, unlike Snowflake. Therefore, the transformation adds RTRIM for equivalence.

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	TO_CHAR Notes: Snowflake's support for this function is partial (see SSC-EWI-PG0005).
TO_DATE	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
AT TIME ZONE 'timezone'	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.
CURRENT_DATE	CURRENT_DATE()
DATE_PART/PGDATE_PART	DATE_PART Notes: this function is partially supported by Snowflake. (See SSC-EWI-PGOOO5).
DATE_TRUNC	DATE_TRUNC Notes: Invalid date part formats are translated to Snowflake-compatible formats.
TO_TIMESTAMP	TO_TIMESTAMP
TRUNC	TRUNC

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

Redshift	Snowflake
JSON_EXTRACT_PATH_TEXT	JSON_EXTRACT_PATH_TEXT 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

Greenplum	Snowflake
ACOS	ACOS
ASIN	ASIN
ATAN	ATAN
ATAN2	ATAN2
CBRT	CBRT
CEIL/CEILING	CEIL
COS	COS
COT	COT
DEGREES	DEGREES
LN	LN
EXP	EXP
FLOOR	FLOOR
LOG	LOG
MOD	MOD
PI	PI
POWER/POW	POWER/POW
RADIANS	RADIANS
RANDOM	RANDOM
ROUND	ROUND
SIN	SIN
SIGN	SIGN
SQRT	SQRT
TAN	TAN
TRUNC	TRUNC

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
ASCII	ASCII
BTRIM	TRIM
CHAR_LENGTH	LENGTH
CHARACTER_LENGTH	LENGTH
CHR	CHR
CONCAT	CONCAT
INITCAP	INITCAP
LEFT/RIGHT	LEFT/RIGHT Notes: For negative lengths in LEFT/RIGHT, Snowflake returns an empty string; Greenplum raises an error.
LOWER	LOWER
OCTET_LENGTH	OCTET_LENGTH Notes: the results may vary between platforms (See SSC-FDM-PG0013).
QUOTE_IDENT (string)	CONCAT ('"', string, '"')
REGEXP_REPLACE	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).
REPEAT	REPEAT
REPLACE	REPLACE
REVERSE	REVERSE
SPLIT_PART	SPLIT_PART Notes: Snowflake and Greenplum handle SPLIT_PART differently with case-insensitive collations.
STRPOS (string, substring )	POSITION ( <expr1> IN <expr> )
SUBSTRING	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).
TRANSLATE	TRANSLATE
TRIM	TRIM Notes: Greenplum uses keywords (BOTH, LEADING, TRAILING) for trim; Snowflake uses TRIM, LTRIM, RTRIM.
UPPER	UPPER

Window functions

Greenplum	Snowflake
AVG	AVG Notes: AVG rounding/formatting can vary by data type between Redshift and Snowflake.
COUNT	COUNT
DENSE_RANK	DENSE_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
FIRST_VALUE	FIRST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.
LAG	LAG
LAST_VALUE	LAST_VALUE Notes: Snowflake needs ORDER BY; missing clauses get ORDER BY <expr>.
LEAD	LEAD Notes: Redshift allows constant or expression offsets; Snowflake allows only constant offsets.
NTH_VALUE	NTH_VALUE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
NTILE	NTILE Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1. (See SSC-FDM-PG0013).
PERCENT_RANK	PERCENT_RANK Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.
PERCENTILE_CONT	PERCENTILE_CONT Notes: Rounding varies between platforms.
PERCENTILE_DISC	PERCENTILE_DISC
RANK	RANK
ROW_NUMBER	ROW_NUMBER Notes: ORDER BY is mandatory in Snowflake; missing clauses are replaced with ORDER BY 1.

Known Issues

1. For more information about quoted identifiers in functions, click here.

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.