CDC updates show duplicate orders: ReplacingMergeTree semantics in practice
An orders table receives CDC from PostgreSQL via Debezium into Kafka and then into ClickHouse. It holds 250 million rows, with ~40,000 updates per hour against ~5,000 inserts. The loader writes every change as a new row into ReplacingMergeTree(updated_at) with ORDER BY order_id. Analysts report that roughly 4% of orders appear twice with different statuses, and a handful of cancelled orders still show as active. Deletes in PostgreSQL arrive as tombstone events, which the loader currently drops. Dashboards are driven by a BI tool that you cannot modify to add FINAL, and p95 latency must stay under 3 seconds on a 3-node cluster. The cluster runs ClickHouse 24.3. OPTIMIZE TABLE orders FINAL takes over 20 minutes on the full table.
1. Why do duplicates appear, and what combination of schema and query changes would you make so the BI tool sees one row per order without changing the BI tool?
Read the worked answer
The duplicates are documented behaviour, not a loader bug. ReplacingMergeTree(updated_at) collapses rows that share a sorting key only during a background merge, and merges are asynchronous and confined to a single partition. Between merges both versions are visible; only FINAL guarantees a single row at read time. At 40,000 updates/hour on a 250M-row table there is essentially always more than one version in flight.
Structural fix: an immutable partition key. Partition by a function of a column that never changes for a given order, e.g. PARTITION BY cityHash64(order_id) % 32 or toYYYYMM(created_at). Every version of an order then lands in the same partition. That lets FINAL merge within partitions only, and do_not_merge_across_partitions_select_final = 1 skips the cross-partition pass entirely - usually the bulk of FINAL's cost.
Hide FINAL behind a view. CREATE VIEW orders_latest AS SELECT * FROM orders FINAL, and point the BI tool at the view. Nothing in the tool changes, and EXPLAIN confirms the plan is a per-partition merge rather than a global sort.
Two traps. The version column must be strictly increasing per key: updated_at collides when two updates land in the same millisecond and the winner becomes arbitrary, so use the Debezium LSN. And OPTIMIZE ... FINAL is not a strategy at 40k updates/hour; it competes with merges for I/O and will not keep up.
2. Deletes currently vanish entirely. Compare the viable ways to represent them in this table and recommend one, including how deletions interact with `FINAL` and TTL.
Read the worked answer
Tombstones via the two-argument engine. Create the table as
ReplacingMergeTree(version, is_deleted)and have the loader emit a row withis_deleted = 1and the delete's LSN as the version. At merge, and atFINALread time, the tombstone wins and the order disappears. The big advantage is that the pipeline stays append-only: a delete cannot be undone by an update row that arrives later but carries a lower LSN, because version ordering decides the winner.Lightweight
DELETE FROM orders WHERE order_id = .... ClickHouse records the deletion in a_row_existsmask and queries filter it out with noFINALneeded. Workable for a few deletes per second, but each statement adds mask parts, it is asynchronous, and it does not fit the existingFINAL-view model as cleanly.A plain
deleted Boolcolumn that you filter in every query - the most fragile option, because one query that forgets the filter resurrects cancelled orders, which is exactly the symptom already reported.
Recommendation: option 1. You are already standardising on the FINAL view, so the tombstone is filtered consistently for every reader. Watch TTL: if TTL event_time + INTERVAL 24 MONTH is active, the tombstone must carry the latest event time so it survives at least as long as the rows it masks - otherwise an old row can reappear after the tombstone expires. Validate by reconciling SELECT count() FROM orders FINAL against the source system's live row count, and confirm that SELECT count() FROM orders FINAL WHERE is_deleted = 1 returns zero.
3. The loader retries after failures and sometimes produces duplicate inserts. What does `insert_deduplicate` actually guarantee, and how would you get reliable idempotency?
Read the worked answer
insert_deduplicate = 1 (on by default for Replicated* tables) suppresses identical blocks: same byte content, same partition, and only within a bounded window of recent blocks per partition. It is a safety net for a client that re-sends exactly the same batch, not a general exactly-once mechanism. If the retry re-orders rows, changes the block size, or lands after the window has moved on, the duplicate gets through.
Deterministic tokens. insert_deduplication_token lets you name the insert: set it to something derived from the source, such as topic + '-' + partition + '-' + first_offset. The server keeps a fingerprint per token and drops a repeat with the same token. The catch is that the token must be stable for the same data - if a retry regroups 5,000 rows into 3,000 + 2,000, the token changes and both halves are accepted. So batching must be deterministic: flush by offset range rather than by wall-clock timer.
If determinism is not achievable, stop chasing insert-time exactly-once. Keep at-least-once into a ReplacingMergeTree keyed by order_id with the Kafka offset as the version, and let read-time FINAL collapse the duplicates. That is idempotent regardless of how the retry regroups rows; the price is query-time merging, which the FINAL view already pays.
Verify with SELECT order_id, count() FROM orders GROUP BY order_id HAVING count() > 1 on a sample partition, and check system.part_log for the insert_deduplicate outcome on retried batches.