Handling CDC Duplicates in ClickHouse: ReplacingMergeTree vs. FINAL vs. ArgMax

ReplacingMergeTree, FINAL, and argMax do not compete as equals. They pay for the same deduplication work at three different moments: at storage time, at query time, and at aggregation time. Which one is cheapest depends on how many rows your filter keeps, how many unmerged parts your table has, and how fresh a result you need.

If you are debugging a pipeline where duplicates have already reached production, start with our companion guide on why CDC deduplication lag happens. It walks through diagnosis. This article answers a different question: given three techniques, which one should you pick for your workload, and how does each one fail?

One result surprised us. In the Insider Engineering benchmark, the winner flipped depending on how much of the data the filter kept, and the source article describes that variable in confusing terms. We sort that out in the dynamic-selection section, and give you a calculator to measure it on your own data.

Scope and version boundary

This article compares three techniques under one scope. We label every claim by evidence class: documented capability, vendor measurement, independent finding, editorial inference, or hands-on observation.

The 30-second answer

Each row below covers one technique, so the table works on its own.

Where You Pay: Storage, Query, vs. Aggregation Time

ReplacingMergeTree: storage-time deduplication

ReplacingMergeTree keeps one row per sorting-key tuple, and it does so during background merges. The engine reference says merge timing is not something you can plan around, so some duplicates can remain unprocessed. That one fact drives everything else in this article.

FINAL: query-time deduplication

FINAL applies merge logic to the rows a query touches, and it does not change what is stored. ClickHouse’s knowledge base separates it clearly from the OPTIMIZE … FINAL command, which physically rewrites parts.

argMax: aggregation-time deduplication

argMax(value, key) returns the value from the row where key is largest, per the function reference. Group by your entity ID, use the version as the key, and you get the latest row without touching the engine. It runs on any MergeTree-family table.

Pick the technique by asking where you want to pay: once in the background, once per query, or once per aggregation.

ReplacingMergeTree: Storage-Time Deduplication & Limits

ReplacingMergeTree reduces stored duplicates over time, but it gives you no point at which a read is guaranteed clean. The official guide and the deduplication strategies page both describe this as eventual deduplication. One Reddit thread on common ClickHouse mistakes appeared in our search results, but we could only read its search snippet, so we treat it as a lead and not as evidence.

How Deduplication Actually Works (ORDER BY vs. PRIMARY KEY)

Uniqueness comes from the ORDER BY clause, not the PRIMARY KEY. We cover that trap in depth in our production guide; here we focus on when the engine is the right choice and when it is not. A merge keeps one row per key. Without a ver column, the last-inserted row wins. With ver, the highest version wins, and a tie goes to the most recently inserted row.

Engine Syntax: The ‘ver’ and ‘is_deleted’ Parameters

The ver column can be UInt*, Date, DateTime, or DateTime64. The is_deleted parameter works only when ver is set, and it is a UInt8 where 1 marks a deleted row. By default ClickHouse keeps the last row for a key even when it is a delete row, so a late row with a lower version cannot bring the key back. The engine reference documents all of this.

The Canonical CDC Table Pattern (Postgres & PeerDB Schema)

This table uses the _peerdb_version and _peerdb_is_deleted columns that ClickPipes documents for Postgres CDC. One difference matters. ClickPipes uses the Postgres primary key as the ordering key by default, and its ordering-keys page warns that a different ordering key can cause deduplication problems. Here tenant_id is part of the key, so it must never change for a row, and it should be covered by the source’s replica identity (see the warning box below).

ClickHouse SQL:

CREATE TABLE users ( id UInt64, tenant_id UInt32, email String, plan LowCardinality(String), updated_at DateTime64(3), _peerdb_version UInt64, _peerdb_is_deleted UInt8 ) ENGINE = ReplacingMergeTree(_peerdb_version, _peerdb_is_deleted) ORDER BY (tenant_id, id);

Watch the version source. If two events can carry the same version, the later insert wins, and that may not be the later change.

When to Choose ReplacingMergeTree

Use it when each CDC row has a stable identity, a version that only moves forward, and an explicit delete flag. It also fits when stale reads are acceptable for a few minutes. Partitioning can help too: ClickHouse’s own guidance names merge-oriented engines as one of two good reasons to partition, because historical partitions can settle into one part and make FINAL cheap.

