Overview

The Sifflet Referential Integrity monitor ensures the validity of columns between two different tables. It checks that values in one table match the valid values of another.

Example: You have an ORDER table with a customer_id foreign key that points to the CUSTOMER table's id key. You want to ensure that the customer_id values in the ORDER table are present in the CUSTOMER table and thus valid.

How To

Table Selection

The Referential Integrity monitor requires you to select two tables. A common use case is to select a fact table first and a dimension table second. After you select two tables, select a field from each table to compare values between the tables.

Configuration

Comparison Settings

Left Records Match Right Dataset (Default)

The most common referential integrity use case. This checks that the values in the first (left) table are present in the second (right) table.

Use case: Every order must have a valid customer that has an entry in the customer table.

Right Records Match Left Dataset

This checks that the values in the second (right) table are present in the first (left) table.

Use case: Every product must have an inventory line.

Left and Right Records Fully Match

Both tables should contain all the values present in their counterpart.

Use case: Every product must have an inventory line, and every inventory line must have a valid product.

Time Settings

In Referential Integrity monitors, the time settings only apply to the first (left) table.

Time settings allow for incremental checks on the first (left) table to avoid scanning the entire table.

For example, with Time Settings configured with a daily aggregation and no lookback settings, the monitor only scans one day's worth of the first table on each run.

Use case: You want to check every day that all the orders from the past day have valid customer IDs.

📘

Incremental Monitors can save costs

Make sure that the first table selected in your monitor is your fact table.

Time Settings documentation.

Time Settings Off: When time settings are not activated, the monitor scans the entire table on each run. Use this when comparing two dimension tables, e.g., every product must have a valid product category.

As Code

Monitor Kind: ReferentialIntegrity

Params

left: References the first table selected.

left.field: The field to match on the left table

left.whereStatement: SQL WHERE statement (without the WHERE) to apply to the left table.

left.timeWindow: Time Window Settings for the left table, to allow for incremental checks.

right: References the second table selected.

right.field: The field to match on the right table.

right.whereStatement: SQL WHERE statement (without the WHERE) to apply to the right table.

matchType:

• LeftOnRight(default): Ensures the values in the left table are present in the right table.
• RightOnLeft: Ensures the values in the right table are present in the left table.
• Full: Values on either side must be present on the other.

Simple Example - Referential Integrity on Customer_Id Foreign Key

datasets:
- uri: snowflake://sifflet/DEMO.SE_ENV.ORDERS
- uri: snowflake://sifflet/DEMO.SE_ENV.CUSTOMERS
parameters:
  kind: ReferentialIntegrity
  left:
    field: CUSTOMER_ID
  right:
    field: CUSTOMER_ID

Same example but with tables in the opposite order

datasets:
- uri: snowflake://sifflet/DEMO.SE_ENV.CUSTOMERS
- uri: snowflake://sifflet/DEMO.SE_ENV.ORDERS
parameters:
  kind: ReferentialIntegrity
  matchType: RightOnLeft
  left:
    field: CUSTOMER_ID
  right:
    field: CUSTOMER_ID

Complex Example - Incremental Referential Integrity on Customer_Id

• Checks that orders in the last day have valid customer IDs.
• Includes where statements to filter out bad orders and expired customers.

datasets:
- uri: snowflake://sifflet/DEMO.SE_ENV.ORDERS
- uri: snowflake://sifflet/DEMO.SE_ENV.CUSTOMERS
parameters:
  kind: ReferentialIntegrity
  left:
    field: CUSTOMER_ID
    whereStatement: goodorder IS TRUE
    timeWindow:
      field: auto
      firstRun: P365D
      frequency: P1D
  right:
    field: CUSTOMER_ID
    whereStatement: NOT expired