ReplacingMergeTree: When Eventual Dedup Is Fine

Learning Objectives

Evaluate whether ReplacingMergeTree's eventual, merge-time deduplication actually fits a given workload's correctness requirements
Choose between ReplacingMergeTree, query-time deduplication (FINAL or argMax/GROUP BY), and upstream dedup, defending the choice against the alternatives' real strengths
Design a version column that makes "last write wins" mean something specific to the business, not just "whichever row merged last"
Diagnose the classic ReplacingMergeTree failure mode — duplicate rows showing up in an unmerged table — and explain why it isn't a bug

Why This Matters

"Just use ReplacingMergeTree" is one of the most common pieces of ClickHouse advice a team hears the first time they need to handle updates or re-delivered rows — and it's advice that's right often enough to be dangerous, because the cases where it's wrong don't fail loudly. They fail as a support ticket three weeks later: "why does this dashboard show 1,200 customers when we only have 1,000?" The engineer who reaches for ReplacingMergeTree without understanding when it deduplicates, not just that it deduplicates, is the engineer who gets paged for that ticket. This lesson is about the judgment call underneath the reflex: what ReplacingMergeTree actually trades away in exchange for its convenience, and how to tell whether your workload can afford that trade.

The trade-off

Every MergeTree table you've built so far in this course keeps every row you insert, forever, exactly as inserted. ReplacingMergeTree is the first engine in this course that doesn't: it's built for data that arrives as a sequence of versions of the same logical row — a customer profile updated three times, a re-delivered event that shouldn't count twice — and it collapses those versions down to one, keeping (by default) whichever row a comparison you define says is "latest."

The trade being made is when that collapsing happens. It happens during a background merge — the same background merge process from Module 3, running on ClickHouse's own schedule, not yours. That means the deduplication ReplacingMergeTree promises is eventual, not immediate, and the central judgment call in this lesson is whether your workload can tolerate seeing duplicate, not-yet-merged rows in between.

The alternatives, steelmanned

Reaching for ReplacingMergeTree isn't the only way to handle this problem, and the other approaches aren't inferior fallbacks — each one is the right call under a real, common set of constraints.

Upstream deduplication — stopping duplicates before they're ever inserted. If your ingestion pipeline can guarantee exactly-once delivery, or can check "have I seen this row's key before" prior to writing, duplicates simply never reach ClickHouse. This is the strongest guarantee available: no query, at any point, ever needs to account for duplicates, because there aren't any. It costs real engineering effort — an idempotency key, a dedup cache, or an exactly-once-capable pipeline stage — and it isn't always possible, especially when the source is a change-data-capture stream from a system you don't control and that only promises at-least-once delivery. Teams that already have this guarantee, or can get it cheaply, are right to prefer it over anything ClickHouse-side, because it makes every subsequent design decision simpler.

Query-time deduplication on a plain MergeTree — FINAL or an explicit argMax/GROUP BY. Instead of a special engine, keep every version in an ordinary MergeTree table and resolve "which version is current" at query time, either with the FINAL modifier or with an explicit aggregation like argMax(value, version) GROUP BY key — argMax(value, version) returns the value from whichever row in the group has the largest version, which is exactly "give me the current row" spelled out as a query instead of delegated to an engine. This is the most correct at every instant option among the three: there's no merge-timing dependency, no window where a query can see stale duplicates, because the resolution happens fresh on every read. The cost is real and it scales with data volume — FINAL has to do the same duplicate-resolution work a merge would have done, but at query time, on demand, which is measurably slower than reading an already-merged part. Teams with strict, always-correct read requirements and a query volume that can absorb that cost make a defensible choice here.

ReplacingMergeTree — eventual, merge-driven deduplication. The case for it: it's nearly transparent. Ingestion looks like ingestion into any other MergeTree table — no idempotency layer, no FINAL tax on every query — and duplicates get cleaned up for free, in the background, as part of the same merge process that's already consolidating parts for other reasons. This is the right tool when duplicates are a real but occasional fact of life (an at-least-once CDC stream, a retry-happy producer) and the workload can tolerate a brief window — seconds to hours, depending on merge cadence — where a query might see a row that's technically been superseded. It is not the right tool when a query needs a guarantee that it will never, under any timing, see a stale duplicate.

Decision framework

The reasoning a senior engineer runs through, in order:

