How to Deduplicate Data in ClickHouse
Last updated July 25, 2026 · By the SaturnSQL team
Use ReplacingMergeTree: it keeps one row per sort key, collapsing duplicates during background merges. Merges are eventual, so add FINAL to a SELECT for query-time deduplication, or run OPTIMIZE TABLE ... FINAL DEDUPLICATE to force it.
CREATE TABLE users
(
user_id UInt64,
email String,
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;Deduplicated reads
SELECT * FROM users FINAL WHERE user_id = 101;Force deduplication on disk
OPTIMIZE ... FINAL rewrites all parts, so treat it as a maintenance operation, not something to run per query.
OPTIMIZE TABLE users FINAL DEDUPLICATE;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free