Analytics PipelineClickHouse & Materialized Views
Guide Real-Time Data Ingestion Pipeline Implementation

Deduplicating Inserts with Insert Deduplication

ClickHouse ships a block-level insert deduplication mechanism that makes a retried INSERT a no-op instead of a double-write: every inserted block carries a hash (or an explicit token), and the server drops any block whose identifier it has already committed. This guide shows how automatic block dedup works on replicated tables through Keeper, how insert_deduplication_token extends the same guarantee to precise application-defined boundaries, how to size the dedup window on non-replicated tables, and how to prove idempotency by reading system.parts and system.query_log — the safety net that makes the retry loops in batch insert optimization correct rather than merely fast.

Prerequisites

What “block-level” deduplication actually means

Deduplication in ClickHouse is not row-level and not based on primary key. When you insert, the server splits your payload into blocks and computes a hash of each block’s contents (or uses the token you supply). On a ReplicatedMergeTree, that identifier is recorded in Keeper alongside the committed part; on a plain MergeTree, it is kept in an in-memory ring buffer sized by non_replicated_deduplication_window. If the same identifier arrives again — because a client timed out mid-write and retried — the block is recognized and silently skipped. The critical consequence: two attempts must produce byte-identical blocks (or share a token) for dedup to fire. Reorder the rows, resize the batch, or let a fresh token slip in, and the safety net has a hole.

How a retried insert block is deduplicated against Keeper A writer sends a block, either hashed automatically or tagged with an explicit insert_deduplication_token, to ClickHouse. The server checks the block identifier against the set of committed identifiers held in Keeper for a replicated table, or in the in-memory non_replicated_deduplication_window for a plain MergeTree. On the first attempt the identifier is new, so the block is committed as a part and its identifier is recorded. When the same writer retries after a timeout, the identifier already exists, so the duplicate block is skipped and no second part is written, making the retry idempotent. Attempt 1 block token=A Retry (timeout) block token=A token seen? check registry committed identifiers Keeper (replicated) · in-memory window (plain) new → commit part new part written identifier recorded duplicate → skipped no second part retry is idempotent

Step 1 — Create a replicated table so dedup is automatic

On a ReplicatedMergeTree, insert deduplication is on by default (replicated_deduplication_window = 100 parts). No token is required for the common case: identical blocks are caught automatically because their content hash is stored in Keeper.