Dealing with CDC deduplication, ROW_NUMBER() window lag, and idempotency in technical interviews? We cover real-world deduplication and data consistency scenarios in our Data Warehouse Interview Questions handbook.

When to Avoid It: Mutable Keys and High-Delete Tables

Skip it when the sorting key contains columns that change, because an update then creates a different key and the old row never goes away. Skip it for tables with many deletes. The official guide recommends delete handling this way only below roughly 10% deletes unless you can schedule cleanup windows.

Production Anti-Patterns: Mutable Keys & Scheduled OPTIMIZE FINAL

ClickHouse SQL (anti-patterns):

-- ANTI-PATTERN: do not do this -- email can change, so an update becomes a new key ENGINE = ReplacingMergeTree(_peerdb_version) ORDER BY (tenant_id, email);
-- ANTI-PATTERN: do not do this -- scheduled from cron every night OPTIMIZE TABLE users FINAL;

The first creates duplicates by design. The second backfires when it runs on a schedule. ClickHouse’s best-practices page says to avoid OPTIMIZE FINAL in most cases, because it ignores the roughly 150 GB merge safety limit and can build very large parts that later merges struggle to process, which can let ReplacingMergeTree duplicates accumulate.

ClickHouse engineering likewise warns against running it blindly on a schedule. The sources do not forbid every use. The best-practices page names finalizing data before a freeze or export as a specific need, and the ReplacingMergeTree guide lists OPTIMIZE FINAL as a manual remedy once merged parts reach the maximum merge size, with a warning that it can be memory-intensive on large tables and may need repeating as new data arrives.

Hard Deletes & Cleanup: The Experimental FINAL CLEANUP Setting

Dropping delete rows for good needs the experimental setting allow_experimental_replacing_merge_with_cleanup. The setting’s reference page still labels it experimental (last modified August 3, 2026). Before you run it, make sure no older-version rows will arrive later, and run SYSTEM SYNC REPLICA.

ClickHouse SQL (experimental cleanup):

-- table created with: -- SETTINGS allow_experimental_replacing_merge_with_cleanup = 1 SYSTEM SYNC REPLICA users; OPTIMIZE TABLE users PARTITION ID '202609' FINAL CLEANUP;

Follow these steps, in this order:

1. Confirm that no row with an older version can arrive for the keys you will clean up. The official guide says rows inserted later with older versions would be wrongly kept, because the delete rows will be gone.
2. Confirm the table has allow_experimental_replacing_merge_with_cleanup = 1, and accept that the feature is still experimental. In our test engine (version 26.9.2.1), OPTIMIZE … FINAL CLEANUP failed with an error saying CLEANUP merges are not allowed when the setting was off, and removed the deleted key when it was on.
3. Pause inserts once step 1 holds. The guide recommends pausing inserts until the sync and the cleanup are complete.
4. On a replicated table, run SYSTEM SYNC REPLICA so all replicas are in sync. On a non-replicated table the command fails (we saw a “not replicated” error), so skip it.
5. Run OPTIMIZE TABLE … PARTITION ID ‘…’ FINAL CLEANUP on one partition that is no longer subject to changes, check the result, then move to the next partition.

ReplacingMergeTree is a good default storage layer, and a poor promise of clean reads.

FINAL: Query-Time Deduplication & Performance Overhead

FINAL gives correct latest-state reads without changing storage, and its cost tracks how many unmerged parts it has to reconcile. ClickHouse’s solutions architects say so directly in a March 2026 best-practices post: FINAL must reconcile all parts before returning results, and on a table with one part per partition it runs as fast as a query without FINAL.

How FINAL Works: Query-Time Merging and PREWHERE Limits

FINAL merges the rows that match the query’s ranges at read time. Insider Engineering reports that it merges only the necessary ranges rather than the whole table. Treat that as a practitioner description, not a documented guarantee.

ClickHouse’s own guide describes FINAL’s overhead as small, and says it is most noticeable when a query does not filter on primary key columns. It also says that when the WHERE condition has no key column, ClickHouse does not currently use the PREWHERE optimization together with FINAL.

