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"

Validation behavior¶

Datetime precision: SQL Server datetime types can carry sub-microsecond precision (a seventh fractional digit). Validation readback truncates that digit, which can surface as L2/L3 differences on high-precision columns.
Fixed-width character types: L3 normalization applies TRIM to NCHAR columns when comparing to Snowflake VARCHAR targets.
UNIQUEIDENTIFIER: Compared as an uppercase string representation in L3.
Limited L3 coverage: TEXT, NTEXT, XML, and SQL_VARIANT have no L3 normalization. GEOGRAPHY and GEOMETRY are validated at the schema level only.

Set source_platform: sqlserver in the validation workflow YAML. For partitioning, L3 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
    index_column_list:
      - ID
    validation_configuration:
      row_validation: true
      row_validation_mode: cell
      max_failed_rows_number: 500
    where_clause: "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	Sub-microsecond precision (7th digit) is truncated
CHAR, VARCHAR, NCHAR, NVARCHAR	VARCHAR	Yes
BINARY, VARBINARY, UNIQUEIDENTIFIER	BINARY / VARCHAR	Yes	UUID compared as an uppercase string
TEXT, NTEXT	VARCHAR	No	Row comparison not supported
XML, SQL_VARIANT	VARIANT	No	Schema-level comparison only
GEOGRAPHY, GEOMETRY	GEOGRAPHY	No	Schema-level comparison only

Platform-specific suggestions¶

Review L2/L3 results on DATETIME, DATETIME2, and DATETIMEOFFSET columns when sub-microsecond precision matters in the source.
For NCHAR columns, expect possible whitespace-related L2 drift; L3 applies TRIM normalization.
Plan validation depth by type: rely on L1 for XML and SQL_VARIANT; treat GEOGRAPHY / GEOMETRY as schema-level only until WKT normalization is available.
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.