Deduplicate rows in a ClickHouse table using ReplacingMergeTree, understanding that dedup happens only on merge (not immediately) and FINAL forces correctness at query time.

Documented steps

1. Create the table with ENGINE = ReplacingMergeTree([ver [, is_deleted]]) and an ORDER BY that defines the uniqueness key (uniqueness is by ORDER BY, not PRIMARY KEY): CREATE TABLE t (key Int64, someCol String, eventTime DateTime) ENGINE = ReplacingMergeTree(eventTime) ORDER BY key;
2. Insert rows normally, including 'duplicate' rows with the same ORDER BY key but different ver values: INSERT INTO t VALUES (1,'first','2020-01-01 01:01:01'); INSERT INTO t VALUES (1,'second','2020-01-01 00:00:00');
3. Without ver, ClickHouse keeps the last-inserted row per key on merge; with ver specified, it keeps the row with the maximum ver value (ties broken by most-recently-inserted).
4. Understand that deduplication occurs only during background merges, which run at an unpredictable time in the background; some duplicate rows may remain unmerged indefinitely, and a plain SELECT can return duplicates.
5. For query-time correctness, add the FINAL modifier: SELECT * FROM t FINAL; this forces per-query merging of matching parts before returning results, and is the only way to guarantee no duplicates without waiting for a background merge.
6. Be aware FINAL is expensive: it must read and merge all parts for the relevant keys at query time, which increases CPU/memory cost and reduces the parallelism benefits ClickHouse normally gets from independent parts; avoid running it on very large tables in hot query paths.
7. For row deletion semantics use an is_deleted column together with ver (ENGINE = ReplacingMergeTree(ver, is_deleted)); rows with is_deleted=1 are logically deleted on merge but are only physically removed by enabling the allow_experimental_replacing_merge_with_cleanup setting and running OPTIMIZE TABLE t FINAL CLEANUP or configuring min_age_to_force_merge_seconds.
8. Reference: https://clickhouse.com/docs/engines/table-engines/mergetree-family/replacingmergetree