Comparison Rules compare the results of two different SQL queries, and generate an incident if the results differ by more than a threshold set by the user. The two queries can both be pointed to the same source (e.g. both at Snowflake), or they can point to two different sources (e.g. one pointed at SQL Server, one pointed at Snowflake). A Comparison Rule can be run against any data source that a user could run a SQL Rule against.

Comparison Rules are often used to validate that the sync of data from a transactional database over to a main data platform has been successful. These are often known as 'source-to-target' checks. As a result, the two SQL queries within a Comparison Rule are called the source query and target query, respectively.

Setup

Select the data format that the source and target queries will return:

Some additional detail about these choices:

• Segmented values requires that each query returns two columns. The first column is treated as the key, and comparisons will be made for values that have matching keys. There is a limit of 100 unique keys per query.
• Count of rows is the only option available for environments where data sampling is turned off. It wraps the results of the query in a COUNT.

Input the source and target queries

For each query, the user defines which warehouse that each query should run against.

Define the alert condition

How the delta between source and target is calculated differs slightly depending on if user has selected % or not.

• When rows or value is selected, delta = abs(target - source). In other words, the absolute value of the difference between target and source.
• When % is selected, delta = abs((target - source)/source) * 100. In other words, the absolute value of target minus source, divided by source, and then converted into a percentage. A practical example would be:
  • Source query returned a count of 200
  • Target query returned a count of 195
  • Delta is 2.5%
  • If the user input a threshold of 2, this would trigger an incident. If the user inputed a threshold of 3, this would not trigger an incident.

Note: if using Segmented values, if one segment is present in the results of one query but not in the other, it will be treated as a breach and generate an Incident.

Input remaining monitor details

This include the description & notes, schedule, and notification preferences.

Incidents and Results

In notifications, IncidentIQ, and Monitor Results, the user is shown the:

• Time of the execution
• Result of the source query
• Result of the target query
• Delta between the source and target queries
• Threshold for an incident to be generated