How to Build a ClickHouse Materialized View for Dashboards

October 27, 2026

A ClickHouse materialized view is a trigger on insert: when rows land in the source table, the view’s SELECT runs over just those new rows and writes the results into a target table. It is not a stored query that runs at read time, which is the assumption most people bring from Postgres.

That distinction drives everything — you need a target table with a matching schema, you must backfill history manually, and your aggregate functions must be mergeable.

How do you create the target table first?

The target table is where results land. Use AggregatingMergeTree (or SummingMergeTree for simple sums) so pre-aggregated states merge as parts combine.

Column types must match what the SELECT produces. For uniq() use AggregateFunction(uniq, UInt64). For sums use SimpleAggregateFunction(sum, UInt64). Getting these wrong is the most common error, and ClickHouse will reject the insert with a type mismatch rather than silently doing something odd.

Order the target by the columns your dashboard filters on — usually (tenant_id, event_date). It is a separate design decision from the source table’s ORDER BY, and worth thinking about independently.

How do you write the materialized view?

The syntax trips people up because the SELECT must read from the source table as written:

CREATE MATERIALIZED VIEW mv_daily TO target_table AS SELECT tenant_id, toDate(event_time) AS event_date, uniqState(toString(user_id)) AS users, sumState(1) AS events FROM source_table GROUP BY tenant_id, event_date;

Note the State suffix functions. The view stores intermediate aggregation states, not final numbers. When querying, you use the matching -Merge functions: uniqMerge(users), sumMerge(events).

If you query the target table with plain sum() instead of sumMerge(), you get numbers that look plausible and are wrong. That failure mode is quiet and dangerous.

How do you backfill history?

This is the step everyone forgets. Creating the view does not populate it with existing data.

  1. Create the target table and the view.
  2. Run INSERT INTO target_table SELECT ... FROM source_table WHERE event_time < now() - INTERVAL 1 HOUR; using the same SELECT, with -State functions.
  3. Verify row counts and a spot-check aggregate against a direct query.

Bound the backfill by time so you do not double-count rows that arrive between step 1 and step 2. Using a cutoff like one hour ago and accepting a small gap, then re-running with a narrower window, beats an exact-window backfill you cannot reason about.

If the source table is partitioned, you can backfill partition by partition and check counts between steps.

How do you query it from a dashboard

Wrap the target table in a normal view so dashboard tools see plain columns:

CREATE VIEW v_daily_stats AS SELECT tenant_id, event_date, uniqMerge(users) AS users, sumMerge(events) AS events FROM target_table GROUP BY tenant_id, event_date;

Then point Metabase, Grafana, or Superset at v_daily_stats. The -State/-Merge awkwardness stays out of the BI tool, which is where it belongs.

For a dashboard needing multiple time granularities, build one MV per granularity (hourly, daily) rather than trying to roll up at query time. Hourly target plus a toStartOfDay group-by in the view layer handles daily too.

What are the traps?

Trap Symptom
Backfill skipped Dashboard shows only data since MV creation
Plain sum() instead of sumMerge() Numbers 2–10x too high
MV created before target table Error, or worse, an implicit table you did not design
Unbounded cardinality with uniqExact Memory spikes during merges
No TTL on target Storage grows forever, queries slow

That fourth one: uniqExact is accurate and memory-hungry. For dashboards, uniq() (HyperLogLog-based, about 0.5% error) is almost always the right trade.

What about late-arriving data?

Materialized views process rows at insert time. If an event arrives 3 days late, it gets written into the bucket for its own event_date, which is correct — as long as the event’s timestamp is in the data, not the insert time.

Deduplication is the harder problem. If your ingestion can deliver the same event twice, the MV will count it twice. ClickHouse ReplacingMergeTree on the source, or an upstream dedupe step, is the fix. Do not rely on the MV to dedupe; it has no notion of identity.

When a materialized view is the wrong tool

If the query is already fast enough on raw data, an MV adds storage and a failure mode for no gain. ClickHouse scans raw data alarmingly fast. Measure the raw query first.

Also skip MVs for exploratory analysis: you cannot easily add a new dimension after the fact, and every schema change means rebuilding. Pre-aggregate for dashboard queries you know are stable, not for questions you are still discovering.

Key Takeaways

  • ClickHouse materialized views fire on insert, not on read — history must be backfilled separately.
  • Create the AggregatingMergeTree target table before the view, with matching aggregate column types.
  • Store -State aggregates and read with -Merge; plain sum() returns wrong numbers.
  • Expose a plain view over the target table so BI tools never see aggregate states.
  • Materialized views do not deduplicate; handle that upstream or in the source table engine.