Data validation

The Data Validation feature provides a fault-tolerant, scalable way to verify that data migrated into Snowflake matches the data in the original source system. It runs on the same infrastructure used by Data Migration, so you can migrate and validate with the same Orchestrator and Workers.

AIM DMV data validation is designed for migration scenarios where you need confidence that migrated data is correct before cutting over. Supported source platforms are SQL Server, Amazon Redshift, Teradata, Oracle, and PostgreSQL.

For shared architecture, deployment options, prerequisites, and Worker tuning, see Data Migration & Validation overview.

Validation levels

AIM DMV performs comparisons at three increasingly detailed levels:

Schema validation (L1)

Confirms that table structure is preserved: table name, column names, ordinal position, data types, character length, numeric precision and scale, nullability, and row count.

Metrics validation (L2)

Compares aggregate statistics: row count, min, max, sum, average, null count, distinct count, standard deviation, and variance (metrics vary by column type).

Row validation (L3)

Performs row-level comparison. When row_validation is enabled, validation fingerprints each partition with MD5 row hashes, then drills into individual cells on mismatches.

Row validation is disabled by default and is typically applied only where needed because it’s the most resource-intensive level.

Prerequisites

Before you use data validation, make sure the following are in place:

  • Snowflake AIM Agent for Data Warehouses: Use the agent’s data-validation skill for guided validation. Workers that run validation tasks receive the validation runtime through the same install path.
  • Snowflake access: Connections for the Orchestrator and Workers using a role that can create and administer SNOWCONVERT_AI and its objects.
  • Source connectivity: Platform-specific drivers on Workers. See the per-platform pages below.
  • Target data available: Tables to validate must already exist in Snowflake. Don’t alter migrated data between migration and validation.

Supported source platforms

Each platform page covers prerequisites (including platform-specific UDF or driver requirements), connectivity, validation behavior, data type mappings, and suggestions:

For workflow and Worker field definitions, see Data validation configuration reference.

Usage

To validate migrated data, complete the following high-level steps:

  1. Start the Orchestrator.
  2. Start the Workers.
  3. Create a data validation workflow.
  4. Monitor the validation workflow until completion.

If you’ve already set up an Orchestrator and Workers for migration, you can reuse them for validation.

Ask the Snowflake AIM Agent for Data Warehouses for validation:

Run cloud data validation for my project

For SnowConvert AI CLI commands, see Manual Migration with SnowConvert AI CLI: Data validation.

Validation results

Workflow lifecycle stages: Pending, Executing, Completed.

Result codes vary by validation level:

LevelResult codesNotes
L1 schemaSUCCESS, WARNING, FAILUREWARNING is treated as a pass (for example source numeric precision lower than target).
L2 metricsSUCCESS, WARNING, FAILURENumeric metrics honor comparisonConfiguration.tolerance (default 0.001).
L3 row/cellMISMATCH, POSSIBLE_MISMATCH, NOT_FOUND_SOURCE, NOT_FOUND_TARGET, DUPLICATE_SOURCE, DUPLICATE_TARGET, DUPLICATE_BOTH_SIDESMISMATCH means values differ. POSSIBLE_MISMATCH is provisional pending accepted-transformation reconcile.

For the precise meaning of each L3 code (including which side a row is missing from or duplicated on), see Validation levels and result codes in the configuration reference.

Task-level failures appear in DATA_VALIDATION_ERROR (distinct from row-level MISMATCH results).

Monitor workflows in SNOWCONVERT_AI.DATA_VALIDATION or the DATA_VALIDATION_DASHBOARD Streamlit app. See The SNOWCONVERT_AI database for views and sample queries filtered by WORKFLOW_ID.

Cancel, pause, resume, or retry a workflow

Prefer asking the Snowflake AIM Agent for Data Warehouses in natural language (for example, “Pause my last validation workflow” or “Cancel the sales validation run”). You can also use the SnowConvert AI CLI (scai data validate pause|resume|cancel) or call the workflow management stored procedures directly.

Canceling a workflow fails active tasks with a cancellation message. You can also pause, resume, retry, or cancel a single source table inside a workflow.

See Managing workflow lifecycle in The SNOWCONVERT_AI database.

CALL SNOWCONVERT_AI.DATA_MIGRATION.CANCEL_WORKFLOW(<workflow_id>);

Considerations and recommendations

For more scenarios (validating views, customizable normalization for row-hashing and cell comparison, partition and index keys, INTERVAL handling, and more) with example prompts, see Data validation advanced configuration.

Initial testing

Start with a small subset of tables, keep L3 disabled on the first run, use where_clause to limit rows, and use small partition sizes. Enable row validation only on tables where you need it.

Early stopping

When L3 validation runs across many partitions, processing every partition can be expensive. Early stopping lets AIM DMV skip remaining L3 partition work once enough mismatches have been found, so you don’t keep scanning a table that already clearly fails validation.

During L3, the orchestrator periodically checks how many mismatch rows have been ingested into the results tables. When the count reaches max_failed_rows_number, pending partition tasks for that stage are skipped.

Configure early stopping in validation_configuration (globally or per table):

OptionDefaultPurpose
max_failed_rows_number1000Mismatch threshold that triggers early stop; also caps reported mismatches per partition
early_stopping_for_row_hashingfalseStop remaining row-hash partitions once the threshold is reached
early_stopping_for_cell_by_cell_comparisontrueStop remaining cell drill-down partitions once the threshold is reached
early_stop_check_interval_minutes5 (when early stopping is enabled)How often the orchestrator rechecks the mismatch count
early_stop_check_interval_secondsunsetAlternative to minutes (mutually exclusive)

Row-hash and cell drill-down early stopping are independent. Per-table values override global settings. See Validation configuration in the configuration reference.

Numeric precision and scale

When source numeric precision or scale exceeds the Snowflake target, validation surfaces the difference at each level:

  • L1: WARNING when the target is wider than the source (pass); FAILURE when the source is wider than the target.
  • L2: aggregate metrics honor tolerance (default 0.001).
  • L3: values are compared using scale-aligned canonical text. Truncation that changes a stored value causes a MISMATCH.

Deploy converted DDL before migration so target types match your expectations. See Numeric precision and scale on the Data migration page.

Query tagging

The Orchestrator and Worker set Snowflake QUERY_TAG on every query. Filter in QUERY_HISTORY with TRY_PARSE_JSON(query_tag):DMVF_WORKFLOW_ID.

Normalization and metrics customization

Built-in normalization and L2 metric templates apply per platform and data type. Override them at the workflow root with validationCustomNormalizations and validationCustomMetrics. See Customizing normalization and metrics in the configuration reference.

Accepted transformations

Accepted transformations declare source-to-target value pairs that migration legitimately changes (encoding, NULL-to-empty-string coercion, status-code remapping, and similar). AIM DMV does not report matching diffs as mismatches. This is an advanced option used when you already know specific column diffs are expected.

Accepted transformations apply only when L3 row validation is enabled. Tables without rules write MISMATCH directly from row hashing. Tables with rules use a provisional POSSIBLE_MISMATCH path until cell drill-down and reconcile complete.

Add an acceptedTransformations array to the validation workflow file. Rules merge from the workflow root, global validation_configuration, and per-table entries.

For configuration examples, the full property reference, and how to query provisional results, see Accepted transformations and The SNOWCONVERT_AI database.

Warning

For accurate validation and to avoid false negatives, don’t alter the migrated data during the validation process.

Related content