Syntax: ‘SELECT … FINAL’ vs. ‘SETTINGS final = 1’

ClickHouse SQL:

-- per table SELECT count() FROM users FINAL WHERE _peerdb_is_deleted = 0;
-- per query: applies to every table in the query SELECT count() FROM users WHERE _peerdb_is_deleted = 0 SETTINGS final = 1;

The first form is the pattern ClickPipes documents for counting live rows. The second form hits every table in the query, which matters for joins.

When to Use FINAL: Key Lookups and Selective Queries

Use FINAL when your queries filter on the same columns as the primary key. That is the official guidance for when FINAL is acceptable. Key lookups, per-tenant reads, and “current row by ID” queries fit well.

When to Avoid FINAL: Wide Scans Across Unmerged Parts

Avoid it on wide scans over tables with many unmerged parts, and when your filter hits no key column. The official guide says FINAL’s overhead is most noticeable when queries do not filter on primary key columns, because more data is read and deduplicated.

Is FINAL Still Slow? Modern Performance & Release 23.12+

The old line “FINAL is slow” is now conditional, and the strongest evidence sits in ClickHouse’s own guide. The official ReplacingMergeTree guide calls FINAL’s overhead small and ties its cost to how much data a query must read. It also documents a version boundary: from 23.12 onward, when the partition key is a prefix of the sorting key, merging across partitions is not performed at query time. Two weaker sources point the same way.

PeerDB’s data-modeling guide says recent ClickHouse releases made significant FINAL improvements, and Insider’s post says it tested versions 22.8, 23.3, and 23.8 and saw gains in the newer ones, but it prints no version-by-version numbers.

We also read the GitHub issue Insider cites, #74963. It is a question from Insider’s author about version 23.3, not a maintainer announcement. A community reply in the thread says FINAL has been drastically improved since 23.3, and it adds a cost factor worth knowing: unless FINAL’s optimizations apply, it reads all columns of the sorting key to decide the final row for each key, while an argMax with a GROUP BY reads only the grouping columns.

The commenter describes themselves as an ordinary DBA, so we treat this as a practitioner explanation (an independent finding), not as documentation. We therefore do not name a single release that fixed FINAL. The honest summary is that FINAL has improved across recent versions, and part count, partition alignment, sorting-key width, and filter shape still decide the cost.

Anti-Patterns: Unfiltered Scans and Dangerous Partition Settings

ClickHouse SQL (anti-patterns):

-- ANTI-PATTERN: do not do this -- full scan with FINAL, no filter on tenant_id or id SELECT plan, count() FROM users FINAL GROUP BY plan;
-- ANTI-PATTERN: do not do this -- do_not_merge_across_partitions_select_final without a partition guarantee SELECT * FROM users FINAL WHERE id = 42 SETTINGS do_not_merge_across_partitions_select_final = 1;

The second needs a caveat. The official guide recommends that setting only when the partitioning key does not change for a row, so that updates to the same row always land in the same partition. In our reading, if versions of one row sit in different partitions, FINAL cannot merge them, and it can return rows a full merge would have removed.

Looking Ahead: The ‘Lonely Part’ FINAL Optimization

Issue #54988 tracks a “lonely part” FINAL optimization for ReplacingMergeTree. We cite it so you can follow it, and we do not describe its behavior here.

Keep part counts low and filters aligned with the key, and FINAL is often the simplest correct choice.

argMax: Aggregation-Time Deduplication & Memory Costs

argMax is a real deduplication technique, not just an add-on to ReplacingMergeTree. The official deduplication guide shows an aggregate-after-GROUP-BY alternative to FINAL, and argMax is the same idea generalized to any column. PeerDB’s guide also recommends argMax next to FINAL for deduplicating at query time.

How argMax Works: Post-Aggregation State and Memory Overhead

argMax runs after rows are read and grouped, so ClickHouse must scan every matching row before it can pick the latest one. Insider describes the same limit: the “latest record” check happens after aggregation. That explains why argMax uses a lot of memory when the filter lets many rows through.

Function Syntax & Tombstones: Why Delete Filters Belong in HAVING

