Validating Data from SQL Server¶

This page covers SQL Server-specific setup for Data validation. For workflow field definitions, see Data validation configuration reference.

Prerequisites¶

ODBC driver on Worker hosts (same guidance as Migrating Data from SQL Server).

Connectivity¶

Reuse [connections.source.sqlserver] Worker TOML from data migration:

[connections.source.sqlserver]
username = "username"
password = "password"
database = "database_name"
host = "127.0.0.1"
port = 1433
odbc_driver = "ODBC Driver 17 for SQL Server"

Set source_platform: sqlserver in the validation workflow YAML. For partitioning, row alignment, and tolerance settings that apply to all platforms, see Data validation configuration reference.

Example workflow excerpt:

source_platform: sqlserver
validation_configuration:
  schema_validation: true
  metrics_validation: true
  row_validation: false
comparison_configuration:
  tolerance: 0.001
tables:
  - fully_qualified_name: SampleStoreDB.dbo.store_employee
    target_name: target_employee
    column_names_to_partition_by:
      - ID
  - fully_qualified_name: SampleStoreDB.dbo.Sales_Simple
    column_names_to_partition_by:
      - ID
    indexColumnList:
      - ID
    validation_configuration:
      row_validation: true
      max_failed_rows_number: 500
    sourceWhereClause: "is_deleted = 0"
    targetWhereClause: "is_deleted = 0"

Data type mappings¶

SQL Server type	Snowflake target type	Supported for validation	Notes
BIT, integers, DECIMAL, MONEY, FLOAT, REAL	NUMBER / FLOAT	Yes
DATE, TIME, DATETIME types	DATE / TIME / TIMESTAMP	Yes
CHAR, VARCHAR, NCHAR, NVARCHAR	VARCHAR	Yes
BINARY, VARBINARY, UNIQUEIDENTIFIER	BINARY / VARCHAR	Yes	UUID compared as an uppercase string
TEXT, NTEXT	VARCHAR	Yes	Row-level comparison reads the full value; very large values may hit memory limits
XML, SQL_VARIANT	VARIANT	Partial	Schema-level comparison only
GEOGRAPHY	GEOGRAPHY	Yes	Compared as despaced Well-Known Text
GEOMETRY	GEOMETRY	Yes	Compared as despaced Well-Known Text
VECTOR	VECTOR(element_type, n)	Yes	Row-level validation compares element by element

Platform-specific considerations¶

Starting validation: Ask the agent to generate a validation workflow with the depth you need (schema validation, metrics validation, and row-level validation, per table).

Prompt:
```
Run cloud data validation for my SQL Server tables, with schema and metrics validation on all tables and row-level validation on ORDERS
```
Anti-locking: Hints are off by default. On busy source tables, set queryModifiers.objectModifier to " WITH (NOLOCK)" to avoid blocking on source locks, at the cost of dirty reads that can cause false MISMATCH results. See Anti-locking and query modifiers.

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