You're designing incremental refresh for a materialized view that maintains a running 'total spend per customer' from an orders table, instead of recomputing the full aggregate from scratch on every refresh. What does the incremental update logic need to handle correctly, and what can go wrong if it doesn't?

## What incremental refresh is Incremental refresh is the technique of updating a materialized view by applying only the delta implied by a change, rather than recomputing the entire view's defining query from scratch. For a simple example like "total spend per customer," a full refresh would re-sum every order row for every customer on every refresh cycle — correct but wasteful, since the vast majority of customers' totals didn't change since the last refresh. Incremental refresh instead reacts to individual order events (an order was created, updated, or canceled) and adjusts just the affected customer's running total by the delta that event implies. ## Every kind of mutation needs its own delta Mechanically, this requires the update logic to correctly categorize and handle every kind of mutation the source data can undergo. - **An insert** (new order) is the simple case: add the order's amount to the customer's running total. - **An update** (an existing order's amount changed, e.g., a partial refund or an added line item) is trickier: naively adding the new amount would double-count the original — the logic must instead compute the delta between old and new values, which typically requires either the event to carry both before/after state or the consumer to track what it previously applied. - **A delete or cancellation** must subtract the amount that was previously added, not simply ignore the row, or the running total permanently overstates spend for that customer. ## Two structural risks a full recompute never has Beyond mutation type, incremental refresh must handle two structural risks that full recompute is naturally immune to: idempotency and ordering. ### Idempotency Idempotency matters because most event/streaming systems provide at-least-once delivery — the same "order created" event can be delivered and processed more than once, especially after a consumer restart or a broker-side redelivery following a timeout. If the update logic just blindly adds the order amount again on redelivery, the total silently inflates. The standard fix is to make the update idempotent: track a processed marker (e.g., last-applied event offset or order ID with a version) per aggregate, and skip or no-op when an already-applied event is seen again, or express the update as an idempotent upsert keyed by the source row's identity and version rather than a blind increment. ### Ordering Ordering matters because an out-of-order or replayed sequence of events — an update or delete arriving before its corresponding insert has been processed, due to partitioning, retries, or multiple producers — can apply a delta against a base state that doesn't yet reflect the referenced row, leaving the view in an inconsistent intermediate state. Systems mitigate this with per-key ordering guarantees (e.g., partitioning a stream by customer ID so all of one customer's order events are strictly ordered relative to each other), buffering/reordering by sequence number, or by designing the delta logic to be commutative and order-independent where possible. ## Why these failures are so easy to miss The reason all of this matters in practice is that incremental maintenance failures are silent and cumulative rather than loud and immediate. A missed delete event doesn't crash anything — it just leaves the running total slightly too high, forever, until someone notices a discrepancy against the source of truth, often via a customer complaint, a finance reconciliation mismatch, or an anomaly in a downstream report. Because each individual failure is small, these bugs can go undetected for a long time and are genuinely hard to debug after the fact, since there's no error to trace — just accumulated drift with no record of which specific event caused it. ## The standard production mitigation The standard production mitigation is to periodically run a full reconciliation pass — recompute the aggregate from the true source of truth on a slower cadence (nightly, weekly) and overwrite the incrementally-maintained value, treating the source as authoritative and the incremental path as a fast-but-fallible optimization layer on top of it. This gives you the low-latency benefit of incremental updates for the common case while bounding how far drift can accumulate before it's automatically corrected. A concrete real-world instance of this whole pattern: stateful stream processors commonly maintain running aggregates (like customer lifetime value or an account balance view) incrementally from a change-event topic, paired with a periodic batch job in a data warehouse that recomputes the same aggregate from raw transaction logs to catch and correct any drift the streaming path introduced.