Why a Running Sum Fails: The Case for Aggregates as Functions
An Aggregate Is a Function, Not a Sum
Storing sales receipts in ClickHouse seemed straightforward until at-least-once delivery, late documents, and pipeline outages broke every running-sum approach. After four failed designs—double-counted inserts, expensive recomputes, memory-hungry key dictionaries, and slow read-time aggregation—the team landed on a different model: an aggregate is a function of its documents, not a sum of what was inserted. A narrow, correctly sorted table and a dirty-cell journal make recomputation cheap and duplicates harmless.
A re-delivered document changes nothing. During recomputation the rows of one document are collapsed by document key. Two deliveries become one row; the cell value stays the same. No dictionary, no key table, no memory of what was seen.