Workload

Spark SQL ETL pipelines to BigQuery

Re-home Spark SQL pipelines—staging, windowed dedupe, incremental apply, and late-data reprocessing—into BigQuery with an explicit run contract and validation gates that prevent KPI drift and scan-cost surprises.

Book assessment Explore workloads

Quick answer

Re-home Spark SQL pipelines—staging, windowed dedupe, incremental apply, and late-data reprocessing—into BigQuery with an explicit run contract and validation gates that prevent KPI drift and scan-cost surprises.

Back to pair page

Context

Why this breaks

Spark SQL pipelines encode correctness in operational behavior: partition overwrite conventions, incremental windows, dedupe logic implemented via window functions, and retry semantics that “usually work” because of job structure. When migrated naïvely, teams translate queries but lose the run contract—keys, ordering/tie-breakers, late-arrival policy, and restartability—so outputs drift after cutover. Common symptoms after migration:

  • Duplicates or missing updates because dedupe keys and tie-breakers were implicit - Late-arrival events double-counted or silently ignored because reprocessing windows weren’t recreated - Full reprocessing replaces incremental apply, causing missed SLAs and cost spikes - Scan-cost surprises because pruning-aware staging boundaries weren’t designed - Orchestration changes retries/ordering, turning failures into silent data issues

Approach

How conversion works

  • Inventory & classify pipelines: Spark SQL jobs/models, staging zones, schedules, and orchestration dependencies. - Extract the run contract: business keys, deterministic ordering/tie-breakers, dedupe rule, late-arrival window policy, and restart semantics. - Re-home transformations to BigQuery-native staging (landing → typed staging → dedupe → apply) with partitioning/clustering aligned to load windows. - Implement late-data behavior explicitly: define reprocessing windows and staged re-apply so outcomes match without full refreshes. - Re-home orchestration: Composer/Airflow/dbt/your runner with explicit DAG dependencies, retries, alerts, and concurrency posture. - Gate cutover with evidence: golden outputs + incremental integrity simulations (reruns, backfills, late injections) and rollback-ready criteria.

Coverage

Supported constructs

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

SourceTargetNotes
Partition overwrite pipelinesPartition-scoped apply (MERGE or overwrite-by-partition)Preserve overwrite semantics without full-table refreshes.
ROW_NUMBER-based dedupeDeterministic dedupe with explicit tie-breakersPrevents nondeterministic drift under retries.
Late-data reprocessing windowsExplicit late-arrival policy + staged re-applyValidated via late-injection simulations.
SCD Type-1 / Type-2 patternsMERGE + current-flag/end-date patternsBackfills and late updates tested as first-class scenarios.
Semi-structured parsing in SparkTyped extraction boundaries in BigQueryExtract once, cast once, reuse everywhere.
Spark orchestrationComposer/dbt DAGs with explicit contractsRetries, alerts, and concurrency posture modeled.

Compare

How workload changes

TopicSpark SQLBigQueryNotes
Incremental correctnessOften relies on partition overwrite and implicit reprocessingExplicit late-window policy + staged apply with integrity gatesCorrectness becomes auditable and repeatable under retries/backfills.
Cost modelCluster runtime and shuffle patternsBytes scanned + slot time + pruning effectivenessApply windows and layout alignment keep spend stable.
OrchestrationJob frameworks and scriptsComposer/dbt orchestration with explicit DAG contractsRetries and alerts become first-class artifacts.

Examples

Examples

Canonical BigQuery apply pattern for incremental Spark-style pipelines: stage → dedupe deterministically → MERGE with scoped partitions + applied-window tracking. Adjust keys, partitions, and casts to your model.

01_applied_windows.sql
-- Applied-window tracking (restartability)
CREATE TABLE IF NOT EXISTS `proj.control.applied_windows` (
  job_name STRING NOT NULL,
  window_start TIMESTAMP NOT NULL,
  window_end TIMESTAMP NOT NULL,
  applied_at TIMESTAMP NOT NULL
);
02_stage_dedupe_apply.sql
-- Stage → dedupe (deterministic) → apply (partition-scoped)
CREATE TEMP TABLE stg AS
SELECT
  CAST(id AS STRING) AS id,
  CAST(status AS STRING) AS status,
  CAST(amount AS NUMERIC) AS amount,
  event_ts,
  SAFE_CAST(src_offset AS INT64) AS src_offset,
  ingested_at
FROM `proj.raw.orders`
WHERE event_ts >= @window_start AND event_ts < @window_end;

CREATE TEMP TABLE stg_dedup AS
SELECT *
FROM stg
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY id
  ORDER BY event_ts DESC, src_offset DESC, ingested_at DESC
) = 1;

