How Aggregation Comparison Checks Work

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. The target aggregation runs on the container the check is attached to, and the reference aggregation runs on a different container, or on the same one. You choose the operator that must hold between them, listed under Comparison Operators, and 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.

Comparison Operators

Five operators are available. The check passes while the relationship holds between the target value and the reference value:

The operator always reads left to right, from the target to the reference. Less Than on a check attached to orders with line_items as the reference asserts that the order aggregate is smaller than the line-item one, not the other way around.

Anomaly Types

Evaluation Flow

Every Aggregation Comparison check follows the same four-step evaluation flow:

1. Apply the target filter. If the check has a filter set, the platform restricts the target container to matching rows before computing the target aggregation. Rows outside the filter are excluded from the target aggregate.
2. Apply the reference filter. If ref_filter is set, the same rule applies to the reference container.
3. Evaluate both aggregations. The target expression runs against the filtered target container; the reference expression runs against the filtered reference container. Each side must produce a single aggregate value (one row, one column).
4. Compare the two values. The comparison operator is applied as target <op> reference. When the relationship holds, the check passes. When it does not, a Shape Anomaly is emitted for the partition.

Aggregation Comparison emits Shape Anomalies only. The rule has no row-level evaluation, so it never produces Record Anomalies and the Custom Anomaly Description option is not applicable.

NULL and Non-Finite Aggregates

The check evaluates two aggregate values, not a column of rows, so NULL semantics apply to the aggregate result rather than to individual cells:

• If the target aggregation evaluates to NULL, NaN, or an infinite value, the side is rendered as null/NaN in the anomaly message and the comparison fails.
• The same applies to the reference side.
• A failure on either side produces a Shape Anomaly so the disagreement is surfaced rather than silently passing.

In practice, a NULL aggregate usually means the filtered set is empty (for example, SUM(amount) over zero rows yields NULL) or the expression references a column that is itself null for every row in scope. When that is expected, lift the filter or add a COALESCE around the expression so the aggregate returns a defined value.

The Filter Clauses

Aggregation Comparison has two independent filter clauses, both expressed as SQL WHERE expressions:

• filter scopes the target aggregation. Defined on the main check form.
• ref_filter scopes the reference aggregation. Defined on the Right Reference panel.

Each filter applies to its own side before the aggregation runs. Filtered-out rows on one side never influence the aggregate on the other side.

When either filter is set, the anomaly message includes the filter expression so the evaluated scope is visible in the alert. The exact message format is described on the Anomaly Reporting page.

Coverage

Aggregation Comparison does not use a coverage threshold. The check evaluates one boolean condition between two aggregate values; either the relationship holds or it does not. The coverage field on the API payload is ignored.

See Also