For a long time, the advice around FINAL in ClickHouse was pretty straightforward:

Avoid it whenever possible.

And honestly, that advice existed for good reasons.

Older versions of ClickHouse could make FINAL extremely expensive depending on:

table size

partitioning

number of parts

merge state

query patterns

So people started treating FINAL almost like a red flag.

But modern ClickHouse has changed a lot.

And I think the conversation around FINAL deserves a bit more nuance now.

Why FINAL Existed in the First Place

To understand why FINAL was historically considered expensive, you first need to understand what it actually does.

In engines like:

ReplacingMergeTree

CollapsingMergeTree

VersionedCollapsingMergeTree

ClickHouse does not immediately rewrite rows in place.

Instead:

inserts create new parts

background merges reconcile rows later

deduplication happens asynchronously

That means queries can temporarily see:

duplicate versions

old versions

intermediate states

Example:

SELECT *…