skip to content

Why can retrying a failed INSERT into a Replicated ClickHouse table leave its materialized view missing rows?

level: seniorimportance: should knowfreq 38%

answer

  1. retries are safe because of a skip rule
  2. the second attempt never reaches the view
  3. source and view targets are not one transaction
  4. identical aggregate blocks are legitimate
  5. a setting moves the dedup check downstream

basics

~20 s

Replicated MergeTree deduplicates identical inserted blocks. If the source write succeeded but the view's write failed, the retry's block is recognised as a duplicate and skipped entirely, so the view never runs again. The deduplicate_blocks_in_dependent_materialized_views setting makes the view's target deduplicate independently.

solid answer

~50 s

`Replicated*MergeTree` records a hash of each inserted block and silently drops a repeat of a recent one, which is what makes retries safe for a single table. With a materialized view attached, the failure is asymmetric. Suppose the source part is written, then the view's insert into its target throws and the client sees an error and retries. On the retry the source recognises the block as a duplicate and skips the whole insert — including the view — so the view's target is permanently short those rows while the source has them. The default exists deliberately: two different source blocks can aggregate to identical view output, and dropping those would lose real data. Setting `deduplicate_blocks_in_dependent_materialized_views = 1` gives each view target its own deduplication check, restoring end-to-end retry idempotency at the cost of dropping legitimately identical view blocks. `insert_deduplication_token` lets you set the dedup key explicitly instead of relying on content hashing.

code

sql · 9 lines
sql
-- explicit, pipeline-controlled batch identity
INSERT INTO events
SETTINGS insert_deduplication_token = 'kafka:events:7:100000-100999'
VALUES (...);

-- opt into an independent dedup check in view targets
INSERT INTO events
SETTINGS deduplicate_blocks_in_dependent_materialized_views = 1
VALUES (...);

go deeper

for a junior

Know that Replicated tables skip a repeated identical insert block, which is why retrying a batch normally does not duplicate data.

for a middle

Explain that the insert plus its views are not one transaction, so a failure between them plus a deduplicated retry leaves the view target permanently short.

for a senior

Weigh the two settings against the pipeline's invariant, prefer explicit deduplication tokens over content hashing, and run reconciliation because the gap is otherwise invisible.

for a principal

Own the delivery contract end to end: what exactly-once means across derived tables, where the reconciliation runs, and which derived datasets are allowed to fail open.

