By David Loshin
What happens if a purchase record refers to a product code that does not exist in the product master table?
Typically, for the execution of the transaction, this would certainly be a problem in attempting to fulfill an order for unknown products. But even if you assume that the product existed when it was purchased, it might have been eliminated or replaced as time advances and the product line is upgraded or enhanced. In this case, historical reporting or trend analysis would inadvertently fail, potentially leading to flawed results and even flawed decisions.
This is an example of the need for assurance of referential integrity, which asserts that a reference to an entity used in one table exists as an attribute in either the same or a different table. More simply, it implies that you may not refer to something that does not exist. Integrity constraints are examples of cross-table data quality rules that can be validated using data profiling.
And in the upcoming post, we will discuss how data profiling tools are instructed to perform cross-column and cross-table validation.