# Aggregation Comparison Check Examples

Source: https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/examples/

Three real-world scenarios that show how the Aggregation Comparison check is typically used in production: reconciling totals across two tables in the same warehouse, comparing row counts between a source system and the downstream warehouse, and enforcing a roll-up relationship between two aggregates on the same container.

**Tab: Order Totals vs Line-Item Totals**

**The situation:** The `orders` table stores a denormalized `total_price` per order; the `lineitem` table stores per-line extended prices that, when summed and adjusted for discount and tax, should equal the rounded `total_price` roll-up. Any drift between the two indicates a calculation or ingestion problem.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Aggregation Comparison |
| Aggregation | `ROUND(SUM(o_totalprice))` |
| Comparison | Equal To (`eq`) |
| Datastore | *(same as target)* |
| Container | `lineitem` |
| Aggregation (right reference) | `ROUND(SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)))` |
| Filter | *(none)* |
| Filter Clause (right reference) | *(none)* |
| Status | Active |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Finance Data on-call)* |
| Tags | `reconciliation`, `finance` |
| Additional Metadata | `jira: DATA-4012` |
| Description | Order totals must equal the line-item roll-up. |

**Payload**

```json
{
    "description": "Order totals must equal the line-item roll-up.",
    "rule": "aggregationComparison",
    "fields": [],
    "container_id": 145,
    "filter": null,
    "properties": {
        "expression": "ROUND(SUM(o_totalprice))",
        "comparison": "eq",
        "ref_container_id": 178,
        "ref_expression": "ROUND(SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)))",
        "ref_filter": null
    },
    "tags": ["reconciliation", "finance"],
    "additional_metadata": {"jira": "DATA-4012"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 12
}
```

**Sample Aggregates**

| Side | Expression | Evaluated Value |
| --- | --- | --- |
| Target (`orders`) | `ROUND(SUM(o_totalprice))` | 5,000,000 |
| Reference (`lineitem`) | `ROUND(SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)))` | 4,999,800 |

**What gets flagged**

The two aggregates differ by 200, so the `eq` comparison fails and a Shape Anomaly is emitted. There is no row-level output: Aggregation Comparison reports the disagreement at the partition level only.

> **Example: Shape Anomaly**
>
> ROUND(SUM(o_totalprice)) evaluates to 5,000,000, which is not equal to ROUND(SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax))) at 4,999,800

**Flowchart**

```mermaid
graph TD
    A["Evaluate target aggregation on orders"] --> B["Evaluate reference aggregation on lineitem"]
    B --> C{"Does target = reference?"}
    C -->|Yes| D["Check passes"]
    C -->|No| E["Shape Anomaly:<br/>target value vs reference value"]
```

**Equivalent SQL**

```sql
-- The check evaluates each side as one aggregate and compares them.
SELECT
    (SELECT ROUND(SUM(o_totalprice)) FROM orders) AS target_value,
    (SELECT ROUND(SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax))) FROM lineitem) AS reference_value;
-- The Shape Anomaly fires when target_value <> reference_value.
```

**Tab: Cross-System Row-Count Reconciliation**

**The situation:** A nightly batch lands customer rows into the analytics warehouse from the operational source system. The two row counts must agree after the batch completes. Both filters scope the comparison to today's load window so backfills do not influence the assertion.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Aggregation Comparison |
| Aggregation | `COUNT(*)` |
| Comparison | Equal To (`eq`) |
| Datastore | `operational_postgres` |
| Container | `customers` |
| Aggregation (right reference) | `COUNT(*)` |
| Filter | `load_date = current_date()` |
| Filter Clause (right reference) | `updated_at >= current_date()` |
| Status | Active |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Ingestion on-call)* |
| Tags | `ingestion`, `reconciliation` |
| Additional Metadata | `jira: DATA-4138` |
| Description | Warehouse row count must match the source row count for today's window. |

**Payload**

```json
{
    "description": "Warehouse row count must match the source row count for today's window.",
    "rule": "aggregationComparison",
    "fields": [],
    "container_id": 212,
    "filter": "load_date = current_date()",
    "properties": {
        "expression": "COUNT(*)",
        "comparison": "eq",
        "ref_datastore_id": 9,
        "ref_container_id": 47,
        "ref_expression": "COUNT(*)",
        "ref_filter": "updated_at >= current_date()"
    },
    "tags": ["ingestion", "reconciliation"],
    "additional_metadata": {"jira": "DATA-4138"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 18
}
```

**Sample Aggregates**

| Side | Expression | Filter | Evaluated Value |
| --- | --- | --- | --- |
| Target (warehouse `customers`) | `COUNT(*)` | `load_date = current_date()` | 10,418 |
| Reference (source `customers`) | `COUNT(*)` | `updated_at >= current_date()` | 10,500 |

**Why both filters matter**

Each filter restricts its own side to today's load window. Without the target filter, prior-day rows in the warehouse would inflate the target count. Without the reference filter, untouched source rows would inflate the reference count. Both filters are applied independently before the aggregation runs.

**What gets flagged**

