ClickHouse Materialized Views for Real-Time Analytics

Real-time analytics demands sub-second query responses over datasets that grow by millions of rows per hour. Traditional approaches—batch ETL into a data warehouse, then query—introduce latency that modern product teams find unacceptable. ClickHouse, the columnar OLAP database originally developed at Yandex, has emerged as a leading solution for this class of problem. Its materialized views are the mechanism that bridges the gap between raw event ingestion and instant analytical queries.

Unlike materialized views in PostgreSQL or Oracle, which are periodic snapshots refreshed on a schedule, ClickHouse materialized views are incremental. They execute their transformation logic on each inserted batch of data and write the results to a separate target table. This design means your aggregated analytics tables stay current within seconds of the raw data arriving, without any external scheduler or refresh mechanism.

This guide walks through the architecture, implementation patterns, and production considerations for building real-time analytics pipelines with ClickHouse materialized views. We will cover everything from basic aggregations to advanced patterns using AggregatingMergeTree and multi-stage view chains.

How ClickHouse Materialized Views Work

Understanding the internal mechanics is essential before building on top of them. A ClickHouse materialized view consists of three components: a source table where raw data lands, a SELECT query that defines the transformation, and a target table where the transformed results are stored.

When you insert rows into the source table, ClickHouse intercepts the insert and runs the materialized view's SELECT query against only the newly inserted block of data, not the entire source table. The query results are then inserted into the target table. This is fundamentally different from a full-refresh model and is what makes ClickHouse materialized views viable for high-throughput streaming workloads.

-- Source table: raw page view events
CREATE TABLE events_raw (
    event_id UUID,
    user_id UInt64,
    page_url String,
    event_type Enum8('pageview' = 1, 'click' = 2, 'scroll' = 3),
    country_code FixedString(2),
    device_type Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3),
    event_time DateTime64(3),
    session_id String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 90 DAY;

This source table uses MergeTree with monthly partitioning and a 90-day TTL. Raw events flow in via Kafka, HTTP inserts, or ClickHouse's native protocol. The TTL automatically cleans up old raw data while the materialized views preserve the aggregated summaries indefinitely.

Creating a Basic Materialized View

The simplest materialized view aggregates raw events into a summary table:

-- Target table for hourly page view counts
CREATE TABLE pageviews_hourly (
    hour DateTime,
    page_url String,
    country_code FixedString(2),
    device_type Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3),
    view_count UInt64,
    unique_users UInt64
) ENGINE = SummingMergeTree()
ORDER BY (hour, page_url, country_code, device_type);

-- Materialized view that populates the target
CREATE MATERIALIZED VIEW pageviews_hourly_mv
TO pageviews_hourly AS
SELECT
    toStartOfHour(event_time) AS hour,
    page_url,
    country_code,
    device_type,
    count() AS view_count,
    uniq(user_id) AS unique_users
FROM events_raw
WHERE event_type = 'pageview'
GROUP BY hour, page_url, country_code, device_type;

The TO clause directs output to an explicitly defined target table. This is the recommended pattern because it gives you full control over the target table's engine, partitioning, and TTL. The alternative—letting ClickHouse create an implicit target—works for prototyping but lacks flexibility.

AggregatingMergeTree for Correct Incremental Aggregation

The basic example above uses SummingMergeTree, which works for additive metrics like counts and sums. But what about non-additive aggregations like unique user counts, percentiles, or averages? SummingMergeTree would simply sum the partial unique counts from each batch, producing incorrect results.

AggregatingMergeTree solves this by storing intermediate aggregation states rather than final values. Each inserted batch contributes its partial state, and ClickHouse merges these states during background merge operations to produce correct final results.

