ClickHouse: deduplication and losses in MV

# Data Loss in ClickHouse: Deduplication and Materialized Views

In ClickHouse, when using materialized views, data in the target table may not match the source. This happens due to block deduplication during INSERT, especially in replicated clusters. The default configuration causes blocks to be ignored on repeated inserts, breaking the expectations of append-only storage. The solution is to adjust the insert_deduplicate and deduplicate_blocks_in_dependent_materialized_views settings.

Consider a typical pipeline:

CREATE TABLE source_table (
    message     String
) engine=MergeTree()
ORDER BY (message);

CREATE TABLE final_table (
    message     String
) engine=MergeTree()
ORDER BY (message);

CREATE MATERIALIZED VIEW mv_source_table TO final_table AS
SELECT 
    message
FROM source_table;

Data from source_table is copied to final_table, but over time, records disappear from final_table without any obvious reason.

INSERT Atomicity in Detail

ClickHouse splits INSERT data into blocks based on the max_insert_block_size setting. Atomicity is guaranteed only for a single block in one MergeTree table partition. If an INSERT is interrupted, ClickHouse attempts to retry inserting the missing blocks without notifying the user.

On retry, deduplication kicks in: each block gets a block_id as a hash of its contents. If that block_id is already in the deduplication log, the entire block is skipped. As a result:

• Part of the data from the original INSERT is lost.
• The materialized view doesn't receive the full data.

Conditions for atomicity:

• Insert into a single partition.
• No concurrent INSERTs into the same partition.
• Data packed into a single block.

Failure to meet these leads to partial inserts and deduplication on retry.

Block Deduplication Mechanism

Deduplication operates at the block level in replicated MergeTree tables. The insert_deduplicate setting (1 by default) enables block_id checks. If a duplicate is found, the block is ignored.

In non-replicated tables, deduplication is disabled (non_replicated_deduplication_window=0). For replication, it's recommended to set:

insert_deduplicate = 0

This prevents block loss on repeated INSERTs but allows duplicates. For analytical append-only workloads, duplicates are acceptable and get cleaned up during merges.

Deduplication in Materialized Views

Materialized views perform INSERTs into the target table based on source data. By default, deduplication is checked on the source table, and the status carries over to the view.

The deduplicate_blocks_in_dependent_materialized_views setting controls this:

• 0 (default): Deduplication on source_table, risk of data loss.
• 1: Deduplication on final_table, blocks inserted fully.

Set:

SET deduplicate_blocks_in_dependent_materialized_views = 1;

ClickHouse documentation explicitly warns that without adjustments, materialized views may miss data on repeated block inserts.

Recommended Settings

To eliminate data loss, apply these changes to server or session configuration:

• insert_deduplicate = 0 — disable deduplication on repeated INSERTs.
• deduplicate_blocks_in_dependent_materialized_views = 1 — deduplication in MV on the target table.
• Check max_insert_block_size: larger values minimize block splitting.
• Avoid concurrent INSERTs into the same partition.

Monitor logs for partial inserts and compare COUNT(*) between source and final tables.

Key Takeaways

• ClickHouse guarantees ACID only for a single block in one partition; everything else depends on settings.
• Block deduplication by block_id causes losses in interrupted INSERTs within MV pipelines.
• Disabling insert_deduplicate solves the issue for replicated MergeTree.
• Setting deduplicate_blocks_in_dependent_materialized_views=1 prevents losses in views.
• Duplicates after disabling deduplication are removed by standard merges.

— Editorial Team