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 Manual Migration with SnowConvert AI CLI: 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).

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.

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

Customizing normalization and metrics¶

AIM DMV ships built-in normalization and metric templates per platform and data type. The workflow keys below override those defaults at the workflow root only (not per table). Each key splits overrides into source and target arrays by platform side. Set these in the validation workflow file, not in Worker TOML.

Custom normalization (validationCustomNormalizations)¶

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.

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.

Use custom normalization when benign formatting differences would otherwise show as L3 mismatches (for example float precision or Teradata PERIOD types).

Example:

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.

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 migration configuration reference
• The SNOWCONVERT_AI database
• Manual Migration with SnowConvert AI CLI: Data validation