Building ClickHouse Materialized Views for Dashboards

A dashboard that queries raw beacons is fast on the day it is built and unusable a quarter later, because its cost grows with retention. The fix is not a bigger machine: it is storing mergeable quantile state at write time so a panel reads thousands of rows instead of billions. This guide sits under Grafana Dashboards for Web Performance and covers the rollup schema, why quantile state rather than quantile values is the whole trick, backfilling without a maintenance window, and how to keep panel latency flat.

Prerequisites

A ClickHouse instance with a raw events table, as set up in Setting Up a Self-Hosted RUM Pipeline with ClickHouse.
A settled set of cohort dimensions — route, metric, device tier, network, country. Adding one later means rebuilding.
Grafana or another dashboard tool pointed at the same database.

How to build it

Step 1 — Understand why storing a p75 does not work

The instinct is to compute the p75 hourly and store the number. It fails immediately: percentiles do not average. The p75 of a day is not the mean of 24 hourly p75 values, and no arithmetic on stored percentiles recovers it.

ClickHouse solves this with aggregate state: quantileTDigestState stores a compressed sketch of the distribution that can be merged. Merge 24 hourly states and you get the exact same sketch you would have built from the day’s raw rows, and quantileTDigestMerge reads a percentile out of it.

Approach	Can re-aggregate to a longer window	Storage per cohort-hour	Accuracy
Store raw events	Yes	Everything	Exact
Store the p75 value	No	8 bytes	Exact for that window only
Store quantile state	Yes	~1–4 KB	Approximate, ~1%

The middle row is the trap, and it is what most homegrown pipelines do first.

Step 2 — Create the rollup table

