An Aggregate Is a Function, Not a Sum

A ClickHouse system that turns Kafka-delivered sales receipts into shift, hourly, daily, and custom-field reports had to cope with duplicate deliveries, receipts arriving weeks late, and pipeline outages while meeting tight response times. Four approaches exposed the trade-offs: summing on insert permanently counted retries twice; periodically rebuilding totals from the wide receipt table read 46 million rows to use 508,000; keeping every receipt key in an in-memory dictionary exceeded available memory and even caused retries to create more duplicates; and calculating reports directly from raw data was correct but too slow, with one report scanning 3.5 billion rows. The central performance issue was ClickHouse’s sparse index: it reads whole granules, so rows scattered by the table’s sort order make even narrowly targeted queries expensive.

The proposed design treats each aggregate as a reproducible function of its underlying documents, rather than an irreversible running sum. Each receipt maps by register, shift, and UTC hour to a cell; recomputation deduplicates receipts by stable document ID, keeps the latest-written version, and calculates measures such as sums, counts, minima, and maxima. Versioned cell results replace earlier results, so duplicate deliveries do not change totals, repeated recomputation is harmless on one node, and late receipts can mark old cells for recalculation. Reports then combine cells across hours, shifts, or other groupings, avoiding separate stored aggregates for each report. Tests found that reports assembled from cells matched direct calculations across all 209 measures. The approach depends on arranging and reading data efficiently, and concurrent recomputation across replicas requires separate care.