DECLARE min_d DATE DEFAULT (SELECT MIN(DATE(event_ts)) FROM stg_dedup);
DECLARE max_d DATE DEFAULT (SELECT MAX(DATE(event_ts)) FROM stg_dedup);

MERGE `proj.mart.fact_orders` t
USING stg_dedup s
ON t.id = s.id
AND DATE(t.updated_at) BETWEEN min_d AND max_d
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);
03_mark_applied.sql
-- Mark window applied (idempotency marker)
INSERT INTO `proj.control.applied_windows` (job_name, window_start, window_end, applied_at)
VALUES (@job_name, @window_start, @window_end, CURRENT_TIMESTAMP());

Workload Assessment

Migrate Spark pipelines with the run contract intact

We inventory your Spark SQL pipelines, formalize dedupe and late-data semantics, migrate a representative pipeline end-to-end, and produce parity evidence with cutover gates—without scan-cost surprises.

Book assessment

Avoid

Common pitfalls

  • Non-deterministic dedupe: ROW_NUMBER without stable tie-breakers causes drift under retries.
  • Late-arrival policy implicit: Spark relied on partition overwrite; BigQuery version becomes append-only.
  • Unbounded apply: MERGE or refresh touches too much history each run (scan bytes spikes).
  • Type drift in staging: Spark implicit casts must be made explicit for stable BigQuery outputs.
  • Schema evolution surprises: upstream fields widen/change; typed targets break without drift policy.
  • Pruning defeated: filters wrap partition columns or cast in WHERE, preventing partition elimination.
  • Orchestration mismatch: concurrency/dependency ordering changes create freshness drift.

Proof

Validation approach

  • Execution checks: pipelines run reliably under representative volumes and schedules.
  • Structural parity: partition/window-level row counts and column profiles (null/min/max/distinct) for key tables.
  • KPI parity: aggregates by key dimensions for critical marts and dashboards.
  • Incremental integrity (mandatory): - _Idempotency:_ rerun same window → no net change - _Late-arrival:_ inject late updates → only expected rows change - _Backfill safety:_ replay historical windows → stable dedupe and SCD logic - _Dedupe stability:_ duplicates eliminated consistently under retries
  • Cost/performance gates: pruning verified; scan bytes/runtime thresholds set for top jobs.
  • Operational readiness: retry/alerting tests, canary gates, and rollback criteria defined before cutover.

Execution

Migration steps

A sequence that keeps pipeline correctness measurable and cutover controlled.

  1. 01

    Inventory pipelines, schedules, and dependencies

    Extract pipeline graph: Spark SQL jobs, staging zones, upstream feeds, orchestration DAGs, schedules, and SLAs. Identify business-critical marts and consumers.

  2. 02

    Formalize the run contract

    Define business keys, deterministic ordering/tie-breakers, dedupe rules, late-arrival window policy, and backfill boundaries. Make restartability explicit.

  3. 03

    Rebuild transformations on BigQuery-native staging

    Implement landing → typed staging → dedupe → apply, with partitioning/clustering aligned to load windows and BI access paths. Define schema evolution policy (widen/quarantine/reject).

  4. 04

    Re-home orchestration and operations

    Implement DAGs in Composer/Airflow/dbt: dependencies, retries, alerts, and concurrency posture. Add applied-window tracking and failure handling.

  5. 05

    Run parity and incremental integrity gates

    Golden outputs + KPI aggregates, idempotency reruns, late-data injections, and backfill windows. Cut over only when thresholds pass and rollback criteria are defined.

FAQ

Frequently asked questions

Is migrating Spark SQL ETL just translating SQL? +

No. The critical work is preserving the run contract: keys, ordering/tie-breakers, dedupe rules, late-arrival policy, and restartability under retries/backfills.

How do you preserve partition overwrite semantics from Spark jobs? +

We implement partition-scoped apply (MERGE or overwrite-by-partition), with applied-window tracking and integrity tests so reruns are safe and outcomes match.

What about late-arriving data? +

We convert it into an explicit late-arrival window policy and staged re-apply strategy, then validate with late-injection simulations to prove only expected rows change.

How do you prevent BigQuery cost surprises? +

We design pruning-aware staging boundaries and choose partitioning/clustering aligned to load windows. Validation includes scan bytes/runtime baselines and regression thresholds for the top jobs.

Migration Acceleration

Cut over pipelines with proof-backed gates

Get an actionable migration plan with incremental integrity tests (reruns, late data, backfills), reconciliation evidence, and cost/performance baselines—so pipeline cutover is controlled and dispute-proof.

Book assessment
Book assessment