Workload

Validation & reconciliation for Spark SQL → BigQuery

Turn “it runs” into a measurable parity contract. We prove correctness *and* pruning posture with golden queries, KPI diffs, and replayable integrity simulations—then gate cutover with rollback-ready criteria.

Book assessment Explore workloads

Quick answer

Turn “it runs” into a measurable parity contract. We prove correctness *and* pruning posture with golden queries, KPI diffs, and replayable integrity simulations—then gate cutover with rollback-ready criteria.

Back to pair page

Context

Why this breaks

Spark SQL migrations fail late when teams validate only compilation and a few samples. Spark systems often encode correctness in job structure: partition overwrite conventions, windowed dedupe, and retry behavior that “usually works” because execution is stable. BigQuery can implement equivalent outcomes, but drift and cost spikes appear when ordering, casting, nested-data semantics, and pruning behavior aren’t made explicit and validated under stress. Common drift drivers in Spark SQL → BigQuery:

  • Non-deterministic dedupe: missing tie-breakers in windowed logic causes reruns to choose different winners
  • Type coercion & NULL semantics: CASE/COALESCE and join keys behave differently without explicit casts
  • Nested data semantics: explode vs UNNEST (empty/NULL arrays) can change row counts
  • Timezone/boundary days: DATE vs TIMESTAMP intent and timezone assumptions drift
  • Pruning contract lost: filters defeat partition elimination → scan bytes explode Validation must treat this as an incremental, operational system and include pruning/cost posture as a first-class gate.

Approach

How conversion works

  • Define the parity contract: what must match (tables, dashboards, KPIs) and what tolerances apply (exact vs threshold). - Define the pruning/cost contract: which queries must prune and what scan-byte/slot thresholds are acceptable. - Define semantic contracts for risky patterns: tie-break ordering, explicit casts, nested-data semantics, and timezone intent. - Build validation datasets: golden inputs, edge cohorts (ties, null-heavy segments, nested edge cases), and representative windows (including boundary dates). - Run layered parity gates: counts/profiles → KPI diffs → targeted row-level diffs where needed. - Validate incremental integrity where applicable: idempotency reruns, backfill windows, and late-arrival injections. - Gate cutover: pass/fail thresholds, canary rollout, rollback triggers, and post-cutover monitors.

Coverage

Supported constructs

Representative validation and reconciliation mechanisms we apply in Spark SQL → BigQuery migrations.

SourceTargetNotes
Golden dashboards/queriesGolden query harness + repeatable parameter setsCodifies business sign-off into runnable tests.
Type and NULL drift riskExplicit cast strategy + null-safe comparisonsValidated with edge cohorts and branch tests.
Windowed dedupe patternsDeterministic ordering + tie-breakersProves stability under reruns.
explode/UNNEST semanticsRow-count equivalence tests on nested edge cohortsPrevents silent row-multiplication or row-loss.
Pruning/cost postureScan-byte baselines + pruning verificationTreat cost posture as part of cutover readiness.
Operational sign-offCanary gates + rollback criteria + monitorsMakes cutover dispute-proof.

Compare

How workload changes

TopicSpark SQLBigQueryNotes
Correctness hiding placesJob structure + implicit ordering and castingExplicit contracts + replayable gatesValidation must cover reruns, ties, and edge cohorts.
Nested data behaviorexplode semantics implicitUNNEST semantics must be chosen explicitlyRow-count equivalence becomes a validation gate.
Cost modelCluster runtimeBytes scanned + slot timePruning posture is validated pre-cutover.

Examples

Examples

Illustrative parity and pruning checks in BigQuery. Replace datasets, keys, and KPI definitions to match your Spark SQL migration.

01_partition_row_counts.sql
-- Row counts by partition/window
SELECT
  DATE(event_ts) AS d,
  COUNT(*) AS rows
FROM `proj.mart.events`
WHERE DATE(event_ts) BETWEEN @start_date AND @end_date
GROUP BY 1
ORDER BY 1;
02_kpi_aggregate_diff.sql
-- KPI aggregate comparison (example)
WITH src AS (
  SELECT DATE(event_ts) d, region, SUM(metric_value) v
  FROM `proj.compare.src_metrics`
  WHERE DATE(event_ts) BETWEEN @start_date AND @end_date
  GROUP BY 1,2
), tgt AS (
  SELECT DATE(event_ts) d, region, SUM(metric_value) v
  FROM `proj.compare.tgt_metrics`
  WHERE DATE(event_ts) BETWEEN @start_date AND @end_date
  GROUP BY 1,2
)
SELECT
  COALESCE(src.d, tgt.d) AS d,
  COALESCE(src.region, tgt.region) AS region,
  src.v AS src_v,
  tgt.v AS tgt_v,
  (tgt.v - src.v) AS diff,
  SAFE_DIVIDE((tgt.v - src.v), NULLIF(src.v, 0)) AS diff_pct
FROM src
FULL OUTER JOIN tgt
USING (d, region)
ORDER BY d, region;
03_checksum_aggregate.sql
-- Checksum-style aggregate (approximate)
SELECT
  DATE(event_ts) AS d,
  COUNT(*) AS rows,
  SUM(ABS(FARM_FINGERPRINT(CONCAT(CAST(id AS STRING), '|', CAST(status AS STRING))))) AS fp_sum
