Data validation configuration reference¶

This page documents the validation workflow YAML and shared Worker TOML settings for AIM DMV data validation. For platform-specific prerequisites, connectivity, and data type mappings, see the per-platform pages linked from Data validation.

For SnowConvert AI CLI commands that generate and submit validation workflows, see Data validation.

Validation workflow file overview¶

Validation is driven by a single YAML workflow file. Key sections:

Top-level object¶

tables, views, and objects are each individually optional, but the workflow must define at least one entry across the three.

Objects with runtime type detection¶

The top-level objects array is an alternative to tables and views that lets you list objects without committing to a type. Each entry uses the same shape as a tables or views entry, plus an optional objectType:

• When objectType is TABLE or VIEW, the entry behaves exactly like the corresponding tables or views entry.
• When objectType is omitted, AIM DMV resolves the type at runtime against the target Snowflake catalog. Only TABLE and VIEW are supported. If a detected type is something else (for example a materialized view) or the object doesn’t exist on the target, only that object’s check fails; other objects continue.

tables, views, and objects can be combined freely in the same workflow.

Validation configuration¶

When validation_configuration is omitted, defaults are: schema validation and metrics validation enabled; row validation disabled; when L3 is enabled, validation fingerprints each partition with MD5 row hashes and drills into individual cells on mismatches; max_failed_rows_number defaults to 1000; early_stopping_for_cell_by_cell_comparison defaults to true; early_stopping_for_row_hashing defaults to false.

Validation levels and result codes¶

Schema validation (L1) compares table name, column names, ordinal position, data types, character length, numeric precision and scale, nullability, and row count. Results use SUCCESS, WARNING (treated as a pass), or FAILURE.

Metrics validation (L2) compares row count, min, max, sum, average, null count, distinct count, standard deviation, and variance (metrics vary by column type). Numeric comparisons honor comparison_configuration.tolerance (default 0.001). Results use SUCCESS, WARNING, or FAILURE.

Row validation (L3) fingerprints each partition with MD5 row hashes, then drills into individual cells on mismatches. Row-level RESULT values include:

Task-level failures are recorded in DATA_VALIDATION_ERROR, distinct from row-level MISMATCH results.

Comparison configuration¶

Accepted transformations¶

Accepted transformations allowlist specific source-to-target value pairs so AIM DMV does not report them as L3 mismatches. See Accepted transformations for the end-to-end flow and POSSIBLE_MISMATCH lifecycle.

Rules can appear at three levels (unioned per table):

1. Workflow root (acceptedTransformations)
2. Global validation_configuration.acceptedTransformations
3. Per-table acceptedTransformations or nested validation_configuration.acceptedTransformations

Each rule object:

Example:

validation_configuration:
  row_validation: true
acceptedTransformations:
  - column: status
    sourceValue: "ACTIVE"
    targetValue: "1"
  - columnPattern: "^flag_"
    sourceValue: null
    targetValue: "false"
tables:
  - fully_qualified_name: MYDB.MYSCHEMA.MYTABLE
    acceptedTransformations:
      - column: code
        sourceValue: "Y"
        targetValue: "YES"

Per-table and per-view entry¶

Property naming and aliases¶

Some per-entry properties accept more than one spelling. Use the documented camelCase name in new workflows; the other spellings are kept for compatibility with existing files.

Three rules apply:

• Set both WHERE clauses or neither. Filtering one side only means you’re comparing different row subsets on source and target, which almost always reports mismatches.
• Don’t combine sourceWhereClause with the legacy whereClause on the same entry. The workflow is rejected when it loads.
• camelCase wins if an entry supplies both camelCase and snake_case spellings for the index column lists.

Filtering compared rows¶

sourceWhereClause and targetWhereClause limit which rows take part in validation. Each filter is stored per side and AND-composed with the partition range predicate when L2 and L3 run, so a filter never widens a partition’s scope. Source-side SQL is written in the source dialect, while target-side SQL goes through column mapping and identifier folding before it runs on Snowflake.