## The deduplication that makes retries safe `ReplicatedMergeTree` (and its family) hashes each inserted block and keeps the hashes of recently inserted blocks in ZooKeeper/Keeper per partition. An insert whose hash matches a recent one is skipped. This is what lets an ingest client retry after a timeout without duplicating data: send the same batch again, get the same result. Plain, non-replicated `MergeTree` does not do this by default; it can be enabled through the `non_replicated_deduplication_window` setting. ## Where materialized views break the guarantee An insert with attached views is not atomic across the tables involved. The sequence is: write the source part, then run each view and write its target part. There is no transaction spanning them. Now consider a failure between those steps — the view's target hits a memory limit, a type error on an edge-case row, or a replica hiccup: 1. The source part is written and its block hash is registered. 2. The view's insert fails; the client receives an exception. 3. The client does the correct thing and retries the identical batch. 4. The source sees a known block hash and skips the insert **entirely** — which means the attached views are not executed either. End state: the source table has the rows, the view's target does not, and no retry will ever fix it. The pipeline looks healthy; only a reconciliation query comparing the rollup against the raw table will reveal the gap. ## Why the default is what it is The behaviour is not an oversight. A materialized view usually aggregates, and aggregation is lossy: two genuinely different source blocks can produce byte-identical view output — think `(day, country, count=1000)` arriving twice from different raw batches. If the view target deduplicated by content, the second legitimate rollup row would be silently dropped and your totals would run low. Skipping dependent views when the source block is deduplicated keeps that from happening and preserves the more common invariant: one source block, one set of view rows. So ClickHouse offers the choice rather than picking for you: - **Default (`deduplicate_blocks_in_dependent_materialized_views = 0`)** — the source's deduplication decision governs everything downstream. Safe against dropping identical aggregate blocks; vulnerable to the partial-failure gap above. - **Enabled (`= 1`)** — each view target performs its own deduplication check on the block it receives. Retries become idempotent end to end, because the source skip no longer implies the view skip is correct; but two legitimately identical view blocks can now be dropped. ## Making it deterministic with a token Content hashing is fragile in either mode, because "identical" depends on exact bytes and block boundaries. `insert_deduplication_token` lets the client supply the deduplication key explicitly: ```sql INSERT INTO events SETTINGS insert_deduplication_token = 'batch-2026-08-21-000173' VALUES ... ``` Now the identity of a batch is something your pipeline controls — a Kafka partition plus offset range, a file name, a job run id — rather than a hash of whatever bytes happened to be batched together. This also fixes the opposite hazard: a re-batched retry with the same rows but different block boundaries hashes differently and would otherwise be inserted twice. ## Practical guidance - **Decide per pipeline, and write it down.** Which invariant matters more here: never losing an aggregate block, or never leaving a view short after a retry? The answer differs for a billing rollup and a debugging counter. - **Prefer explicit tokens** derived from the upstream source's own identity. It removes the dependence on block-boundary luck. - **Reconcile.** Whatever settings you choose, run a periodic comparison between the raw table and each rollup for a recent window. Gaps of this kind are invisible until measured. - **Keep view SELECTs total.** Most of these incidents start with an avoidable exception inside a view — an overflow, a `toUInt32` on an out-of-range value, a memory limit on a heavy `GROUP BY`. A view that cannot throw cannot produce a partial insert. - **Know the escape hatch and its price.** `materialized_views_ignore_errors` keeps ingest flowing when a view fails, at the cost of silently dropping that view's output — a reasonable trade for a non-critical derived table, wrong for a trusted one. - **Remember the window is bounded.** Deduplication only remembers recent blocks, so a retry that arrives much later may be inserted anyway. Idempotency by deduplication is a short-horizon guarantee, not a permanent one. ## Saying it in an interview *"Replicated tables dedupe by block hash. If the source write succeeds and a dependent view's write fails, the retry gets deduped at the source and the view never re-runs, so the rollup is permanently short. The default protects against dropping identical aggregate blocks; `deduplicate_blocks_in_dependent_materialized_views` moves the check into the view targets, and `insert_deduplication_token` makes batch identity explicit instead of content-derived."*

  • Why isn't deduplication in dependent view targets enabled by default?
    Because aggregation is lossy: two different source blocks can produce byte-identical view output, such as the same (day, country, count) tuple from separate raw batches. Deduplicating those would silently discard real data. The default therefore lets the source's decision govern the whole cascade, and you opt into per-target checks with `deduplicate_blocks_in_dependent_materialized_views = 1` when retry idempotency matters more.
  • How does insert_deduplication_token improve on content hashing?
    It makes batch identity explicit and stable. Content hashing depends on exact bytes and block boundaries, so a retry that re-batches the same rows differently hashes differently and gets inserted twice. Supplying a token derived from the upstream source — a file name, a Kafka partition and offset range, a job run id — gives deterministic identity regardless of how the rows are batched.
  • How would you detect that a rollup has quietly fallen behind its source because of this?
    Run a scheduled reconciliation over a recent window: aggregate the raw table for, say, the last few hours and compare against the same window read from the rollup with the appropriate `-Merge` functions, alerting on any difference beyond a tolerance. Also watch `system.query_views_log` for view exceptions, since almost every gap of this kind starts with one.

saying these in an interview costs you the question

  • Assuming an INSERT with views is atomic across all tables
  • Believing a retry always re-runs the materialized views
  • Turning on dependent-view dedup without considering identical aggregate blocks
  • Treating block-hash dedup as a permanent exactly-once guarantee
  • Relying on ignore-errors settings for a rollup a dashboard trusts

context

resolving links…