The value comes first and the key second: argMax(value, key). argMaxIf adds a condition inside the aggregate. Insider’s introductory snippet writes argMax(writ, age), which reads as reversed against the documented order. Their prose describes the correct intent, and we use the documented order everywhere.

ClickHouse SQL:

SELECT id, argMax(email, _peerdb_version) AS email, argMax(plan, _peerdb_version) AS plan FROM users WHERE tenant_id = 42 GROUP BY id HAVING argMax(_peerdb_is_deleted, _peerdb_version) = 0;

The delete check sits in HAVING, after the aggregate. Put it in WHERE and you break the result (see the anti-pattern below).

When to Choose argMax: Tight Filters and Existing GROUP BY Queries

Use it when the filter keeps very few rows, when the query already has a GROUP BY, or when your table is plain MergeTree and append-only. ClickHouse’s own benchmark (covered below) found argMax steadier while a table is still receiving inserts.

When to Avoid argMax: Wide Columns and Memory Explosions

Avoid it on wide rows with a loose filter. Every column needs its own argMax, and memory grows with the number of groups times the number of columns.

Append-Only Pattern: Deduplicating on Plain MergeTree (No RMT)

Here is a ledger table that only ever receives INSERTs. A Reddit thread discusses a similar idea, a ledger column plus argMax instead of FINAL, but we could only read its search snippet, so treat it as a lead.

ClickHouse SQL:

CREATE TABLE account_events ( account_id UInt64, status LowCardinality(String), balance Decimal(18, 2), event_id UInt64, updated_at DateTime64(3) ) ENGINE = MergeTree ORDER BY (account_id, updated_at);
CREATE VIEW account_current AS SELECT account_id, argMax(status, (updated_at, event_id)) AS status, argMax(balance, (updated_at, event_id)) AS balance FROM account_events GROUP BY account_id;

The tuple key breaks timestamp ties with event_id. Run EXPLAIN indexes = 1 on a filtered query against the view, to confirm your account_id filter still reaches the primary index.

Critical Anti-Patterns: Resurrected Deletes and Inverted Arguments

ClickHouse SQL (anti-patterns):

-- ANTI-PATTERN: do not do this -- filtering deletes before argMax brings deleted rows back to life SELECT id, argMax(email, _peerdb_version) AS email FROM users WHERE _peerdb_is_deleted = 0 GROUP BY id;
-- ANTI-PATTERN: do not do this -- arguments reversed: returns the version at the largest email SELECT id, argMax(_peerdb_version, email) FROM users GROUP BY id;

In the first query, the old non-deleted versions pass the filter, and the row returns even though its latest version is a delete. The second runs without an error and returns the wrong data, which is the worst kind of bug.

Version Independence: Engine-Agnostic Aggregation

argMax has no engine dependency, so it behaves the same across the versions in our scope. Its cost is the part that changes with your data.

argMax is a first-class option when your filter is tight or your query already aggregates.

The dynamic-selection pattern: FINAL vs argMax by filter selectivity

The best public evidence says the winner depends on how many rows your filter keeps. Furkan Kahveci of Insider One Engineering measured both techniques on a production contacts table (ReplicatedReplacingMergeTree, ordered by partner and a hash of the user ID). Credit for the idea and the numbers goes to that team.

The Cost Model: Memory Footprint vs. Part Reconciliation

This part is our analysis, built on their numbers and ClickHouse’s description of how each technique works. FINAL pays to reconcile overlapping parts inside the ranges the query reads. argMax pays to read every matching row and hold one aggregate state per group per column. When the filter keeps few rows, argMax has little to hold and FINAL still pays its merge setup. When the filter keeps many rows, argMax holds a lot of state and FINAL’s merge is cheaper.

Measuring Match Fraction: Formula, SQL Sample & Calculator

Insider samples a small slice of the table and divides rows that pass the filter by all rows. That ratio is the match fraction: the share of rows the filter keeps. We use that term because “selectivity” is used both ways in the source. A reader commented on exactly that confusion under the post.

ClickHouse SQL:

SELECT countIf(plan = 'pro') AS matching_rows, count() AS sampled_rows, round(matching_rows / sampled_rows, 2) AS match_fraction FROM users WHERE tenant_id = 42 AND cityHash64(id) % 1000 = 0;