tables:
  - fully_qualified_name: MYDB.dbo.ORDERS
    target_name: ORDERS
    sourceWhereClause: "STATUS = 'ACTIVE'"
    targetWhereClause: "STATUS = 'ACTIVE'"
    indexColumnList:
      - ORDER_ID

A targetWhereClause that filters out every row is not treated as an empty target table: partition sizing continues against the filtered count. A genuinely empty target behaves differently, running L1 if enabled and skipping L2 and L3 with an explanatory failure row.

When a table uses incremental validation, neither filter is applied while detecting change. Change probes read partition boundaries only. The filters apply when L2 and L3 run on a partition that changed.

Column filtering with regex patterns¶

Each entry in column_selection_list is matched against every column name:

• Literal — plain string (for example LOAD_DATE), case-insensitive unless is_case_sensitive: true
• Regex — entry wrapped in single quotes with r"..." inside (for example 'r".*_TS"')

Partitioning¶

When column_names_to_partition_by is set, the Orchestrator splits the table into range-based partitions:

1. Compute target rows-per-partition from target_partition_size_rows or target_partition_size_mb (default 200 MB).
2. Apply internal caps for safe infrastructure bounds.
3. Derive partition count as ceil(row_count / effective_rows_per_partition).

Automatic partition and index key selection¶

When column_names_to_partition_by or indexColumnList is omitted for a table that needs metrics or row validation, AIM DMV infers keys automatically from source catalog metadata:

• Partition key: clustered, sort, or distribution key columns when available, otherwise a unique index, otherwise the first non-boolean schema column as a last resort.
• Index key (for L3 row alignment): the declared primary key, otherwise the first unique index as a fallback.

Inferred keys are cached per source table and reused by later workflows. A manually specified column_names_to_partition_by or indexColumnList always takes precedence. Views can’t be inferred; set these explicitly when validating a view, and prefer a partition key that matches the underlying tables plus a sourceWhereClause to limit the scan. See Validating views.

Each partition key must be a real, physical column name. Persisted computed columns (SQL Server) and virtual columns (Oracle) qualify. Bare SQL expressions, pseudo-columns (for example Oracle ROWID, ROWNUM, or ORA_ROWSCN), and hidden system columns are not valid: AIM DMV quotes partition key names as identifiers, so those values won’t resolve. To partition on a derived value, add a persisted or virtual computed column on the source and reference that column name.

Index keys deserve particular attention when you enable L3. They’re what lets AIM DMV pair a source row with its target row, so they need to identify rows uniquely: a non-unique index key produces DUPLICATE_SOURCE and DUPLICATE_TARGET results rather than useful comparisons. Set targetIndexColumnList as well whenever the target column names differ from the source, for example after a columnNameMappings rename during migration.

Incremental validation¶

By default, every validation run compares every partition of every table. Incremental validation detects which partitions changed since the last run and re-validates only those, which cuts cost and runtime on scheduled re-validation of large tables.

Incremental validation uses the same synchronization block as incremental sync in data migration, but it’s read-only: nothing is written to the target, and no rows are moved or reconciled.

SynchronizationStrategy model¶

Prerequisites¶

• The table must be partitioned. Set column_names_to_partition_by, or let AIM DMV infer it. Change detection works per partition, so an unpartitioned table has nothing to skip.
• At least one prior full validation must have completed, to establish baseline metadata in PARTITION_METADATA.SYNCHRONIZATION_DATA. The first run with synchronization configured still validates everything.

What happens on each run¶

Unchanged partitions report Not validated rather than a pass. That’s expected on an incremental run, not a failure.

Change-detection probes read partition boundaries only. sourceWhereClause and targetWhereClause aren’t applied while detecting change; they’re applied when L2 and L3 run on a partition that was found to have changed.

