Workload

Databricks SQL queries to BigQuery

Translate Spark SQL idioms—MERGE/upserts, window logic, array/map patterns, and timezone handling—into BigQuery Standard SQL with validation gates that prevent semantic drift and scan-cost surprises.

Book assessment Explore workloads

Quick answer

Translate Spark SQL idioms—MERGE/upserts, window logic, array/map patterns, and timezone handling—into BigQuery Standard SQL with validation gates that prevent semantic drift and scan-cost surprises.

Back to pair page

Context

Why this breaks

Databricks SQL estates are typically Spark SQL plus Delta-centric patterns. Many queries translate syntactically, but drift happens when implicit semantics differ: type coercion inside CASE/COALESCE, NULL behavior in joins, timestamp/timezone assumptions, and array/map handling. In BigQuery, scan bytes and pruning posture also become first-class concerns. Common symptoms after cutover:

  • KPI drift from window logic and top-N selections with incomplete ordering - MERGE behavior changes because match keys, casts, or update predicates were implicit
  • Arrays/maps and semi-structured fields don’t translate 1:1 - Date/time logic shifts due to timezone assumptions and TIMESTAMP vs DATE intent - Costs spike because Spark-era shapes don’t maximize BigQuery pruning SQL migration must preserve both meaning and a BigQuery-native execution posture.

Approach

How conversion works

  • Inventory & prioritize the SQL corpus (dbt models, notebooks, BI extracts, jobs, views). Rank by business impact and risk patterns (MERGE, windows, arrays/maps, time). - Normalize Spark SQL: identifier/quoting rules, function idioms, and query shapes to reduce dialect noise. - Rewrite with rule-anchored mappings: Spark SQL → BigQuery function equivalents, explicit cast strategy, and deterministic ordering for windowed filters. - Apply pattern libraries for high-risk constructs: MERGE/upserts, late-arrival dedupe, arrays/maps, timezone normalization, and null-safe comparisons. - Validate with gates: compilation, catalog/type alignment, and golden-query parity (plus edge cohorts for ties/null-heavy segments). - Performance-safe refactors: pruning-aware filters, join strategy adjustments, and pre-aggregation/materialization guidance for expensive BI queries.

Coverage

Supported constructs

Representative Databricks/Spark SQL constructs we commonly convert to BigQuery Standard SQL (exact coverage depends on your estate).

SourceTargetNotes
Spark SQL window filters (ROW_NUMBER/RANK)BigQuery window functions + QUALIFYDeterministic ordering and tie-breakers enforced.
Delta MERGE and upsert queriesBigQuery MERGE with staged applyMatch keys, casts, and update predicates made explicit and testable.
explode/arrays/maps/structsUNNEST + ARRAY/STRUCT handling (or normalized staging)Empty-array semantics preserved intentionally.
DATE/TIMESTAMP arithmeticBigQuery date/time functionsTimezone intent normalized explicitly.
NULL-safe comparisonsExplicit null-safe equality patternsPrevents join drift and duplicate inserts.
String/regex functionsBigQuery string/regex equivalentsEdge-case behavior validated via golden cohorts.

Compare

How workload changes

TopicDatabricks / Spark SQLBigQueryNotes
Execution assumptionsShuffle-heavy plans and partition overwrite patterns commonPruning-first filters + partitioning/clustering alignmentBigQuery cost is dominated by bytes scanned; pruning is a correctness gate.
Semi-structured handlingStruct/array/map types and explode patternsARRAY/STRUCT + UNNEST or JSON-based modelingChoose typed modeling vs JSON-centric boundaries deliberately.
Time semanticsTimezone assumptions often implicitExplicit timezone conversions and DATE vs TIMESTAMP intentBoundary-day reporting must be tested explicitly.

Examples

Examples

Representative Databricks → BigQuery rewrites for windowed dedupe, explode/unnest, and MERGE patterns. Adjust keys, paths, and casts to your model.

