How to test Reference Data using Validation Rule?
In this use case, we will demonstrate how to test whether the reference data in a table matches the expected list of values using iceDQ. By following these steps, you can ensure data accuracy and identify any discrepancies in the reference data.
Steps
Below steps have been followed in the video.
Create Rule
To begin, create a validation rule in iceDQ. Give the rule an appropriate name. In this example, we will validate the reference data in the "phone number type" table of the AdventureWorks database.
Set up Connections
Set up the source connection to the table. In this case, we connect to the "person" schema and select the "phone number type" table.
Preview Data
Preview the data to understand the distinct phone number types present in the table. In this example, we have identified three distinct phone number types: 'cell', 'home', and 'work'.
Add Checks
Add a check to verify whether the 'name' column contains all the expected reference values. Choose the 'Contains' check and select the 'name' column. Enter the expected reference values: 'cell', 'home', and 'work'. Save the check.
Publish & Run
Publish the rule and execute it. After execution, check the status. A successful execution indicates that the reference table contains only the expected phone number types: 'cell', 'home', and 'work'.
Video: How to test Reference Data using Validation Rule?
Conclusion
By following this use case with iceDQ, you can effectively validate reference data and ensure data accuracy. By applying similar approaches to other reference tables or datasets, organizations can maintain reliable data sources, enabling better decision-making and data-driven insights.