Because change detection relies on the same hashing as migration checksums, review Changes a checksum may not detect before using strategy: checksum. Columns excluded from the hash won’t trigger re-validation of their partition.

Example¶

source_platform: sqlserver
target_database: MY_DB
defaultTableConfiguration:
  column_names_to_partition_by:
    - ID
  synchronization:
    strategy: checksum
tables:
  # Inherits the checksum strategy and the partition column.
  - fully_qualified_name: MYDB.dbo.CUSTOMERS
  # Overrides both: partition on ORDER_ID, detect change by watermark.
  - fully_qualified_name: MYDB.dbo.ORDERS
    column_names_to_partition_by:
      - ORDER_ID
    synchronization:
      strategy: watermark
      watermarkColumn: UPDATED_AT
  # Opts out: always validate in full, despite the global default.
  - fully_qualified_name: MYDB.dbo.LOOKUP_CODES
    synchronization:
      strategy: none

Anti-locking and query modifiers¶

Anti-locking hints are off by default and work the same way as in migration: they apply to source queries only and are set with queryModifiers at the workflow root or per table. See Anti-locking and query modifiers for per-platform behavior and configuration.

The validation-specific consideration: WITH (NOLOCK) and similar read-uncommitted hints allow dirty reads that can produce false MISMATCH results. The impact is usually stronger at row-level validation (byte-exact hashing) than at metrics validation (aggregates compared within tolerance). Enable these hints only when source locking is actually blocking your workflow, ideally against a low-write window.

INTERVAL data type handling¶

intervalHandling controls how INTERVAL columns are compared, and should match the value used for the same table during migration. Neither setting is universally better: "interval" compares as a native Snowflake INTERVAL (on PostgreSQL, mixed year-month and day-time values were already folded into INTERVAL DAY TO SECOND during migration), and "varchar" compares as text when migration preserved the original interval text. See INTERVAL columns and intervalHandling for the PostgreSQL tradeoff.

Set intervalHandling at the workflow root or on a per-table entry. See INTERVAL data type handling in the Data migration configuration reference for how values are extracted and cast during migration.

Customizing normalization and metrics¶

AIM DMV ships built-in normalization and metric templates per platform and data type. The keys below override those defaults so benign formatting differences (for example float precision, or a platform-specific type such as Teradata PERIOD) don’t show up as L3 mismatches. Set these in the validation workflow file, not in Worker TOML.

Custom normalization rules (validationCustomNormalizationRules)¶

validationCustomNormalizationRules is the preferred way to override normalization. Unlike the legacy validationCustomNormalizations key (still supported, see below), rules can target a specific column or column name pattern, not only a data type, and can be scoped to the workflow root or to an individual table.

Each rule:

When more than one rule could match the same column, AIM DMV applies the most specific one, in this order: table-level column match, table-level columnPattern match, table-level dataType match, workflow-level column match, workflow-level columnPattern match, workflow-level dataType match, then the built-in template for that platform and type.

validationCustomNormalizationRules:
  - dataType: FLOAT
    sourceExpression: "TRIM(CAST({{ col_name }} AS VARCHAR(100)))"
    targetExpression: "TRIM(TO_VARCHAR({{ col_name }}))"
  - column: AMT
    sourceExpression: "TRIM(CAST({{ col_name }} AS VARCHAR(50)))"
  - columnPattern: "^GEO_"
    sourceExpression: "ST_AsText({{ col_name }})"
    targetExpression: "TO_VARCHAR({{ col_name }})"
tables:
  - fully_qualified_name: MYDB.MYSCHEMA.MYTABLE
    validationCustomNormalizationRules:
      - column: LEGACY_FLAG
        sourceExpression: "TRIM(CAST({{ col_name }} AS VARCHAR(10)))"
        targetExpression: "TRIM(TO_VARCHAR({{ col_name }}))"

