Workload

Validation & reconciliation for Oracle → BigQuery

Turn “it compiles” into a measurable parity contract. We prove correctness for batch and incremental Oracle-fed systems—including watermark/SCN behavior—then gate cutover with evidence and rollback-ready criteria.

Book assessment Explore workloads

Quick answer

Turn “it compiles” into a measurable parity contract. We prove correctness for batch and incremental Oracle-fed systems—including watermark/SCN behavior—then gate cutover with evidence and rollback-ready criteria.

Back to pair page

Context

Why this breaks

Oracle migrations fail late when teams validate only syntax and a few reports. Oracle systems encode correctness in operational behavior: SCN/watermark rules, change ordering, constraints/triggers, and rerun/backfill conventions. BigQuery can implement equivalent outcomes, but only if these rules are made explicit and validated under retries, late updates, and partial failures. Common drift drivers in Oracle → BigQuery:

  • Watermark/SCN drift: wrong high-water mark selection or non-durable state leads to missed/duplicate changes
  • MERGE semantics drift: match keys, casts, and NULL/empty-string intent differ
  • Ordering ambiguity: CDC events need deterministic tie-breakers; otherwise reruns choose different winners
  • Trigger side effects lost: audits/derived fields/control table updates disappear unless recreated
  • SCD drift: end-dating/current-flag logic breaks under backfills and late updates Validation must treat the system as an incremental, operational workload, 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). - Define the incremental contract: watermarks (SCN/timestamp), ordering/tie-breakers, dedupe rule, late-arrival window policy, and restart semantics. - 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 (routines, DQ checks), and job reliability. - Run layered parity gates: counts/profiles → KPI diffs → targeted row-level diffs where needed. - Validate incremental integrity (mandatory for CDC/upserts): 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 Oracle → BigQuery migrations.

SourceTargetNotes
Golden queries and reportsGolden query harness + repeatable parameter setsCodifies business sign-off into runnable tests.
Watermarks (SCN/timestamp)Durable control tables + restart simulationsPrevents missed/duplicate changes under retries.
KPI validationAggregate diffs by key dimensions + tolerance thresholdsAligns validation with business meaning.
Counts and column profilesPartition-level counts + null/min/max/distinct profilesCheap early drift detection before deep diffs.
Incremental behaviorIdempotency + late-arrival + backfill simulationsProves correctness under operational stress.
Operational sign-offCanary gates + rollback criteria + monitorsPrevents cutover from becoming a long KPI debate.

Compare

How workload changes

TopicOracleBigQueryNotes
Correctness stateSCN/watermark logic often embedded in tools and scriptsDurable control tables + explicit replay semanticsValidation must include restart and watermark progression tests.
Data quality enforcementConstraints/triggers can enforce invariants implicitlyExplicit DQ gates and drift policiesValidation checks replace implicit Oracle enforcement.
Cutover evidenceOften based on limited report checksLayered gates + rollback triggersCutover becomes measurable, repeatable, dispute-proof.

Examples

Examples

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

01_window_row_counts.sql
-- Window-level row counts
SELECT
  DATE(updated_at) AS d,
  COUNT(*) AS rows
FROM `proj.mart.fact_orders`
WHERE DATE(updated_at) 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, region, 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, region, 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.region, tgt.region) AS region,
  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, region)
ORDER BY d, region;
03_checksum_aggregate.sql
-- Checksum-style aggregate (approximate)
SELECT
  DATE(updated_at) 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(updated_at) 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 and watermark behavior measurable before cutover

We define parity + incremental contracts, build golden queries, and implement layered reconciliation gates—including watermark/restart simulations—so Oracle→BigQuery cutover is evidence-based.

Book assessment

Avoid

Common pitfalls

  • Validating only the backfill: parity on a static snapshot doesn’t prove correctness under reruns and late updates.
  • No durable watermark: using job runtime or ad-hoc queries instead of persisted SCN/high-water marks.
  • No ordering tie-breakers: CDC events processed nondeterministically; reruns drift.
  • Ignoring triggers/constraints: data quality degrades silently when Oracle enforcement disappears.
  • No tolerance model: teams argue about diffs because thresholds weren’t defined upfront.
  • 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/procedures, reference data, DQ checks, control tables) Gate 1 — Execution - Converted jobs compile and run reliably under representative volumes - 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 - Ranking/top-N parity validated on tie/edge cohorts Gate 4 — Incremental integrity (mandatory) - _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 - _Watermark correctness:_ SCN/timestamp advances correctly and is durable Gate 5 — Cutover & monitoring

  • Canary criteria + rollback triggers - Post-cutover monitors: latency, failures, watermark lag, KPI sentinels

Execution

Migration steps

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

  1. 01

    Define parity and incremental contracts

    Decide what must match (tables, dashboards, KPIs) and define tolerances. Make watermarks (SCN/timestamp), ordering, and late-arrival rules explicit.

  2. 02

    Create validation datasets and edge cohorts

    Select representative windows and cohorts that trigger edge behavior (ties, null-heavy segments, boundary dates) and incremental stress cases (late updates).

  3. 03

    Implement layered gates

    Start with cheap checks (counts/profiles), then KPI diffs, then deep diffs only where needed. Add watermark progression and restart simulations for CDC/upsert pipelines.

  4. 04

    Run incremental integrity simulations

    Rerun the same window, simulate partial failure and resume, replay backfills, and inject late updates. Verify only expected rows change and watermarks advance correctly.

  5. 05

    Gate cutover and monitor

    Establish canary/rollback criteria and post-cutover monitors for KPI sentinels, watermark lag, failures, and latency.

FAQ

Frequently asked questions

Is a successful backfill enough to cut over? +

No. Backfills don’t prove correctness under retries, late arrivals, or restart scenarios. For Oracle-fed incremental systems, we require idempotency and watermark/restart simulations as cutover gates.

How do you validate SCN/watermark correctness? +

We require durable control tables, verify watermark progression under normal runs, and run restart simulations to ensure partial failures don’t skip or duplicate changes.

Do we need row-level diffs for everything? +

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

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

Book assessment
Book assessment