Add a materialized view

An incremental materialized view transforms newly inserted blocks and writes results into a target table. It moves repeated query work to ingestion time.

Think of it as an insert trigger, not a cached query. When a block of rows lands in the source table, the view’s SELECT runs on just that block and appends the result to a target table you own. If your dashboard recomputes the same hourly counts every thirty seconds, this trades that repeated scan for a little work on each insert.

The target table comes first, then the view that feeds it:

CREATE TABLE events_hourly (
  hour     DateTime,
  service  LowCardinality(String),
  requests UInt64
)
ENGINE = SummingMergeTree
ORDER BY (service, hour);

CREATE MATERIALIZED VIEW events_hourly_mv TO events_hourly AS
SELECT
  toStartOfHour(ts) AS hour,
  service,
  count() AS requests
FROM events
GROUP BY hour, service;

SummingMergeTree matters here: each insert block produces partial counts, and the target engine sums rows sharing the same key during merges. Query it with sum(requests) and a GROUP BY so partial rows that haven’t merged yet are combined correctly.

Backfill without double counting

The view does not automatically rewrite old source rows. It only sees inserts that happen after it exists, so history needs a manual backfill. Plan a backfill and avoid double counting while live inserts continue.

The safe pattern is a cutoff. The view handles everything from its creation moment forward; you insert everything strictly before that moment:

INSERT INTO events_hourly
SELECT toStartOfHour(ts) AS hour, service, count() AS requests
FROM events
WHERE ts < '2026-08-03 12:00:00'
GROUP BY hour, service;

Backfill a separate time range, then compare the view result with the raw query. If the boundary overlaps — or you run the backfill twice — those hours count double, and nothing warns you.

The failure modes are duplicates

Understand how retries and source deduplication affect the target. A retried insert that the source table deduplicates may still have been processed by the view, and a view whose SELECT throws can fail the original insert. Duplicated source rows become duplicated aggregates. Whenever a dashboard number looks slightly wrong, this comparison is the diagnostic:

SELECT sum(requests) FROM events_hourly
WHERE hour = toStartOfHour(now() - INTERVAL 1 HOUR);

SELECT count() FROM events
WHERE toStartOfHour(ts) = toStartOfHour(now() - INTERVAL 1 HOUR);

Matching numbers mean the pipeline is honest. Diverging numbers mean a backfill overlap or a retry got counted twice — and because you own events_hourly as a real table, you can drop the affected hours and rebuild them from the source.

Create an hourly count target for new events, backfill, and run that comparison before you point any dashboard at the target.