SSC-FDM-RS0006

Strings may cause different behavior in Snowflake.

Description

a. In Redshift, NULL is only returned when all the elements are NULL. In contrast, in Snowflake, if any element is NULL, the functions will return NULL.

b. There are two key differences when using GREATEST and LEAST for strings:

1. Trailing spaces: Snowflake considers trailing spaces, whereas Redshift does not.

2. Collation settings: Different collation settings may yield different results for the same string with varying cases.

Code Example

Input Code:

Redshift

IN -> Redshift_01.sql

SELECT LEAST('GERMANY', 'USD') AS LEAST, GREATEST('GERMANY', 'USD') AS GREATEST;

Output Code:

Snowflake

OUT -> Redshift_01.sql

SELECT
       --** SSC-FDM-RS0006 - STRINGS MAY CAUSE DIFFERENT BEHAVIOR IN SNOWFLAKE. **
       LEAST_IGNORE_NULLS('GERMANY', 'USD') AS LEAST,
       --** SSC-FDM-RS0006 - STRINGS MAY CAUSE DIFFERENT BEHAVIOR IN SNOWFLAKE. **
       GREATEST_IGNORE_NULLS('GERMANY', 'USD') AS GREATEST;

Recommendations

• If you need more support, you can email us at [e-mail]