Metric

Definition

Records the value of the selected field during each scan operation and asserts limits based upon an expected change or absolute range (inclusive).

In-Depth Overview

The Metric rule is designed to monitor the values of a selected field over time. It is particularly useful in a time-series context where values are expected to evolve within certain bounds or limits. This rule allows for tracking absolute values or changes, ensuring they remain within predefined thresholds.

Field Scope

Single: The rule evaluates a single specified field.

Accepted Types

Type
`Integral`
`Fractional`

General Properties

DetailsFilter Guide

Name	Supported
Filter Allows the targeting of specific data based on conditions
Coverage Customization Allows adjusting the percentage of records that must meet the rule's conditions

The filter allows you to define a subset of data upon which the rule will operate.

It requires a valid Spark SQL expression that determines the criteria rows in the DataFrame should meet. This means the expression specifies which rows the DataFrame should include based on those criteria. Since it's applied directly to the Spark DataFrame, traditional SQL constructs like WHERE clauses are not supported.

Examples

Direct Conditions

Simply specify the condition you want to be met.

Correct usage

O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'

Incorrect usage

WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

Combining Conditions

Combine multiple conditions using logical operators like AND and OR.

Correct usage

O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

(L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'

Incorrect usage

WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'

Utilizing Functions

Leverage Spark SQL functions to refine and enhance your conditions.

Correct usage

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7

Incorrect usage

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
) = 'URGENT'

EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7

Using scan-time variables

To refer to the current dataframe being analyzed, use the reserved dynamic variable {{_qualytics_self}}.

Correct usage

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{_qualytics_self}}
    WHERE O_TOTALPRICE > 1000
)

Incorrect usage

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM ORDERS
    WHERE O_TOTALPRICE > 1000
)

While subqueries can be useful, their application within filters in our context has limitations. For example, directly referencing other containers or the broader target container in such subqueries is not supported. Attempting to do so will result in an error.

Important Note on {{_qualytics_self}}

The {{_qualytics_self}} keyword refers to the dataframe that's currently under examination. In the context of a full scan, this variable represents the entire target container. However, during incremental scans, it only reflects a subset of the target container, capturing just the incremental data. It's crucial to recognize that in such scenarios, using {{_qualytics_self}} may not encompass all entries from the target container.

Specific Properties

Determines the evaluation method and allowable limits for field value comparisons over time.

Name	Description
Comparison	Specifies the type of comparison: Absolute Change, Absolute Value, or Percentage Change.
Min Value	Indicates the minimum allowable increase in value. Use a negative value to represent an allowable decrease.
Max Value	Indicates the maximum allowable increase in value.

Details

Comparison Options

Absolute Change

The Absolute Change comparison works by comparing the change in a numeric field's value to a pre-set limit (Min / Max). If the field's value changes by more than this specified limit since the last relevant scan, an anomaly is identified.

Illustration

Any record with a value change smaller than 30 or greater than 70 compared to the last scan should be flagged as anomalous

Thresholds: Min Change = 30, Max Change = 70

Scan	Previous Value	Current Value	Absolute Change	Anomaly Detected
#1	-	100	-	No
#2	100	150	50	No
#3	150	220	70	No
#4	220	300	80	Yes

Absolute Value

The Absolute Value comparison works by comparing the change in a numeric field's value to a pre-set limit between Min and Max values. If the field's value changes by more than this specified range since the last relevant scan, an anomaly is identified.

Illustration

The value of the record in each scan should be within 100 and 300 to be considered normal

Thresholds: Min Value = 100, Max Value = 300

Scan	Current Value	Anomaly Detected
#1	150	No
#2	90	Yes
#3	250	No
#4	310	Yes

Percentage Change

The Percentage Change comparison operates by tracking changes in a numeric field's value relative to its previous value. If the change exceeds the predefined percentage (%) limit since the last relevant scan, an anomaly is generated.

Illustration

An anomaly is identified if the record's value decreases by more than 20% or increases by more than 50% compared to the last scan.

Thresholds: Min Percentage Change = -20%, Max Percentage Change = 50%

Percentage Change Formula: ( (current_value - previous_value) / previous_value ) * 100

Scan	Previous Value	Current Value	Percentage Change	Anomaly Detected
1	-	100	-	No
2	100	150	50%	No
3	150	120	-20%	No
4	120	65	-45.83%	Yes
5	65	110	69.23%	Yes

Thresholds

At least the Min or Max value must be specified, and including both is optional. These values determine the acceptable range or limit of change in the field's value.

Min Value

Represents the minimum allowable increase in the field's value.
A negative Min Value signifies an allowable decrease, determining the minimum value the field can drop to be considered valid.

** Max Value**

Indicates the maximum allowable increase in the field’s value, setting an upper limit for the value's acceptable growth or change.

Anomaly Types

Type	Supported
Record Flag inconsistencies at the row level
Shape Flag inconsistencies in the overall patterns and distributions of a field

Example

Objective: Ensure that the total price in the ORDERS table does not fluctuate beyond a predefined percentage limit between scans.

Thresholds: Min Percentage Change = -30%, Max Percentage Change = 30%

Sample Scan History

Scan	O_ORDERKEY	Previous O_TOTALPRICE	Current O_TOTALPRICE	Percentage Change	Anomaly Detected
#1	1	-	100	-	No
#2	1	100	110	10%	No
#3	1	110	200	81.8%	Yes
#4	1	200	105	-47.5%	Yes

Anomaly Explanation

In the sample scan history above, anomalies are identified in scans #3 and #4. The O_TOTALPRICE values in these scans fall outside the declared percentage change limits of -30% and 30%, indicating that something unusual might be happening and further investigation is needed.

FlowchartSQL

graph TD
A[Start] --> B[Retrieve O_TOTALPRICE]
B --> C{Is Percentage Change in O_TOTALPRICE within -30% and 30%?}
C -->|Yes| D[End]
C -->|No| E[Mark as Anomalous]
E --> D

-- An illustrative SQL query demonstrating the rule applied to example dataset(s)
select 
    o_orderkey,
    o_totalprice,
    lag(o_totalprice) over (order by o_orderkey) as previous_o_totalprice
from
    orders
having
    abs((o_totalprice - previous_o_totalprice) / previous_o_totalprice) * 100 > 30
    or
    abs((o_totalprice - previous_o_totalprice) / previous_o_totalprice) * 100 < -30;

Potential Violation Messages

Record Anomaly (Percentage Change)

The percentage change of O_TOTALPRICE from '110' to '200' falls outside the declared limits

Record Anomaly (Absolute Change)

using hypothetical numbers

The absolute change of O_TOTALPRICE from '150' to '300' falls outside the declared limits

Record Anomaly (Absolute Value)

using hypothetical numbers

The value for O_TOTALPRICE of '50' is not between the declared limits

Last update: April 27, 2024