Column Comparison Thresholding

Versions: 8.0+

Versions:4.5+

Comparisons with Thresholds

By default, QueryPairs execute with an "exact-match" comparison - if the data differs between any pair of Source and Target "cells" in any way, the QueryPair fails. In certain cases, you may want to allow for a range of acceptable values when testing your data. QuerySurge lets you set a threshold for any numeric data-type columns. When the QueryPair results are evaluated, the Threshold values will be applied to the comparison for each of the specified columns.

Note: We strongly recommend that you order your Source and Target queries (i.e. using an ORDER BY clause on both queries so that they are identically ordered) so that your results are more readable, and that you use Shared Keys wherever possible (or comply with our "implicit keys" assumption). See our Key Column Specification article for more information.

Column Threshold Prerequisites

Because the idea of threshold-based comparisons applies only to numeric types, you must have Data Type Checking turned On (otherwise all columns are treated as string data). This is the default setting for QueryPairs, so you'll have to turn data type checking on only if it was previously turned off.

Note: The Data Type Checking On radiobutton must be selected in order for Thresholding to be applied.

Column Threshold Settings

Threshold values can be set under the Properties tab for each QueryPair; the Thresholding feature offers either percentage based or absolute value based threshold evaluations.

Column Origin

The Column Origin can be from either the Source or Target query of your QueryPair. Any delta in the values is measured against the selected Origin Column during comparison. 

Percentage-Based and Absolute Thresholds

Consider a Source column S and a Target column T. If we set up a percentage based threshold based on S (i.e. we select From Source Query), for percentage P, then the QueryPair will pass if, for each row in T:

T ≥ S - (P% · S) and T ≤ S + (P% · S)

In other words, each value in T must be in the range defined by S ± P%.

Likewise, for an absolute threshold of A if the Target is within ±A of the Source, the QueryPair will pass, which means that T is in the range defined by S ± A:

T ≥ S - (A) and T ≤ S + (A)

If we had selected From Target Query in our setup, the roles of the Source column S and the Target column T are reversed.

Note: The ranges, whether percentage-based or absolute, are inclusive ranges.

Percentage-Based Thresholds

This option allows the user to define an acceptable percentage deviation from the Origin Column value. Percentage increases and decreases are treated the same way. Acceptable Threshold percent values range from 1E+30% to 1E-6% (0.000001%).

Absolute Thresholds

This option allows the user to define an absolute ± range of acceptable deviation from the selected Origin Column value. Acceptable Threshold values range from 1E+30 to to 1E-6 (0.000001).

Generate Column Names from the QueryPair

The  button auto-populates all available column names based on your query. You can then select the columns to apply a threshold to:  

Add Column Names Manually

There are some cases in which QuerySurge may not be able to parse your query to auto-populate the column names. You can add in column names manually by clicking on the  button and typing the column name. Column names are returned with your resultset, and it may be helpful to add aliases to your query: 

Deleting Column Names

Column names can be deleted individually by clicking on the  button in each row.

You also can clear all column names by clicking on the  button.

A Worked Threshold Example

 In this example we are looking at QueryPair that compares the Price column for the Product Widget.

When QueryPairs are created the default setting is to have Column Thresholds Options set to Disabled. In this case, an exact match comparison will be performed, resulting in failure.

300 != 100

Percentage-Based Thresholds

If we enable Thresholding for the Price column, we need to specify the range of the threshold. Here we set the Column Origin as the Source Query and the Threshold value to ±150%

Now we see that the Design-Time Runs status is Passed. This is because 100 falls within the ±150% deviation from 300, or between -150 and +750:

300 - (1.5 · 300) = -150

300 + (1.5 · 300) = +750 

 

However, if we switch our Column Origin to Target while leaving the Threshold Value at 150%, we get see that we get a different result.

A failure is now generated when using the Target Query as the Column Origin. This is because 300 is greater than a 150% deviation from 100. Its a 200% change in value, i.e. 300 is outside the range -50 to +250:

100 - (1.5 · 100) = -50

100 + (1.5 · 100) = +250

Absolute Thresholds

If we switch our Thresholds to use Absolute and set our Threshold Value to 200 we get passing results for both From Source Query and From Target Query Column Origin settings. Here 100 is within 200 of 300, i.e. 100 falls in the range of +100 to +500:

300 - (200) = +100

300 + (200) = +500

and 300 is also within 200 of 100 (300 falls in the range of -100 and +300):

100 - (200) = -100

100 + (200) = +300

Important Notes

• Threshold values are inclusive, as noted. This means any differences up to an including the Threshold Value will result in a Pass outcome. Anything outside of the defined Threshold value will result in a Fail outcome. 
• Data Type Checking must be enabled for Thresholding to work. QuerySurge will warn you if you attempt to turn Data Type Checking off.
• Threshold values are included in all QueryPair Exports.
• Columns must have a numeric data type defined or be cast as a numeric data-type in your QueryPair. If a column is not returned as a numeric type, the Thresholding checks will not apply.