Redshift to Databricks: function mapping
2 of 8 common Redshift functions need a change in Databricks. Mapping table with Databricks syntax and behavior differences.
Other targets: Redshift to Snowflake · Redshift to BigQuery
Needs a rewrite (2)
No Databricks function with the same behavior; use the construct shown.
| Redshift | Databricks | Notes |
|---|---|---|
| APPROXIMATE COUNT(DISTINCT) | Rewrite approx_count_distinct(expr [, relativeSD]) | relativeSD sets the allowed relative error. 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. |
| JSON_EXTRACT_PATH_TEXT | Rewrite get_json_object(expr, '$.a.b') | Returns NULL when the path is missing. For new code Databricks recommends VARIANT columns and the : operator. 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 | Databricks | Notes |
|---|---|---|
| CONVERT_TIMEZONE | Same name convert_timezone([sourceTz, ]targetTz, sourceTs) | Databricks SQL and Databricks Runtime 13.3 LTS and above. Without sourceTz the session time zone is the source. 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(unit, start, end) | Synonym of timestampdiff (Databricks Runtime 10.4 LTS and above); counts whole elapsed units. Month, quarter and year differences can be 1 lower than in Redshift, because Databricks only counts a month once the day and time reach the start value. |
| GETDATE | Same name getdate() | Synonym of current_timestamp(), evaluated once at the start of the query. 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([ALL | DISTINCT] expr [, delimiter]) [WITHIN GROUP (ORDER BY ...)] | Databricks SQL and Databricks Runtime 16.4 and later; older runtimes use array_join(collect_list(expr), delimiter). Redshift's DISTINCT ignores trailing spaces ('a' and 'a ' count as one value); the targets treat them as different values. |
| MEDIAN | Same name median([ALL | DISTINCT] expr) | Databricks SQL and Databricks Runtime 11.3 LTS and above; also usable as a window function. |
| SPLIT_PART | Same name split_part(str, delim, partNum) | Databricks SQL and Databricks Runtime 11.3 LTS and above; negative numbers count from the end, out of range returns an empty string. |