Build your first validation

This topic describes the validation process and key concepts, then walks you through creating a simple validation to compare a source table against a target table.

Understanding the validation process

At a high level, Validata's validation process can be understood in the following phases:

Configure a Validation
1. Create a validation.
2. Select a validation scope and method.
  - Validata supports two validation scopes: comparing a singleton Validation Pair or a Validation Set (multiple Validation Pairs).
  - Validata supports six validation methods:
    Full-dataset Validation — use methods such as Vector, Fast Record, or Full Record Validation to compare entire datasets.
    Partial-dataset Validation — use methods such as Interval Validation to validate records updated within a time interval, or Key Validation to confirm the presence or absence of records.
    Custom Validation — use this method to compare a specific pair of tables using your own custom SQL query (available for comparing singleton Validation Pairs only).
3. Connect securely to your Source and Target data systems using Connection Profiles.
4. Complete the Advanced Settings:
  - Configure Halt Threshold.
  - Set the Revalidation interval.
  - Set up the schedule.
5. Create Validation Pair(s) by mapping the Source and Target tables and columns for comparison.
  Validata intelligently automaps the Source and Target tables and columns to build an initial mapping. You can review Validata's automapping and, where needed, map tables and columns differently. Validata will attempt to select a comparison key for every Validation Pair; you can choose to retain or modify the selected comparison key.
  Finally, you can also include or exclude tables or columns for comparison based on your business requirements.
Run the Validation
- Manually run the Validation, or
- Set up a recurring schedule on a weekly, daily, or hourly cadence.
Review Validation reports
Review the results of the comparison. If Validata determines that a pair of corresponding Source-Target tables are out-of-sync based on the Validation configuration, it generates a SQL script that you can execute on the Target data system to resolve the identified discrepancies.
Reconcile out-of-sync records
If there are out-of-sync records, reconcile the out-of-sync Target table(s) by executing the SQL-based reconciliation script. You can download the script from the Validation Pair Report to resolve the identified discrepancies.

Key concepts

Validata uses consistent terminology throughout the UI. The following concepts appear throughout this guide and in the console.

Validation: A validation represents a comparison between two datasets.
Source: The source is the dataset designated as the trusted reference or source of truth for the validation process. Generally, this is the upstream dataset in a replication pipeline, or the dataset you consider as the reference for any comparison.
Target: The target is the dataset compared against the trusted source dataset to identify discrepancies. Typically, this is the downstream dataset in a replication pipeline that is being validated for accuracy against the source.
Connection profile: A connection profile is a reusable configuration object that securely stores the authentication and connection attributes required to access an external system from within Validata.
Validation pair: A validation pair is the fundamental mapping relationship established between a source table and its corresponding target table.
Validation set: A validation set is a collection of one or more validation pairs that are validated together under a single validation configuration.
Validation type: Validata supports the following validation methods: Vector Validation, Fast Record Validation, Full Record Validation, Interval Validation, Key Validation, and Custom Validation.
Validation run: A validation run is a single execution of a validation. During each run, Validata compares the source and target datasets specified in the validation configuration.

For a detailed explanation of these and other concepts, see Concepts.

Walkthrough

This walkthrough guides you through creating a simple validation to compare a source table against a target table. In this example, you will use Vector Validation, the default validation method.

For an interactive version of this walkthrough, see the Interactive demo section.

Create a new validation: From the Validata home screen, click Create Validation.
Name: Enter a unique name for your validation.
Scope: Select Quick Start (Validation Pair). This scope is ideal for your first run as it focuses on a single pair of tables—a validation pair—from the source and target.
Validation type: Select Vector Validation. This default method computes signatures on the source and target systems to quickly identify discrepancies without transferring large volumes of data to the Validata Server.
Connect to source and target: You must now define where your data lives.
1. Connect source: Select an existing connection profile or create a new one. Enter the connection details for your source database (for example, Oracle), including hostname, port, and credentials.
2. Connect target: Repeat the process for your target system (for example, Snowflake).
3. Test connection: Always click Test Connection to ensure Validata can successfully access both systems before proceeding.
Enter the fully-qualified source and target table names.
Manage validation pair: Once connected, you must map the columns in the source table to the corresponding columns in the target table. Validata intelligently automaps the source columns to the target columns. You can review and modify the column mappings. If needed, you must also select the comparison key that specifies the columns that Validata must use to compare the tables.
Save and run: Save and run the validation.
Review results: When the run completes, Validata generates a validation report. If the source and target tables are identical, Validata will report that the tables are In-Sync.
You can download the JSON report for the validation run by clicking the download button to the right of the validation run name.

Testing out-of-sync detection

To see how Validata reports mismatched tables, you can override Validata's automapping and map non-identical columns between the source and target tables.

Open the validation and go to the Map Columns page. Map non-equivalent columns—such as mapping the first_name column in the source table to the last_name column in the target table, and the last_name column in the source table to the first_name column in the target table, as shown in the example—to intentionally create a comparison mismatch.
Save and run the validation.
Since you knowingly mapped non-identical columns, Validata reports that the tables are Out-of-Sync.
You can analyze the validation pair report to learn more about the mismatches.
1. The validation pair report shows the key comparison metrics, including the number of source and target records processed and details about the out-of-sync records.
2. If you view the out-of-sync records by their columns, you can see that the mismatches are created by your mapping the first_name column to last_name.
When Validata detects discrepancies in a validation pair, it generates a reconciliation script that you can execute on the target system to align the target table to the source table. Click Download SQL to Fix. The SQL script contains CREATE, INSERT, and UPDATE statements based on the results of the comparison.
The reconciliation script is based on a point-in-time comparison of the source and target datasets. The SQL script can align the source and target tables only if the source table has not changed since the comparison started.

Interactive demo

The following interactive demo covers the steps described in the Walkthough section.

To navigate the interactive demo, click the highlighted elements to advance to the next step. You can also use the left and right arrow keys on your keyboard to move between steps, or click the progress bar at the bottom to jump to a specific step. Some steps include short video clips that show screen transitions.