sql
CREATE TABLE analytics.events_repl
(
    `event_id`   UUID,
    `event_ts`   DateTime64(3),
    `user_id`    UInt64,
    `event_type` LowCardinality(String),
    `payload`    String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events_repl', '{replica}')
PARTITION BY toYYYYMMDD(event_ts)
ORDER BY (event_type, user_id, event_ts)
SETTINGS
    replicated_deduplication_window = 100,          -- last 100 block hashes tracked in Keeper
    replicated_deduplication_window_seconds = 604800; -- 7-day retention of those hashes

The window is a sliding count of recent blocks, not an all-time set. If more than 100 distinct blocks (or 7 days) pass between the original insert and a retry, the hash has aged out and the duplicate will be admitted — size the window to comfortably exceed your maximum retry latency.

Step 2 — Attach an explicit insert_deduplication_token per logical batch

Content hashing breaks the moment two attempts differ by a byte — a re-serialized JSON payload, a reordered batch, a regenerated UUID. Bind an explicit insert_deduplication_token derived from the source identity (a Kafka topic-partition-offset range, a file name plus byte range, a batch sequence number) so the identifier is stable regardless of block contents.

python
import clickhouse_connect

client = clickhouse_connect.get_client(host="clickhouse", database="analytics")

def insert_batch(rows, batch_key: str, attempt: int = 0):
    # The token is derived from the SOURCE, so every retry of this logical
    # batch carries the same value even if the bytes differ slightly.
    token = f"events-{batch_key}"          # e.g. "events-topicA-3-100..200"
    try:
        client.insert(
            "events_repl",
            rows,
            column_names=["event_id", "event_ts", "user_id", "event_type", "payload"],
            settings={"insert_deduplication_token": token},
        )
    except clickhouse_connect.driver.exceptions.OperationalError:
        if attempt < 3:
            return insert_batch(rows, batch_key, attempt + 1)  # same token → safe
        raise

Because the retry reuses token verbatim, the second insert is recognized and skipped even though the network failure left the client unsure whether the first one committed. This is the crux of making an at-least-once delivery source effectively-once at the ClickHouse boundary.

Step 3 — Size the window on non-replicated tables

Plain MergeTree tables also deduplicate when a token is supplied, but the identifiers live in an in-memory ring buffer rather than Keeper. Its size is non_replicated_deduplication_window, which defaults to 0 (disabled) — you must enable it.

sql
ALTER TABLE analytics.events_local
MODIFY SETTING non_replicated_deduplication_window = 1000;

With the window set, an insert_deduplication_token on a non-replicated table is honored for the last 1000 blocks. Because the buffer is in memory, it does not survive a server restart — non-replicated dedup is best-effort across process lifetime, whereas replicated dedup is durable in Keeper. For anything that must survive a restart, use a replicated table.

Step 4 — Keep batch boundaries stable across retries

Dedup is per block, so a retry must reconstruct the same blocks. If attempt 1 sent 500k rows as one block and the retry re-chunks into two 250k blocks, neither new block matches the stored identifier and both are admitted as duplicates. Freeze the batching before the first send and replay the identical partitioning on retry.

python
def stable_chunks(rows, chunk_rows=500_000):
    # Deterministic slicing: the same input always yields the same chunks,
    # so a retry regenerates byte-identical blocks (and identical tokens).
    for i, start in enumerate(range(0, len(rows), chunk_rows)):
        yield i, rows[start:start + chunk_rows]

for idx, chunk in stable_chunks(all_rows):
    insert_batch(chunk, batch_key=f"{source_file}-{idx}")

Pairing a deterministic chunker with a per-chunk token gives every block a stable identity, which is what the deduplication window is comparing against.

Verification

First, prove that a deliberate duplicate insert did not create a second part. Insert a batch, insert it again with the same token, then count parts:

sql
SELECT partition, count() AS active_parts, sum(rows) AS rows
FROM system.parts
WHERE database = 'analytics' AND table = 'events_repl' AND active
GROUP BY partition
ORDER BY partition;

The row count must be unchanged after the duplicate attempt. Next, confirm the server actually skipped the block rather than writing zero rows by accident — a deduplicated insert appears in system.query_log with written_rows = 0 even though the query succeeded (flush logs first with SYSTEM FLUSH LOGS):

sql
SELECT event_time, written_rows, result_rows, query_duration_ms,
       Settings['insert_deduplication_token'] AS token
FROM system.query_log
WHERE type = 'QueryFinish' AND query_kind = 'Insert'
  AND has(tables, 'analytics.events_repl')
  AND event_time > now() - INTERVAL 15 MINUTE
ORDER BY event_time DESC
LIMIT 10;

The original insert shows written_rows equal to the batch size; the deduplicated retry shows written_rows = 0 with the same token. That pair — full write then zero-write with a matching token — is the signature of dedup doing its job.

Gotchas and edge cases

  • A fresh token per attempt defeats the whole mechanism. Generating a UUID token inside the retry loop means every attempt looks new and every retry double-writes. Derive the token from the immutable source coordinates, compute it once, and reuse it for all attempts of that batch.
  • Dedup is per block, not per row. If one row inside a block changed but the rest repeat, the block hash differs and the entire block — including the repeated rows — is admitted. Deduplication cannot merge partially overlapping batches; that is a job for a ReplacingMergeTree keyed on a version column.
  • The window ages out under high insert rates. replicated_deduplication_window = 100 means only the last 100 blocks are remembered; a firehose can push a batch’s hash out of the window in seconds, so a slow retry sneaks a duplicate through. Widen the window (count and seconds) to exceed your worst-case retry delay.
  • Materialized views deduplicate independently. With deduplicate_blocks_in_dependent_materialized_views at its default, a view fed by a deduplicated insert may still process or skip differently than its source. If a downstream aggregate double-counts on retry, enable that setting so the dedup decision propagates to attached views built with the materialized view creation patterns.

Up: Batch Insert Optimization