Eighty-two source rows did not land in the warehouse, so the counts disagree and the `eq` check fails.

> **Example: Shape Anomaly**
>
> COUNT(*) evaluates to 10,418, which is not equal to COUNT(*) at 10,500. The comparison applied load_date = current_date() to COUNT(*) and updated_at >= current_date() to COUNT(*)

**Flowchart**

```mermaid
graph TD
    A["Apply filter:<br/>load_date = current_date()"] --> B["Evaluate COUNT(*) on warehouse customers"]
    A2["Apply reference filter:<br/>updated_at >= current_date()"] --> B2["Evaluate COUNT(*) on source customers"]
    B --> C{"Do counts match?"}
    B2 --> C
    C -->|Yes| D["Check passes"]
    C -->|No| E["Shape Anomaly with both<br/>values and the filter note"]
```

**Equivalent SQL**

```sql
-- Target side (warehouse).
SELECT COUNT(*) AS target_count
FROM warehouse.customers
WHERE load_date = current_date();

-- Reference side (source).
SELECT COUNT(*) AS reference_count
FROM source.customers
WHERE updated_at >= current_date();
-- The Shape Anomaly fires when target_count <> reference_count.
```

**Tab: Net Sales Must Not Exceed Gross Sales**

**The situation:** The `transactions` table stores both gross and net amounts on every row. By definition, the sum of `net_amount` for any closed batch must be less than or equal to the sum of `gross_amount`. A violation indicates an upstream calculation error or a corrupted row. Both aggregations target the same container, with a shared filter that restricts the assertion to closed batches.

**Check configuration**

| Field | Value |
| --- | --- |
| Rule | Aggregation Comparison |
| Aggregation | `SUM(net_amount)` |
| Comparison | Less Than Or Equal To (`lte`) |
| Datastore | *(same as target)* |
| Container | `transactions` |
| Aggregation (right reference) | `SUM(gross_amount)` |
| Filter | `batch_status = 'closed'` |
| Filter Clause (right reference) | `batch_status = 'closed'` |
| Status | Active |
| Owner | *(check creator)* |
| Anomaly Assignee | *(Finance Data on-call)* |
| Tags | `invariant`, `finance` |
| Additional Metadata | `jira: DATA-4271` |
| Description | Net sales must never exceed gross sales for closed batches. |

**Payload**

```json
{
    "description": "Net sales must never exceed gross sales for closed batches.",
    "rule": "aggregationComparison",
    "fields": [],
    "container_id": 318,
    "filter": "batch_status = 'closed'",
    "properties": {
        "expression": "SUM(net_amount)",
        "comparison": "lte",
        "ref_container_id": 318,
        "ref_expression": "SUM(gross_amount)",
        "ref_filter": "batch_status = 'closed'"
    },
    "tags": ["invariant", "finance"],
    "additional_metadata": {"jira": "DATA-4271"},
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 24
}
```

**Sample Aggregates**

| Side | Expression | Filter | Evaluated Value |
| --- | --- | --- | --- |
| Target | `SUM(net_amount)` | `batch_status = 'closed'` | 1,250,400.00 |
| Reference | `SUM(gross_amount)` | `batch_status = 'closed'` | 1,248,300.00 |

**What gets flagged**

The net total is 2,100 higher than the gross total, which is impossible under the business rule. The `lte` comparison fails and a Shape Anomaly is emitted.

> **Example: Shape Anomaly**
>
> SUM(net_amount) evaluates to 1,250,400, which is not less than or equal to SUM(gross_amount) at 1,248,300. The comparison applied batch_status = 'closed' to SUM(net_amount) and batch_status = 'closed' to SUM(gross_amount)

**Flowchart**

```mermaid
graph TD
    A["Apply filter: batch_status = 'closed' (both sides)"] --> B["Evaluate SUM(net_amount)"]
    A --> B2["Evaluate SUM(gross_amount)"]
    B --> C{"Is target <= reference?"}
    B2 --> C
    C -->|Yes| D["Check passes"]
    C -->|No| E["Shape Anomaly:<br/>net is higher than gross"]
```

**Equivalent SQL**

```sql
-- Both sides aggregate the same container with the same filter.
SELECT SUM(net_amount) AS net_total,
       SUM(gross_amount) AS gross_total
FROM transactions
WHERE batch_status = 'closed';
-- The Shape Anomaly fires when net_total > gross_total.
```

## See Also

- **Best Practices**

  Guidelines for writing comparable aggregations, scoping both sides, and choosing between reconciliation rule types.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/best-practices/)
- **Permissions**

  The team permission each action needs: view, create, edit, archive, restore, and delete.

  [Permissions](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/permissions/)
- **How It Works**

  The complete reference: definition, field scope, comparison operators, evaluation flow, NULL handling, and the two filter clauses.

  [How It Works](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/how-it-works/)
- **Anomaly Reporting**

  The anomaly message the check produces, how the evaluated values are rendered, and why per-row reporting does not apply.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/aggregation-comparison/deep-dive/anomaly-reporting/)

*Last updated: September 22, 2026.*
