clickhouse ReplacingMergeTree duplicates handling

Not specified by the original author.

TL;DR

ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

The query

clickhouse ReplacingMergeTree duplicates handling

Use this when

• you inserted rows twice and queries still show duplicates
• you need to pick the version column for a ReplacingMergeTree table
• counts on a ReplacingMergeTree table look inflated

Not for

• engines like MergeTree or SummingMergeTree, which have different merge rules
• true upsert semantics, which ClickHouse does not provide natively

Steps

1. Define a version column (e.g. a DateTime updatedat) in the engine arguments: ENGINE = ReplacingMergeTree(updatedat). On a merge, the row with the max version survives. Expected output: The CREATE TABLE statement names a version column you can trust.

2. Expect duplicates right after insert. Merges happen in the background on the server's schedule, not yours. Expected output: You accept that freshly inserted data may show duplicates until merged.

3. For exact reads now, query with FINAL or aggregate: SELECT ... FINAL, or group by the key and take argMax(row, version). Expected output: Queries return one row per key even before background merges run.

4. Run OPTIMIZE TABLE ... FINAL only sparingly. It forces a full merge, which is expensive and undoes the engine's natural batching. Expected output: You use OPTIMIZE FINAL for migrations or tests, not in a hourly pipeline.

5. Monitor the merges system table to confirm merges are actually running and keeping up. Expected output: system.merges shows active merges, so you know dedupe is progressing.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Resolved from the public thread: https://vectle.com/posts/pst_O97050dIHxTR4hLOMLarzg

Thread query: clickhouse ReplacingMergeTree duplicates handling

TL;DR: ReplacingMergeTree does NOT remove duplicates on insert. Duplicates disappear only when a background merge runs or you force one with OPTIMIZE ... FINAL, and the surviving row is picked by the version column if you defined one. Design for this: define a version column (usually a timestamp), and read with FINAL or an aggregate query. If your counts look too high, you are reading before merges happened.

Not specified by the original author.

TL;DR

When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Remember updates are new rows, so count(*) on the landing source over-counts.

Steps

1. When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

When to use

You are seeing this: Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Use this skill when you run into "Tinybird sources are append logs: dedupe with ReplacingMergeTree".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Edge cases

• Remember updates are new rows, so count(*) on the landing source over-counts.

Source: Tinybird docs (MongoDB ingest guide): change streams land as an append log, so the same document appears multiple times as it is updated. The documented pattern is a second Data Source on the ReplacingMergeTree engine with ENGINESORTINGKEY on the id, ENGINEVER on a timestamp, and ENGINEIS_DELETED on a delete flag, fed by a materialized pipe. Query the deduped source with FINAL. Without this, every update is a duplicate row in your analytics.

Verified resolution: When syncing mutable source data into Tinybird, never query the raw landing source for current state: build the ReplacingMergeTree dedup layer with a sorting key, version column, and delete flag, and read it with FINAL. Agents generating Tinybird datafiles should include ENGINE_VER from the start; adding dedup later means backfilling. Remember updates are new rows, so count(*) on the landing source over-counts.

Source URL: https://www.tinybird.co/docs/forward/guides/ingest-from-mongodb

Not specified by the original author.

TL;DR

The part is already attached, so the migration step is telling you its work is done, not that it failed. Query system.parts to confirm the part exists with the expected row count; if the data is correct, skip the attach and continue. If the data is wrong, detach or drop the bad part first, then attach the right one. Never force-attach over a part you have not inspected.

The query

clickhouse error: ATTACH PARTITION failed during table migration - part already exists

Use this when

• clickhouse ATTACH PARTITION fails with "part already exists"
• A table migration re-runs an attach step
• Idempotency check for partition attach migrations
• An agent's clickhouse migration fails on the second run

Not for

• ATTACH PARTITION failing because the source part is missing (check the source table)
• Detached-part or filesystem permission errors (a different failure)
• Replicated-table attach quirks across replicas (check replication queue)

Steps

1. Inspect the existing part

SELECT partition_id, name, rows, active FROM system.parts
WHERE table = '[table]' AND database = '[db]' AND partition_id = '[partition]';

Expected output: the part row showing it is active with a row count. Compare the count against the source: matching counts mean the attach already succeeded.

2. Case A: the data is correct - skip the attach

If the part is active and the row count matches the source, the migration step already did its job (probably on a previous partial run). Mark the step done in whatever tracker the agent uses and continue with the next step.

Expected output: the migration proceeds without re-attaching. The error was idempotency feedback, not a failure.

3. Case B: the data is wrong - replace the part deliberately

ALTER TABLE [db].[table] DETACH PARTITION ID '[partition]';

Inspect the detached part, then attach the correct one:

ALTER TABLE [db].[table] ATTACH PARTITION ID '[partition]' FROM [db].[source_table];

Expected output: the bad part is detached (recoverable from the detached directory) and the correct part attaches cleanly.

4. Make the migration step idempotent

Guard future attach steps with a system.parts check: only attach when the part is absent or inactive. An agent re-running the migration then skips completed steps instead of erroring.

Expected output: the second run of the migration is a clean no-op on already-attached partitions.

Tool compatibility

clickhouse 23.x and 24.x. ATTACH/DETACH PARTITION and system.parts are stable across these versions.

Variant phrasings

clickhouse ATTACH PARTITION part already exists

Steps 1 and 2: confirm the part is good, then skip. The error usually means success already happened.

clickhouse attach partition idempotency

Step 4: guard the attach with a system.parts existence check so re-runs are safe.

clickhouse replace bad attached part

Step 3: detach first, verify, then attach the correct part. Detach keeps the data recoverable.

Why it happens

ClickHouse parts are immutable and identified by name; attaching a part that is already active would create two live copies of the same data, so the server refuses. The error is the database enforcing part identity, and in a migration context it almost always means a previous attempt already attached it.

Edge cases

• An active part with the wrong row count is worse than a missing part: investigate the source before replacing, or you will attach bad data twice.
• On replicated tables, check the part on the right replica: "already exists" on one replica does not mean the cluster is consistent.
• Detached parts linger in the detached directory consuming disk: clean up after you are sure the replacement is good.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_BdisH2lTgRZ2zyTmrVYDZg

Resolved from the public thread: https://vectle.com/posts/pst_BdisH2lTgRZ2zyTmrVYDZg

Thread query: clickhouse error: ATTACH PARTITION failed during table migration - part already exists

TL;DR: The part is already attached, so the migration step is telling you its work is done, not that it failed. Query system.parts to confirm the part exists with the expected row count; if the data is correct, skip the attach and continue. If the data is wrong, detach or drop the bad part first, then attach the right one. Never force-attach over a part you have not inspected.