# Aggregation Comparison Check FAQ

Source: https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/faq/

Answers to common questions about how the Aggregation Comparison check evaluates two aggregates, how it handles NULL or non-finite results, how filters scope each side, and how anomalies are reported, grouped by topic.

## Behavior

### Which comparison operators are supported?

Five operators are accepted: `lt` (less than), `lte` (less than or equal to), `eq` (equal to), `gte` (greater than or equal to), and `gt` (greater than). The comparison is always applied as `target <op> reference`.

### Can the reference container live in a different datastore?

Yes. Set `ref_datastore_id` in the payload to the datastore holding the reference container. When the reference container is in the same datastore as the target, omit `ref_datastore_id` (or send `null`) and only set `ref_container_id`.

### Can the target and reference be the same container?

Yes. Set `ref_container_id` to the same ID as `container_id` and use the **Right Reference** panel to define the reference aggregation. This is useful when asserting an invariant between two aggregates on the same dataset (for example, `SUM(net) <= SUM(gross)`).

### What happens when an aggregation returns NULL?

If the target or reference aggregation evaluates to `NULL`, `NaN`, or an infinite value, that side renders as `null/NaN` in the anomaly message and the comparison fails. A common cause is an empty filtered set; lift the filter or wrap the expression in `COALESCE` so the aggregate returns a defined value.

### Do the two aggregations need to produce comparable types?

Yes. Both expressions are evaluated as numeric values for the comparison. Wrap non-numeric outputs (for example, `MIN(some_date)`) so they reduce to a numeric form that the operator can compare.

---

## Anomaly Reporting

### What does the anomaly message look like?

When both aggregates evaluate successfully:

```text
<expression> evaluates to <observed>, which is not <comparison> <reference expression> at <expected>
```

When either aggregate fails to evaluate, that side renders as `null/NaN` and the same template is used.

When the reference expression is filtered, the message adds a sentence naming each filter against the expression it applied to, such as `The comparison applied load_date = current_date() to COUNT(*) and updated_at >= current_date() to COUNT(*)`. With a filter on the reference alone it reads `The comparison applied <reference filter> to <reference expression> only`. When only the target is filtered, the first sentence ends with `(applied to records where <expression>)` instead.

### Does Aggregation Comparison emit Record Anomalies?

No. The rule is Shape-only. It evaluates one boolean condition between two aggregate values and reports a single Shape Anomaly when the relationship does not hold. There are no per-row anomalies.

### Does Custom Anomaly Description work for Aggregation Comparison?

No. **Custom Anomaly Description** (and the `anomaly_message_field` payload field) applies only to Record Anomalies. Aggregation Comparison emits Shape Anomalies, which always use the fixed template described above.

### Why does the message show `null/NaN` instead of a number?

That side's aggregation returned `NULL`, `NaN`, or an infinite value. The most common causes are an empty filtered set, a column that is null for every row in scope, or an expression that produced an undefined result (for example, division by zero).

---

## Configuration

### Can I lower the coverage on an Aggregation Comparison check?

No. The rule does not use a coverage threshold. On the API payload, omit `coverage` or send `1`. Either the comparison holds for the two aggregates or it does not.

### Can I change the comparison operator on an existing check?

Yes. In the UI, open the check, pick a different **Comparison**, and click **Update**; see [Edit a Check](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/how-tos/edit-a-check/) for the full steps. Through the API, a `PUT` to `/api/quality-checks/{id}` updates `properties.comparison` along with both expressions and both filters. The rule type, the target container, and the associated Check Template stay immutable; see the [API page](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/api/) for the editable/immutable matrix.

### What permission do I need to create or edit an Aggregation Comparison check?

The **Drafter** team permission on the datastore covers Draft work (creating a check as Draft or editing it while it stays Draft). Anything that puts the check into evaluation, such as creating or editing an Active check, archiving, or deleting, requires the **Author** team permission (or above). Viewing only requires **Reporter**. See [Permissions](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/permissions/) for the full matrix.

### How do I assert that two row counts must match?

Use `COUNT(*)` as both the target and reference aggregations and set the comparison to `eq`. Apply filters on each side independently to scope each count to the same logical set of rows.

### Can I reference Check Variables inside the aggregation expressions?

Yes. Both `expression` and `ref_expression` accept Check Variables (for example, `{{batch_id}}`). The variables are resolved before the aggregation runs. When you validate the check or save it as Active, the expression is checked, and a variable that has no value at that point is left empty. Write the expression so it stays valid SQL without the value. Placing a variable inside a quoted literal, such as `COUNT(CASE WHEN n_name <> '{{skip_name}}' THEN 1 END)`, keeps it valid. A variable used as a bare operand, such as `COUNT(*) * {{multiplier}}`, fails that check when the variable has no value.

### How does Aggregation Comparison differ from Metric?

Aggregation Comparison compares two aggregates that must satisfy a relationship with each other. [Metric](https://userguide.qualytics.io/data-quality-checks/metric/introduction/) pins a single aggregate to a fixed boundary (for example, `between 0 and 1`). Use Aggregation Comparison for cross-source reconciliation; use Metric when the bound is a constant.

### How does Aggregation Comparison differ from Data Diff?

[Data Diff](https://userguide.qualytics.io/data-quality-checks/data-diff/introduction/) performs a row-level diff between two containers and reports each mismatched row. Aggregation Comparison only checks an aggregate-level relationship. Choose Data Diff when you need to know *which* rows differ; choose Aggregation Comparison when an aggregate-level reconciliation is enough.

**Still have questions?**
Contact the Qualytics support team at
[support@qualytics.ai](mailto:support@qualytics.ai).

*Last updated: October 12, 2026.*
