How can replay double-count a ClickHouse materialized view?

Follow one event through two attempts

Imagine a fictional adapter that inserts a batch and then loses the response. It cannot tell whether the database accepted the work, so it retries. There are now two attempts for one logical batch. If the ingestion contract allows both attempts to contribute to an incremental aggregate, an hourly count can rise twice even when a later source query appears to contain one event.

Identify what the view actually processes

An incremental materialized view transforms newly inserted blocks. It is not a standing reconciliation process over the final deduplicated appearance of the source. This distinction matters when the source uses replacement or later correction. Ask how the derived target responds to repeated input, updates, deletion, and replay; do not infer those responses from the source table’s eventual display.

Separate transport identity from event identity

Give retries a stable logical operation identity when the ingestion mechanism supports it, and keep individual attempt IDs for diagnosis. Event identity must survive legitimate replay while distinguishing separate events that happen to share text and timestamps. A content hash can accidentally collapse repeated real events if its input omits the distinction your domain needs.

Make a small replay experiment decisive

Use a dedicated test dataset with known event IDs and expected aggregates. Pin the ClickHouse version, insert settings, table engines, view definitions, and batch boundaries. Different mechanisms provide different duplicate-handling guarantees; evaluate the supported configuration instead of assuming a universal exactly-once claim.

1. Insert a known batch and check both canonical records and derived totals.
2. Repeat the same logical batch with the same intended retry identity.
3. Simulate a lost acknowledgement after accepted work.
4. Replay older records across the intended recovery window.
5. Compare totals and record which layer suppressed or repeated contributions.

Decide how historical aggregates are repaired

If the count is already wrong, stopping new duplicates does not repair history. Define a bounded rebuild, a compensating correction, or replacement of a derived partition, as appropriate for the data model. Capture the source population and cutover boundary so that repair traffic does not introduce another overlap. Recheck the user-visible result after the repair completes.

Publish the narrow guarantee

A defensible contract states which retries are suppressed, under which identities and settings, over what window, and with what effect on each derived table. Retain tests for batch-boundary changes and adapter upgrades. In this exercise, “the source looks deduplicated” is an observation about one query surface, not proof that every downstream metric was counted once.

Evidence and scope

• Incremental materialized views: Incremental materialized views compute transformations over newly inserted blocks rather than continuously re-reading the complete source table.
• ClickHouse asynchronous inserts: Asynchronous insertion has different acknowledgement behavior depending on whether the client waits for the buffer flush.

The proposed checks are teaching tools; validate their behavior in the actual environment.