Insider uses SAMPLE 1 / 100000, which needs a SAMPLE BY clause in the table definition. The hash filter above works on any table. Both sample raw rows, so the result is approximate. On a small table the hash filter can match no rows and return nan (we saw this on a five-row test table), so use a smaller modulus or a larger table. Feed matching_rows and sampled_rows into the calculator below.

The Insider Engineering Benchmark: Dissecting the 50% Rule

Challenge: Insider stores each contact update as a new row, so any segmentation query must find the latest row per user. Their older approach grouped rows and filtered after aggregation.

Action: They compared argMax against FINAL across filters with different match fractions, and measured time and memory.

Outcome: The chart shows what they reported (values approximate, from their post; one workload, table version not stated).

Lesson: In this workload FINAL won whenever the filter kept more than about half the rows. argMax won when it kept almost none (match fractions of 0 to 0.10). The crossover sits somewhere between 0.10 and 0.56. We read the table’s “selectivity” column as the match fraction.

Two things support that reading: the post’s own query divides matching rows by all rows, and in the zero row argMax used only 78 MB, which only makes sense if almost no rows matched. But the post’s prose states its 50% rule in terms of rows “eliminated”, which would be the opposite reading, and the author does not reconcile the two.

Treat our reading as an inference, and confirm the direction on your own data with the calculator above. Do not copy 50% as a rule: it comes from one table, one hardware setup, and one data shape. Also note FINAL used far less memory in every row except the last.

Conditions we could not find in the post: the ClickHouse version used for the table, the hardware, row counts, concurrency, and cache state. For that reason we treat these numbers as one reported workload that we did not reproduce, and we use them for direction, not as thresholds. The benchmark protocol below lists what to record when you run your own.

The decision tree

Step by step: choose a technique for one query

1. Write the query with its real filters, and note whether the filter uses a key prefix.
2. Run the match-fraction query on a small sample and enter the two counts in the calculator.
3. Run system.parts (diagnostic query 2 below) and note the parts per partition on the partitions you read.
4. Run the query once with FINAL and once with argMax, on the same data and the same cache state.
5. Compare duration, memory, and rows read in system.query_log, then keep the cheaper correct one.

Reproducible Benchmark Protocol: How to Test Your Own Data

The published numbers above come from private data, so you cannot reproduce them. Use this protocol on your own data. It records the conditions that make a result worth trusting.

ClickHouse SQL:

-- 1. Synthetic table: 1M keys, 20% updated, 2% deleted CREATE TABLE bench_users (id UInt64, tenant_id UInt32, email String, plan LowCardinality(String), _peerdb_version UInt64, _peerdb_is_deleted UInt8) ENGINE = ReplacingMergeTree(_peerdb_version, _peerdb_is_deleted) ORDER BY (tenant_id, id);
INSERT INTO bench_users SELECT number, intDiv(number, 100000), concat('u', toString(number), '@x.com'), if(number % 5 = 0, 'pro', 'free'), 1, 0 FROM numbers(1000000);
INSERT INTO bench_users SELECT id, tenant_id, email, if(plan = 'pro', 'free', 'pro'), 2, 0 FROM bench_users WHERE id % 5 = 0;
INSERT INTO bench_users SELECT id, tenant_id, email, plan, 3, 1 FROM bench_users WHERE id % 50 = 1;
-- 2. Equivalence check: must return 0 before you time anything SELECT count() FROM ( SELECT id, email, plan FROM bench_users FINAL WHERE tenant_id = 3 AND _peerdb_is_deleted = 0 ) AS f FULL OUTER JOIN ( SELECT id, argMax(email, _peerdb_version) AS email, argMax(plan, _peerdb_version) AS plan FROM bench_users WHERE tenant_id = 3 GROUP BY id HAVING argMax(_peerdb_is_deleted, _peerdb_version) = 0 ) AS a ON f.id = a.id WHERE f.id != a.id OR f.email != a.email OR f.plan != a.plan;
-- 3. Before each cold run SYSTEM DROP MARK CACHE; SYSTEM DROP UNCOMPRESSED CACHE;

