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
sqlserverextra to use the preferredmssql-pythondriver without a system ODBC installation. The Worker falls back topyodbcwhen the native driver isn’t available or the Worker TOMLextra_optionscontains 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:
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:
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.
Data type mappings¶
| SQL Server type | Snowflake target type | Supported for validation | Notes |
|---|---|---|---|
| BIT | BOOLEAN | Yes | |
| Integers, DECIMAL, NUMERIC, FLOAT, REAL | NUMBER / FLOAT | Yes | |
| MONEY, SMALLMONEY | NUMBER(38, 4) | Yes | |
| DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME | DATE / TIME / TIMESTAMP_NTZ | Yes | |
| DATETIMEOFFSET | TIMESTAMP_TZ | Yes | |
| CHAR, VARCHAR, NCHAR, NVARCHAR | VARCHAR | Yes | |
| BINARY, VARBINARY, UNIQUEIDENTIFIER | BINARY / VARCHAR | Yes | UUID compared as an uppercase string |
| ROWVERSION, TIMESTAMP | BINARY(8) | Yes | SQL Server TIMESTAMP is a synonym for ROWVERSION, not a datetime. L3 compares the value as hexadecimal |
| HIERARCHYID | VARCHAR(4000) | Yes | Compared as its hierarchy path string |
| TEXT, NTEXT, IMAGE | VARCHAR / BINARY | Yes | L1 and L2 check these columns. L3 leaves them out unless includeLobColumnsInRowValidation is true. See LOB columns in L3 |
| XML, SQL_VARIANT | VARIANT | Yes | L2 compares NULL counts. L3 compares the value as text |
| JSON | VARIANT | Yes | SQL Server 2025 or later. L3 compares the JSON text |
| 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 |
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:
-
Anti-locking: Hints are off by default. On busy source tables, set
queryModifiers.objectModifierto" WITH (NOLOCK)"to avoid blocking on source locks, at the cost of dirty reads that can cause falseMISMATCHresults. See Anti-locking and query modifiers.Prompt: