Lookup Check Rule

Description

The Lookup check rule in data quality refers to the process of validating data by comparing it against a reference dataset or source to ensure that the values are accurate, consistent, and conform to predefined rules.

Rule Configuration

The Rule configuration for a lookup check involves using a reference table, which contains valid values for comparison. The reference column within this table holds the specific data or keys that the target dataset is validated against, ensuring that only valid entries are accepted during the lookup process.

• Reference Table A reference table in a lookup check is a dataset or table that contains valid or predefined values used for comparison during the lookup process. It serves as the authoritative source to validate the data being checked.

• Reference Column Reference column is the specific column in a reference table that holds the valid values used for comparison during a lookup check. It contains the data or keys that the target dataset will be validated against.

Success Criteria

The success criteria for a Lookup Check rule are based on how the data from the target dataset matches the predefined values in the reference table and column. The success condition is evaluated based on the comparison of data using the defined operator (e.g., Greater than, Equal to, etc.) and a threshold value.

• The success condition depends on how the Reference Tableand Reference Column is configured.

• The success condition is met if the count satisfies the given operator and value.

• For example if reference tableand contents in reference column match with the input it passes else it fails.

  Configuration Fields

  • Operator Options

    • Greater than

    • Less than

    • Equal to

    • Between (requires specifying a start and end range)

    • Operator Defines the comparison operation (Greater Than, Less Than, Equal To, or Between).

    • Value The threshold value used for success criteria. Required for Greater than, Less than, and Equal to operators.

    • Value Range Required only when the Between operator is selected, specifying the start and end range.

    • Threshold Type Indicates whether the Value or Value Range to be considered as percentage or an absolute count.

    • Allow Null Values Determines if null values are permitted.

Sample Input

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

Country details

Country Code	Country Name
US	United States
CA	Canada
FR	France
GB	United Kingdom

Sample Rule Configuration

• Reference Table Countrydetails
• Reference Column Country_Code

Sample Success Criteria Configuration

• Operator Equal to
• Value 3
• Threshold Type Absolute Count
• Allow Null Values False

Sample Output

Column Name	Rule Name	Success Count	Failure Count	Within Threshold	Null Count
Country Code	Country Lookup Check	3	1	Yes	1
Other Country Code	Lookup Check	1	3	No	1