Workload

Validation & reconciliation for Databricks → Snowflake

Turn “it runs” into a measurable parity contract. We prove correctness for Delta MERGE and incremental systems with golden queries, KPI diffs, and 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 for Delta MERGE and incremental systems with golden queries, KPI diffs, and integrity simulations—then gate cutover with rollback-ready criteria.

Back to pair page

Context

Why this breaks

Databricks migrations fail late when teams validate only a one-time backfill and a few spot checks. Delta systems encode correctness in operational behavior: partition overwrite assumptions, MERGE/upsert semantics, retries, and late-arrival corrections. Snowflake can implement equivalent outcomes—but only if the correctness rules are made explicit and tested under stress. Common drift drivers in Databricks/Delta → Snowflake:

  • MERGE semantics drift: match keys, casts, and update predicates differ subtly
  • Non-deterministic dedupe: window ordering missing tie-breakers; reruns choose different winners
  • Late-arrival behavior: Delta reprocessing windows vs Snowflake staged apply not equivalent by default
  • SCD drift: end-dating/current-flag logic breaks under backfills and late updates
  • Operational failures: retry behavior changes; failures become silent data issues Validation must treat the workload as an incremental system, not a static batch.

Approach

How conversion works

  • Define the parity contract: what must match (facts/dims, KPIs, dashboards) and what tolerances apply (exact vs threshold). - Build validation datasets: golden inputs, edge cohorts (ties, null-heavy segments), and representative windows (including boundary days). - Run readiness + execution gates: schemas/types align, dependencies deployed, and jobs run reliably. - Run layered parity gates: counts/profiles → KPI diffs → targeted row-level diffs where needed. - Validate incremental integrity (mandatory for MERGE/upserts): idempotency reruns, late-arrival injections, and backfill simulations. - Gate cutover: define pass/fail thresholds, canary strategy, rollback triggers, and post-cutover monitors.

Coverage

Supported constructs

Representative validation and reconciliation mechanisms we apply in Databricks → Snowflake migrations.

SourceTargetNotes
Delta MERGE/upsert correctnessMERGE parity contract + rerun/late-data simulationsProves behavior under retries, late arrivals, and backfills.
Golden dashboards/queriesGolden query harness + repeatable parametersCodifies business sign-off into runnable tests.
Counts and 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.
Cutover readinessCanary gates + rollback criteria + monitorsPrevents ‘successful cutover’ turning into KPI debates.

Compare

How workload changes

TopicDatabricks / DeltaSnowflakeNotes
Where correctness hidesJob structure + partition overwrite/reprocessing semanticsExplicit staged apply + idempotency contractsValidation must simulate retries/late data, not just backfills.
Drift driversImplicit ordering and casting toleratedExplicit casts and deterministic ordering requiredEdge cohorts (ties/null-heavy) are mandatory test cases.
Operational sign-offOften based on “looks right” dashboard checksEvidence-based gates + rollback triggersCutover becomes measurable, repeatable, dispute-proof.

Examples

Examples

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

01_partition_row_counts_snowflake.sql
-- Row counts by window (Snowflake)
SELECT
  TO_DATE(updated_at) AS d,
  COUNT(*) AS rows
