Your ClickHouse table must reflect updates to existing rows — how do you choose between ReplacingMergeTree, CollapsingMergeTree and mutations?

## The constraint everything follows from ClickHouse stores MergeTree data as immutable parts. There is no in-place row update, no row-level lock, no undo log. Every mechanism for "changing" a row is therefore one of two things: **rewrite the part**, or **write another row and resolve the conflict later**. Your design choice is which of those costs you would rather pay, and where. ## Option 1 — ReplacingMergeTree with a version ```sql CREATE TABLE customers ( customer_id UInt64, name String, tier LowCardinality(String), updated_at DateTime ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY customer_id; ``` Every change is an ordinary insert of the new full row. Ingest stays fast because nothing is read before writing. Background merges eventually keep only the highest `updated_at` per `customer_id`, and reads that must be exact use `FINAL` or `GROUP BY customer_id` with `argMax`. **Choose it when** updates are frequent, the source can emit the full current row, and readers can absorb read-time resolution. This covers most change-data-capture and event-sourced pipelines and should be your default. **Watch for**: the sorting key must be exactly the business key; the partition key must keep all versions of a key in one partition or they can never collapse; and duplicates are never *guaranteed* to be removed, so correctness must live in the query, not in hope. ## Option 2 — CollapsingMergeTree with a sign Retract by inserting a copy of the old row with `sign = -1` and the new one with `sign = 1`; aggregate with `sum(metric * sign)` and `HAVING sum(sign) > 0`, which is correct whether or not the merge has run. **Choose it when** the table serves additive aggregates over mutable facts — running totals from a change stream — and the producer genuinely has the before-image. **Reject it when** it does not: a cancel row with wrong values silently corrupts every aggregate. If ordering per key cannot be guaranteed, use `VersionedCollapsingMergeTree(sign, version)`. ## Option 3 — Mutations ```sql ALTER TABLE customers UPDATE tier = 'gold' WHERE customer_id = 42; ``` This is not an OLTP update. It is an asynchronous job, tracked in `system.mutations`, that **rewrites every part containing a matching row** — potentially gigabytes of I/O to change one field. Mutations queue, consume the same background pool as merges, and can stall ordinary merging if you issue them in a loop. Modern ClickHouse also offers lightweight `DELETE FROM ...`, which marks rows through a mask rather than rewriting immediately; it is much cheaper to issue, adds a small filtering cost to subsequent reads, and the rows are physically removed only when a later rewrite happens. **Choose mutations when** the change is rare, bulk and administrative: a schema backfill, a bad-data correction, a right-to-erasure request. **Never** put them on the per-record update path. ## Option 4 — do not store it in a MergeTree at all For small, hot, frequently-changing dimension data — a few million rows of customer attributes, feature flags, price lists — a **dictionary** is usually the better structure. It loads from its source (a database, a file, another ClickHouse table) on a refresh interval, lives in memory, and is read with `dictGet('dict_name', 'attr', key)` directly inside queries. Updates cost nothing at the fact-table level, point lookups are essentially free, and there is no merge or resolution work at all. The limits are size — it must fit in memory — and freshness bounded by the refresh interval. ## The decision framework Ask these in order: 1. **How small and how hot is the changing data?** If it fits in memory and is read as a lookup, use a dictionary. 2. **How often does a given key change?** Rare and bulk → mutations. Continuous → insert-only. 3. **What do readers do with it?** Aggregate additive measures → collapsing is available. Fetch latest state → replacing. 4. **Can the producer supply a before-image?** No → replacing, not collapsing. 5. **Can events arrive out of order?** Yes → carry an explicit version column in whichever engine you pick. 6. **What is the read latency budget?** Tight → pay the cost at write time by pre-resolving into a downstream rollup rather than running `FINAL` on every dashboard query. ## Operational consequences to own Whichever you choose, the raw table is not the contract — the resolving view or the downstream rollup is. Expose that and keep the raw table for pipelines only, or someone will eventually read duplicates into a financial report. Monitor `system.mutations` for a stuck queue and `system.parts` for part-count growth, because both failure modes appear as "the cluster got slow" long before anyone connects them to an update strategy. ## How to answer Name the immutability constraint, present insert-only plus read-time resolution as the default, place mutations firmly in the administrative bucket, and mention dictionaries — that last one is what separates a candidate who has designed a ClickHouse schema from one who has read about the engines.