A system processing sales receipts from every cash register faced a recurring problem: reports for a specific day were never truly final because receipts from offline registers could arrive weeks late, and Kafka's 'at-least-once' delivery often caused the same receipt to be counted twice. To solve this, engineers moved away from the traditional 'running sum' model, which is easily corrupted by stream irregularities, toward a design where an aggregate is a deterministic function of its source documents.
This visual explains the specific stream irregularities, such as duplicates and late arrivals, that the engineers identified as the root cause of data inconsistencies.
The Failure of Running Sums
Standard aggregation pipelines often rely on 'materialized views' that add values to a total as they arrive. However, this approach breaks when a stream provides a document twice; the system adds the sum a second time with no way to identify or subtract the duplicate. Similarly, if a document arrives a month late, a running sum that has already 'closed' a period cannot easily incorporate it without complex logic.
This image demonstrates the specific failure mode of the 'add on insert' method mentioned in the text.
The engineers tested four different approaches to solve this. They tried recomputing from wide tables, but the database had to read 46 million rows to find 508,000 needed records because the data was not sorted by date. They also tried keeping a dictionary of every seen key in memory, but the key set was too large to fit in production memory, causing the system to fail entirely.
This figure supports the explanation of why recomputing from wide tables required reading millions of unnecessary rows.
Chart drawn by HN Digest from figures reported by alnovis.io; values are shown as written in the source.
“An aggregate is not a running sum of what was inserted but a function of its documents.”
— An Aggregate Is a Function, Not a Sum | Alexander Novikov
This image serves as a primary visual for the core thesis of the article: treating aggregates as deterministic functions of source documents.
The Cell Model and Functional Aggregates
The solution was to treat an aggregate as a function of its documents. The system defines a 'Cell'—the smallest unit of aggregation, such as a specific register's shift and hour. Every document belongs to exactly one cell. The value of that cell is computed by grouping the documents belonging to that key and applying a fold operation (like a sum or max).
Because the value is a function of the documents, recomputing it is idempotent. If a document is delivered twice, the system collapses it by its unique ID during the computation. If a document arrives late, it simply marks the corresponding cell as 'dirty' for the next recomputation pass. This removes the need for a complex memory-heavy deduplication table.
Optimizing with Narrow Tables and Journals
To make this efficient in ClickHouse, the team used a 'narrow table' containing only the columns needed for measures, sorted by register and document number. This layout ensures that all documents for a single cell sit next to each other, allowing the system to perform a short range scan rather than a full table scan. In testing, this reduced the number of rows read by 30 to 37 times compared to the wide table.
A 'dirty mark' journal tracks which cells need recomputing. When a document is inserted, a mark is written to a `ReplacingMergeTree`. A background process compares the time of the last mark to the time of the last successful recomputation. If a mark is newer than the last recomputation, the cell is added to the next processing batch. This allows the system to recover automatically from outages by simply picking up where it left off.