FROM MART.FACT_ORDERS
WHERE TO_DATE(updated_at) BETWEEN :start_d AND :end_d
GROUP BY 1
ORDER BY 1;
02_kpi_aggregate_diff.sql
-- KPI aggregate comparison (example)
-- Assume both outputs landed in Snowflake: COMPARE.SRC_* vs COMPARE.TGT_*
WITH src AS (
  SELECT TO_DATE(order_ts) d, country, SUM(revenue) rev
  FROM COMPARE.SRC_ORDERS
  WHERE TO_DATE(order_ts) BETWEEN :start_d AND :end_d
  GROUP BY 1,2
), tgt AS (
  SELECT TO_DATE(order_ts) d, country, SUM(revenue) rev
  FROM COMPARE.TGT_ORDERS
  WHERE TO_DATE(order_ts) BETWEEN :start_d AND :end_d
  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,
  IFF(src.rev = 0, NULL, (tgt.rev - src.rev) / src.rev) AS diff_pct
FROM src
FULL OUTER JOIN tgt
USING (d, country)
ORDER BY d, country;
03_checksum_style_aggregate.sql
-- Checksum-style aggregate (approximate)
-- Use stable stringification for key columns
SELECT
  TO_DATE(updated_at) AS d,
  COUNT(*) AS rows,
  SUM(ABS(HASH(TO_VARCHAR(id) || '|' || TO_VARCHAR(amount) || '|' || TO_VARCHAR(status)))) AS hash_sum
FROM MART.FACT_ORDERS
WHERE TO_DATE(updated_at) BETWEEN :start_d AND :end_d
GROUP BY 1
ORDER BY 1;
04_idempotency_rerun_assertion.sql
-- Idempotency gate pattern: compare snapshot metrics before/after rerun
-- (Example uses counts + checksum)
WITH snap AS (
  SELECT
    COUNT(*) c,
    SUM(ABS(HASH(TO_VARCHAR(id)))) h
  FROM MART.FACT_ORDERS
  WHERE TO_DATE(updated_at) = :d
)
SELECT
  s1.c AS before_count, s2.c AS after_count,
  s1.h AS before_hash,  s2.h AS after_hash,
  IFF(s1.c = s2.c AND s1.h = s2.h, 'PASS', 'FAIL') AS verdict
FROM snap s1, snap s2;

Workload Assessment

Make MERGE parity measurable before you cut over

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

Book assessment

Avoid

Common pitfalls

  • Validating only the backfill: parity on a static snapshot doesn’t prove correctness under reruns/late data.
  • Spot checks instead of gates: a few sampled rows miss drift in ties and edge windows.
  • No tolerance model: teams argue over diffs because thresholds weren’t defined upfront.
  • Unstable ordering: ROW_NUMBER/RANK without complete ORDER BY; winners change under retries.
  • MERGE scope blind: full-target scans hide problems and spike credits; apply windows must be bounded.
  • Ignoring operational signals: no monitors for lag, retries, credit burn, and failure patterns after cutover.

Proof

Validation approach

### Gate set (layered) Gate 0 — Readiness - Schemas, permissions, and warehouses ready - Dependent assets deployed (UDFs/procedures, reference data, control tables) Gate 1 — Execution - Pipelines run reliably under representative volume and concurrency - Deterministic ordering + explicit casts enforced Gate 2 — Structural parity - Row counts by partitions/windows - Null/min/max/distinct profiles for key columns Gate 3 — KPI parity - KPI aggregates by key dimensions - Top-N and ranking parity validated on tie/edge cohorts Gate 4 — Incremental integrity (mandatory) - _Idempotency:_ rerun same micro-batch → no net change - _Late-arrival:_ inject late updates → only expected rows change - _Backfill safety:_ replay historical windows → stable SCD and dedupe - _Dedupe stability:_ duplicates eliminated consistently under retries Gate 5 — Cutover & monitoring

  • Canary criteria + rollback triggers - Post-cutover monitors: latency, credit burn, failures, and KPI sentinels

Execution

Migration steps

A practical sequence for making validation fast, 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 outputs 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 days, late updates).

  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, late-arrival injections, and backfill simulations. These are the scenarios that usually break after cutover if not tested.

  5. 05

    Gate cutover and monitor

    Establish canary/rollback criteria and post-cutover monitors for KPIs and pipeline health (latency, credits, failures, queueing).

FAQ

Frequently asked questions

Is a successful backfill enough to cut over? +

No. Backfills don’t prove correctness under retries, late arrivals, or backfills-with-corrections. We require idempotency and late-data simulations as cutover gates for MERGE/upsert systems.

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 do you prove MERGE behavior parity? +

We codify match keys, ordering, and dedupe rules, then run simulations: rerun the same batch, inject late updates, and replay historical windows. Only when outputs stay within thresholds do we sign off.

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, repeatable, 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 Databricks→Snowflake cutover is controlled and dispute-proof.

Book assessment
Book assessment