In our test engine the equivalence check returned 0 differences, and FINAL and argMax both returned 98,000 live keys for tenant 3. The cache commands also ran there. That shows the SQL works. It is not a performance result, and we publish no timings from it.

Follow these steps:

1. Build the synthetic table above, or copy your own table to a test server.
2. Run the equivalence check. If it does not return 0, fix the query before any timing.
3. Clear the caches before each cold run. These commands do not clear the operating system page cache.
4. Run each technique at least five times cold and five times warm, first with one client and then with your real concurrency.
5. Read duration, memory, and rows read from system.query_log, and keep every row.
6. Decide only with the conditions table attached to the result.

Measure the match fraction on your own data, then benchmark the two nearest options.

End-to-End CDC Pipelines: Postgres, MySQL, and Kafka Architectures

Most CDC pipelines can deliver the same change more than once, so plan row-level deduplication as part of the design. Insert-level deduplication is a different mechanism. It works on inserted blocks (see ClickHouse’s guide to deduplicating inserts on retries and the settings reference), and the settings reference records that its window defaults changed in 25.9 and 25.10. In our reading, block-level deduplication cannot repair out-of-order change events, because those arrive as different rows, not as repeated blocks.

PostgreSQL CDC: ClickPipes, PeerDB & Replica Identity Rules

The documented pattern is ReplacingMergeTree with _peerdb_version and _peerdb_is_deleted, queried with FINAL plus an _peerdb_is_deleted = 0 filter, per ClickPipes’ deduplication page. The same page shows a row policy that hides the delete filter from applications, and a refreshable materialized view as an alternative for heavy query loads.

ClickHouse SQL:

CREATE ROW POLICY cdc_policy ON users FOR SELECT USING _peerdb_is_deleted = 0 TO ALL;

The same ClickPipes page shows two more read-side templates. A view hides the FINAL and the delete filter from analysts, and a refreshable materialized view writes the deduplicated result to a separate table on a schedule.

ClickHouse SQL:

CREATE VIEW users_view AS SELECT * FROM users FINAL WHERE _peerdb_is_deleted = 0;
-- target table with the same columns CREATE TABLE dedup_users AS users ENGINE = MergeTree ORDER BY (tenant_id, id);
CREATE MATERIALIZED VIEW dedup_users_mv REFRESH EVERY 1 HOUR TO dedup_users AS SELECT * FROM users FINAL WHERE _peerdb_is_deleted = 0;

Results from the refreshable view are only as fresh as its last refresh, so pick the interval from your freshness requirement. We ran the view and the target table in our test engine. We could not run the refreshable view there, because that engine rejected it in its default database, so that statement follows the pattern in ClickHouse’s docs and is untested by us.

When you migrate from Postgres, include the source primary key in the ClickHouse ORDER BY, as the official ReplacingMergeTree guide recommends. For tool context, see our Estuary Flow production review and our notes on Postgres CDC failover, retries, and duplicates.

MySQL CDC: Debezium Binlog Positions vs. Wall-Clock Timestamps

The pattern is the same: ReplacingMergeTree, a version column, and a delete flag. The open question is where the version comes from. Wall-clock timestamps can tie or move backward. In our judgment (not a documented ClickHouse rule), a source log position such as the MySQL binlog file and position is a safer ordering signal.

Check your connector’s event schema before you rely on any field. We covered adjacent patterns in MySQL CDC for Apache Doris, and our Debezium vs Estuary Flow vs MaterializedPostgreSQL comparison helps with tool choice.

Kafka-Direct Ingestion: At-Least-Once Replays & Exactly-Once Limits

Plain Kafka consumption is at-least-once, so a restart can replay messages and create duplicates. ClickHouse’s announcement of its Kafka connector explains how that happens and how the connector’s design relies on insert deduplication to aim for exactly-once delivery.

The connector documentation says the sink ships with exactly-once semantics and warns that buffering is incompatible with that mode, because buffering changes batch boundaries and breaks block deduplication. An older Altinity KB note says the Kafka table engine, acting as a Kafka client, could guarantee only at-least-once in some corner cases such as lost connections. It predates current releases, so check your own version.

