Someone on the team creates a materialized view to roll up an events table into hourly counts. It looks right in testing. Then a week later a dashboard is quietly missing three days of data, or a retried ingestion job has doubled every row in the rollup table, and nobody can explain why the source table is fine but the aggregate is wrong. Nine times out of ten, the root cause is the same: whoever built the view assumed it behaves like a materialized view in Postgres or Oracle. In ClickHouse it doesn't, and the gap between the two mental models is exactly where these bugs live.
What a materialized view actually is in ClickHouse
In Postgres, a materialized view is a stored snapshot of a query. You run REFRESH MATERIALIZED VIEW, it re-executes the whole SELECT against the current state of the table, and the snapshot updates. It knows about every row in the source, old and new, because it re-derives itself from scratch each time.
A ClickHouse materialized view is a different mechanism wearing the same name. It's an insert trigger: attach it to a source table, and every time a block of rows is inserted into that source, the view's SELECT runs over just that block and writes the result into a target table. It never re-scans the source. It has no concept of "current state." It reacts to inserts, once, as they happen.
CREATE TABLE analytics.events
(
event_time DateTime,
event_type LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (event_type, event_time);
CREATE TABLE analytics.events_per_hour
(
hour DateTime,
event_type LowCardinality(String),
cnt UInt64
)
ENGINE = SummingMergeTree
ORDER BY (hour, event_type);
CREATE MATERIALIZED VIEW analytics.events_per_hour_mv
TO analytics.events_per_hour
AS
SELECT
toStartOfHour(event_time) AS hour,
event_type,
count() AS cnt
FROM analytics.events
GROUP BY hour, event_type;
That TO clause is doing the real work. analytics.events_per_hour_mv is a standing subscription on analytics.events, not a view you query. Every INSERT into events fires the SELECT above against the newly inserted rows only, and the result lands in events_per_hour. The view itself stores nothing. If you drop it, the target table and its data are untouched, because it was never anything more than the wiring between the two. The full mechanics, including the older inline-engine form and the newer REFRESH EVERY variant that actually does behave like a scheduled snapshot, are covered in the materialized view guide; read it before you design one.
The gap: existing data and later changes
Here's the part that catches people. If analytics.events already has a year of history when you run that CREATE MATERIALIZED VIEW statement, none of it shows up in events_per_hour. The view starts listening the moment it's created. Everything inserted before that moment simply isn't its problem.
-- events already has 12 months of rows
CREATE MATERIALIZED VIEW analytics.events_per_hour_mv
TO analytics.events_per_hour
AS SELECT toStartOfHour(event_time) AS hour, event_type, count() AS cnt
FROM analytics.events GROUP BY hour, event_type;
SELECT count() FROM analytics.events_per_hour;
-- 0 rows. The MV has fired zero times.
INSERT INTO analytics.events VALUES (now(), 'signup');
SELECT count() FROM analytics.events_per_hour;
-- 1 row. Only the new insert triggered it.
There's a POPULATE keyword that runs the SELECT once against existing data at creation time, and it looks like the fix. Historically it wasn't much of one: it couldn't be combined with the TO clause used above at all, and rows inserted into the source while POPULATE was running were silently lost from the result. ClickHouse 26.8 finally reworked this: POPULATE now works with TO, and the population is atomic on the server you run it on, so locally concurrent inserts are no longer missed or duplicated. The caveats didn't all go away — the guarantee doesn't cover inserts arriving on another replica of a ReplicatedMergeTree source or through a distributed write path, and POPULATE is still unsupported on ClickHouse Cloud and in Replicated databases. On anything older than 26.8, or in any of those setups, the pattern that actually holds up in production is still to create the target table, create the MV so it starts capturing new inserts immediately, and then backfill the target separately with an explicit INSERT INTO ... SELECT ... WHERE event_time < <view_creation_time>. That gives you a backfill you can throttle, checkpoint, and rerun on failure instead of an all-or-nothing operation.
The same "only reacts to inserts" rule applies to changes on the source after the view exists. UPDATEs and DELETEs on analytics.events do not propagate. If a row gets corrected or removed upstream, the rollup in events_per_hour keeps reflecting the value that was true at insert time, forever, until something else fixes it. Teams that discover this the hard way are usually debugging a dashboard that "can't be wrong" because the query is trivial. The query is fine; the wrong part is the assumption that the target table tracks the source's current state.
Retries turn the same trigger into a duplication bug
The insert-trigger model also explains a failure mode that looks nothing like the one above but has the same root cause: retried inserts producing duplicate rows in the target table.
A plain insert into a ReplicatedMergeTree table is normally safe to retry. ClickHouse hashes each inserted block into a block_id and keeps a log of recent ones; a retry that produces an identical block gets recognized and silently skipped, so at-least-once ingestion behaves like exactly-once. The problem is that this dedup log belongs to the table being inserted into — and an MV's target table is a separate table with its own, independent dedup log. By default, the setting that would connect the two, deduplicate_blocks_in_dependent_materialized_views, is off.
That produces a specific, replayable sequence: an insert lands in the source table and succeeds. The MV's own write into the target table fails partway — a timeout, a too many parts error, a node restart, anything. The client, doing exactly what retry logic should do, resends the same insert. The source table recognizes the block and deduplicates it, so nothing new is written there — correctly. But because the source skipped the insert, the MV never fires for it a second time, and the target table's earlier partial failure is never corrected. The source ends up complete. The MV target stays permanently short. Flip the setting the other way and you get the mirror-image bug: an aggregating MV that emits count() AS c can produce a byte-identical output block from two genuinely different source inserts, and with naive MV-side dedup on, the second, legitimate block gets dropped.
INSERT INTO analytics.events
SETTINGS
deduplicate_blocks_in_dependent_materialized_views = 1,
insert_deduplication_token = 'ingest-batch-2026-10-27-0042'
VALUES (now(), 'signup');
The fix is to enable deduplicate_blocks_in_dependent_materialized_views so the MV target's dedup key is derived from the source insert's identity rather than the MV's own output, and to pin an explicit insert_deduplication_token per logical batch (a Kafka offset range, a file name, a UUID generated once and reused on every retry of that batch). Get the token wrong — a fresh one per attempt, or a non-deterministic INSERT ... SELECT that produces different bytes on retry — and you're back to unprotected duplicates. This is genuinely fiddly to get right, and the idempotent inserts guide walks through the settings, the chained-MV case, and the failure signatures in more detail than fits here.
Where ReplacingMergeTree comes in, and its own catch
A common instinct once you've been burned by MV duplication once is to make the target table forgiving of duplicates instead of trying to prevent them outright — point the MV at a ReplacingMergeTree and let duplicate rows collapse on their own. That works, with a catch that's easy to miss: ReplacingMergeTree does not deduplicate on insert. It deduplicates only when a background merge happens to run, and merges are asynchronous, scheduled by ClickHouse's own heuristics, with no guarantee about when — or whether — they happen for any given table.
CREATE TABLE analytics.events_dedup
(
event_id UInt64,
payload String,
updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY event_id;
INSERT INTO analytics.events_dedup VALUES (1, 'v1', now());
INSERT INTO analytics.events_dedup VALUES (1, 'v2', now());
SELECT * FROM analytics.events_dedup;
-- returns TWO rows for event_id = 1. No merge has run.
SELECT * FROM analytics.events_dedup FINAL;
-- returns ONE row — the 'v2' version, deduplicated at query time.
So a ReplacingMergeTree target doesn't make the duplication problem disappear, it moves it: a naive SELECT still sees every retry's duplicate rows until a merge happens to collapse them, and small or low-traffic tables can sit with visible duplicates for a long time because they never accumulate enough parts to trigger one. FINAL forces the reconciliation at read time and gives a correct answer regardless of merge state, at the cost of doing that reconciliation on every query — cheap when you filter on the ORDER BY key, expensive on a wide, unfiltered scan. For high-volume reads, GROUP BY with argMax() is often faster than FINAL and gives you explicit control over which columns' latest values you're keeping. Either way, the mental model has to be: ReplacingMergeTree tolerates duplicates and cleans them up eventually, it doesn't prevent them from existing in the first place. Combine it with the token-based dedup from the previous section — RMT as the safety net, deduplication tokens as the primary defense — and you have something that survives retries in practice.
The common thread
All three of these — the backfill gap, the retry duplication, and the RMT visibility lag — trace back to the same fact: a ClickHouse materialized view fires once, on insert, against exactly the rows in that insert, and nothing about it looks backward or forward in time. Once that's the mental model instead of "a live query result," the gotchas stop being surprising and become things you design for up front: backfill explicitly, turn on dedup propagation, and choose FINAL or argMax instead of assuming any of it happens for you.
This is also exactly the kind of drift that's hard to catch by eyeballing a schema. An MV whose target has quietly diverged from its source, or a ReplacingMergeTree table accumulating unmerged duplicate parts, doesn't throw an error. It returns wrong numbers, confidently, until someone reconciles counts by hand. NeverBlink watches MV target counts against source volume, dedup-window pressure, and merge activity on ReplacingMergeTree tables, and surfaces the specific view or table that's drifted, with the setting or backfill that fixes it — a recommendation you review, so the gap is flagged before it turns into a postmortem.
