Deduplicating Inserts on Retries

### Different materialized view inserts into one underlying table with equivalent data[​](#different-materialized-view-inserts-into-one-underlying-table-with-equivalent-data "Direct link to Different materialized view inserts into one underlying table with equivalent data")

```
CREATE TABLE dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE TABLE mv_dst
(
    `key` Int64,
    `value` String
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS non_replicated_deduplication_window=1000;

CREATE MATERIALIZED VIEW mv_first
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

CREATE MATERIALIZED VIEW mv_second
TO mv_dst
AS SELECT
    0 AS key,
    value AS value
FROM dst;

SET deduplicate_blocks_in_dependent_materialized_views=1;

select 'first attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER by all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘
```

Two equal blocks inserted to the table `mv_dst` (as expected).

```
SELECT 'second attempt';

INSERT INTO dst VALUES (1, 'A');

SELECT
    'from dst',
    *,
    _part
FROM dst
ORDER BY all;

┌─'from dst'─┬─key─┬─value─┬─_part─────┐
│ from dst   │   1 │ A     │ all_0_0_0 │
└────────────┴─────┴───────┴───────────┘

SELECT
    'from mv_dst',
    *,
    _part
FROM mv_dst
ORDER by all;

┌─'from mv_dst'─┬─key─┬─value─┬─_part─────┐
│ from mv_dst   │   0 │ A     │ all_0_0_0 │
│ from mv_dst   │   0 │ A     │ all_1_1_0 │
└───────────────┴─────┴───────┴───────────┘
```

That retry operation is deduplicated on both tables `dst` and `mv_dst`.

- [Limitations](#limitations)
  - [Uncertain insert status](#uncertain-insert-status)- [Deduplication window limit](#deduplication-window-limit)- [Enabling insert deduplication on retries](#enabling-insert-deduplication-on-retries)
    - [Insert deduplication for tables](#insert-deduplication-for-tables)- [Query-level insert deduplication](#query-level-insert-deduplication)- [How insert deduplication works](#how-insert-deduplication-works)- [Insert deduplication with materialized views](#insert-deduplication-with-materialized-views)- [Examples](#examples)
          - [Identical blocks after materialized view transformations](#identical-blocks-after-materialized-view-transformations)- [Identical blocks on insertion](#identical-blocks-on-insertion)- [Identical blocks in insertion with `insert_deduplication_token`](#identical-blocks-in-insertion-with-insert_deduplication_token)- [Different insert operations generate the same data after transformation in the underlying table of the materialized view](#different-insert-operations-generate-the-same-data-after-transformation-in-the-underlying-table-of-the-materialized-view)- [Different materialized view inserts into one underlying table with equivalent data](#different-materialized-view-inserts-into-one-underlying-table-with-equivalent-data)

Was this page helpful?