01_windowed_dedupe_databricks.sql
-- Databricks: latest row per key
SELECT *
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY business_key
  ORDER BY event_ts DESC
) = 1;
01_windowed_dedupe_bigquery.sql
-- BigQuery: enforce deterministic tie-breakers
SELECT *
FROM `proj.ds.events`
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY business_key
  ORDER BY event_ts DESC, SAFE_CAST(src_offset AS INT64) DESC, ingested_at DESC
) = 1;
02_explode_databricks.sql
-- Databricks: explode array
SELECT o.id, i.item_id
FROM orders o
LATERAL VIEW explode(o.items) e AS i;
02_unnest_bigquery.sql
-- BigQuery: UNNEST
SELECT o.id, i.item_id
FROM `proj.ds.orders` o
CROSS JOIN UNNEST(o.items) AS i;
03_merge_bigquery.sql
-- BigQuery MERGE (staged apply)
MERGE `proj.mart.fact_orders` t
USING `proj.ds.stg_dedup` s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET
  t.status = s.status,
  t.amount = s.amount,
  t.updated_at = s.event_ts
WHEN NOT MATCHED THEN
  INSERT (id, status, amount, updated_at)
  VALUES (s.id, s.status, s.amount, s.event_ts);

Workload Assessment

Translate Databricks SQL with parity and pruning gates

We inventory your SQL estate, convert a representative slice, and deliver parity evidence on golden queries—plus pruning baselines so BigQuery spend stays predictable.

Book assessment

Avoid

Common pitfalls

  • Type coercion drift: Spark implicit casts inside CASE/COALESCE differ; BigQuery may require explicit casts to preserve intent.
  • NULL semantics in joins: join keys and filters change match behavior if null-safe rules aren’t explicit.
  • Window ordering ambiguity: ROW_NUMBER/RANK without stable tie-breakers causes nondeterministic drift.
  • Array/map handling: Spark explode, map, and nested structs need explicit UNNEST/JSON handling rewrites.
  • Timezone assumptions: session timezone differences change boundary-day reporting unless standardized.
  • Pruning defeated: filters wrap partition columns or cast in WHERE, causing scan bytes to explode.
  • Over-trusting “it runs”: compilation success is not parity; validate with golden outputs and edge cohorts.

Proof

Validation approach

  • Compilation gates: converted queries compile and execute in BigQuery reliably under representative parameters.
  • Catalog/type checks: referenced objects exist; implicit casts are surfaced and made explicit.
  • Golden-query parity: business-critical queries/dashboards match outputs or agreed tolerances.
  • KPI aggregates: compare aggregates by key dimensions (date, region, product, cohorts).
  • Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and timezone transitions.
  • Performance baseline: capture runtime/bytes scanned/slot time for top queries; set regression thresholds.

Execution

Migration steps

A sequence that keeps correctness measurable and prevents semantic drift.

  1. 01

    Collect and prioritize the query estate

    Export dbt models, notebooks, job SQL, view definitions, and BI SQL. Rank by business impact, frequency, and risk patterns (MERGE, windows, arrays/maps, time).

  2. 02

    Define semantic and pruning contracts

    Make tie-breakers, NULL handling, casting strategy, timezone intent, and array/map handling explicit. Define pruning expectations and scan-byte thresholds for top queries.

  3. 03

    Convert with rule-anchored mappings

    Apply deterministic rewrites for common constructs and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, timezone assumptions).

  4. 04

    Validate with golden queries and edge cohorts

    Compile and run in BigQuery, compare KPI aggregates, and run targeted diffs on edge cohorts (ties, null-heavy segments, boundary dates).

  5. 05

    Tune top queries for BigQuery

    Align partitioning/clustering to access paths, enforce pruning-first filters, and recommend materializations for the heaviest BI workloads.

FAQ

Frequently asked questions

Is Databricks SQL directly compatible with BigQuery? +

Not directly. Databricks uses Spark SQL with Delta-centric patterns; BigQuery has different functions, typing behavior, and semi-structured handling. We convert syntax and make semantic intent explicit where drift is common.

What are the biggest drift risks when converting Spark SQL to BigQuery? +

Implicit casts, NULL semantics in joins/CASE branches, timezone assumptions, and window/top-N ordering. These areas get explicit contracts and golden-query validation.

Do you handle MERGE and upsert query patterns? +

Yes. We migrate MERGE logic with explicit match keys, deterministic dedupe, and staged apply patterns, then validate idempotency and KPI parity under reruns and late data.

Will performance be the same after translation? +

Not automatically. Spark-era shapes often need pruning-first rewrites and BigQuery layout alignment (partitioning/clustering). We baseline and tune the top queries as part of this workload.

Migration Acceleration

Ship BigQuery queries with proof-backed sign-off

Get a conversion plan, review markers, and validation artifacts so query cutover is gated by evidence and rollback-ready criteria—without scan-cost surprises.

Book assessment
Book assessment