Redshift to Snowflake: function mapping
2 of 8 common Redshift functions need a change in Snowflake. Mapping table with Snowflake syntax and behavior differences.
Other targets: Redshift to Databricks · Redshift to BigQuery
Needs a rewrite (1)
No Snowflake function with the same behavior; use the construct shown.
| Redshift | Snowflake | Notes |
|---|---|---|
| APPROXIMATE COUNT(DISTINCT) | Rewrite APPROX_COUNT_DISTINCT(expr) | HyperLogLog estimate. The estimates differ slightly between engines, so do not compare approximate counts across systems for exact equality during validation; compare with an exact COUNT(DISTINCT) on a sample instead. |
Different name (1)
Same behavior under another name; rename the call and check the notes.
| Redshift | Snowflake | Notes |
|---|---|---|
| JSON_EXTRACT_PATH_TEXT | Different name JSON_EXTRACT_PATH_TEXT(column, 'a.b') | One path string in dotted notation, for example 'customer.address.city'; returns NULL when the path does not exist. JSON_EXTRACT_PATH_TEXT(js, 'customer', 'address', 'city') becomes a single path: 'customer.address.city' in Snowflake, '$.customer.address.city' in Databricks and BigQuery. Redshift fails on invalid JSON unless null_if_invalid is true. Check how the target treats invalid input before relying on errors to catch bad data. |
Works as written (6)
The same call runs, but check the notes for behavior differences.
| Redshift | Snowflake | Notes |
|---|---|---|
| CONVERT_TIMEZONE | Same name CONVERT_TIMEZONE(source_tz, target_tz, source_timestamp_ntz) | The three-argument form returns TIMESTAMP_NTZ. The two-argument form takes a TIMESTAMP_TZ or LTZ value and returns TIMESTAMP_TZ. CONVERT_TIMEZONE('Asia/Shanghai', ts) in Redshift converts from UTC. Rewrite it as the three-argument CONVERT_TIMEZONE('UTC', 'Asia/Shanghai', ts) before migrating; it means the same thing in Redshift, Snowflake and Databricks. |
| DATEDIFF | Same name DATEDIFF(date_or_time_part, expr1, expr2) | Same argument order and the same boundary counting. |
| GETDATE | Same name GETDATE() | Alias of CURRENT_TIMESTAMP, returned in the session time zone (TIMEZONE parameter) as TIMESTAMP_LTZ. Redshift GETDATE returns TIMESTAMP without time zone. Snowflake returns TIMESTAMP_LTZ in the session time zone; if the account default is not UTC, set TIMEZONE = 'UTC' on the session or user to keep the old values. TRUNC(GETDATE()) is the Redshift idiom for today; use CURRENT_DATE in all three targets. |
| LISTAGG | Same name LISTAGG([DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Ignores NULL values; default delimiter is an empty string. Redshift's DISTINCT ignores trailing spaces ('a' and 'a ' count as one value); the targets treat them as different values. |
| MEDIAN | Same name MEDIAN(expr) | Aggregate or window function; with an even count it returns the average of the two middle values. |
| SPLIT_PART | Same name SPLIT_PART(string, delimiter, partNumber) | Negative numbers count from the end; out-of-range positions return an empty string. |