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 comparisonConfiguration.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 whitelist 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¶

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 index_column_list 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 index_column_list 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 where_clause 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.

Anti-locking and query modifiers¶

User-configured anti-locking hints are off by default. Unless you set queryModifiers, AIM DMV runs plain source queries. Modifiers apply to source queries only, never the Snowflake target. L3 row-hash validation uses the same source modifiers, so dirty reads can cause false MISMATCH results.

Source platforms fall into two groups:

• MVCC / snapshot engines (Oracle, PostgreSQL, Amazon Redshift, and Snowflake itself): readers don’t block writers, so anti-locking hints aren’t needed.
• Lock-based engines (SQL Server and Azure Synapse): shared locks on reads can block or be blocked by production writers. Opt-in hints such as WITH (NOLOCK) avoid blocking but use read-uncommitted semantics (dirty reads). AIM DMV never auto-applies these hints. Dirty reads can produce false MISMATCH results during validation; the impact is usually stronger at L3 (byte-exact row hashing) than at L2 (aggregate metrics compared within tolerance).

Teradata is a special case: AIM DMV prepends LOCKING ROW FOR ACCESS on every Teradata source scan automatically. This is Teradata’s native, safe row-access pattern (not a dirty read) and cannot be disabled.

Set queryModifiers at the workflow root or on individual table or view entries. See the QueryModifiers model in the Data migration configuration reference.

Per-platform behavior¶

Pros and cons¶

Workflow example¶

queryModifiers:
  objectModifier: " WITH (NOLOCK)"
tables:
  - fully_qualified_name: MYDB.dbo.ORDERS
    queryModifiers:
      objectModifier: " WITH (NOLOCK)"

Worker TOML example¶

[connections.source.sqlserver.query_modifiers]
object_modifier = " WITH (NOLOCK)"
select_modifier = ""

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)"

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.

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)