Workload

Spark SQL queries to BigQuery

Translate Spark SQL idioms—windowed dedupe, explode/UNNEST patterns, conditional casting, 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—windowed dedupe, explode/UNNEST patterns, conditional casting, 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

Spark SQL is flexible about types and operational patterns: implicit casts in CASE/COALESCE, permissive handling of nested data, and windowed logic that “works” because job structure is stable. BigQuery compiles translated SQL, but drift appears when implicit Spark semantics differ—especially around type coercion, NULL behavior, nested arrays/structs, and timezone handling. In addition, Spark-era query shapes often miss BigQuery pruning opportunities, causing scan-cost surprises. Common symptoms after cutover:

  • KPI drift from window logic and top-N selections with incomplete ordering - Dedupe behavior changes under retries because tie-breakers were implicit
  • Arrays/structs and explode patterns don’t translate 1:1 - Date/time logic shifts due to timezone assumptions and DATE vs TIMESTAMP intent - Costs spike because filters and joins aren’t pruning-friendly SQL migration must preserve both meaning and a BigQuery-native execution posture.

Approach

How conversion works

  • Inventory & prioritize the Spark SQL corpus (jobs, notebooks, dbt models, views, BI SQL). Rank by business impact and risk patterns (windows, explode, nested fields, time, casts). - Normalize Spark SQL: identifier/quoting rules and common idioms to reduce dialect noise. - Rewrite with rule-anchored mappings: Spark functions → BigQuery equivalents, explicit cast strategy, and deterministic ordering for windowed filters. - Nested data strategy: decide when to keep ARRAY/STRUCT vs normalize into typed tables; rewrite explode to UNNEST with intended semantics. - Time normalization: standardize timezone intent and DATE vs TIMESTAMP semantics; test boundary-day cohorts. - Validate with gates: compilation, catalog/type alignment, golden-query parity, and edge-cohort diffs. - Performance-safe refactors: pruning-aware filters, join strategy adjustments, and materialization guidance for expensive BI queries.

Coverage

Supported constructs

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

SourceTargetNotes
Spark window filters (ROW_NUMBER/RANK)BigQuery window functions + QUALIFYDeterministic ordering and tie-breakers enforced.
explode(array) patternsUNNEST with intended empty/NULL semanticsRow count preservation validated on edge cohorts.
ARRAY/STRUCT nested projectionsARRAY/STRUCT or normalized typed stagingChoose modeling strategy explicitly to avoid drift.
DATE/TIMESTAMP arithmeticBigQuery date/time functionsTimezone intent normalized explicitly.
Implicit casts and mixed-type CASEExplicit CAST + SAFE_CAST patternsPrevents branch type drift and join-key mismatch.
String/regex functionsBigQuery string/regex equivalentsEdge-case behavior validated via golden cohorts.

Compare

How workload changes

TopicSpark SQLBigQueryNotes
Typing behaviorImplicit casts common and often toleratedExplicit casts recommended for stable outputsValidation focuses on CASE/COALESCE and join keys.
Nested dataexplode and nested structs commonUNNEST + explicit ARRAY/STRUCT modelingRow count semantics must be chosen intentionally.
Cost modelCluster runtime + shuffle patternsBytes scanned + slot timePruning-first filters and layout alignment drive spend.
Time semanticsTimezone assumptions often implicitExplicit timezone conversions and DATE vs TIMESTAMP intentBoundary-day cohorts are mandatory test cases.

Examples

Examples

Representative Spark SQL → BigQuery rewrites for windowed dedupe and explode/UNNEST. Adjust keys, paths, and casts to your model.

01_windowed_dedupe_spark.sql
-- Spark SQL: 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_spark.sql
-- Spark SQL: 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_empty_array_semantics.sql
-- BigQuery: preserve Spark explode semantics for empty arrays if needed
-- Spark explode on NULL returns 0 rows; on empty array returns 0 rows.
-- If you need to preserve parent rows, use LEFT JOIN UNNEST with IFNULL(items, [])
SELECT o.id, i.item_id
FROM `proj.ds.orders` o
LEFT JOIN UNNEST(IFNULL(o.items, [])) AS i;

Workload Assessment

Translate Spark 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 differ; BigQuery often needs explicit casts for stable outputs.
  • 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.
  • explode vs UNNEST semantics: empty arrays and NULL arrays can change row counts if not handled intentionally.
  • Timezone assumptions: session timezone differences shift boundary-day results unless standardized.
  • Pruning defeated: filters wrap partition columns or cast in WHERE, causing scan bytes explosion.
  • 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 and cohorts.
  • Edge-cohort diffs: validate ties, null-heavy segments, boundary dates, and nested-data edge cases.
  • Pruning/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 job SQL, notebooks, dbt models, view definitions, and BI SQL. Rank by business impact, frequency, and risk patterns (windows, explode, nested fields, time, casts).

  2. 02

    Define semantic and pruning contracts

    Make tie-breakers, NULL handling, casting strategy, timezone intent, and nested-data semantics explicit. Define pruning expectations and scan-byte thresholds for top queries.

  3. 03

    Convert with rule-anchored mappings

    Apply deterministic rewrites for common Spark SQL constructs and flag ambiguous intent with review markers (implicit casts, ordering ambiguity, nested edge behavior).

  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, nested-data edge cases).

  5. 05

    Tune top queries for BigQuery

    Ensure partition filters are pruning-friendly, align partitioning/clustering to access paths, and recommend materializations for the most expensive BI workloads.

FAQ

Frequently asked questions

Is Spark SQL directly compatible with BigQuery? +

Not directly. BigQuery uses Standard SQL with different functions and type behavior. Many constructs translate cleanly, but parity depends on explicit casts, deterministic ordering, and deliberate handling of nested arrays/structs.

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. Nested data (explode/UNNEST) also needs explicit row-count semantics.

Do you handle explode/arrays/structs? +

Yes. We rewrite explode patterns using UNNEST and explicitly choose semantics for NULL/empty arrays and parent-row preservation. These are validated with edge cohorts to prevent silent row-count drift.

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