Redshift to BigQuery: function mapping
8 of 8 common Redshift functions need a change in BigQuery. Mapping table with BigQuery syntax and behavior differences.
Other targets: Redshift to Snowflake · Redshift to Databricks
Needs a rewrite (7)
No BigQuery function with the same behavior; use the construct shown.
| Redshift | BigQuery | Notes |
|---|---|---|
| APPROXIMATE COUNT(DISTINCT) | Rewrite APPROX_COUNT_DISTINCT(expression) | 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. |
| CONVERT_TIMEZONE | Rewrite DATETIME(TIMESTAMP(ts, 'source_tz'), 'target_tz') | TIMESTAMP(datetime, tz) interprets a DATETIME in a zone; DATETIME(timestamp, tz) renders it in another. 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 | Rewrite DATE_DIFF(end_date, start_date, granularity) | End date first; counts boundaries like Redshift. Swapping the arguments silently changes the sign of the result. |
| GETDATE | Rewrite CURRENT_DATETIME([time_zone]) | DATETIME in UTC unless a time zone is passed. 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. |
| JSON_EXTRACT_PATH_TEXT | Rewrite JSON_VALUE(json_string, '$.a.b') | Returns a scalar as STRING; use JSON_QUERY for objects and arrays. 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. |
| MEDIAN | Rewrite APPROX_QUANTILES(expr, 2)[OFFSET(1)] | Approximate. For an exact median use PERCENTILE_CONT(expr, 0.5) OVER (PARTITION BY ...), which is a window function only. PERCENTILE_CONT cannot be used in GROUP BY; compute it in a window over the group and then take one row per group, for example with QUALIFY or SELECT DISTINCT. |
| SPLIT_PART | Rewrite SPLIT(value, delimiter)[SAFE_OFFSET(n - 1)] | Zero-based offset; out-of-range positions return NULL rather than an empty string. Redshift returns '' for a missing part, BigQuery's SAFE_OFFSET returns NULL. Wrap the result in IFNULL(..., '') where downstream code compares with ''. |
Different name (1)
Same behavior under another name; rename the call and check the notes.
| Redshift | BigQuery | Notes |
|---|---|---|
| LISTAGG | Different name STRING_AGG([DISTINCT] expr [, delimiter] [ORDER BY ...]) | ORDER BY goes inside the parentheses; default delimiter is a comma. Redshift's DISTINCT ignores trailing spaces ('a' and 'a ' count as one value); the targets treat them as different values. WITHIN GROUP is not accepted; move the ORDER BY inside STRING_AGG(...). |