Validator Overview

The Referential Integrity Validator ensures record-level consistency between two datasets within a data warehouse. This validator verifies that keys (and optionally values) in your primary source have corresponding matches in a reference source, to help you identify "orphaned" records or data mismatches between related tables.

Fields mapping for referential Integrity validator

For an overview of all validator types, see Validator Types.

Metric Configuration Parameters

The following parameters are used to configure the Metric calculation for this validator.

Parameters	Description
Reference source	Choose the source that contains the dataset you want to compare against the primary source. The source selection is restricted to sources that use the same credential.
Window	Select from a list of windows or create a new window to specify how to aggregate the data on the primary source.
Reference source window	Select from a list of windows or create a new window to specify how to aggregate the data on the reference source. See Window Configuration and Constraints.
Key fields mapping	Used to join or "match" records between the two sources (for example, `order_ID` to `transaction_ID`). At least one key mapping is required to identify matching records.
Value fields mapping	(Optional) Once records are matched using key fields, the validator compares the specified value fields to ensure they are identical across both sources. A record whose values differ counts as unmatched.
Source filter	(Optional) Select from a list of filters or create a new filter to specify which source records to include (to narrow the scope of the check).
Reference filter	(Optional) Select from a list of filters or create a new filter to specify which reference records to include.
Segmentation	(Optional) Apply segmentation to analyze data in separate groups. Default is `Unsegmented`.

Window Configuration and Constraints

The Window determines how data is aggregated before the comparison. To ensure a valid check, the following rules apply:

Primary Source Window	Allowed Reference Source Window	Restriction
Global	Global	Must match exactly.
Tumbling	Global or Tumbling	If both are Tumbling, they must be the same size (for example, both 24-hour windows).

Metric Calculation

The Referential Integrity metric is the number of unmatched records in the primary source for each window and segment. It is a count of rows, not a ratio or percentage.

A primary source record counts as unmatched when the reference source window has no record with the same values in all key fields and, if configured, all value fields. A key match with differing values counts the same as a missing key.

Metric = number of primary source records with no matching record in the reference source

Keep the following in mind when you interpret the metric:

One direction only. The validator checks that primary source records exist in the reference source. Records that exist only in the reference source are not counted.
Every row counts. If an unmatched key appears in several primary source rows, each row adds to the metric. Duplicate rows in the reference source don't affect the metric.
Filters narrow the scope. Primary source records excluded by the source filter are not counted. Windows and segments with no matching records report a metric of 0.
Segmentation applies to the primary source. Reference records are matched on key fields, not on segment.

Null and Empty Values

Situation	Result
Key field is null in the primary source	Counted as unmatched
Value field is null in either source (including both)	Counted as unmatched
Empty string compared with empty string	Match
Empty string compared with null	Counted as unmatched

Values of different types can match when they look the same, for example the number 1 and the string '1'.

To exclude records with null keys from the check, add a source filter that uses a Null filter with the Is not null operator on the key fields.

Pass Condition

A record set with full referential integrity has a metric of 0. Use a Fixed threshold set to Equal to 0 so that any unmatched record creates an incident. To tolerate a known number of orphaned records, set the fixed threshold to Less than or equal to that number.

New validators default to a Dynamic threshold, which learns the usual number of unmatched records and only alerts on unusual changes. Change it to Fixed if every unmatched record matters. For more information, see About Thresholds.

Debug Information

When you debug an incident, the debug query lists the unmatched primary source records, and a Diff type column shows why each record failed:

RECORD_MISSING: no reference record has the same key field values.
VALUE_MISMATCH: a reference record has the same key field values, but one or more value fields differ.

🚧
Records whose value fields are null in both sources count toward the metric but don't appear in the debug query results.

For more information, see Debugging an Incident.