-- Hourly rollup. AggregateFunction columns hold mergeable state, not values.
CREATE TABLE rum_hourly
(
  hour          DateTime,
  metric        LowCardinality(String),
  route         LowCardinality(String),
  device_tier   LowCardinality(String),
  network       LowCardinality(String),
  country       LowCardinality(FixedString(2)),
  samples       AggregateFunction(count),
  value_state   AggregateFunction(quantilesTDigest(0.5, 0.75, 0.95, 0.99), Float32),
  good_count    AggregateFunction(sum, UInt32),
  poor_count    AggregateFunction(sum, UInt32)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (metric, route, device_tier, network, country, hour)
TTL hour + INTERVAL 13 MONTH;

LowCardinality on every dimension is what keeps this small — those columns are dictionary-encoded, so a route repeated a million times costs almost nothing. The ordering key puts the columns dashboards filter on first, which is what makes the reads cheap.

Step 3 — Attach a materialized view so it fills itself

-- Every insert into rum_events also lands, aggregated, in rum_hourly.
CREATE MATERIALIZED VIEW rum_hourly_mv TO rum_hourly AS
SELECT
  toStartOfHour(ts)                                        AS hour,
  metric,
  route,
  device_tier,
  network,
  country,
  countState()                                             AS samples,
  quantilesTDigestState(0.5, 0.75, 0.95, 0.99)(value)      AS value_state,
  sumState(toUInt32(rating = 'good'))                      AS good_count,
  sumState(toUInt32(rating = 'poor'))                      AS poor_count
FROM rum_events
GROUP BY hour, metric, route, device_tier, network, country;

The view runs on insert, on the inserted block only. It never scans the existing table, so adding it to a live pipeline is safe — it simply starts producing rows from the next batch onward.

Step 4 — Read percentiles back out

-- A dashboard panel: p75 by route over an arbitrary window.
SELECT
  route,
  countMerge(samples)                                        AS n,
  quantilesTDigestMerge(0.5, 0.75, 0.95, 0.99)(value_state)  AS q,
  q[2]                                                       AS p75,
  sumMerge(good_count) / countMerge(samples)                 AS good_share
FROM rum_hourly
WHERE metric = 'LCP'
  AND hour >= now() - INTERVAL 28 DAY
  AND device_tier = {tier:String}
GROUP BY route
HAVING n >= 1000
ORDER BY p75 DESC;

The Merge combinators are the counterpart to the State ones. Any window, any subset of dimensions, one pass over a small table.

Raw-table cost grows with retention; rollup cost does not. That divergence is the entire argument for materialized views.

Step 5 — Add a daily rollup on top

For year-long trends, merge the hourly states into daily ones. The same pattern applies, and a view can read from a view.

CREATE TABLE rum_daily AS rum_hourly
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (metric, route, device_tier, day);

CREATE MATERIALIZED VIEW rum_daily_mv TO rum_daily AS
SELECT
  toStartOfDay(hour)                     AS day,
  metric, route, device_tier, network, country,
  countMergeState(samples)               AS samples,
  quantilesTDigestMergeState(0.5, 0.75, 0.95, 0.99)(value_state) AS value_state,
  sumMergeState(good_count)              AS good_count,
  sumMergeState(poor_count)              AS poor_count
FROM rum_hourly
GROUP BY day, metric, route, device_tier, network, country;

MergeState is the combinator that merges existing state and keeps it as state — the mechanism that lets rollups stack without ever collapsing to a number.

Step 6 — Backfill without blocking ingestion

A new view only sees new inserts, so history has to be backfilled explicitly. Do it a partition at a time so a failure costs one month rather than the whole job.

-- Backfill one month, then repeat. Watch memory: a wide GROUP BY over a
-- large partition is the part that fails on a small instance.
INSERT INTO rum_hourly
SELECT
  toStartOfHour(ts) AS hour, metric, route, device_tier, network, country,
  countState(),
  quantilesTDigestState(0.5, 0.75, 0.95, 0.99)(value),
  sumState(toUInt32(rating = 'good')),
  sumState(toUInt32(rating = 'poor'))
FROM rum_events
WHERE ts >= '2026-06-01' AND ts < '2026-07-01'
GROUP BY hour, metric, route, device_tier, network, country
SETTINGS max_memory_usage = 8000000000, max_threads = 4;

Run the backfill after the view is live, and check for double-counting at the boundary: if the view was created mid-hour, that hour will contain both view-generated and backfilled rows. Deleting the boundary partition before backfilling it is the simplest fix.

The last row is cheapest and cannot answer a question it was not built for. The daily rollup costs seven times as much and can be re-cut by any dimension it carries.

Choosing the grain

The single most consequential decision is which dimensions go into the rollup, because it fixes what you can ask later.

Every dimension multiplies the row count. Metric (5) × route (200) × device tier (4) × network (5) × country (40) × hours (720/month) is a theoretical 5.7 billion rows a month — except that most combinations never occur, and the real count is typically a fraction of a percent of that. Still, the sparsity is doing the work rather than the design.

Two rules keep it manageable. Drop dimensions that are never filtered on together: if nobody ever asks for country crossed with network, keep two narrower rollups instead of one wide one. And cap the cardinality of every dimension at the collector, so a client bug cannot introduce ten thousand new route values overnight — the validation layer for that is in Rate Limiting and Validating RUM Beacons.

Where a dimension is needed occasionally but not routinely, leave it out of the rollup and query the raw table for those investigations. A rollup that answers 95% of questions in 50 ms plus a raw table for the other 5% is a better system than one rollup that answers everything slowly.

Verifying it works

Rollup percentiles match raw percentiles within about 1% on a sample window. A larger gap means the t-digest parameters need tightening or the backfill is incomplete.
Panel latency is flat over time. Measure the same query weekly; growth means a panel is still reading raw data.
Sample counts agree between rollup and raw for the same window. A mismatch points at a boundary double-count or a dropped block.
The daily rollup agrees with the hourly one re-aggregated over a day. This checks the MergeState chain end to end.
New dimension values appear in the rollup within an hour of first being sent.

The percentile is computed once, at the end, from whatever window the reader asked for. Everything before that stays mergeable.

Edge cases and gotchas

Materialized views do not see historical data. Creating one is not a migration; the backfill is a separate, explicit job.
They also do not see ALTERs. Changing the raw table schema does not update the view’s SELECT; both have to change together.
State columns are engine-specific binaries. They cannot be read by anything but ClickHouse, and their format is tied to the function and its parameters.
AggregatingMergeTree merges in the background. Immediately after insert you may see several partial rows per key; the Merge combinator handles that correctly, a naive SELECT does not.
Approximate quantiles have error. t-digest is roughly 1% at the median and better in the tails. Where exactness matters, keep raw data for that window.
TTL deletes silently. A 13-month TTL on the rollup and 90 days on raw means year-old data cannot be recomputed if the rollup grain turns out to be wrong.

Grafana Dashboards for Web Performance — the panels these rollups feed.
Setting Up a Self-Hosted RUM Pipeline with ClickHouse — the raw table and ingestion path underneath.
Calculating Confidence Intervals for a Sampled p75 — why the sample count has to survive the rollup.