Validation Types

Mechanism: Validata computes vector signatures of the Source and Target tables in their respective data systems, then compares the signatures on the Validata server to identify out-of-sync records.

Resource usage: Because only compact signatures are transferred, this method significantly reduces network traffic and potentially lowers bandwidth costs—making it especially well-suited for very large datasets. However, the vector computations can increase the compute load on the external data system.

Use case: Vector Validation is fast and efficient across datasets of all sizes, but it is especially well-suited for massive datasets.

Constraints: Vector Validation does not identify and flag “perfect” duplicate records in a table, i.e. records where the data in the comparison key columns and all other columns are identical. If you have defined constraints such as a Primary Key or a Unique Constraint, then your data system will prevent “perfect” duplicate records, and you can use the Vector Validation method.

Mechanism: In Fast Record Validation, Validata evaluates the comparison key columns and a record hash of the non-key columns for every record from the Source and Target tables to determine dataset differences. The hashes are computed in the respective data systems and then transferred, along with the comparison key columns, to the Validata server.

Resource usage: This method is optimized for reduced impact on the Source and Target data systems because it offloads the full set difference computation to the Validata server, reducing the work on the external data systems. However, the data transferred to the Validata server and the resource usage on the Validata server are both greater than the Vector Validation method.

Use case: You can use the Fast Record Validation to evaluate databases of all sizes, but it is particularly useful when comparing medium-to-large datasets.

Mechanism: Full Record Validation fetches the dataset from both Source and Target systems and performs a full column-by-column comparison on the Validata server.

Resource usage: Because all computation happens within the Validata server, there is minimal impact on the Source and Target systems. This approach provides the most complete comparison but is resource-intensive and results in higher data-transfer volumes.

Use case: Full Record Validation is recommended for small-to-medium datasets. While you can use it to compare large datasets, expect increased transfer times and processing overhead.

Mechanism: Key Validation provides an easy way to confirm record presence based solely on the comparison key. Validata compares only key values in the Validata server and classifies each unique key as one of In-Sync, Out-of-Sync: Extra at Source or Out-of-Sync: Extra at Target.

Resource usage: This is a lightweight validation method.

Use case: You can use Key Validation when you need to verify record existence or unique-record counts, rather than full record content.

Mechanism: Interval Validation compares only those records updated within a specified time window. You can define the window using:

Resource usage: Because all computation happens within the Validata server, there is minimal impact on the Source and Target systems. The data transferred to and the resources used on the Validata server depend on the size of the time window.

Use case: You can choose Interval Validation to align your validations with your replication cadence or to perform incremental checks that catch discrepancies sooner, without waiting for full maintenance-window checks. For example, run a validation every 2 hours to compare data updated in the previous 2 hours.

Constraints: This method requires the presence of a timestamp or datetime column in both the Source and Target tables.

Background: When replicating data to a data warehouse (e.g., Snowflake, BigQuery, or Databricks) using APPEND mode, updated records are written as new rows in the Target table, rather than modifying existing ones. This creates duplicate rows that share the same keys.

Validata’s built-in validation methods automatically classify and exclude these duplicates, which means they compare only original, unchanged data and do not validate updated records.

Solution: To validate APPEND-mode datasets, you can use Custom Validation with SQL queries that produce deduplicated views of both the Source and Target tables. Each query should return only the latest version of each record and output columns in the same order.

Validata runs these queries against their respective systems and compares the results, enabling accurate validation of updated records.

Custom Validation supports validation between a database table and a warehouse table populated in APPEND mode, as well as between two warehouse tables when one or both use APPEND-mode replication.

Background: When replicating data, the Target system may store certain values in a different format or structure than the Source,such as rewritten date fields, truncated strings, or numbers with reduced precision. These differences may arise from transformation logic applied during replication or simply from the Target using a different data type or representation for the same underlying value.

Because Validata’s built-in methods compare values exactly as stored, they will report these differences as Content Mismatch errors even when the information is effectively equivalent.

Solution: When the transformation can be expressed using an SQL function or query—such as reformatting dates, converting timestamps, trimming or padding text, or adjusting numeric precision—you can use Custom Validation to align the Source and Target before comparison.

By defining SQL queries that apply the required transformation (or its inverse), you can generate standardized result sets that reflect equivalent values, allowing Validata to compare the datasets accurately.