The Reference Data Validation rule ensures that values in a dataset match valid entries from a designated reference dataset. This is typically used to maintain consistency, enforce business rules, and validate the accuracy of values.
Example Usage:
- Verify that all country codes in the input data exist in a reference country table
- Validate product categories or region codes against an authoritative list
Configuration Fields
Rule-Specific Configuration
| Field Name | Description | Required | Data Type |
|---|
| Reference Table | The dataset or table containing valid reference values | Yes | String |
| Reference Column | The column within the reference table used for comparison | Yes | String |
Success Criteria Configuration
| Field Name | Description | Required | Options | Default |
|---|
| Operator | Comparison operation for the result count | Yes | GreaterThan, LessThan, EqualTo, Between | |
| Threshold Value | Value for comparison (for non-Between operators) | Conditional | Number | - |
| Threshold Min | Minimum value (for Between operator) | Conditional | Number | - |
| Threshold Max | Maximum value (for Between operator) | Conditional | Number | - |
| Threshold Type | Whether threshold represents an absolute count or percentage | No | Absolute Count / Percentage | Absolute Count |
| Allow Nulls | Whether null values should be treated as valid | No | true / false | false |
| Check For Match | Whether to validate for match or negation of the lookup condition | No | true / false | true |
| ID | Name | Country Code | Other Country Code |
|---|
| 1 | Alice | US | NULL |
| 2 | Bob | CA | BR |
| 3 | Charlie | FR | US |
| 4 | David | NULL | NZ |
Sample Reference Table
Table: CountryDetails
| Country Code | Country Name |
|---|
| US | United States |
| CA | Canada |
| FR | France |
| GB | United Kingdom |
Sample Configurations
Example: Country Code Validation
| Configuration Field | Value |
|---|
| Reference Table | CountryDetails |
| Reference Column | Country Code |
| Operator | EqualTo |
| Threshold Value | 3 |
| Threshold Type | Absolute Count |
| Allow Nulls | false |
| Check For Match | false |
Explanation:
Validates that exactly 3 values in the Country Code column do not exist in the reference table (because Check For Match is false). This allows validating for non-matches (e.g., to find invalid entries).
Sample Output
| Column Name | Rule Name | Success Count | Failure Count | Null Count | Within Threshold |
|---|
| Country Code | Reference Data Validation | 3 | 1 | 1 | Yes |
| Other Country Code | Reference Data Validation | 1 | 3 | 1 | No |
