Oracle to Snowflake migration validation: a six-gate acceptance framework

Originally published by Ariftly. This version focuses on the engineering method. The test scenarios below are synthetic specifications, not executed benchmark results.

A successful SQL conversion answers one question: can the target system accept the translated statement? A migration acceptance decision answers a harder one: does the translated workload preserve the behavior the team actually requires?

For Oracle-to-Snowflake work, those questions diverge around type semantics, input representation, session settings, DML behavior, transformed outputs, and approved business changes. A useful validation process therefore needs more than a compiler result or conversion score.

Define the behavior contract first

Before testing, separate three claims:

1. The SQL has been translated.
2. The translated SQL executes.
3. The translated SQL produces the accepted behavior for the intended workflow.

Each claim provides evidence. None proves the others.

A behavior contract records the source artifact, relevant DDL and dependencies, representative fixtures, session configuration, expected outputs, comparison rules, tolerances, and who can approve an intentional difference. Without it, reviewers cannot reliably distinguish a defect from an agreed modernization change.

Three risks that deserve explicit tests

1. NULL and zero-length strings

Oracle currently treats zero-length character values as NULL. Snowflake distinguishes empty strings from SQL NULL and exposes file-format controls that affect their treatment during loading and unloading.

A small fixture should include SQL NULL, zero-length text, one space, and ordinary text. Declare the expected representation before running the comparison. Do not silently normalize every blank-looking value to NULL.

Sources: Oracle Nulls and Snowflake data unloading considerations.

2. Oracle DATE contains time

Oracle DATE stores year, month, day, hour, minute, and second. Snowflake DATE stores a calendar date without time. A source value such as 2026-01-15 17:45:10 therefore needs an intentional target type and test.

Use two observations on the same day with different times. Record the selected Snowflake timestamp variant and relevant session settings instead of relying on an unspecified alias.

Sources: Oracle data types and Snowflake date/time types.

3. Duplicate MERGE matches

Snowflake documents cases in which multiple source rows match one target row. With ERROR_ON_NONDETERMINISTIC_MERGE=TRUE, the documented default, an ambiguous update or delete raises an error. When disabled, the selected source row is undefined.

Test whether the workflow should reject the ambiguity, deduplicate using a declared rule, or route it to an accountable owner. Source: Snowflake MERGE.

Use existing validation capabilities deliberately

Snowflake's documented migration validation has three levels:

• L1 — schema: column structure, types, precision, scale, nullability, and row count.
• L2 — metrics: counts, minimums, maximums, sums, averages, and other supported aggregates.
• L3 — row: row signatures and cell-level mismatch investigation in supported configurations.

Row validation is optional and disabled by default in the documented configuration. Accepted transformations can reconcile explicitly declared source-to-target changes for applicable row-level validation.

These capabilities are valuable, but migrated-table validation is not automatically equivalent to validating every transformed query, procedure, scheduled job, or business calculation. The test boundary must match the workflow being accepted.

Source: Snowflake data validation.

A six-gate acceptance framework

We use a compact evidence chain:

1. Source contract: versioned SQL, DDL, dependencies, settings, fixtures, and expected outputs.
2. Translation: source and target SQL, diagnostics, and a record of material changes.
3. Execution: environment settings, run identifiers, output schemas, and captured results.
4. Comparison: typed assertions, predeclared tolerances, and mismatch reports.
5. Exceptions: classification, owner, and documented disposition for every consequential difference.
6. Approval: a versioned acceptance decision linked to the evidence above.

The framework is an engineering proposal, not a certification standard or performance claim.

Compare the right thing

Use stable business keys where they exist. When they do not, multiset comparison can preserve duplicate counts without treating arbitrary row position as identity. Enforce ordering only when the source contract requires it.

Compare typed values rather than display strings, especially for timestamps and decimals. Aggregate checks can pass even when keyed records differ, so validation depth should follow the required behavior and risk.

AI can help interpret legacy SQL, propose translations, and suggest tests. Exact value comparisons, schema assertions, duplicate detection, tolerances, and acceptance rules should remain deterministic wherever practical. A plausible explanation is not proof that a transformation is correct.

Start small, but preserve the evidence

A practical first suite can cover empty strings, temporal preservation, duplicate MERGE matches, decimal boundaries, aggregate masking, declared value mappings, session-sensitive time behavior, and missing or duplicate identities.

Ariftly has published the fixture specification and evidence-manifest template. They are test designs, not observed results.

The immediate next step is simple: define one source-to-target acceptance contract, build a representative fixture, and decide which differences are defects, which are intended transformations, and who has authority to accept the result.

A migration is ready for approval when its required evidence supports that decision—not merely when the SQL has been converted.