ClickHouse Materialized Views

A materialized view in ClickHouse is not what the name implies if you come from PostgreSQL. It is not a cached snapshot you refresh on a schedule. It is an insert trigger: a query that runs on each block of rows as they land in a source table, writing the result into a separate target table. This shifts aggregation cost from query time to insert time, so dashboards that read the target table stay fast as the source grows. This guide covers the model, the table engines that make rollups work, and the mistakes that cost people a weekend.

The Trigger Model

When you insert rows into the source table, ClickHouse runs the view's SELECT over just that inserted block and appends the result to the target table. It never sees historical data and never re-reads the source. That single fact explains most of the surprising behavior below.

-- Source: raw events
CREATE TABLE events (
    event_time DateTime,
    user_id    UInt64,
    country    String,
    revenue    Decimal(10, 2)
) ENGINE = MergeTree
ORDER BY event_time;

A view needs somewhere to write. The clean approach is to create the target table explicitly and point the view at it with TO.

-- Target: daily revenue per country
CREATE TABLE revenue_by_day (
    day      Date,
    country  String,
    revenue  Decimal(10, 2)
) ENGINE = SummingMergeTree
ORDER BY (day, country);
 
-- The view: a trigger that fills the target on insert
CREATE MATERIALIZED VIEW revenue_by_day_mv
TO revenue_by_day
AS
SELECT
    toDate(event_time) AS day,
    country,
    revenue
FROM events;

From now on, every insert into events pushes a transformed copy into revenue_by_day. Queries hit the small pre-aggregated table.

Rolling Up with SummingMergeTree

In the example above the view does not aggregate; it relies on SummingMergeTree to do the work. That engine collapses rows with the same sorting key during background merges, summing the numeric columns that are not part of the key.

SELECT day, country, sum(revenue) AS revenue
FROM revenue_by_day
GROUP BY day, country;

The sum() and GROUP BY at read time are still required. Merges happen asynchronously and may not have run yet, so the target table can hold several partial rows per key until a merge consolidates them. Always aggregate on read; never assume one row per key.

SummingMergeTree only handles sums. For counts, averages, uniques, min/max, or anything else, you need AggregatingMergeTree.

AggregatingMergeTree and the State/Merge Combinators

AggregatingMergeTree stores intermediate aggregation states rather than finished values. You produce a state with the -State combinator on insert, and resolve it to a number with the -Merge combinator on read.

CREATE TABLE user_stats (
    day          Date,
    country      String,
    visits       AggregateFunction(count, UInt64),
    unique_users AggregateFunction(uniq, UInt64),
    avg_revenue  AggregateFunction(avg, Decimal(10, 2))
) ENGINE = AggregatingMergeTree
ORDER BY (day, country);
 
CREATE MATERIALIZED VIEW user_stats_mv
TO user_stats
AS
SELECT
    toDate(event_time)     AS day,
    country,
    countState()           AS visits,
    uniqState(user_id)     AS unique_users,
    avgState(revenue)      AS avg_revenue
FROM events
GROUP BY day, country;

The columns hold opaque state blobs, not readable numbers. To get values back, apply the matching -Merge function and group:

SELECT
    day,
    country,
    countMerge(visits)        AS visits,
    uniqMerge(unique_users)   AS unique_users,
    avgMerge(avg_revenue)     AS avg_revenue
FROM user_stats
GROUP BY day, country
ORDER BY day, country;

The rule of thumb: -State on the way in, -Merge on the way out, and the -State/-Merge function names must match exactly. Querying the raw state column without -Merge returns gibberish, which is the most common confusion here. See the aggregate functions guide for the full combinator family.

Backfilling Existing Data

Because the view only fires on new inserts, creating it does nothing for data already in the source table. To populate the target with history, run a one-time INSERT INTO target SELECT ... that mirrors the view's query.

INSERT INTO user_stats
SELECT
    toDate(event_time) AS day,
    country,
    countState(),
    uniqState(user_id),
    avgState(revenue)
FROM events
GROUP BY day, country;

Order matters: if you create the view first and then backfill, you risk double-counting any rows inserted in the gap. A safe pattern is to create the view, then backfill only data with timestamps older than the view's creation moment.

The POPULATE Keyword and Why to Avoid It

CREATE MATERIALIZED VIEW ... POPULATE backfills automatically at creation time, but any rows inserted into the source during the populate run are missed, silently. For production tables that are actively receiving writes, prefer the manual backfill above. POPULATE is fine for static or paused tables.

Coupling and Cascades

A materialized view is tied to its source table. Two consequences worth knowing:

• If an insert into the source succeeds but the view's query throws (a type mismatch, a divide by zero), the behavior depends on the materialized_views_ignore_errors setting; by default the failing view can fail the whole insert. Test the view's SELECT independently before deploying it.
• Inserting into the target table of one view can itself trigger another view if a second view reads that target. Chains of views are supported and useful for multi-level rollups, but they make data lineage harder to trace.

Common Mistakes

• Expecting a Postgres-style refreshable snapshot. Incremental views are insert triggers; they never re-scan the source. (ClickHouse does have separate REFRESHable views for the schedule-based model, but they are a different feature.)
• Reading state columns without -Merge. An AggregateFunction column is an intermediate blob until merged.
• Assuming one row per key in SummingMergeTree. Merges are asynchronous; always GROUP BY and aggregate on read.
• Forgetting to backfill. A new view ignores all pre-existing rows. History needs an explicit INSERT ... SELECT.
• Using POPULATE on a live table. Rows written during populate are lost without warning.

When you are validating that a view's target table actually matches a re-aggregation of the source, Mako's AI autocomplete helps you write the -Merge queries and side-by-side checks, which is handy given how easy it is to forget a combinator.

Related Guides

• ClickHouse Aggregate Functions for the -State/-Merge combinator family in detail.
• ClickHouse Window Functions for analytics that don't need a pre-aggregated target.
• Import CSV to ClickHouse to load source data for testing views.

Mako connects to ClickHouse with AI-powered autocomplete for writing and exploring analytical queries. Try it free at mako.ai.