Can duplicates be prevented upstream, cheaply? If yes, do that first — it dominates every ClickHouse-side option, because no downstream engine or query pattern has to compensate for a problem that no longer exists.
If duplicates are going to happen anyway, can every query that matters tolerate an eventual-consistency window? A dashboard refreshed every few minutes almost certainly can. A balance check before authorizing a transaction almost certainly cannot. This single question is usually the deciding one.
If the window is tolerable, is the actual failure mode "duplicate rows" or "conflicting versions of a row that need one to win"? ReplacingMergeTree is built for the second case — it needs a version column with real business meaning, not just "insertion order," or "last write wins" becomes "whichever part happened to merge last," which is a much weaker and less predictable guarantee than it sounds.
If the window is not tolerable but ReplacingMergeTree's ingestion simplicity is still wanted, is FINAL on read affordable? Some teams land on ReplacingMergeTree for ingestion simplicity and FINAL on the specific queries that need a hard guarantee, accepting the read-time cost only where it's actually needed rather than paying it everywhere.

Designing the version column

ReplacingMergeTree(version_column) breaks ties between duplicate sorting-key rows by keeping the row with the greatest value in version_column — not the one inserted most recently. A version column that's just "whatever timestamp the row happened to have when it was written to ClickHouse" answers the wrong question if the actual business rule is "the most recently updated record in the source system" — a re-delivered old CDC event, inserted late but carrying an old source timestamp, should lose to a row that already reflects a newer state, regardless of which one physically landed in ClickHouse first. The version column has to encode the source system's notion of recency, not ClickHouse's.

A version column tied to the source of truth, not to insertion ordersql

