Analytics PipelineClickHouse & Materialized Views
Guide ClickHouse Core Architecture & Analytics Fundamentals

Deduplicating Rows with ReplacingMergeTree

ReplacingMergeTree deduplicates rows that share an ORDER BY key by keeping only the highest-version row when a background merge combines them — but the collapse is asynchronous, so duplicates stay visible until a merge fires and only correct queries hide them in the meantime. This walkthrough builds a deduplicating table with a version column and an is_deleted tombstone, proves duplicates persist pre-merge with system.parts, and lays out the FINAL, do_not_merge_across_partitions_select_final, and OPTIMIZE … FINAL trade-offs so reads stay correct without paying merge cost on every query.

Prerequisites

How deduplication happens at merge time

An INSERT never deduplicates. Each statement writes a new part containing its rows exactly as given, so two writes of the same key produce two parts, each with that key. Deduplication is a side effect of the background merge that later combines those parts: as the merge streams rows in sorted ORDER BY order, a run of rows sharing a key is reduced to the single row with the maximum version, and if that survivor has is_deleted = 1, a FINAL merge drops it entirely.

ReplacingMergeTree collapse: two parts merge to one row per key by maximum version Two input parts each hold rows for key 42 at different versions plus key 77. A merge keeps the max-version row per key, producing an output part with key 42 at version 3 and key 77 at version 1. Tombstoned rows with is_deleted set are dropped on FINAL. Before the merge, a plain SELECT sees all duplicates. part_1 · newly inserted 42 | v=1 | plan=free 42 | v=2 | plan=pro 77 | v=1 | plan=free part_2 · later write 42 | v=3 | plan=pro 77 | v=1 | plan=free merge: max(version)/key merged part · one row per key 42 | v=3 | plan=pro 77 | v=1 | plan=free lower versions of 42 discarded Before this merge runs, a plain SELECT still returns all five input rows. A survivor with is_deleted = 1 is physically removed on a FINAL merge — the key disappears entirely.

Because a merge only ever runs within a partition and only when the scheduler decides two parts are worth combining, there is no guaranteed moment at which the table is fully deduplicated. The engine gives you eventual convergence, not immediate uniqueness — which is why the read path, not the insert path, is where correctness is enforced.

Step 1 — Create the table with a version and tombstone

Key the table on the entity identity and pass both a version column and an is_deleted column to the engine. Partition so that every version of a key lands in the same partition — for a pure latest-state table that usually means PARTITION BY tuple(), because partitioning by a mutable updated_at would scatter versions of one key across partitions where merges can never unite them.

sql
CREATE TABLE analytics.account_state
(
    `account_id`  UInt64,
    `status`      LowCardinality(String),
    `balance`     Decimal(18, 2),
    `updated_at`  DateTime64(3),
    `version`     UInt64,           -- highest wins on collapse
    `is_deleted`  UInt8 DEFAULT 0   -- 1 = tombstone, dropped on FINAL
)
ENGINE = ReplacingMergeTree(version, is_deleted)
PARTITION BY tuple()
ORDER BY account_id;

Expected: the table registers with engine = ReplacingMergeTree. The version argument makes the newest logical write win independent of insert order; the is_deleted argument turns a soft delete into a real removal at merge time.

Step 2 — Insert duplicates in separate statements

Separate INSERT statements guarantee separate parts, which is what lets you observe the pre-merge state. Write three versions of one account plus an unrelated account:

sql
INSERT INTO analytics.account_state VALUES (100, 'active', 50.00, now64(3), 1, 0);
INSERT INTO analytics.account_state VALUES (100, 'active', 75.00, now64(3), 2, 0);
INSERT INTO analytics.account_state VALUES (100, 'closed', 0.00,  now64(3), 3, 1);
INSERT INTO analytics.account_state VALUES (200, 'active', 10.00, now64(3), 1, 0);

Expected: four rows, three parts for account_id = 100 (one row each) and one for 200.

Step 3 — Confirm the duplicates persist before any merge

A plain count sees every part. This is the behavior that surprises engineers who expect insert-time deduplication:

