Configuring Custom Validation

When you replicate data to a data warehouse (e.g. Snowflake, BigQuery or Databricks) in APPEND mode, each incoming record is added as a new row. Existing rows are never updated or deleted.

If you update an existing record in your Source dataset, the data warehouse appends the updated version as a separate row in the Target table, creating duplicates that share the same primary or other identifying keys.

Validata’s built-in validation methods—Vector Validation, Fast Record Validation, Full Record Validation, Key Validation, Interval Validation—automatically classify and exclude these duplicate records from comparison. Consequently, these methods only compare original, unchanged records in the Target table and do not validate any updated rows produced by APPEND-mode replication.

You can use an SQL query to create a unique view from the APPEND-mode data warehouse table, fetching only the most recently updated version of each record. You will need a similar, appropriate SQL query for the Source table. Validata compares the results of these standardized, query-driven datasets.

To validate data produced by APPEND-mode replication, use Custom Validation with SQL-driven, deduplicated views of your Source and Target tables:

You can use Custom Validation to validate data between:

When you replicate data from one system to another, the Target may store certain fields in a different format or structure than the Source. These differences often result from transformation logic applied during replication (in-flight or by the Target system) or simply because the Target requires a different data type for the same underlying value.

Validata’s built-in validation methods—Vector Validation, Fast Record Validation, Full Record Validation, Key Validation, and Interval Validation—compare Source and Target values exactly as stored. Because they do not apply transformation logic, they will flag every row containing a transformed column as OUT-OF-SYNC: Content Mismatch, even when the underlying data is equivalent.

Common examples include:

In each of these scenarios, the built-in methods compare the raw values and do not account for the transformations that produced the Target results, leading to mismatched outcomes.

To validate datasets where the Source and Target differ due to a transformation, use Custom Validation when the transformation can be expressed using SQL functions or queries. By defining custom SQL queries, you can standardize the output from both the Source and Target systems, ensuring Validata compares identical data representations.

You can use Custom Validation to compare data between any two systems—databases, data warehouses, or a mix—whenever the required transformation can be represented using SQL.

Text-based comparison keys are interpreted and sorted according to the collation and character-set settings of each system. If the Source and Target differ in case sensitivity, accent handling, encoding, or sort order, they return rows in a different sequence.

Validata's row-aligned comparison methods require consistent ordering of returned rows. When textual keys sort differently across systems, matching records appear in different positions, and Validata may incorrectly classify them as Out-of-Sync even though the underlying business data is the same.

For example, SQL Server often uses a case-insensitive collation (such as SQL_Latin1_General_CP1_CI_AS), while Snowflake uses UTF-8 binary ordering, which is case-sensitive. Identical values therefore sort differently, resulting in inconsistent row ordering and incorrect mismatches. Encoding differences (for example, UTF-16 vs. UTF-8) can further influence comparison behavior.

To validate table pairs where the comparison key is a text column affected by collation or character-set differences, use Custom Validation and explicitly enforce identical ordering in your SQL queries. This ensures Validata receives both datasets in the same sequence.

SELECT StringKey, STATUS
FROM SQLTestdata.TimestampKeyTable
ORDER BY StringKey COLLATE Latin1_General_100_BIN2;

SELECT StringKey, STATUS
FROM SQLTestdata.TimestampKeyTable
ORDER BY StringKey;

This method of Custom Validation is strongly recommended whenever a text-based comparison key in the Source and Target is subject to: