Why Did Yesterday's Report Total Change? Root-Causing Metric Drift

Cause 1: Late-arriving data

A row logically belonging to a past date can still be inserted after that date's report already ran — a mobile app syncing offline transactions once connectivity returns, a batch import that runs on a delay. The report simply didn't have that row yet the first time it ran.

-- Rows logically dated yesterday but inserted well after that day ended
SELECT order_id, order_date, created_at
FROM orders
WHERE order_date = CURRENT_DATE - 1
  AND created_at::DATE > order_date;

Cause 2: Backdated corrections

-- Rows for a past date that were modified well after that date, e.g. refunds
SELECT order_id, order_date, amount, status, updated_at
FROM orders
WHERE order_date = '2026-07-15'
  AND updated_at::DATE > order_date
ORDER BY updated_at DESC;

If the report sums by order_date, a refund processed today against a July 15th order still shifts July 15th's total — which is often the intended, correct behavior, not a bug, depending on what the metric is meant to represent.

Cause 3: Full re-run vs incremental recompute

A full re-run recalculates a metric from all underlying data every time it runs, so it naturally reflects every late arrival and correction since the original run. An incremental process only touches new data and typically never revisits an already-processed historical period — so if a genuinely old number moved, that specifically points to a full re-run (or a manual backfill) having touched it, not the regular incremental pipeline.

Confirming the cause with SQL

-- Does the total change match records actually modified since the original run?
SELECT SUM(amount) AS delta_explained_by_modifications
FROM orders
WHERE order_date = '2026-07-15'
  AND updated_at > '2026-08-01 00:00:00';  -- the report's original run time
-- If this sum accounts for the observed change in the total, it's explained;
-- if not, the discrepancy needs further investigation as a possible bug

Real-world example: last month's revenue moved by $2,100

A monthly finance report re-run this week shows July's revenue $2,100 higher than the number originally reported in early August. Running the backdated-modifications query for July with a cutoff at the original report's run timestamp returns exactly $2,100 in orders that were modified (specifically, three orders whose payment status changed from "pending" to "completed" after initially failing to process). The change is fully explained and correct — those orders genuinely completed and belong in July's revenue. The follow-up decision isn't a data fix; it's whether finance wants historical months frozen after reporting (an immutable snapshot) or always reflecting the latest known state.

Common mistakes

• Assuming a changed historical number is automatically a bug without checking for legitimate late arrivals or corrections first.
• Not distinguishing frozen-snapshot reporting from always-live reporting when deciding whether historical movement is expected.
• Comparing against the wrong "original run" timestamp, making the modified-records delta check inaccurate.
• Not documenting which of a company's reports are frozen vs live, causing repeated confusion when the same kind of drift resurfaces.