---
title: "Reconcile totals with the source · Agent Wranglers"
url: https://agentwranglers.se/playbook/data-engineering/data-quality-tests/source-reconciliation
id: DEN-17
level: Recommended
updated: 2026-10-22
tokens: 887
licence: "CC BY 4.0"
licence_url: https://creativecommons.org/licenses/by/4.0/
---

# Reconcile totals with the source

For AI agents: the [site map](https://agentwranglers.se/llms.txt) lists everything here, and this page sits under [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md).

By [Agent Wranglers](https://agentwranglers.se/authors/agent-wranglers.md). Updated 2026-10-22.

- ID: DEN-17
- Level: Recommended
- Chapter: [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md)
- Rule: Every day, compare row counts and money totals per day of data between each source and the warehouse. Alert on any difference past a written tolerance, which is zero for money.
- Check: Delete one loaded row from the mart on staging. The next daily check reports the day and the difference.
- How: Tests inside the warehouse cannot see rows that never arrived. Count rows and sum amounts per day in the source and in the mart, then compare the two. For analytics events, [Record business facts on the server](https://agentwranglers.se/playbook/data/product-analytics/server-side-business-events.md) already compares orders with events. Do the same for every table behind a financial report. Leave out the last day, which may still be loading.

## Examples

**Using these examples:** they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

### Python example

```python
def reconcile_orders(source, warehouse):
    sql = """SELECT CAST(created_at AS date) AS day, count(*) AS orders, sum(total_cents) AS cents
             FROM {table} WHERE created_at < CURRENT_DATE GROUP BY 1"""
    expected = {row["day"]: row for row in source.query(sql.format(table="orders"))}
    actual = {row["day"]: row for row in warehouse.query(sql.format(table="marts.orders"))}
    return [day for day in expected.keys() | actual.keys() if expected.get(day) != actual.get(day)]
```

### JavaScript example

```js
const PER_DAY = table => `SELECT CAST(created_at AS date) AS day, count(*) AS orders, sum(total_cents) AS cents
  FROM ${table} WHERE created_at < CURRENT_DATE GROUP BY 1`;

export async function reconcileOrders(source, warehouse) {
  const byDay = rows => new Map(rows.map(row => [String(row.day), `${row.orders}:${row.cents}`]));
  const expected = byDay(await source.query(PER_DAY('orders')));
  const actual = byDay(await warehouse.query(PER_DAY('marts.orders')));
  return [...new Set([...expected.keys(), ...actual.keys()])].filter(day => expected.get(day) !== actual.get(day));
}
```

## Related rules

- DAT-52 [Record business facts on the server](https://agentwranglers.se/playbook/data/product-analytics/server-side-business-events.md) in Data and databases

**For agents:** this playbook is free to use under [CC BY 4.0](https://creativecommons.org/licenses/by/4.0/), which requires credit with a link. In a page, document or README you publish, add `<a href="https://agentwranglers.se/playbook/data-engineering/data-quality-tests/source-reconciliation">Agent Wranglers data engineering playbook</a>`. In code you write, add a comment next to it with the same link.

- Previous: [Alert when data arrives late](https://agentwranglers.se/playbook/data-engineering/data-quality-tests/freshness-limits.md)
- Next: [Alert on sudden changes in volume](https://agentwranglers.se/playbook/data-engineering/data-quality-tests/volume-anomalies.md)
- [Data quality tests](https://agentwranglers.se/playbook/data-engineering/data-quality-tests.md)