Add a materialized view

An incremental materialized view runs a query on every block of rows as it is inserted, and writes the result into a target table. It moves repeated query work from read time to ingestion time.

Think of it as an insert trigger, not a cached query. When a block lands in the source table, the view’s SELECT runs on just that block and appends the result to a 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 this engine sums rows that share the same key during merges. Query the target with sum(requests) and a GROUP BY, so partial rows that haven’t merged yet are combined correctly.

Backfill without double counting

The view only sees inserts that happen after it exists. It never rewrites old source rows, so history needs a manual backfill, and you have to do it while live inserts keep arriving.

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;

Be careful with the boundary. If it overlaps with what the view already processed, or you run the backfill twice, those hours count double and nothing warns you.

The failure modes are all duplicates

Retries and deduplication on the source table affect the target in ways that surprise people. A retried insert that the source table deduplicates may still have been processed by the view. A view whose SELECT throws fails the original insert. Duplicated source rows become duplicated aggregates.

Whenever a dashboard number looks slightly off, 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 events_hourly is a real table you own, you can delete the affected hours and rebuild them from the source.

Try this in your lab: create the hourly target, insert a few new events, backfill the older ones with a cutoff, and run the comparison. Do it before you point any dashboard at the target. I never trust a materialized view until the two numbers match.