Workload

Validation & reconciliation for Redshift → BigQuery

Turn “it compiles” into a measurable parity contract. We prove correctness for batch and incremental systems with golden queries, KPI diffs, and replayable integrity simulations—then gate cutover with rollback-ready criteria.

Book assessment Explore workloads

Quick answer

Turn “it compiles” into a measurable parity contract. We prove correctness for batch and incremental systems with golden queries, KPI diffs, and replayable integrity simulations—then gate cutover with rollback-ready criteria.

Back to pair page

Context

Why this breaks

Most Redshift migrations fail late due to undetected semantic drift. Teams validate syntax and a few spot checks, then cut over—only to discover KPI divergence weeks later. Redshift estates often encode behavior in UPSERT conventions (staging + delete/insert), implicit casts, and operational rules around watermarks and reruns. BigQuery can produce equivalent outcomes, but only if the correctness rules are made explicit and tested under stress. Common drift drivers in Redshift → BigQuery:

  • UPSERT drift: delete/insert patterns behave differently under retries and partial failures
  • Implicit casts & NULL semantics: CASE/COALESCE branches and join keys silently cast differently
  • Window/top-N ambiguity: missing tie-breakers changes winners under parallelism
  • Date/time intent: DATE vs TIMESTAMP and boundary days not validated
  • Performance-driven behavior: pruning differences change refresh cadence, which changes what users trust Validation must treat the system as operational and incremental, not a one-time batch.

Approach

How conversion works

  • Define the parity contract: what must match (tables, dashboards, KPIs) and what tolerances apply (exact vs threshold). - Build validation datasets: golden inputs, edge cohorts (ties, null-heavy segments), and representative windows (including boundary dates). - Run readiness + execution gates: schema/type alignment, dependency readiness, and job reliability. - Run layered parity gates: counts/profiles → KPI diffs → targeted row-level diffs where needed. - Validate incremental integrity (mandatory for incremental loads): idempotency reruns, restart simulations, 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 Redshift → BigQuery migrations.

SourceTargetNotes
Golden queries and reportsGolden query harness + repeatable parameter setsCodifies business sign-off into runnable tests.
Counts and column profilesPartition-level counts + null/min/max/distinct profilesCheap early drift detection before deep diffs.
KPI validationAggregate diffs by key dimensions + tolerance thresholdsAligns validation with business meaning.
Row-level diffsTargeted sampling diffs + edge cohort testsUse deep diffs only where aggregates signal drift.
Incremental loads and UPSERT behaviorIdempotency reruns + restart simulations + late-arrival testsProves correctness under retries and partial failures.
Operational sign-offCanary gates + rollback criteria + monitorsPrevents cutover from becoming a long KPI debate.

Compare

How workload changes

TopicRedshiftBigQueryNotes
Drift driversImplicit casts and delete/insert UPSERT conventionsExplicit casts + MERGE semantics + bounded apply windowsValidation focuses on join keys, types, and rerun behavior.
Cost of validationCluster resources + WLMBytes scanned + slot timeUse layered gates to keep validation economical.
Incremental behaviorRerun behavior encoded in scripts/control tablesRerun/backfill behavior must be simulated explicitlyIntegrity simulations are mandatory gates.

Examples

Examples

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

01_partition_row_counts.sql
-- Partition/window row counts (BigQuery)
SELECT
  DATE(event_ts) AS d,
  COUNT(*) AS rows
FROM `proj.mart.fact_orders`
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(order_ts) d, country, SUM(revenue) rev
  FROM `proj.compare.src_orders`
  WHERE DATE(order_ts) BETWEEN @start_date AND @end_date
  GROUP BY 1,2
), tgt AS (
  SELECT DATE(order_ts) d, country, SUM(revenue) rev
  FROM `proj.compare.tgt_orders`
  WHERE DATE(order_ts) BETWEEN @start_date AND @end_date
  GROUP BY 1,2
)
SELECT
  COALESCE(src.d, tgt.d) AS d,
  COALESCE(src.country, tgt.country) AS country,
  src.rev AS src_rev,
  tgt.rev AS tgt_rev,
  (tgt.rev - src.rev) AS diff,
  SAFE_DIVIDE((tgt.rev - src.rev), NULLIF(src.rev, 0)) AS diff_pct
FROM src
FULL OUTER JOIN tgt
USING (d, country)
ORDER BY d, country;
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(amount AS STRING), '|', CAST(status AS STRING))))) AS fp_sum
FROM `proj.mart.fact_orders`
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.fact_orders`
  WHERE DATE(updated_at) = @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

Make parity measurable before you cut over

We define your parity contract, build the golden-query set, and implement layered reconciliation gates—including reruns and backfill simulations—so KPI drift is caught before production cutover.

Book assessment

Avoid

Common pitfalls

  • Spot-check validation: a few samples miss drift in ties and edge cohorts.
  • No tolerance model: teams argue about diffs because thresholds weren’t defined upfront.
  • Wrong comparison level: comparing raw rows when the business cares about rollups (or vice versa).
  • Ignoring reruns/backfills: parity looks fine once but fails under retries and historical replays.
  • Unstable ordering: top-N/window functions without complete ORDER BY.
  • Cost-blind diffs: exhaustive row-level diffs can be expensive; use layered gates (cheap→deep).

Proof

Validation approach

### Gate set (layered) Gate 0 — Readiness - BigQuery datasets, permissions, and target schemas exist - Dependent assets deployed (UDFs/routines, reference tables, 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 and top-N validated on tie/edge cohorts Gate 4 — Integrity (incremental systems) - _Idempotency:_ rerun same window → no net change - _Restart simulation:_ fail mid-run → resume → correct final state - _Backfill:_ historical windows replay without drift - _Late-arrival:_ inject late corrections → only expected rows change Gate 5 — Cutover & monitoring

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

Execution

Migration steps

A practical sequence for making validation repeatable and dispute-proof.

  1. 01

    Define the parity contract

    Decide what must match (tables, dashboards, KPIs), at what granularity, and with what tolerance thresholds. Identify golden queries and sign-off owners.

  2. 02

    Create validation datasets and edge cohorts

    Select representative windows and cohorts that trigger edge behavior (ties, null-heavy segments, boundary dates, rerun scenarios).

  3. 03

    Implement layered gates

    Start with cheap checks (counts/profiles), then KPI diffs, then deep diffs only where needed. Codify gates into runnable jobs so validation is repeatable.

  4. 04

    Validate incremental integrity

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

  5. 05

    Gate cutover and monitor

    Establish canary/rollback criteria and post-cutover monitors for critical KPIs and pipeline health (latency, scan bytes/slot time, failures).

FAQ

Frequently asked questions

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 handle small KPI differences? +

We define tolerance thresholds up front (exact vs %/absolute). If differences exceed thresholds, we trace back using dimensional rollups and targeted sampling to isolate the drift source.

What if our pipelines rely on reruns and backfills? +

Then validation must include simulations: idempotency reruns, restart scenarios, and historical replays. These gates prove the migrated system behaves correctly under operational stress.

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, monitors) so Redshift→BigQuery cutover is controlled and dispute-proof.

Book assessment
Book assessment