Referential Integrity

Referential integrity is the concept of maintaining relationships between different data elements across multiple databases and systems to maintain data consistency. Bigeye can be utilized to automate referential integrity monitoring to ensure that data is always accurate and consistent by creating a referential integrity check.

For example, a company might have a product master table containing a list of unique products they sell and a product price table containing the names and prices of those products. By performing a referential integrity check, the company can ensure that all the products in the price table are consistent with the master product list and that no products are missing or renamed.

To perform referential integrity check in Bigeye, follow the steps below to create a template and use that template in a metric.

Create a template

A template is a custom SQL query that you can populate to perform the referential integrity check in Bigeye. To create the template, follow these steps:

1. Log in to your Bigeye account and navigate to the Custom Monitoring page.

2. Go to the Templates tab and click New Template.

3. Select the source where you want to perform the referential integrity check.

4. Enter a name for the template.

5. Select the Results type as a Boolean.

   📘

   The template checks if each row satisfies the referential integrity condition and returns a True or False result. For instance, if the table has 10 records, the template generates 10 true or false statements based on whether each row satisfies the referential integrity. When we use this template to create a metric, it summarizes the results by counting either the number or the percentage of true values.

6. Populate the template input box with the referential integrity query. Add parameters using {{parameter_name}} to make the query dynamic. Here's a sample query for your reference:

{{ column_to_check }} in (select distinct({{ lookup_column }}) FROM {{ lookup_table }} where {{{ condition }}})

7. Select the type for each parameter.

📘

Bigeye's templates can reference one table at a time. Therefore, the column you want to check must have the parameter type as a column while every other input, such as lookup table, lookup column, and condition, must be strings.

8. Click Create Template.

The Referential Integrity Check template is created, and it can now be used with a metric to summarize the results of the referential integrity check.

Create a metric

To create the metric, go to the source and find the table where you want to create the metric.

1. Select the table from your source catalog browser where you want to create the metric and click New Metric.

2. Select the column to use for the referential integrity check.

3. In the Metric type dropdown, select the Referential Integrity Check template.

4. Name the metric and provide a description.

5. Populate the template configuration inputs:

   1. Select an aggregation type of Percent to summarize your template results as a percentage.
   2. The column_to_check input should already be populated with the column you selected in the column input box above. If it is not, then populate it with the column you want to perform your referential integrity check upon.
   3. Populate the condition (string) input with filters to have in the where clause of your template.
   4. The lookup_table (string) input should be the table you want to perform your lookup check on.
   5. The lookup_column (string) input should be the column on the table from the previous bullet to perform your lookup check.

   

6. Enter the time configuration details, such as the Lookback window, Window type, and Bucket size. To learn more about time configuration, see Row Creation Time.

7. Set the threshold type as Constant with a lower bound of 1 (100%). The metric will alert when the value falls below this bound.

8. Populate the other threshold configuration inputs as desired.

9. Add channel details to receive notifications when the metric alerts.

10. Click Create Metric.

Use the Referential Integrity Check

When the metric is executed, Bigeye will drop the referential integrity check template directly in the executed query on the source, surrounded by the level of aggregation selected.