Skip to content

Aggregation Comparison Check

Definition

Asserts that a chosen comparison operator holds true when applied to two aggregation expressions, one evaluated on the target container and one on a reference container.

Overview

The Aggregation Comparison rule compares two aggregate values: a target aggregation evaluated on the container the check is attached to, and a reference aggregation evaluated on a different (or the same) container. The comparison operator is one of less than, less than or equal to, equal to, greater than or equal to, or greater than. The check fires a Shape Anomaly when the relationship does not hold.

Typical use cases:

  • Verify that two tables that should agree on a roll-up actually do (for example, order totals against line-item totals).
  • Confirm that a derived metric stays within an expected relationship with its source (for example, sum(net) <= sum(gross)).
  • Reconcile counts across systems, such as count(*) in an ingestion table against count(*) in the downstream warehouse.

Field Scope

Calculated: the rule does not pick a column. Both sides are SQL expressions that must each evaluate to a single aggregate value (one row, one column).

The target expression is evaluated against the container the check is attached to. The reference expression is evaluated against a separate container chosen on the Right Reference panel; that container can live in the same datastore or in a different one.

General Properties

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" collapsible="true 
O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'
Incorrect usage" collapsible="true 
WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

Combining Conditions

Combine multiple conditions using logical operators like AND and OR.

Correct usage" collapsible="true 
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" collapsible="true 
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" collapsible="true 
RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7
Incorrect usage" collapsible="true 
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" collapsible="true 
O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{ _qualytics_self }}
    WHERE O_TOTALPRICE > 1000
)
Incorrect usage" collapsible="true 
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

Aggregation Comparison has six rule-specific properties:

Name Description
Aggregation
 The target aggregation expression. Must produce a single aggregate value (for example, SUM(amount), COUNT(*), ROUND(AVG(score), 2)).
Comparison
 The operator applied between the target and reference values. One of Less Than, Less Than Or Equal To, Equal To, Greater Than Or Equal To, or Greater Than (sent on the API payload as lt, lte, eq, gte, gt).
Datastore
 The datastore containing the reference container. Leave empty to use the target's datastore.
Container
 The table or file holding the reference data. Required.
Aggregation
(right reference)		 The reference aggregation expression. Must also produce a single aggregate value.
Filter Clause
(right reference)		 Optional SQL WHERE expression applied to the reference container before its aggregation runs.

Anomaly Types

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

Next Steps

  • How It Works

    Full semantics: evaluation flow, NULL handling, filter behavior, anomaly message structure, and how Aggregation Comparison relates to other reconciliation rule types.

    How It Works

  • Examples

    Three production scenarios with sample aggregates, anomaly messages, and the SQL equivalent of what the check evaluates.

    Examples

  • API

    Payload shape and field notes for creating an Aggregation Comparison check programmatically.

    API

  • FAQ

    Short answers to questions about NULL aggregates, cross-datastore references, filters, and anomaly reporting.

    FAQ