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

  • SQL Server connection driver on Worker hosts. Install the Worker with the sqlserver extra to use the preferred mssql-python driver without a system ODBC installation. The Worker falls back to pyodbc when the native driver isn’t available or the Worker TOML extra_options contains an unsupported keyword. See Migrating Data from SQL Server.
  • L3 object storage (Recommended): Use CETAS (extraction.strategy: cet_as) so signatures land on Azure Blob (same CETAS objects and Snowflake external stage as Migrating Data from SQL Server). CETAS requires SQL Server 2022 or later or Azure SQL Managed Instance, and Microsoft ODBC Driver 18 for SQL Server on the Worker host. See Object storage backends and Azure Blob Storage.

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
set_context_info = true

Set set_context_info = false if your audit triggers or other applications already use the SQL Server session’s CONTEXT_INFO slot.

For Windows authentication, use the same source connection as data migration. On Snowpark Container Services that connection needs a Kerberos keytab. See Windows authentication.

Set sourcePlatform: 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:

sourcePlatform: sqlserver
validationConfiguration:
  schemaValidation: true
  metricsValidation: true
  rowValidation: false
comparisonConfiguration:
  tolerance: 0.001
tables:
  - fullyQualifiedName: SampleStoreDB.dbo.store_employee
    targetName: target_employee
    columnNamesToPartitionBy:
      - ID
  - fullyQualifiedName: SampleStoreDB.dbo.Sales_Simple
    columnNamesToPartitionBy:
      - ID
    indexColumnList:
      - ID
    validationConfiguration:
      rowValidation: true
      maxFailedRowsNumber: 500
    sourceWhereClause: "is_deleted = 0"
    targetWhereClause: "is_deleted = 0"

BCP and validation

BCP speeds up data migration only. With the default regular extraction strategy, validation reads the source through the SQL Server driver at every level, whatever use_bcp is set to, so you can keep one Worker TOML for both migration and validation. For faster row validation (L3) on large tables, set validationConfiguration.extraction.strategy to cet_as (SQL Server 2022 or later, or Azure SQL Managed Instance) or cloud_direct (for example, on Azure SQL Database), and set validationConfiguration.extraction.externalStage to the matching Snowflake external stage. See L3 extraction.

validationConfiguration:
  rowValidation: true
  extraction:
    strategy: cet_as
    externalStage: TARGET_DB.PUBLIC.AZURE_LANDING

Data type mappings

SQL Server typeSnowflake target typeSupported for validationNotes
BITBOOLEANYes
Integers, DECIMAL, NUMERIC, FLOAT, REALNUMBER / FLOATYes
MONEY, SMALLMONEYNUMBER(38, 4)Yes
DATE, TIME, DATETIME, DATETIME2, SMALLDATETIMEDATE / TIME / TIMESTAMP_NTZYes
DATETIMEOFFSETTIMESTAMP_TZYes
CHAR, VARCHAR, NCHAR, NVARCHARVARCHARYes
BINARY, VARBINARY, UNIQUEIDENTIFIERBINARY / VARCHARYesUUID compared as an uppercase string
ROWVERSION, TIMESTAMPBINARY(8)YesSQL Server TIMESTAMP is a synonym for ROWVERSION, not a datetime. L3 compares the value as hexadecimal
HIERARCHYIDVARCHAR(4000)YesCompared as its hierarchy path string
TEXT, NTEXT, IMAGEVARCHAR / BINARYYesL1 and L2 check these columns. L3 leaves them out unless includeLobColumnsInRowValidation is true. See LOB columns in L3
XML, SQL_VARIANTVARIANTYesL2 compares NULL counts. L3 compares the value as text
JSONVARIANTYesSQL Server 2025 or later. L3 compares the JSON text
GEOGRAPHYGEOGRAPHYYesCompared as despaced Well-Known Text
GEOMETRYGEOMETRYYesCompared as despaced Well-Known Text
VECTORVECTOR(element_type, n)YesRow-level validation compares element by element

Note

Metrics validation (L2) summarizes each column with aggregates such as NULL count, min, and max; it doesn’t compare the values themselves. Distinct count is added only when you opt in with includeCountDistinct. For VECTOR, GEOGRAPHY, GEOMETRY, UNIQUEIDENTIFIER, XML, and SQL_VARIANT, a value change that preserves those aggregates (for example, editing a GUID or an XML document without changing the column’s NULL count) passes L2 without a mismatch. To compare the actual values of these columns, enable row validation (L3).

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?