Data validation advanced configuration¶

Validation workflows have a full YAML and TOML configuration surface documented in Data validation configuration reference. Most projects don’t need to hand-edit that file: the Snowflake AIM Agent for Data Warehouses generates it for you and walks through the settings that matter for your source platform, then shows you the result before anything runs.

This page describes common situations where you’ll want to go beyond the defaults, the prompt that gets you there, and where to find the exact property if you want to review or edit it yourself.

Choosing how deep to validate¶

Validation runs at up to three levels: schema (L1), aggregate metrics (L2), and row-level fingerprinting with cell drill-down (L3). L3 is the most resource-intensive and is disabled by default.

Prompt:

Run full row-level validation on the ORDERS and CUSTOMERS tables, but keep the rest at schema and metrics validation only

The agent sets row_validation: true for the tables you named (globally it stays off). See Validation levels.

Stopping L3 early when a table is clearly failing¶

When L3 runs across many partitions, finishing every partition can be expensive after a table has already failed. Early stopping skips remaining L3 partition work once enough mismatches have been ingested.

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

Row-hash and cell drill-down early stopping are independent. You can set these globally under validation_configuration or override them per table.

Prompt:

Enable early stopping for row hashing on the ORDERS table, and stop after 200 mismatches

The agent sets early_stopping_for_row_hashing: true and lowers max_failed_rows_number on the tables you name (or globally, if you ask for that). See Early stopping and Validation configuration.

Declaring expected source-to-target differences (accepted transformations)¶

Some value changes during migration are intentional (encoding normalization, NULL-to-empty-string coercion, status-code remapping) and shouldn’t be reported as failures. Accepted transformations whitelist specific source-to-target value pairs so AIM DMV does not treat matching diffs as L3 mismatches.

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. Rows that match an accepted rule are cleared; the rest become MISMATCH.

Rules can appear at the workflow root, under global validation_configuration, or on an individual table. Matching scopes are unioned. Each rule names either an exact column or a columnPattern regex, plus the expected sourceValue and targetValue (use null for SQL NULL).

Prompt:

We intentionally remap STATUS = 'ACTIVE' on the source to STATUS = '1' on the target. Don't flag that as a mismatch.

The agent adds an acceptedTransformations rule at the appropriate scope.

Caveat: POSSIBLE_MISMATCH after the workflow finishes¶

POSSIBLE_MISMATCH is temporary. Reconcile runs after cell drill-down and should leave either:

• no row (the diff matched an accepted transformation), or
• MISMATCH (a real problem that did not match any rule).

On a completed workflow, ROW_VALIDATION_RESULTS should not still contain POSSIBLE_MISMATCH for that table. Leftover provisional rows usually mean reconcile did not finish (for example the workflow failed or was canceled before the reconcile task ran). Treat them as incomplete validation, not as accepted diffs. Re-run validation for the affected tables, or investigate task status in the workflow, before deciding the table passed.

For the property shape and examples, see Accepted transformations and Accepted transformations (configuration reference). To query provisional rows while a workflow is still running, see The SNOWCONVERT_AI database.

Customizing normalization for row-hashing and cell comparison¶

Validation wraps each column in a normalization expression before hashing (L3 row fingerprinting) and comparing cell values, so that benign formatting differences (trailing zeros, timezone representation, and similar) don’t show up as mismatches. Built-in templates cover common data types per platform, but you can override them when a specific column, naming pattern, or table needs different handling.

Prompt:

The AMOUNT column keeps showing mismatches that look like formatting differences, not real data problems. Can we normalize it before comparing?

The agent proposes a normalization expression for that column (or column pattern, or data type, depending on scope) and adds a validationCustomNormalizationRules entry, either at the workflow root or scoped to just the affected table. See Custom normalization rules for the property shape, matching precedence, and more examples (including geometry and legacy platform-specific types).

Overriding partition and index keys¶

Like migration, validation can infer partition keys (for L2/L3 chunking) and index keys (for L3 row alignment) from source catalog metadata when you don’t set column_names_to_partition_by or index_column_list. Inference can happen while the workflow runs, so you won’t always see the chosen keys up front. A value you set yourself always takes precedence. Views can’t be inferred; see Validating views.

If you already know the columns to use, set them explicitly.