sql
SELECT count() FROM analytics.account_state;               -- 4 rows still present
SELECT account_id, count() AS versions
FROM analytics.account_state
GROUP BY account_id;                                        -- account 100 -> 3

Now confirm the parts really are separate on disk:

sql
SELECT name, partition, rows, active
FROM system.parts
WHERE database = 'analytics' AND table = 'account_state' AND active
ORDER BY name;

Expected: four active parts, each with rows = 1. Nothing has collapsed because no merge has run.

Step 4 — Read correctly with FINAL

FINAL applies the collapse on the read path: it merges the relevant parts on the fly, keeps the max-version row per key, and drops tombstoned survivors. It is the simplest correct read.

sql
SELECT account_id, status, balance, version
FROM analytics.account_state FINAL;

Expected: exactly one row — account_id = 200. Account 100 collapses to its version = 3 row, which is tombstoned (is_deleted = 1), so FINAL removes it entirely. To keep the tombstone logic explicit and avoid the merge cost, the argMax idiom reproduces the same result at query time:

sql
SELECT account_id, argMax(status, version) AS status, argMax(balance, version) AS balance
FROM analytics.account_state
GROUP BY account_id
HAVING argMax(is_deleted, version) = 0;   -- same one-row result, no FINAL merge

Step 5 — Force convergence with OPTIMIZE when you must

OPTIMIZE TABLE … FINAL forces a merge of all parts in a partition down to one, physically deduplicating the data. It is useful after a bulk backfill or before a full-table export, but it is an expensive, I/O-heavy operation that rewrites entire parts and competes with live merges — never put it in a query path or a per-batch loop.

sql
OPTIMIZE TABLE analytics.account_state FINAL;

-- After it completes, the plain count matches the FINAL count.
SELECT count() FROM analytics.account_state;   -- now 1

Expected: one active part per partition and a plain count that equals the FINAL count. Reach for OPTIMIZE … FINAL as an occasional maintenance action, and lean on the automatic min_age_to_force_merge_seconds setting for routine convergence instead.

Verification

Confirm the state converged by comparing the raw and FINAL counts and inspecting part consolidation in system.parts:

sql
SELECT
    (SELECT count() FROM analytics.account_state)       AS raw_rows,
    (SELECT count() FROM analytics.account_state FINAL) AS deduped_rows;

When these two numbers are equal, every part has collapsed. Track the part count over time — a persistently high count means merges are not keeping pace with writes:

sql
SELECT partition, count() AS active_parts, sum(rows) AS rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE database = 'analytics' AND table = 'account_state' AND active
GROUP BY partition
ORDER BY partition;

To make FINAL cheaper on partitioned tables whose key never spans partitions, enable do_not_merge_across_partitions_select_final so FINAL only reconciles within each partition:

sql
SELECT account_id, status
FROM analytics.account_state FINAL
SETTINGS do_not_merge_across_partitions_select_final = 1;

Gotchas and edge cases

  • Duplicates across partitions never collapse. Merges are partition-scoped, so partitioning by a column that changes between versions of a key (like updated_at) strands duplicates permanently. Keep the version history of a key inside one partition — PARTITION BY tuple() for latest-state tables.
  • No version argument means order-dependent results. ReplacingMergeTree() with no version keeps a row from the most recently inserted part, so a late-arriving out-of-order write can overwrite a newer one. Always supply a monotonic version unless writes are strictly ordered.
  • FINAL cost scales with part count, not result size. A FINAL read merges parts on the fly, so a table with thousands of un-merged parts makes even a tiny lookup slow. Batch inserts and let background merges (or min_age_to_force_merge_seconds) keep the part count low.
  • is_deleted needs FINAL to actually delete. Without FINAL (or clean_deleted_rows), a tombstoned row is only hidden by the FINAL-aware read; the physical row and its storage remain until a FINAL merge removes it.
  • OPTIMIZE … FINAL is not idempotent-cheap. It rewrites whole parts every time and can build a part larger than max_bytes_to_merge_at_max_space_in_pool, after which the engine will never merge it again automatically — so a giant table can end with a permanent split that only FINAL reads reconcile.

Up: MergeTree Engine Variants