-- Target table using AggregatingMergeTree
CREATE TABLE analytics_daily (
    day Date,
    page_url String,
    country_code FixedString(2),
    view_count AggregateFunction(count, UInt8),
    unique_users AggregateFunction(uniq, UInt64),
    avg_session_duration AggregateFunction(avg, Float64),
    p95_load_time AggregateFunction(quantile(0.95), Float64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(day)
ORDER BY (day, page_url, country_code);

-- Materialized view using -State combinators
CREATE MATERIALIZED VIEW analytics_daily_mv
TO analytics_daily AS
SELECT
    toDate(event_time) AS day,
    page_url,
    country_code,
    countState(toUInt8(1)) AS view_count,
    uniqState(user_id) AS unique_users,
    avgState(session_duration) AS avg_session_duration,
    quantileState(0.95)(page_load_time) AS p95_load_time
FROM events_raw
WHERE event_type = 'pageview'
GROUP BY day, page_url, country_code;

The key syntax elements are the -State suffix on aggregation functions in the materialized view and the AggregateFunction column types in the target table. When querying, you use the -Merge suffix to finalize the aggregation:

-- Querying the AggregatingMergeTree table
SELECT
    day,
    page_url,
    countMerge(view_count) AS total_views,
    uniqMerge(unique_users) AS unique_visitors,
    avgMerge(avg_session_duration) AS avg_duration,
    quantileMerge(0.95)(p95_load_time) AS p95_load
FROM analytics_daily
WHERE day >= today() - 7
GROUP BY day, page_url
ORDER BY day DESC, total_views DESC;

This pattern produces mathematically correct results regardless of how many batches contributed to each aggregation period. It is the foundation for reliable real-time analytics in ClickHouse.

Streaming Ingestion Patterns

Materialized views reach their full potential when paired with a streaming ingestion layer. The most common production pattern uses Kafka as the message bus with ClickHouse's built-in Kafka table engine, creating a pipeline that flows from application events through Kafka into ClickHouse with sub-second latency. For teams already running stream processing with Flink and Kafka, ClickHouse materialized views complement that architecture by handling the analytical storage layer.

Kafka Table Engine Integration

-- Kafka engine table (virtual - only used for consuming)
CREATE TABLE events_kafka (
    event_id UUID,
    user_id UInt64,
    page_url String,
    event_type String,
    country_code String,
    device_type String,
    event_time DateTime64(3),
    session_id String
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka-1:9092,kafka-2:9092,kafka-3:9092',
    kafka_topic_list = 'page_events',
    kafka_group_name = 'clickhouse_analytics',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4,
    kafka_max_block_size = 65536;

-- Materialized view moves data from Kafka to MergeTree
CREATE MATERIALIZED VIEW events_kafka_consumer
TO events_raw AS
SELECT
    event_id,
    user_id,
    page_url,
    CAST(event_type AS Enum8('pageview' = 1, 'click' = 2, 'scroll' = 3)) AS event_type,
    CAST(country_code AS FixedString(2)) AS country_code,
    CAST(device_type AS Enum8('desktop' = 1, 'mobile' = 2, 'tablet' = 3)) AS device_type,
    event_time,
    session_id
FROM events_kafka;

This creates a three-stage pipeline: Kafka engine table consumes messages, a materialized view transforms and lands data into the raw MergeTree table, and downstream materialized views (the hourly and daily aggregations defined earlier) trigger automatically on each insert. The entire chain executes within a single insert transaction.

Handling Schema Evolution

Production Kafka topics evolve over time. Fields get added, types change, optional fields become required. ClickHouse handles this gracefully with the input_format_skip_unknown_fields setting and default values:

-- Allow unknown fields in JSON and set defaults
SET input_format_skip_unknown_fields = 1;

-- Add a new column with a default
ALTER TABLE events_raw
    ADD COLUMN referrer_url String DEFAULT '';

For more complex schema evolution scenarios, place a stream processing layer like Flink between Kafka and ClickHouse to handle format transformations, field mapping, and backward compatibility.

Multi-Stage View Chains and View Architecture

Complex analytics often require multiple levels of aggregation. ClickHouse supports chaining materialized views: the target table of one view serves as the source for another. This lets you build layered aggregation architectures.

Three-Level Aggregation Example

-- Level 1: Raw events → Minute-level aggregation
CREATE MATERIALIZED VIEW events_per_minute_mv
TO events_per_minute AS
SELECT
    toStartOfMinute(event_time) AS minute,
    page_url,
    count() AS events,
    uniqState(user_id) AS users_state
FROM events_raw
GROUP BY minute, page_url;

-- Level 2: Minute → Hourly rollup
CREATE MATERIALIZED VIEW events_per_hour_mv
TO events_per_hour AS
SELECT
    toStartOfHour(minute) AS hour,
    page_url,
    sum(events) AS events,
    uniqMergeState(users_state) AS users_state
FROM events_per_minute
GROUP BY hour, page_url;

-- Level 3: Hourly → Daily rollup
CREATE MATERIALIZED VIEW events_per_day_mv
TO events_per_day AS
SELECT
    toDate(hour) AS day,
    page_url,
    sum(events) AS events,
    uniqMergeState(users_state) AS users_state
FROM events_per_hour
GROUP BY day, page_url;

This tiered approach keeps each materialized view simple and focused. Minute-level data can have a short TTL (7 days), hourly data a moderate TTL (90 days), and daily data is kept indefinitely. Queries automatically hit the most appropriate aggregation level based on the time range.

Design your materialized view chains like a pyramid: wide at the base with raw data, progressively narrower at each aggregation level. Each level should answer a distinct class of queries at a specific time granularity.

Production Optimization and Monitoring

Running materialized views in production requires attention to performance, error handling, and operational monitoring. These are the patterns that separate a prototype from a production system.

Insert Batching

ClickHouse performs best with large, infrequent inserts rather than small, frequent ones. Each insert creates a new data part, and materialized views execute once per insert. Aim for batches of 10,000–100,000 rows inserted every 1–5 seconds rather than single-row inserts:

-- Buffer table to batch small inserts
CREATE TABLE events_buffer AS events_raw
ENGINE = Buffer(
    currentDatabase(), events_raw,
    16,        -- num_layers
    10, 100,   -- min_time, max_time (seconds)
    10000, 1000000,  -- min_rows, max_rows
    10000000, 100000000  -- min_bytes, max_bytes
);

Note that Buffer tables flush directly to the target, bypassing materialized views on the target table. If you need materialized views, insert into the source table directly and handle batching on the client side or through the Kafka engine, which naturally batches.

Monitoring View Health

ClickHouse exposes materialized view performance through system tables. Query these regularly to detect issues early:

-- Check materialized view errors
SELECT
    database,
    table,
    last_exception,
    last_exception_time
FROM system.mutations
WHERE is_done = 0 AND last_exception != '';

-- Monitor insert rates and latency per view
SELECT
    event_date,
    table,
    sum(written_rows) AS total_rows,
    sum(written_bytes) AS total_bytes,
    count() AS insert_count,
    avg(query_duration_ms) AS avg_duration_ms
FROM system.query_log
WHERE type = 'Insert' AND query_kind = 'Insert'
    AND event_date = today()
GROUP BY event_date, table
ORDER BY total_rows DESC;

Integrating these queries into a data observability platform gives you proactive alerting on view failures, insert slowdowns, and data freshness degradation.

Handling View Failures

If a materialized view fails (due to a type mismatch, out-of-memory error, or disk pressure), ClickHouse will still accept the insert into the source table but skip the failing view. This means your raw data is safe, but the aggregated table falls behind. To recover:

1. Fix the root cause (schema mismatch, resource limits)
2. Identify the gap by comparing max timestamps between source and target tables
3. Backfill the target table with a manual INSERT INTO ... SELECT for the missing period
4. Verify counts match between the backfill and what the view would have produced

Comparison with Other Approaches

ClickHouse materialized views are not the only way to build real-time analytics. Understanding the alternatives helps you choose the right tool.

Approach	Latency	Complexity	Best For
ClickHouse Materialized Views	Sub-second	Low	Pre-defined aggregations, dashboards
Flink + ClickHouse	Sub-second	High	Complex event processing, joins
Snowflake Dynamic Tables	Seconds to minutes	Low	Cloud-native, managed infrastructure
Druid Real-Time Ingestion	Sub-second	Medium	High-cardinality time series
Batch ETL + OLAP	Hours	Medium	Historical reporting, large backfills

ClickHouse materialized views excel when your analytics queries are well-defined ahead of time and the transformation logic is primarily aggregation. For complex stream processing involving multi-stream joins, windowed computations, or stateful event processing, a dedicated stream processor like Flink feeding into ClickHouse is the more robust architecture.

Advanced Patterns and Best Practices

Beyond the fundamentals, several advanced patterns unlock additional capabilities from ClickHouse materialized views.

The Null Engine Pattern

If you do not need to retain raw events and only care about the aggregated results, use a Null engine for the source table. The Null engine accepts inserts but discards the data immediately after materialized views process it:

-- Source table that discards raw data after view processing
CREATE TABLE events_null AS events_raw
ENGINE = Null;

-- Materialized views still trigger on inserts to Null tables
CREATE MATERIALIZED VIEW aggregate_from_null
TO analytics_daily AS
SELECT
    toDate(event_time) AS day,
    page_url,
    countState(toUInt8(1)) AS view_count,
    uniqState(user_id) AS unique_users
FROM events_null
GROUP BY day, page_url;

This pattern saves significant storage when raw event volumes are massive and you only query aggregated data. The tradeoff is that you cannot backfill or debug from raw events if a view fails.

Conditional Routing with Multiple Views

You can attach multiple materialized views to the same source table, each with different WHERE clauses to route data to specialized target tables:

-- Route click events to a click-specific analytics table
CREATE MATERIALIZED VIEW clicks_mv
TO click_analytics AS
SELECT toDate(event_time) AS day, page_url, count() AS clicks
FROM events_raw WHERE event_type = 'click'
GROUP BY day, page_url;

-- Route scroll events to engagement tracking
CREATE MATERIALIZED VIEW scrolls_mv
TO engagement_analytics AS
SELECT toDate(event_time) AS day, page_url,
       avg(scroll_depth) AS avg_scroll
FROM events_raw WHERE event_type = 'scroll'
GROUP BY day, page_url;

Projections as an Alternative

ClickHouse projections offer an alternative to materialized views for certain use cases. A projection is a pre-sorted, pre-aggregated representation of data stored within the same table. ClickHouse automatically selects the optimal projection at query time:

ALTER TABLE events_raw
ADD PROJECTION daily_country_agg (
    SELECT
        toDate(event_time) AS day,
        country_code,
        count() AS views,
        uniq(user_id) AS unique_users
    GROUP BY day, country_code
);

Projections are simpler to manage (no separate target table) but less flexible than materialized views. They cannot target a different table engine, cannot chain, and are always co-located with the source data. Use projections for query acceleration on a single table; use materialized views for building a derived data architecture.

ClickHouse materialized views provide a powerful primitive for building real-time analytics pipelines that scale to millions of events per second. By combining AggregatingMergeTree for correct incremental aggregation, Kafka integration for streaming ingestion, and multi-stage view chains for layered architecture, you can build systems that deliver sub-second query performance over continuously updating datasets. The key is designing your view topology thoughtfully: keep individual views simple, choose the right table engine for each aggregation level, and invest in operational monitoring to catch failures before they impact your analytics consumers.