In ClickHouse, what does adding FINAL to a SELECT do, and what does it cost?

## Why FINAL exists The MergeTree family stores data as immutable parts and rewrites them in the background. Several engines attach a *transformation* to that rewrite: `ReplacingMergeTree` keeps one row per sorting key (the highest version, or the last inserted when no version column is declared), `CollapsingMergeTree` cancels matched pairs by sign, `SummingMergeTree` adds numeric columns, `AggregatingMergeTree` merges aggregate states. Critically, that transformation happens **only when a merge runs**, and merges are best-effort background work with no completion guarantee. So a plain `SELECT` from a `ReplacingMergeTree` can return several rows for the same key — the old version and the new one, sitting in different parts. The engine is not broken; deduplication is eventual by design. `FINAL` closes the gap for one query: it applies the engine's merge logic at read time, so the result matches what a fully merged table would return. ```sql SELECT count() FROM orders; -- may count superseded versions SELECT count() FROM orders FINAL; -- engine-correct view ``` ## What it costs Without `FINAL`, ClickHouse can read parts independently and in parallel, streaming granules as they come. With `FINAL`, rows for the same sorting key may live in several overlapping parts, so those parts must be read together and merge-sorted on the sorting key before the transformation can be applied. That means more CPU, more memory, and less freedom to short-circuit. The penalty scales with how many overlapping parts a partition has, so a table with heavy small-batch inserts suffers most — the same table that most needs deduplication. Modern versions parallelize `FINAL` processing across parts and threads, so it is far from the disaster it once was, but it remains strictly more work than the same query without it. One structural optimization matters: `FINAL` is applied **per partition**, and the setting `do_not_merge_across_partitions_select_final` lets the engine skip merging across partitions when the partition key is a prefix of the sorting key, which can cut the work substantially on time-partitioned tables. ## What FINAL does not fix - **Cross-partition duplicates.** Merges never span partitions, so two rows with the same key inserted into different partitions both survive `FINAL`. If your partition key is the month and a row's key can appear in two months, deduplication will not happen. This is the most common "FINAL didn't work" report. - **Keys that differ.** Deduplication is on the full `ORDER BY` tuple. If a column in that tuple changed between versions, they are different keys and both remain. - **Insert-time deduplication.** Retried identical inserted blocks are deduplicated by a separate mechanism entirely; `FINAL` has nothing to do with it. ## The alternatives Because `FINAL` is a per-query tax, production queries often rewrite it away: ```sql -- latest version per id, expressed as an aggregation SELECT id, argMax(status, updated_at) AS status, max(updated_at) AS updated_at FROM orders GROUP BY id; -- or: one row per id, chosen by an ordering SELECT * FROM orders ORDER BY updated_at DESC LIMIT 1 BY id; ``` `argMax` is often the cheapest when you need a handful of columns, because it is an ordinary aggregation with no merge-sort of parts. `LIMIT 1 BY` is ClickHouse's top-N-per-group clause and keeps all columns. For `AggregatingMergeTree`, the natural form is a `GROUP BY` with the `-Merge` combinator rather than `FINAL`. A further pattern is to not deduplicate at read time at all: write an append-only log, and let a downstream rollup or a scheduled rewrite produce the deduplicated table that dashboards actually query. `OPTIMIZE TABLE t FINAL` is a different thing — an operational command that forces a full merge now, rewriting each partition into a single part. It is expensive (it rewrites all the data), and new inserts immediately create new parts again, so it is a maintenance tool, not a substitute for query-time correctness. ## Making the choice Use `FINAL` when correctness must be exact, the table is not enormous, and the query is not on a hot dashboard path. Rewrite to `argMax`/`LIMIT 1 BY` when the query is frequent and touches many rows. Consider whether the duplicates matter at all: for counters and monitoring, reading a slightly stale un-deduplicated view is sometimes acceptable and is by far the cheapest option. ## What interviewers probe Expect "our ReplacingMergeTree returns duplicates — why, and what do you do?" A strong answer explains that merges are eventual, that `FINAL` applies the logic at read time and what that costs, that deduplication is per partition on the sorting key, and offers at least one rewrite that avoids `FINAL` on the hot path.