Translating SQL from BigQuery to Databricks is mostly mechanical — except for a dozen patterns that break consistently and require structural rewrites. This post is the catalog: the twelve, with before/after code for each, and the translator-pass mental model that catches most of them on the first sprint.
Why build a translator instead of translating query by query
Every team that starts a BigQuery → Databricks migration begins by translating queries by hand. It works for the first ten. By query thirty the same five patterns are recurring; mechanical translation pays for itself by query forty.
The translator is a series of named passes — P1, P2, P3, and so on — each performing one specific transform on the SQL. Each pass has a regex pattern that catches the input shape, a transform that produces the output shape, and a unit test that confirms a known broken query becomes a known working one.
Dialect translation is not commutative. You have to do the date_trunc argument reorder before you do the WITH clause flattening, or the regex catches the wrong tokens. The pass model is a deterministic state machine: each pass leaves the SQL in a state the next pass can operate on. Run the passes in order, validate after each one, and you converge to a working query.
Failure 1 — date_trunc argument order
This one shows up first because it shows up in everything. BigQuery puts the date first, the granularity second:
-- BigQuery
SELECT DATE_TRUNC(order_date, MONTH) AS order_month
Databricks puts the granularity first, the date second:
-- Databricks (Spark SQL)
SELECT DATE_TRUNC('MONTH', order_date) AS order_month
Symptom: [UNRESOLVED_ROUTINE] Cannot resolve function date_trunc with arguments (DATE, STRING).
Pattern: \bDATE_TRUNC\(([^,]+),\s*([A-Z]+)\)\b — capture group 1 is the date, group 2 is the granularity literal. Replace with DATE_TRUNC('$2', $1).
The granularity argument in Databricks must be a quoted string literal, not an unquoted token. Half the broken translations in this class get the order right but forget the quotes.
Failure 2 — DATE_DIFF argument order and naming
BigQuery’s DATE_DIFF(date1, date2, granularity) becomes Databricks’ DATEDIFF(granularity, date2, date1) — note the name change (no underscore in Databricks), the argument reorder, AND the sign flip.
-- BigQuery: days from earlier to later
SELECT DATE_DIFF(CURRENT_DATE(), order_date, DAY) -- positive when order_date is in the past
-- Databricks: days from earlier to later
SELECT DATEDIFF(DAY, order_date, CURRENT_DATE()) -- same sign
Symptom: [UNRESOLVED_ROUTINE] Cannot resolve function date_diff or — worse — silent wrong-sign results.
Spark SQL also accepts datediff(end, start) with two arguments and an implicit DAY. If you don’t standardize, half your translated queries will have implicit-DAY math hidden in them. The pass always forces the three-argument form.
Failure 3 — TIMESTAMP_DIFF doesn’t exist
BigQuery has TIMESTAMP_DIFF(timestamp1, timestamp2, granularity). Databricks doesn’t. Translate to UNIX_TIMESTAMP math or use DATEDIFF with hour/minute granularity.
-- BigQuery
SELECT TIMESTAMP_DIFF(end_at, start_at, HOUR)
-- Databricks (equivalent)
SELECT (UNIX_TIMESTAMP(end_at) - UNIX_TIMESTAMP(start_at)) / 3600
-- Or, for whole-hour math:
SELECT DATEDIFF(HOUR, start_at, end_at)
The translation depends on whether the granularity is whole-unit (HOUR, DAY) or sub-unit (SECOND, MILLISECOND). Two patterns — one for whole-unit, one for sub-unit — route based on the granularity token.
Failure 4 — INFORMATION_SCHEMA.JOBS_BY_PROJECT doesn’t exist in Databricks
This one breaks every cost-analytics and meta-query script you have. Databricks’ equivalent is system.query.history, joined to system.billing.usage. The columns don’t line up one-to-one.
-- BigQuery (cost per user, last 30 days)
SELECT user_email, SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY user_email;
-- Databricks (cost per user, last 30 days)
SELECT
qh.executed_by,
SUM(usage.usage_quantity * lp.pricing.default) AS approx_usd
FROM system.query.history qh
JOIN system.billing.usage usage
ON qh.workspace_id = usage.workspace_id
AND DATE(qh.start_time) = usage.usage_date
JOIN system.billing.list_prices lp
ON usage.sku_name = lp.sku_name AND usage.usage_unit = lp.usage_unit
WHERE qh.start_time >= CURRENT_TIMESTAMP() - INTERVAL 30 DAYS
GROUP BY qh.executed_by;
There is no clean regex translation for this. Hand-rewrite, document the mapping, and add a meta-queries.sql file to your repo that everyone uses for cost analytics on the Databricks side. If you don’t centralize, every analyst writes their own slightly-wrong version.
Failure 5 — Nested WITH clauses and the ambiguous-column trap
BigQuery’s optimizer is forgiving about ambiguous column references inside nested WITH clauses. Spark SQL’s is not.
-- BigQuery: works (column "id" resolves to the inner CTE)
WITH outer_cte AS (
WITH inner_cte AS (
SELECT id, name FROM customers
)
SELECT * FROM inner_cte
)
SELECT id FROM outer_cte;
-- Databricks: AMBIGUOUS_REFERENCE if any column appears in multiple
-- scopes without aliasing — even when humans can tell what it means
Symptom: [AMBIGUOUS_REFERENCE] Reference 'id' is ambiguous.
Explicit aliasing at every CTE boundary fixes this. The pass wraps every SELECT * inside a CTE with explicit column lists derived from the source table’s schema. SELECT * is also a translator-pass anti-pattern generally — see Failure 9.
Failure 6 — Trailing commas
BigQuery is permissive about trailing commas in column lists. Spark SQL is not.
-- BigQuery: works
SELECT
user_id,
order_id,
amount,
FROM orders
-- Databricks: PARSE_SYNTAX_ERROR near 'FROM'
Pattern: ,\s*FROM\b → \n FROM. Trivial regex. This failure shows up often enough that it earned its own pass number — P11.
Failure 7 — ARRAY_AGG semantics
BigQuery’s ARRAY_AGG(x ORDER BY y LIMIT 10 IGNORE NULLS) is rich. Databricks’ array_agg(x) is the lowest-common-denominator version, plus collect_list(x) (preserves dupes, no order), plus collect_set(x) (dedups). The ORDER BY and LIMIT clauses inside ARRAY_AGG don’t translate.
The fix is restructuring the query:
-- BigQuery
SELECT user_id, ARRAY_AGG(order_id ORDER BY created_at DESC LIMIT 10) AS recent_orders
FROM orders
GROUP BY user_id;
-- Databricks: pre-sort and limit with a window function, then aggregate
WITH ranked AS (
SELECT user_id, order_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
)
SELECT user_id, COLLECT_LIST(order_id) AS recent_orders
FROM ranked
WHERE rn <= 10
GROUP BY user_id;
This one has no clean regex — it requires query restructuring. The pass catches it with a warn-only flag on every ARRAY_AGG containing ORDER BY or LIMIT and routes it to a human.
Failure 8 — STRUCT and UNNEST
BigQuery uses STRUCT and UNNEST heavily. Databricks uses STRUCT and EXPLODE. The semantics are similar but the syntax for laterally-correlated unnesting is different.
-- BigQuery
SELECT order_id, item.name, item.qty
FROM orders, UNNEST(line_items) AS item
-- Databricks
SELECT order_id, item.name, item.qty
FROM orders
LATERAL VIEW EXPLODE(line_items) AS item
-- (or in SQL-2023 mode: LATERAL UNNEST)
Symptom: [UNRESOLVED_COLUMN] Cannot resolve 'item.name' from a missing LATERAL VIEW.
The translation is mechanical but requires recognizing the comma-then-UNNEST shape, which is brittle in regex. Use a SQL parser (sqlglot) for this pass rather than regex.
Failure 9 — SELECT * semantics with column ordering
In BigQuery, SELECT * returns columns in the order they were defined in the source table. In Databricks Delta tables, SELECT * returns columns in the order they exist at query time, which may be different if the table has had columns added, dropped, and re-added. Downstream queries that depend on positional column ordering — rare, but it happens, especially with INSERT INTO ... SELECT * — break silently.
Ban SELECT * from translated queries entirely. The pass expands it into an explicit column list at translation time. This also fixes Failure 5.
Failure 10 — GROUP BY ROLLUP and GROUP BY CUBE differences
BigQuery accepts GROUP BY ROLLUP(a, b, c). Databricks accepts the same syntax but produces a different NULL pattern for the “subtotal” rows. If your downstream queries filter on WHERE x IS NULL to find rollup rows, you get different result sets.
Use GROUPING(x) = 1 instead of x IS NULL to identify rollup rows. Cross-platform safe; works in both.
Failure 11 — QUALIFY clause
BigQuery’s QUALIFY (filtering on window function results) is supported in Databricks SQL since 2023, but only on Photon-accelerated warehouses. If your migration targets a classic SQL warehouse, translate QUALIFY to a wrapping subquery.
-- BigQuery
SELECT order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
QUALIFY rn = 1;
-- Databricks (classic warehouse safe)
SELECT order_id FROM (
SELECT order_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) WHERE rn = 1;
Trivial restructure. Mechanical pass.
Failure 12 — SAFE_DIVIDE and SAFE_CAST
BigQuery’s SAFE_DIVIDE(a, b) returns NULL on divide-by-zero. Databricks doesn’t have this; the equivalent is IF(b = 0, NULL, a / b) or — if you’re on Databricks Runtime 14+ — try_divide(a, b).
SAFE_CAST(x AS INT) becomes try_cast(x AS INT). Same idea: gives NULL instead of throwing on bad input.
Pattern: SAFE_DIVIDE\((.+?),\s*(.+?)\) → try_divide($1, $2). Mechanical.
The translator-pass mental model
Thirteen passes cover the bulk of the dialect translation work — P1 through P13:
PASSES = [
P1_date_trunc_argument_order,
P2_date_diff_rename,
P3_timestamp_diff_to_unix_math,
P4_information_schema_routing, # warn-only, requires meta-queries.sql
P5_select_star_expansion, # expands * to explicit columns
P6_ambiguous_column_aliasing, # needs schema awareness
P7_lateral_view_explode, # requires sqlglot parser
P8_trailing_comma_fix,
P9_array_agg_warning, # warn-only, routes to human
P10_qualify_to_subquery,
P11_safe_divide_translation,
P12_safe_cast_translation,
P13_struct_unnest_routing,
]
def translate(sql):
for pass_fn in PASSES:
sql, warnings = pass_fn(sql)
if warnings:
log_warnings(warnings)
return sql
Each pass has a unit test that confirms a known broken query becomes a known working one. Each pass is independent. Adding a new failure mode means adding a new pass at the right position in the order — not modifying existing passes. When your team finds the next failure mode, they add P14 and ship.
Run the translator over the entire query corpus at the start of any migration sprint, validate the output against the source, and feed the diffs back into the pass library. Patterns that show up across migrations stay in the library; one-off patterns get a hand-fix and a note in the project README.
A word about INFORMATION_SCHEMA
The single most disruptive failure mode in any BigQuery → Databricks migration is INFORMATION_SCHEMA. Every cost analytics script, every “find the orphaned tables” query, every audit job uses it. None of it translates. You will rewrite all of it. Plan for this in the scope; do not try to translate it line-by-line.
Treat it as a separate workstream: a meta-queries.sql file in the repo that contains the Databricks-native equivalents (system.query.history, system.billing.usage, information_schema.tables for Delta, DESCRIBE EXTENDED for individual tables). Every team member uses the same file; no analyst writes their own version.
What’s coming in part 3
Part 3 is the cutover itself — staged wave plans, dedup-loser identification, the legacy second-writer trap, and the DRY_RUN green but TABLE_OR_VIEW_NOT_FOUND in PROD gotcha that appears in every migration.