## TL;DR
ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

## The query
```text
clickhouse ReplacingMergeTree duplicates handling
```

## Use this when
- you inserted rows twice and queries still show duplicates
- you need to pick the version column for a ReplacingMergeTree table
- counts on a ReplacingMergeTree table look inflated

## Not for
- engines like MergeTree or SummingMergeTree, which have different merge rules
- true upsert semantics, which ClickHouse does not provide natively

## Steps
1. Define a version column (e.g. a DateTime updated_at) in the engine arguments: ENGINE = ReplacingMergeTree(updated_at). On a merge, the row with the max version survives.
   Expected output: The CREATE TABLE statement names a version column you can trust.

2. Expect duplicates right after insert. Merges happen in the background on the server's schedule, not yours.
   Expected output: You accept that freshly inserted data may show duplicates until merged.

3. For exact reads now, query with FINAL or aggregate: SELECT ... FINAL, or group by the key and take argMax(row, version).
   Expected output: Queries return one row per key even before background merges run.

4. Run OPTIMIZE TABLE ... FINAL only sparingly. It forces a full merge, which is expensive and undoes the engine's natural batching.
   Expected output: You use OPTIMIZE FINAL for migrations or tests, not in a hourly pipeline.

5. Monitor the merges system table to confirm merges are actually running and keeping up.
   Expected output: system.merges shows active merges, so you know dedupe is progressing.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg