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
Metric configuration parameters
The following parameters are used to configure the Metric calculation for this validator.Window configuration and constraints
The Window determines how data is aggregated before the comparison. To ensure a valid check, the following rules apply: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. 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
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 of0. 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.