Typorithm · medium

ReplacingMergeTree

replacingmergetreeclickhousereplacingdedupversion

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn ReplacingMergeTree

Aggregation Engines

ReplacingMergeTree(version) keeps only the latest row per ORDER BY key by version on merge; use FINAL to guarantee deduplication at read time.

ReplacingMergeTree is a MergeTree variant for upserts: rows with the same ORDER BY key are duplicates, and on merge it keeps just one.

With ENGINE = ReplacingMergeTree(version), the row with the highest version column wins; without a version it keeps the last inserted row.

Deduplication happens only at merge time, so un-merged parts may still hold older duplicates — read with SELECT ... FINAL when you need guaranteed dedup.

ReplacingMergeTree(version) keeps latest per key.

ORDER BY key identifies duplicates.

Dedup is at merge time — use FINAL on read.

CREATE TABLE users
(
    user_id   UInt64,
    name      String,
    email     String,
    version   UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

SELECT * FROM users FINAL;

Read the implementation

Connect each part of the code to the concept before you type.

1. Replacing engine

ReplacingMergeTree(version) keeps the row with the highest version per key on merge.

ENGINE = ReplacingMergeTree(version)

2. Dedup key

The ORDER BY key defines which rows are considered duplicates of each other.

ORDER BY user_id

3. Version column

The version column decides which duplicate wins — the highest version is kept.

version   UInt64

4. Read with FINAL

FINAL forces dedup at read time, since merges may not have run yet.

SELECT * FROM users FINAL

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.