Aggregation Comparison Best Practices

Guidelines for getting reliable signal from Aggregation Comparison checks while keeping the noise (and the maintenance) low.

Make both sides comparable before comparing them

The two expressions are evaluated as numbers, and the comparison is applied as target <op> reference. Reconciliations break most often because the two sides are not measuring the same thing: one includes tax and the other does not, one counts cancelled rows and the other filters them out. Write both expressions together, and state in the check's description what the two sides are supposed to represent.

Round explicitly when comparing money or ratios

An Equal To comparison between two floating-point roll-ups will fail on the last decimal even when the business values agree. Wrap both sides in ROUND(..., 2) (or the precision your domain uses) so the comparison reflects the business rule rather than accumulated floating-point error. Prefer a tolerant operator (Less Than Or Equal To, Greater Than Or Equal To) when the relationship is an inequality by nature.

Guard against empty scopes

When a filtered set is empty, SUM(...) returns NULL and the comparison fails with null/NaN in the message. That is the correct behavior for a reconciliation that must always have data, but it produces confusing anomalies when an empty scope is legitimate. Wrap the expression in COALESCE(SUM(amount), 0) when zero is the right answer for an empty set.

Scope each side with its own filter

The rule has two independent filters: filter on the target container and the Filter Clause on the Right Reference panel for the reference container. Use them to align the two sides on the same logical slice (the same day, the same tenant, the same status). Both expressions are echoed in the anomaly message, so the evaluated scope stays visible when someone triages the alert.

Keep the aggregations simple, especially across datastores

Each side computes one aggregate value. A cross-datastore reconciliation reads the reference container from its own datastore, so a simple SUM or COUNT lets the engine push the aggregation down instead of pulling rows. Filters that narrow each side reduce the work proportionally.

Use Check Variables for moving scopes

Both expressions and the reference filter accept Check Variables (for example, ``), resolved before the aggregation runs. This keeps one check reconciling the current batch or partition instead of hardcoding a value that goes stale.

Choose the right rule for the job

Use Aggregation Comparison when two aggregates from different sources (or different slices of the same source) must satisfy a relationship with each other.
Use Metric when a single aggregate must stay inside a fixed interval; the bound is a constant, not another query.
Use Data Diff when you need to know which rows differ, not just that the totals disagree.
Use Volumetric for within-source volume drift measured against historical behavior rather than against a second container.

Route the anomalies to the right people

A reconciliation failure usually needs someone who understands both sides. Set an Anomaly Assignee from the team that owns the pipeline feeding the target container, tag the check (reconciliation, finance) so related checks are easy to find, and record in the description which upstream systems the two sides come from.