CREATE TABLE customers
(
    customer_id  UInt64,
    plan_tier    String,
    updated_at   DateTime  -- from the source system's own change timestamp
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY customer_id;

Cold Start — No AI

Your team ingests customer profile updates from a CDC stream (a tool capturing every row change in an upstream Postgres customers table) into ClickHouse. Each captured change is a full row snapshot carrying the source table's own updated_at timestamp. The CDC pipeline only guarantees at-least-once delivery — the same change occasionally arrives twice. A dashboard needs an accurate, roughly-real-time count of customers per plan tier.

Before reading further or asking an AI assistant anything, work through this on your own: which MergeTree engine would you choose, what would you use as the version column and why, and would you query this table with a plain SELECT ... GROUP BY plan_tier, count() or something else? Write down your reasoning.

Reflection: Where were you tempted to just ask an AI "what ClickHouse engine handles deduplication" and take whatever it said? Would that prompt have surfaced the distinction between "deduplication happens eventually, at merge time" and "deduplication happens on every read," or the fact that a plain count() without FINAL or an aggregation can double-count rows that haven't merged yet? A generic answer to a generic prompt tends to name the engine and stop there — the judgment call this lesson is actually about is what happens after you've picked it.

Where were you tempted to reach for AI just now? What made you resist, or give in?

Reasoning Prompt

A separate team runs a table receiving roughly 5 million upserts a day (order status updates, keyed by order_id), queried thousands of times a day by latency-sensitive internal dashboards showing current order status. They're deciding among three approaches: (1) ReplacingMergeTree with a periodic scheduled OPTIMIZE ... FINAL, (2) ReplacingMergeTree with every query using the FINAL modifier, and (3) a plain MergeTree with every query using an explicit argMax(status, updated_at) grouped by order_id. Reason through which you'd recommend, and under what conditions your recommendation would change. There isn't a single correct answer here — multiple choices are defensible depending on assumptions you'll need to state explicitly.

Write your reasoning before revealing the model answer.

Model Answer⌄

A reasonable default: ReplacingMergeTree with FINAL used only on the specific dashboard queries that need a hard guarantee, not a blanket scheduled OPTIMIZE ... FINAL and not FINAL used indiscriminately everywhere.

Why not rely solely on the scheduled OPTIMIZE ... FINAL: running OPTIMIZE ... FINAL forces a full merge of all parts into one, which is a heavyweight operation on a table taking 5 million writes a day — ClickHouse's own documentation notes plain OPTIMIZE "will read and write a large amount of data." And even right after it completes, new upserts arriving before the next scheduled run reintroduce exactly the duplicate window this table is trying to avoid. It buys a mostly-deduplicated table most of the time, not a guarantee at query time, which doesn't match "latency-sensitive dashboards showing current status."
Why not FINAL on every single query: at thousands of queries a day against a table this size, paying FINAL's query-time merge cost on every single read is real, compounding load, especially on the dashboards' most frequent queries. If only a handful of dashboard views actually require a hard, no-stale-data guarantee, applying FINAL there and leaving other, more tolerant views (e.g., a historical trend chart that doesn't care about the last few minutes) to plain, unqualified reads gets most of the correctness where it matters without paying the cost everywhere it doesn't.
What would change the recommendation: if it turned out every dashboard query genuinely needed a hard correctness guarantee — say, this table feeds an operational alert that pages someone if an order is stuck, where a stale duplicate could mask a real problem — the reasoning tips toward option (3), the plain MergeTree with explicit argMax, because it doesn't depend on ClickHouse's merge internals at all and its cost is at least fully visible and tunable (e.g., pre-aggregating it into a materialized view) rather than hidden inside engine behavior. Conversely, if the dashboards are known to tolerate a several-minute staleness window without issue, the simplest option — ReplacingMergeTree with no FINAL at all, just accepting the eventual-consistency window — might be the right call, and paying for FINAL anywhere would be solving a problem nobody actually has.

The shape of the reasoning matters more than the specific pick: state what correctness window each specific query actually needs, and pay the query-time dedup cost only where that window is actually violated by staleness — not uniformly, and not based on which engine sounds most authoritative.

Case studies

A good fit: change-data-capture pipelines landing in ClickHouse. PeerDB, an open-source tool for streaming CDC from Postgres (and other OLTP databases) into ClickHouse, markets ClickHouse as a supported CDC destination. Independent of PeerDB specifically, the general shape — a CDC stream with at-least-once delivery and its own well-defined row-versioning semantics, landing in ClickHouse — is the textbook workload ReplacingMergeTree was designed for: a source system that already has a well-defined notion of row versioning (the source database's own change ordering), feeding a destination where brief eventual consistency is an acceptable trade for ingestion simplicity.

A bad fit: a table backing a real-time inventory check before allowing a purchase. A team that put ReplacingMergeTree (with no FINAL) behind a "is this item still in stock" check learned the hard way that a several-second window of stale, unmerged duplicate rows is exactly long enough to double-sell a low-stock item under concurrent checkout traffic. The fix wasn't a different engine — it was recognizing that this specific query needed a same-instant guarantee that no eventual-consistency engine can provide without FINAL, and that inventory-check traffic was exactly the case where FINAL's cost was worth paying, or where the check belonged in the OLTP system of record rather than in ClickHouse at all.

Anti-pattern gallery

Assuming deduplication happens immediately after insert. This is the single most common ReplacingMergeTree mistake: inserting a duplicate row and querying right afterward, seeing both rows, and concluding the engine is broken. It isn't — merges run on ClickHouse's own background schedule, and a freshly inserted part hasn't been merged with anything yet. A table can sit with un-deduplicated rows indefinitely if the parts they're in never happen to merge with each other.

Using ReplacingMergeTree as a substitute for an application-level uniqueness constraint. ReplacingMergeTree deduplicates on the sorting key you define, not on some abstract notion of "the primary key" the way a uniqueness constraint would in an OLTP database, and it only does so during merges. Treating it as an enforced invariant that's always true (rather than an eventual cleanup that's usually true) leads directly to the inventory-check-style failure above.

Version columns based on ingestion time instead of source-of-truth time. Covered above in "Designing the version column" — worth repeating here because it's genuinely easy to get backwards: the column has to answer "which of these represents the newer real-world state," not "which one ClickHouse saw more recently."

Summary

ReplacingMergeTree trades immediate correctness for ingestion simplicity: duplicate rows sharing a sorting key get collapsed down to one, keeping whichever has the greatest value in the version column — but only during a background merge, on ClickHouse's own schedule, not on your query's. That makes the central judgment call whether a workload can tolerate an eventual-consistency window between "duplicate inserted" and "duplicate merged away." Upstream deduplication is the strongest option when it's achievable, because it removes the problem before ClickHouse ever sees it. Query-time deduplication (FINAL, or an explicit argMax/GROUP BY on a plain MergeTree) is the strongest option when a query needs a guarantee that holds at every instant, at the real cost of doing dedup work on every read rather than once, in the background. ReplacingMergeTree itself is the right choice when duplicates are a real but tolerable fact of a pipeline's life — the classic fit is a change-data-capture stream with at-least-once delivery and its own well-defined version ordering — and the classic mistake is treating its eventual dedup as an always-true guarantee, whether that shows up as confusion over duplicate rows right after insert or as a genuine correctness bug in a query that needed a same-instant answer.

Quiz

1. A table uses ReplacingMergeTree(updated_at). Immediately after inserting a duplicate row (same sorting key, newer updated_at), a plain SELECT * still shows both the old and new row. What's the most likely explanation?
2. Why does using FINAL on every query against a high-write-volume ReplacingMergeTree table risk becoming a real operational problem, even though it guarantees correct, deduplicated results?
3. A team sets a ReplacingMergeTree's version column to the DateTime the row was inserted into ClickHouse, rather than a timestamp from the source system. What problem does this risk?
4. A team is choosing between ReplacingMergeTree and upstream deduplication (preventing duplicates before they're ever inserted). Under what condition is upstream deduplication clearly the stronger choice, if it's achievable?
5. A dashboard query needs an absolute guarantee that it never shows a stale, superseded row, even momentarily, and the team wants to avoid depending on ClickHouse's merge timing at all. Which approach best satisfies this, independent of engine internals?