FROM `proj.mart.events`
WHERE DATE(event_ts) BETWEEN @start_date AND @end_date
GROUP BY 1
ORDER BY 1;
04_idempotency_rerun_assertion.sql
-- Idempotency gate: compare snapshot metrics before/after rerun
WITH snap AS (
  SELECT
    COUNT(*) c,
    SUM(ABS(FARM_FINGERPRINT(CAST(id AS STRING)))) fp
  FROM `proj.mart.events`
  WHERE DATE(event_ts) = @d
)
SELECT
  s1.c AS before_count, s2.c AS after_count,
  s1.fp AS before_fp, s2.fp AS after_fp,
  IF(s1.c = s2.c AND s1.fp = s2.fp, 'PASS', 'FAIL') AS verdict
FROM snap s1, snap s2;

Workload Assessment

Validate parity and scan-cost before cutover

We define parity + pruning contracts, build golden queries, and implement layered reconciliation gates—so Spark SQL→BigQuery cutover is gated by evidence and scan-cost safety.

Book assessment

Avoid

Common pitfalls

  • Spot-check validation: a few samples miss drift in ties, nested-data edges, and boundary windows.
  • No pruning gate: scan-cost regressions slip through because bytes scanned isn’t validated.
  • Unstable ordering: windowed dedupe lacks tie-breakers; reruns drift.
  • Implicit casts left implicit: BigQuery outputs drift due to branch type changes.
  • Nested semantics ignored: explode→UNNEST changes row counts for empty/NULL arrays.
  • Ignoring reruns/backfills: parity looks fine once but fails under retries and historical replays.
  • Cost-blind diffs: exhaustive row-level diffs can be expensive; use layered gates (cheap→deep).

Proof

Validation approach

### Gate set (layered) Gate 0 — Readiness - Datasets, permissions, and target schemas exist - Dependent assets deployed (UDFs/routines, reference data, control tables) Gate 1 — Execution - Converted jobs compile and run reliably - Deterministic ordering + explicit casts enforced Gate 2 — Structural parity - Row counts by partition/window - Null/min/max/distinct profiles for key columns Gate 3 — KPI parity - KPI aggregates by key dimensions - Rankings/top-N validated on tie/edge cohorts - Nested-data row-count checks for explode→UNNEST equivalence Gate 4 — Pruning & cost posture (mandatory) - Partition filters prune as expected on representative parameters - Bytes scanned and slot time remain within agreed thresholds - Regression alerts defined for scan blowups Gate 5 — Incremental integrity (when applicable) - _Idempotency:_ rerun same window → no net change - _Backfill:_ historical windows replay without drift - _Late-arrival:_ inject late corrections → only expected rows change - _Dedupe stability:_ duplicates eliminated consistently under retries Gate 6 — Cutover & monitoring

  • Canary criteria + rollback triggers - Post-cutover monitors: latency, scan bytes/slot time, failures, KPI sentinels

Execution

Migration steps

A practical sequence for making validation repeatable and scan-cost safe.

  1. 01

    Define parity and cost/pruning contracts

    Decide what must match (tables, dashboards, KPIs) and define tolerances. Identify workloads where pruning is mandatory and set scan-byte/slot thresholds.

  2. 02

    Make semantic contracts explicit

    Define tie-breakers for dedupe/top-N, casting strategy, nested-data semantics (explode vs UNNEST), and timezone intent. These become testable requirements.

  3. 03

    Implement layered gates

    Start with cheap checks (counts/profiles), then KPI diffs, then deep diffs only where needed. Add pruning verification and baseline capture for top workloads.

  4. 04

    Validate incremental integrity

    Run idempotency reruns, backfill windows, and late-arrival injections where applicable. These scenarios typically break if not tested.

  5. 05

    Gate cutover and monitor

    Establish canary/rollback criteria and post-cutover monitors for KPIs and scan-cost sentinels (bytes/slot), plus latency and failures.

FAQ

Frequently asked questions

Why is pruning part of validation for Spark SQL migrations? +

Because BigQuery cost is driven by bytes scanned. A migration can be semantically correct but economically broken if pruning is lost. We validate both parity and scan-cost posture before cutover.

Do we need row-level diffs for everything? +

Usually no. A layered approach is faster and cheaper: start with counts/profiles and KPI diffs, then do targeted row-level diffs only where aggregates signal drift or for critical entities.

How do you validate explode→UNNEST semantics? +

We define the intended row-count semantics (whether parent rows should be preserved) and test nested edge cohorts (NULL arrays, empty arrays, varying cardinalities). This prevents silent row loss or multiplication.

How does validation tie into cutover? +

We convert gates into cutover criteria: pass/fail thresholds, canary rollout, rollback triggers, and post-cutover monitors. Cutover becomes evidence-based and dispute-proof.

Cutover Readiness

Gate cutover with evidence and rollback criteria

Get a validation plan, runnable gates, and sign-off artifacts (diff reports, thresholds, pruning baselines, monitors) so Spark SQL→BigQuery cutover is controlled and dispute-proof.

Book assessment
Book assessment