When does a ClickHouse ReplacingMergeTree table actually remove a duplicate row?

## What ReplacingMergeTree is for `ReplacingMergeTree` is the standard ClickHouse answer to "my source system sends updates and I want the latest state per key". It has exactly the same storage as `MergeTree` — immutable sorted parts, a sparse index, partitions, TTL — and differs in one respect: when a background merge encounters several rows with the same sorting key, it emits only one of them. ```sql CREATE TABLE users ( user_id UInt64, name String, updated DateTime ) ENGINE = ReplacingMergeTree(updated) ORDER BY user_id; ``` ## The deduplication key is the ORDER BY key This is the single most common misunderstanding. The identity of a row for deduplication purposes is its **full `ORDER BY` (sorting key)**, not a primary key you declared separately and not "all columns". If you write `ORDER BY (user_id, updated)`, then two rows for the same user with different `updated` values have *different* sorting keys and will never be deduplicated — the table simply grows. The sorting key must be exactly the business key you want one row per. ## When the duplicate actually disappears Deduplication happens **only inside a merge**, and merges have three properties that shape everything: 1. **They are asynchronous.** Nothing happens at insert time. Immediately after loading, both versions of the row are in separate parts and both are returned by a plain `SELECT`. 2. **They are size-bounded.** The background scheduler will not merge parts beyond a configured maximum resulting size (`max_bytes_to_merge_at_max_space_in_pool`), so a very large part may sit unmerged beside a small newer one indefinitely. There is no guarantee — and the documentation is explicit about this — that a duplicate is ever removed. 3. **They never cross partitions.** If the table is partitioned by month and an update for a key arrives with a timestamp in a different month, the old and new rows live in different partitions and can never be merged together. Partition by something stable relative to the key, or accept permanent duplicates. ## The version parameter With `ReplacingMergeTree(ver)` the merge keeps the row with the **maximum** `ver` for each sorting key; `ver` is typically a `DateTime` update timestamp or a monotonically increasing sequence number from the source. Without the parameter, the last row in the merge selection — effectively the most recently inserted among the parts being merged — survives, which is fine for genuinely idempotent replays and dangerous for out-of-order change data capture. Modern versions also accept a second parameter, `ReplacingMergeTree(ver, is_deleted)`, where a `UInt8` column marks a row as a tombstone so a key can be retired rather than just superseded; physically dropping those rows requires an explicit cleanup merge. Treat this as version-sensitive and check the behaviour on the version you actually run. ## Reading correct results before the merge Three practical options: - **`SELECT ... FINAL`** asks ClickHouse to merge the matching rows on the fly during the query. It is correct and simple, and it costs — the engine must read and collapse overlapping key ranges rather than stream parts independently. Modern versions parallelise it far better than old ones did, and it is a legitimate production choice on tables whose parts are reasonably merged. - **Explicit resolution:** `SELECT user_id, argMax(name, updated) FROM users GROUP BY user_id`. This is deterministic, uses ordinary aggregation, and lets you resolve only the columns you need. - **Filtering on the version** when your access pattern allows, for example reading only the newest partition. A common shape is to expose one of the latter two through a view so that consumers cannot accidentally query the raw table. ## What it is not ReplacingMergeTree is not a uniqueness constraint, not a primary-key enforcement mechanism, and not an upsert that happens at write time. ClickHouse has no constraint that rejects a duplicate insert. (A separate, unrelated feature — insert deduplication on replicated tables — discards a re-sent *identical block* by hash, which protects retries but has nothing to do with row-level replacement.) ## How to answer Lead with "only during background merges, eventually, and only within a partition", then name the version parameter and the read-time remedies. Candidates who say "it deduplicates on insert" have not run it in production, and interviewers ask this question precisely to find out.