Skip to main content

Choosing Validation scope and method

Before creating a Validation, you must decide what to validate (the Scope) and how Validata should perform the comparison (the Method).

Selecting the right combination ensures that the Validation configuration aligns with your objectives. Once selected, you cannot change the Scope and Method in your Validation configuration.

Validation scope and methods

Validata provides two distinct scopes:

  1. Singleton Validation Pair (Quick Start):

    You can quickly get started by validating a single Validation Pair—comparing one Source table against its corresponding Target table.

    • Flexibility: A Singleton Validation Pair focuses on a single table-to-table comparison, giving you full control over how that specific pair is validated without the complexity of managing a larger configuration. Once configured, you cannot add additional Validation Pairs to this validation instance.

    • Supported Methods: All built-in validation methods are supported at this scope—Vector Validation, Fast Record Validation, Full Record Validation, Key Validation, and Interval Validation—as well as Custom Validation, which is available only for single-table comparisons.

    • Best for: Quick testing, targeted investigations, and one-off validations where you want complete control over how a specific Source–Target table pair is compared.

  2. Validation Set:

    You can use the Validation Set scope to compare one or more Validation Pairs simultaneously, allowing you to validate tables across one or multiple schemas in a single run.

    • Flexibility: A Validation Set is designed for scale and adaptability. You can select tables manually or use wildcard patterns to define rules that automatically select matching tables. Unlike the Singleton scope, you can modify this configuration at any time by adding new Validation Pairs or deselecting existing Pairs.

    • Supported Methods: This scope supports the standard suite of built-in methods: Vector Validation, Fast Record Validation, Full Record Validation, Key Validation, and Interval Validation.

    • Best for: Larger or recurring validations where you want to compare multiple tables together, apply consistent validation rules across schemas, or automate broad data quality checks. A Validation Set is highly customizable but may take more time to refine than a single-table validation.

See the Concepts article on Validation Types to learn more about the different Validation methods.

Validation use cases and recommendations

Example use case

Recommendations & Considerations

You want to compare a single Source table with its corresponding Target table.

  • We recommend that you begin with the Quick Start (Validation Pair) option, and then select the Validation Method of your choice.

  • This approach compares exactly one Source table to one Target table. This is the fastest way to get started and supports all built-in methods as well as Custom Validation.

  • The singleton Validation Pair comparison is suitable for focused checks, troubleshooting, and SQL-based comparisons that require fine-grained control.

You want to compare data between two databases.

  • We recommend that you start with by selecting the Validation Set scope so that you can validate multiple tables across the Source and Target datasets.

  • If the Source and Target data systems store data in compatible structures, begin with built-in full-dataset methods such as Vector Validation or Fast Record Validation.

You want to validate data in a data warehouse that stores data in APPEND mode (paired with a database or another warehouse).

  • You must use Custom Validation. Built-in methods exclude the duplicate rows created by APPEND-mode pipelines, which prevents them from validating updated records. Define SQL queries that return only the latest version of each record, ensuring both sides of the comparison represent the most current dataset.

  • Custom Validation is only supported when you validate a single pair of Source-Target tables using the Quick Start (Validation Pair) approach.

You want to validate data in a data warehouse that stores data in MERGE mode (paired with a database or another warehouse).

  • We recommend that you start with by selecting the Validation Set scope so that you can validate multiple tables across the Source and Target datasets.

  • If the Source and Target data systems store data in compatible structures, begin with built-in full-dataset methods such as Vector Validation or Fast Record Validation.

You want to validate data in a data warehouse where some tables are populated in APPEND mode and other tables are populated in MERGE mode (paired with a database or another warehouse).

  • You must use Custom Validation to validate a data warehouse table that is populated in APPEND mode. You must create a separate Validation configuration for each table that is populated in APPEND mode.

  • You can compare the other data warehouse tables by selecting the Validation Set scope, and starting with Vector Validation or Fast Record Validation.

You want to validate data that is transformed before being written to the Target.

  • Use Custom Validation when the transformation can be represented using SQL functions or queries (e.g., converting timestamps, reformatting dates, trimming/padding strings, adjusting numeric precision).

  • Custom Validation is only supported when you validate a single pair of Source-Target tables using the Quick Start (Validation Pair) approach.

  • If the data transformations cannot be expressed in SQL, then Validata cannot automatically align the values and, therefore, cannot validate the Source and Target datasets.

You want to match record counts between the Source and Target datasets.

  • Use Key Validation, the simplest approach for verifying record existence.

  • It compares only the Comparison Key values and identifies whether each key is present in both systems. This is the most efficient method when you need to confirm row-level presence without comparing full record content.

  • Both scopes - Validation Pair and Validation Set - support Key Validation. Choose the scope that fits with your specific needs.

You want to run a Validation every few hours to validate data that was recently updated.

  • If your Source and Target datasets contain a timestamp or datetime column, begin with Interval Validation to compare only the data updated within the specified time window.

  • Both scopes - Validation Pair and Validation Set - support Interval Validation. Choose the scope that fits with your specific needs.

You want to validate records between Source and Target table pairs where the comparison key is a text column that behaves differently between Source and Target due to differences in collation or character-set settings.

  • We strongly recommend using a unique, non-text column (numeric key) as the comparison key whenever possible to avoid collation issues.

  • If a numeric key is not available, you must use Custom Validation to normalize textual keys so that both Source and Target systems apply the same collation and character-set rules (case sensitivity, accent handling, encoding, etc.).

  • Define SQL queries that enforce identical ordering in both systems (for example, apply a binary collation in SQL Server to match Snowflake). Ensure both queries return the same columns in the same order.

  • Use Custom Validation whenever the text-based comparison key in the Source and Target is subject to:

    • Different sort order

    • Different case-sensitivity rules

    • Different encodings

    • Different handling of special characters or accents

Operational considerations

  • You can significantly enhance your validation strategy by combining multiple validation methods to achieve comprehensive coverage. A common approach is to use partial-dataset methods—like Key Validation or Interval Validation—to quickly detect incremental discrepancies, complemented by scheduling a full-dataset validation (e.g., Vector, Fast Record, or Full Record Validation) during your scheduled maintenance windows.