Use custom normalization rules when a benign, predictable formatting difference (not a real data problem) would otherwise show up as an L3 mismatch, and you want the fix scoped to one column, one naming pattern, or one table rather than every column of a data type.

Custom normalization (validationCustomNormalizations, legacy)¶

Normalization is the SQL AIM DMV wraps around each column so source and target values compare in a canonical form (consistent date, number, or string formatting) for L2 metrics and L3 hashing. This key is global-only and keyed by data type; prefer validationCustomNormalizationRules for new workflows, especially when you need per-column or per-table control. When both are present for the same column, the granular rule wins.

Each array entry is a single-key map of data type → SQL template. Keys are data types (uppercased), not column names. Use the "{{ col_name }}" placeholder for the column reference.

An override for a data type replaces the built-in template for that type; other types are unchanged.

validationCustomNormalizations:
  source:
    - BIGINT: "TRIM(TO_CHAR(\"{{ col_name }}\", '999990.000000000000000000'))"
    - DECIMAL: "TRIM(TO_CHAR(\"{{ col_name }}\", '999990.000000000000000000'))"
  target:
    - NUMBER: "TO_CHAR(\"{{ col_name }}\", 'FM999990.000000000000000000')"

Custom metrics (validationCustomMetrics)¶

L2 metrics are chosen by column data type. A DEFAULT set applies to types without a specific entry.

Each array entry:

Each metric definition:

Control which metrics run with these related settings:

Example:

validationCustomMetrics:
  source:
    - datatype: BIT
      replace_all: false
      metrics:
        - name: sum
          metric_query: "SUM(\"{{ col_name }}\")"
          metric_return_datatype: NUMBER
        - name: stddev_pop
          metric_query: "STDDEV_POP(\"{{ col_name }}\")"
          metric_return_datatype: FLOAT
  target: []

L1 type mapping overrides (validationCustomTypes)¶

validationCustomTypes overrides how source data type names map for L1 schema comparison. This is distinct from normalization (L2/L3).

Example:

validationCustomTypes:
  source:
    - "PERIOD(DATE)": "VARCHAR(40)"

Per-column L1 type overrides (validationCustomTypeRules)¶

Where validationCustomTypes remaps a type name everywhere it appears, validationCustomTypeRules overrides the expected target type for one column. Use it when a single column was deliberately migrated to a different type than the default mapping would produce.

validationCustomTypeRules:
  - column: GEO_COL
    sourceType: GEOGRAPHY
    targetType: VARIANT

A rule affects only the L1 DATA_TYPE criterion. The other L1 criteria (CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, and NUMERIC_SCALE) still report the genuine metadata difference, because a type rule doesn’t claim those match.

A type rule also doesn’t change how values compare. When the two sides hold the same information in different types, pair the rule with a custom normalization rule so L3 sees equal values.

Set these rules at the workflow root or on an individual table entry. Per-table rules win for matching columns.

Worker configuration¶

Workers reuse the same TOML format as Data Migration. See Data migration configuration reference for the full property list, including custom extraction plugin setup, external secret managers, and environment variables.

Validation objects such as the results stage and file format live in the validation metadata schema. When you override its name, the Worker’s [application].snowflake_schema_for_data_validation_metadata key must match the Orchestrator’s CUSTOM_SNOWFLAKE_SCHEMA_FOR_DATA_VALIDATION_METADATA. See Matching metadata locations between the Orchestrator and Workers.

Workers that execute validation tasks must have the validation runtime available. The [connections.source.*] section matches the source platform documented on each Validating Data from … page.

Observability¶

Validation metadata lives under SNOWCONVERT_AI.DATA_VALIDATION by default. Filter queries by WORKFLOW_ID. See The SNOWCONVERT_AI database for tables, views, workflow management procedures, and sample queries.

Related content¶

• Data validation
• Data validation advanced configuration
• Data migration configuration reference
• The SNOWCONVERT_AI database
• Data validation (CLI)