ClickHouse ReplacingMergeTree for Late-Arriving Event Deduplication

Problem: Late-Arriving Duplicates Without Row Updates

ClickHouse does not support efficient row-level UPDATE or DELETE for routine corrections. When corrected events arrive late and share the same sorting key as earlier rows, you need a way to keep only the newest version without manually rewriting partitions. The practical takeaway is to use ReplacingMergeTree with a version column: ClickHouse background merges drop older duplicates automatically, and the FINAL query modifier returns only the surviving row.

Prerequisites

• A ClickHouse instance running a recent stable release; exact merge cost and scheduling vary by version.
• A table where each logical event has a stable sorting key, such as event_id.
• A version column, for example event_time or update_seq, declared in the engine definition.
• A user with CREATE, INSERT, and SELECT privileges, accessing clickhouse-client or the HTTP interface.

How ReplacingMergeTree handles versions

ReplacingMergeTree is a MergeTree-family engine that keeps multiple rows with the same sorting key until a background merge runs. During that merge, ClickHouse compares the version column and retains only the row with the highest value. This behavior applies to both standalone and replicated tables; for replicated setups, use ReplicatedReplacingMergeTree with ClickHouse Keeper or ZooKeeper managing metadata.

Create the table and declare the version column

Run the DDL in clickhouse-client. Replace events_dedup with your table name.

CREATE TABLE events_dedup (
    event_id UInt64,
    event_time DateTime,
    payload String,
    update_seq UInt32
)
ENGINE = ReplacingMergeTree(update_seq)
ORDER BY event_id;

ReplacingMergeTree(update_seq) designates update_seq as the version column. Rows that share the same ORDER BY key compete during merges, and the row with the higher version is the candidate survivor.

Insert duplicate versions of the same event

Insert two rows with the same sorting key but different version values.

INSERT INTO events_dedup VALUES (1001, '2026-11-10 08:00:00', 'first payload', 1);
INSERT INTO events_dedup VALUES (1001, '2026-11-10 09:00:00', 'corrected payload', 2);

Check visibility before a merge

ReplacingMergeTree does not deduplicate at insert time. Query without FINAL:

SELECT * FROM events_dedup WHERE event_id = 1001;

Expected check: both rows may appear because they reside in separate parts and no merge has run yet.

Force a merge and verify the surviving row

Run an optimization to trigger a merge for testing. In production, background merges run on their own schedule.

OPTIMIZE TABLE events_dedup FINAL;

Then query with FINAL:

SELECT * FROM events_dedup FINAL WHERE event_id = 1001;

Expected check: only the row with update_seq = 2 and payload 'corrected payload' should return. Without FINAL, older rows can remain visible until parts are merged.

Inspect merge state with system tables

Confirm the engine and active part count before relying on background deduplication:

SELECT name, engine FROM system.tables WHERE database = currentDatabase() AND name = 'events_dedup';
SELECT name, active_parts FROM system.parts WHERE table = 'events_dedup' AND active;

Expected check: engine shows ReplacingMergeTree, and active_parts decreases after a successful merge. High part counts mean duplicates may stay visible longer.

Cost comparison: FINAL versus ordinary scans

Query form	Read behavior	Typical use
SELECT * FROM events_dedup	Reads active parts normally; duplicates may appear	Raw stream monitoring, ingestion checks
SELECT * FROM events_dedup FINAL	Reads relevant parts, applies version logic, returns latest row per sorting key	Current-state dashboards, reconciliation reports

FINAL forces ClickHouse to read all parts that contain the sorting key, merge them in memory, and return only the latest version. On a table with many small parts, this can increase memory usage and read latency substantially. For high-traffic dashboards, consider a materialized summary table or a projection instead of querying the base table with FINAL on every page load.

Limitations and recovery

• Duplicates are not removed immediately after INSERT; they persist until a background merge selects the surviving version.
• Equal version values tie-break by insertion order, so the engine does not guarantee exactly-once visibility by itself.
• FINAL increases read latency and memory usage on large merge trees.
• Native DELETE or UPDATE is not part of this model; use ClickHouse mutations only for exceptional cleanup.

Because older parts are physically removed during a merge, you cannot undo the merge itself. If an incorrect version survives due to a bad sequence number, insert a correcting row with the same event_id and a higher update_seq. The next background merge will prefer the higher version, but there is no immediate visibility fix without another merge or a FINAL query.

Practical way to confirm the result

1. Insert duplicate rows with different version values.
2. Run OPTIMIZE TABLE events_dedup FINAL in a test environment only.
3. Query with and without FINAL and compare row counts.
4. Check system.parts to confirm the active part count dropped after the merge.

If the latest version does not survive, verify that the version column is monotonic and matches the column declared in ENGINE = ReplacingMergeTree(column).