Materialized Views

The secret weapon for real-time aggregation. Pre-compute results as data arrives — your dashboards will load in milliseconds.

🤔 What is a Materialized View?

🧒 ELI5: The Score Keeper

Imagine a basketball game. Instead of counting all baskets at the end (slow!), you have a scorekeeper who updates the score after every basket in real-time. At any moment, you can glance at the scoreboard and see the current score instantly.

A materialized view IS that scorekeeper. Every time new data is inserted, it automatically updates a pre-computed summary table. When you query the summary, it's instant because the work was already done!

🛠️ Creating a Materialized View

-- Step 1: Create the destination (summary) table
CREATE TABLE hourly_stats (
    hour DateTime,
    country LowCardinality(String),
    events UInt64,
    unique_users AggregateFunction(uniq, UInt64),
    total_revenue Float64
) ENGINE = AggregatingMergeTree()
ORDER BY (hour, country);

-- Step 2: Create the materialized view
CREATE MATERIALIZED VIEW hourly_stats_mv
TO hourly_stats
AS SELECT
    toStartOfHour(event_time) AS hour,
    country,
    count() AS events,
    uniqState(user_id) AS unique_users,
    sum(revenue) AS total_revenue
FROM events
GROUP BY hour, country;

-- Step 3: Query the summary (instant!)
SELECT
    hour,
    country,
    events,
    uniqMerge(unique_users) AS unique_users,
    total_revenue
FROM hourly_stats
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY hour, country, events, total_revenue
ORDER BY hour DESC;

🎯 Common Patterns

Pattern 1: Real-Time Dashboard

Pre-aggregate metrics by minute/hour for instant dashboard loading.

Pattern 2: Deduplication Pipeline

Use a materialized view with ReplacingMergeTree to deduplicate streaming data.

Pattern 3: Data Transformation

Transform raw JSON/CSV data into structured columns as it arrives.

🌍 Real World: Uber's Architecture

Uber uses materialized views in ClickHouse to power their real-time trip analytics dashboard. Raw trip events flow in at millions per minute, but the dashboard queries a pre-aggregated summary table that returns results in under 50ms.

⚠️ Gotchas

Materialized views only process NEW inserts — they don't backfill existing data
If the MV query fails, the original INSERT still succeeds (data isn't lost)
Use -State and -Merge function pairs for correct aggregation

Performance Replication & Sharding

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

A normal view stores no data. An incremental materialized view writes each insert block into a target MergeTree. It does not see updates you make with ALTER. A refreshable materialized view reruns a query on a schedule. The REFRESH clause needs EVERY, AFTER, or DEPENDS ON. Do not POPULATE a target that the first refresh also fills, or you double the rows. EMPTY skips that first refresh. On a replicated target, Keeper coordinates the refresh.

CREATE MATERIALIZED VIEW daily_totals_mv
REFRESH EVERY 1 HOUR
TO daily_totals
AS
SELECT toDate(event_time) AS day, count() AS n
FROM events
GROUP BY day

Knowledge check

What does a refreshable materialized view do?