How does ClickHouse's CollapsingMergeTree use its sign column to retract a row?

## The problem it solves ClickHouse has no cheap row update. `CollapsingMergeTree` offers an insert-only way to express "this fact is no longer true", which is useful for mutable aggregates: an order whose amount changed, a session whose duration was corrected, a running total maintained from a change stream. ```sql CREATE TABLE order_totals ( order_id UInt64, amount Decimal(18, 2), sign Int8 ) ENGINE = CollapsingMergeTree(sign) ORDER BY order_id; ``` ## The write protocol Every row carries a sign. `sign = 1` marks a **state** row — a fact that is currently true. `sign = -1` marks a **cancel** row — an exact copy of a previously written state row, same sorting key and same measure values, that retracts it. To change an order's amount from 100 to 120 you insert two rows: `(order_id, 100, -1)` and `(order_id, 120, 1)`. To delete it entirely you insert only the cancel row. Nothing is ever modified in place; the table is append-only and the *net* of the signs is the truth. This puts a real obligation on the writer: to cancel a row you must know its previous values. In practice that means either your source of change events includes the before-image (many change-data-capture streams do) or you keep the last-known state in the application. If you cannot produce an accurate before-image, this engine is the wrong tool — `ReplacingMergeTree` with a version is far more forgiving. ## What a merge does When the background merge processes a run of rows sharing a sorting key, it cancels out state and cancel pairs and writes only what survives. In the ordinary case — one state row and one cancel row — both vanish; where state rows outnumber cancel rows, the latest state survives. What the merge can only do correctly is pair rows it sees in the right relative order: `CollapsingMergeTree` assumes the cancel row was inserted after the state row it retracts. Rows delivered out of order can leave residue that never collapses, and in awkward cases produce values that are simply wrong rather than merely uncollapsed. As with every MergeTree variant, collapsing is **eventual** and confined to a partition. Nothing happens at insert time, and rows for the same key that land in different partitions never meet. ## Querying a collapsing table Because you cannot know whether a pair has collapsed yet, you never read the rows directly. You let the sign carry the arithmetic: ```sql SELECT order_id, sum(amount * sign) AS amount FROM order_totals GROUP BY order_id HAVING sum(sign) > 0; ``` An uncollapsed `+1 / -1` pair contributes `amount - amount = 0` to the sum and `1 - 1 = 0` to the sign total, so the `HAVING` clause drops fully cancelled keys. This expression is correct whether or not the merge has run — that is the elegance of the design and the reason the pattern is so idiomatic. `FINAL` also works and is simpler to write, at query cost. Note what this implies for non-additive columns: multiplying by the sign works for sums and counts. For a value you merely want to carry — a status string, a latest label — collapsing is awkward and a replacing table is the better model. ## VersionedCollapsingMergeTree `VersionedCollapsingMergeTree(sign, version)` adds a version column to the engine parameters. The version identifies which state a cancel row belongs to, so pairs collapse correctly even when rows arrive out of order — the usual situation when a partitioned message stream feeds several concurrent inserters. If your ingestion cannot guarantee ordering per key, this is the variant to reach for. ## When to use it at all Honestly: less often than it used to be. `ReplacingMergeTree` with a version column covers most "latest state per key" needs, requires no before-image, and is much easier to reason about. Collapsing earns its place when you maintain **additive aggregates over mutable facts** and want the sign trick to keep sums correct without resolving the latest row per key first — a running total over a change stream is the canonical case. Knowing the model, and knowing when *not* to choose it, is what an interviewer is actually probing. ## How to answer Describe the two-row protocol, say that the merge annihilates pairs eventually, show the `sum(metric * sign)` and `HAVING sum(sign) > 0` query, and name the ordering assumption plus the versioned variant that removes it. Close by contrasting it with `ReplacingMergeTree` so the interviewer sees you would not reach for it reflexively.