Skip to content

Aggregation Comparison Troubleshooting

Common problems when creating and running an Aggregation Comparison check, their causes, and how to resolve them.

Validation Fails When Creating the Check

Clicking Validate returns an error instead of a success message.

Cause: The rule could not run against the selected data. The most common reasons are a syntax error in one of the aggregation expressions, an expression that does not return a single aggregate value, an invalid filter clause, or a container that failed to load.

Resolution:

  1. Review the error message: it explains what needs attention.
  2. Check both Aggregation expressions: each must reduce to one row and one column. SELECT, GROUP BY, and multi-column outputs are not valid here; write the aggregate alone (SUM(amount), COUNT(*)).
  3. Check both Filter Clause fields for typos: each must be a valid SQL WHERE expression for its own container.
  4. If the message says the container is marked as Unloadable, the container was skipped after repeated operation failures. Follow the steps in Unloadable Container Error.

The Message Shows null/NaN Instead of a Number

One side of the comparison has no value in the anomaly message.

Cause: That side's aggregation evaluated to NULL, NaN, or an infinite value, and the comparison fails whenever either side is undefined. The usual causes are an empty filtered set (SUM(...) over zero rows returns NULL), a column that is null for every row in scope, or an undefined result such as a division by zero.

Resolution: Confirm the filter on that side actually matches rows. When an empty set is legitimate and zero is the right answer, wrap the expression in COALESCE(SUM(amount), 0). When the aggregation divides, guard the denominator.

An Equal To Comparison Fails on Values That Look Identical

The two values in the message look the same, or differ only in the last decimals.

Cause: The comparison runs on the full numeric values, so accumulated floating-point differences make an exact Equal To fail even when the business values agree.

Resolution: Round both sides explicitly in the expressions (for example, ROUND(SUM(amount), 2)), or use a tolerant operator when the relationship is an inequality by nature.

The Two Sides Disagree but the Data Looks Correct

The reconciliation fails and both values are defined, but the underlying data seems fine.

Cause: The two expressions are not measuring the same thing. Common mismatches: one side includes tax, discounts, or cancelled rows while the other does not; the filters scope the two sides to different periods; the reference container holds a different grain (line items versus orders).

Resolution: Compare the two expressions and both filters side by side, as echoed in the anomaly message. Align the scopes with the filters, and note in the check's description what each side is meant to represent.

No Anomaly Although the Comparison Should Fail

A Scan ran and the relationship clearly does not hold, but nothing was reported.

Cause: One of two configurations:

  • The check is in Draft status: Draft checks are not evaluated by Scans.
  • A filter excludes the rows that would make the aggregation disagree, so both sides happen to match within the filtered scope.

Resolution: Confirm the check is Active, then test each filter against its own container to see which rows each side actually aggregates.

Expected Per-Row Anomalies, Got a Single One

The check reports one anomaly instead of flagging the rows that caused the difference.

Cause: Aggregation Comparison is a Shape-only rule. It compares two aggregate values and reports a single Shape Anomaly; it never evaluates rows individually.

Resolution: This is expected behavior. When you need to know which rows differ, use a Data Diff check instead, which performs a row-level comparison between two containers.

Check Variables Are Not Being Replaced

An expression that references a variable runs with the literal token instead of a value.

Cause: The variable is not defined for the operation that ran the check, so there was nothing to substitute.

Resolution: Confirm the variable name matches the one supplied to the scan, and that it is provided when the operation runs. Both aggregation expressions and the reference filter accept Check Variables; they are resolved before the aggregations run.