Even with exactly-once ingestion, replays upstream of Kafka and CDC retries can still produce logical duplicates in different batches. That is our inference, and it is why we keep a version column and a read-time technique in the design. Community reports are mixed. One r/Clickhouse thread and one r/apachekafka thread describe duplicate problems with ReplacingMergeTree and FINAL, but we could only read their search snippets, so we treat them as leads, not evidence. Issue #112143 tracks dedup logic for the Kafka engine.

Tombstone & Delete Handling: How Each Technique Treats Deletes

Why ALTER Mutations and Lightweight Deletes Fail for CDC

Mutating a row per change event is the wrong tool for CDC, even though it looks like the obvious one. ALTER TABLE … UPDATE and ALTER TABLE … DELETE are mutations. The official ReplacingMergeTree guide says they are handled asynchronously, processed with a single thread, and rewrite data on disk for updates, so they should not be used for high numbers of small changes. The ALTER TABLE … DELETE docs add that it rewrites every data part containing matching rows, and recommend ReplacingMergeTree or CollapsingMergeTree for workloads with frequent deletions or corrections.

Lightweight DELETE FROM is cheaper, and the DELETE docs describe how. It saves a mask (the hidden _row_exists column), later queries skip the masked rows, and the rows are physically removed only at the next merge. The same page warns that deleting large volumes can hurt SELECT performance, that mutations on a table run one after another, and that by default DELETE does not work on tables with projections. It suits rare, targeted deletes. It does not suit one delete per source event.

ClickHouse SQL:

-- ANTI-PATTERN: do not do this -- one mutation per CDC update event ALTER TABLE users UPDATE email = 'new@example.com' WHERE tenant_id = 42 AND id = 7;
-- Reasonable: a rare, targeted delete (for example a legal erasure request) DELETE FROM users WHERE tenant_id = 42 AND id = 7;

Insert the new version as a row and let ReplacingMergeTree, FINAL, or argMax resolve it. That keeps writes append-only, which is what the engine is built for.

Use the version and delete columns from your CDC tool, then pick the read technique per query. Keep mutations for rare corrections.

Matrix: Choosing Your Technique

The thresholds below are judgment calls built on the sources above, and not ClickHouse rules. Use them to pick what to test first.

1. Choosing by Table Size and Part Volume

2. Choosing by Match Fraction and Selectivity

3. Choosing by Query Pattern (Point Lookups vs. Analytics)

4. Choosing by Ingestion Source (Postgres, Debezium, Kafka)

5. Choosing by Freshness SLA: Strict vs. Eventual Consistency

Summary: The Role-Based Decision Guide

Start with the row that matches your workload, then verify with a benchmark.

Case study: ClickHouse’s own benchmark on 152 million rows

Challenge: A ReplacingMergeTree table of Amazon reviews (150M originals plus 2M duplicates) needed a correct latest-row lookup while ingestion was running.

Action: A ClickHouse solutions architect compared FINAL and argMax on the same lookup at two table states.

Outcome: With 9 unmerged parts, FINAL took 1.5 s and argMax 1.0 s. After merging down to one part, they measured 0.48 s and 0.40 s.

Lesson: Part count moved FINAL far more than it moved argMax. The post’s advice: FINAL for simplicity on well-merged tables, argMax for predictable latency during active inserts. This is a single lookup on one dataset.

Conditions the post gives: a ReplacingMergeTree table with about 150 million original rows plus 2 million duplicates, measured with 9 unmerged parts and again after merging to one part. Conditions it does not give: the version, the hardware, and the cache state.

We found no public, named-company postmortem that reports measured results for ReplacingMergeTree-based CDC deduplication. These two cases are the best-documented measurements we could verify, and the Kafka reports earlier are practitioner signals, not case studies.

Production Anti-Patterns: How Each Deduplication Technique Fails

Retry and failover mistakes live upstream of all three, so read our Postgres CDC production guide alongside this table.

Diagnostic Toolkit: Pinpointing Which Technique Bottlenecks Your Query

When duplicate rows or query latency spikes appear in production, guessing whether the bottleneck stems from unmerged parts, FINALoverhead, or argMax memory allocation wastes critical triage time. ClickHouse exposes its entire internal execution state through system tables, meaning you never have to guess. Use the symptom-to-cause matrix below to identify your specific failure mode, then run the five diagnostic queries that follow to inspect part health, active merges, and real execution metrics from system.query_log.