Each partition key must be a real, physical column name (single or composite). Persisted computed columns (SQL Server) and virtual columns (Oracle) qualify because they appear as real columns in the source catalog. Bare SQL expressions, pseudo-columns (for example Oracle ROWID, ROWNUM, or ORA_ROWSCN), and hidden system columns are not valid: AIM DMV quotes each configured name as an identifier in partition boundary queries and range predicates, so those values won’t resolve. To partition on a derived value, add a persisted or virtual computed column on the source and name that column in column_names_to_partition_by.

Prompt:

Partition LEGACY_ORDERS by ORDER_DATE, and align rows on ORDER_UUID

See Automatic partition and index key selection.

Validating views¶

Views go through the same L1, L2, and L3 pipeline as tables, but they don’t expose the catalog metadata AIM DMV uses to infer partition and index keys. When you validate a view, set those keys yourself and use a row filter so L2/L3 don’t scan an unbounded result.

Choose a partition key that matches the underlying tables. Prefer a column that would also be a good partition key on the base tables the view reads (for example the date or ID column that drives filters and joins in the view definition), not an arbitrary projected column. That keeps partition ranges meaningful and avoids uneven or ineffective chunking. For L3, also set an index_column_list that uniquely identifies rows in the view result.

Filter with a WHERE clause. Views often expand to large joins. Use where_clause (and target_where_clause when the target needs a matching predicate) to limit the rows under validation, for example to a recent date range or an active subset.

Prompt:

Validate the ORDERS_SUMMARY view. Partition by ORDER_DATE (same key as the ORDERS table the view is built on), align rows on ORDER_ID, and only validate the last 30 days

The agent sets column_names_to_partition_by, index_column_list, and where_clause on the view entry (and target_where_clause if the target filter should differ). See Automatic partition and index key selection and Per-table and per-view entry.

Validating INTERVAL columns¶

Use the same intervalHandling value as the matching migration workflow: "interval" to compare as a native Snowflake INTERVAL, or "varchar" to compare as text. Neither option is universally better; the tradeoff is the same as during migration. On platforms such as PostgreSQL, "interval" means mixed year-month and day-time values were folded into INTERVAL DAY TO SECOND (losing calendar-accurate year-month precision), while "varchar" preserves the original interval text.

Prompt:

The EVENTS table was migrated with intervalHandling set to varchar. Use the same setting for validation.

The agent sets intervalHandling on that table’s validation entry to match migration. A mismatch between migration and validation produces false mismatches, because the two sides are normalized differently. See INTERVAL data type handling.

Reducing lock contention on the source¶

If validation reads are blocked by production writers, you can opt in to anti-locking query hints. This trades consistency guarantees (dirty reads) for reduced blocking, so use it only when locking is actually a problem.

On MVCC engines (Oracle, PostgreSQL, Redshift), readers don’t block writers and hints usually aren’t needed. On lock-based engines (SQL Server), WITH (NOLOCK) avoids blocking but allows dirty reads that can produce false MISMATCH results. The impact is usually stronger at L3 (byte-exact row hashing) than at L2 (aggregate metrics within tolerance). Teradata gets LOCKING ROW FOR ACCESS automatically (safe, not a dirty read).

Prompt:

Source reads are getting blocked during validation. Can we reduce locking?

The agent explains the platform-specific tradeoff and, if you confirm, sets the appropriate queryModifiers (for example WITH (NOLOCK) on SQL Server). See Anti-locking and query modifiers for per-platform defaults and the pros and cons.

Adjusting numeric tolerance for metrics¶

L2 aggregate comparisons (sum, average, and similar) use a relative tolerance by default, since floating-point and precision differences between platforms are expected.

Prompt:

Our source and target use different floating-point precisions, so metric comparisons are noisy. Can we loosen the tolerance?

The agent sets comparisonConfiguration.tolerance to a value you agree on. See Comparison configuration.

Overriding L2 metrics or L1 type mapping¶

Prompt:

Add a stddev_pop metric for the BIT columns, and treat the Teradata PERIOD(DATE) type as VARCHAR(40) for schema comparison

The agent sets validationCustomMetrics and validationCustomTypes respectively. See Custom metrics and L1 type mapping overrides.

Filtering rows or columns¶

Prompt:

Only validate active customers, and skip the internal AUDIT_* columns

The agent sets where_clause to filter rows and column_selection_list (with use_column_selection_as_exclude_list for the audit columns) to filter columns. See Per-table and per-view entry and Column filtering with regex patterns.

Related content¶

• Data validation
• Data validation configuration reference
• Data migration advanced configuration
• The SNOWCONVERT_AI database
• Glossary