Symptom-to-Root-Cause Matrix: Common Deduplication Failures

Run these in order. The first two show whether duplicates exist and why, and the last three show where the time goes.

ClickHouse SQL:

-- 1. Unmerged duplicate surplus for one tenant SELECT count() AS raw_rows, uniqExact(id) AS live_keys, count() - uniqExact(id) AS surplus FROM users WHERE tenant_id = 42;
-- 2. Parts per partition (many parts raise FINAL cost) SELECT partition, count() AS parts, sum(rows) AS total_rows FROM system.parts WHERE active AND table = 'users' GROUP BY partition ORDER BY parts DESC;
-- 3. Are merges running right now? SELECT table, elapsed, progress, num_parts FROM system.merges;
-- 4. Does the filter reach the primary index? EXPLAIN indexes = 1 SELECT count() FROM users FINAL WHERE tenant_id = 42;

ClickHouse SQL:

-- 5. FINAL vs argMax in your real query log, last 24 hours SELECT if(positionCaseInsensitive(query, ' final') > 0, 'FINAL', if(position(query, 'argMax') > 0, 'argMax', 'other')) AS technique, count() AS queries, round(quantile(0.95)(query_duration_ms)) AS p95_ms, formatReadableSize(quantile(0.95)(memory_usage)) AS p95_memory, round(avg(read_rows)) AS avg_read_rows FROM system.query_log WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY AND query ILIKE '%users%' GROUP BY technique;

Watch for high read_rows with low result counts in query 5: that points to a filter that misses the index. On multi-replica services, wrap system.* tables in clusterAllReplicas as ClickHouse recommends.

Query 5 needs system.query_log, which our embedded test engine does not have, so we did not run it. Test it on a non-production server first. Queries 1 to 4 ran in our test engine.

Read parts per partition and rows read before you blame any technique.

Query 1 gives you two numbers. The calculator below turns them into the error a reader would see if they queried the table with no FINAL and no argMax.

ClickHouse Deduplication Timeline: Key Version Changes & Defaults

ClickHouse moves fast, and deduplication behavior that was true in version 22.x or 23.x may no longer apply today. Before following advice from older blog posts, verify your deployed engine version against the architectural boundaries and default settings below.

Check the version column and the defaults in this table before you trust a blog post from an older release.

Beyond the Big Three: Alternative Engines and Upstream Deduplication

If one of the three techniques keeps failing, the engine or the pipeline may be the wrong choice.VersionedCollapsingMergeTree uses a sign column and a version, which suits data that changes often. A refreshable materialized view pre-deduplicates on a schedule, but ClickHouse’s own guidance warns that these need careful handling of late data, backfills, and idempotency. Incremental materialized views do not propagate updates or deletes from their source, so they fit append-only data.

Upstream deduplication in a stream processor such as Flink or ksqlDB removes duplicates before they reach ClickHouse, at the cost of running another system. For more reading, see Altinity’s row-level deduplication KB and Altinity’s ReplacingMergeTree explainer. We did not re-verify their contents for this update.

Switch engines or move deduplication upstream when a technique keeps failing for structural reasons.

The decision rule to keep

Store with ReplacingMergeTree, because it keeps the table small over time. Never trust it for a clean read. For a key lookup, read with FINAL and keep parts per partition low. For an aggregation whose filter keeps almost nothing, read with argMax. For everything in between, measure the match fraction, benchmark both, and write down the result with your ClickHouse version beside it. The evidence we could verify is one production workload and one ClickHouse benchmark, so your own numbers outrank both.

The deduplication, version-ordering, and delete-tombstone decisions in this article also appear as interview questions about CDC, incremental loading, and exactly-once semantics, see our 50-question ETL Interview Questions guide for the underlying patterns.

FAQs: ClickHouse CDC Duplicates

About The Author

A Gadallh

Ahmed Gadallah is the Founder and Editor of Vertex Frontier, where he publishes research-driven articles on AI, data science, cloud computing, cybersecurity, software engineering, and emerging technologies, with a focus on technical accuracy, clarity, and practical insights.

View all articles by A Gadallh →