ClickHouse Materialized Views: triggers on INSERT

Materialized Views in ClickHouse: The Power of Incremental Processing

1. How MV Works in ClickHouse — It's an INSERT Trigger, Not a SELECT Cache

In familiar databases (PostgreSQL, Oracle), a materialized view (MV) is the result of a SELECT that is stored on disk and updated on a schedule (REFRESH). You decide when to recalculate it.

In ClickHouse, it's completely different. Here, an MV is an INSERT trigger. You create an MV based on a SELECT, and when you insert data into the source table, ClickHouse automatically executes that SELECT over the inserted rows and inserts the result into the target table.

Key difference:

• In PostgreSQL: you update the MV manually or on a schedule, reading the ENTIRE source table.
• In ClickHouse: the MV fires on each insert, processing ONLY the new rows.

Real-life analogy: A regular VIEW is like glasses with magnifying lenses. You look at the source data through them, recalculating the result each time. A materialized view in ClickHouse is like a secretary sitting nearby who, every time a new document arrives, copies it into a special filing cabinet in the required format. You don't need to flip through all the documents each time; you just go to the filing cabinet.

Why this matters: ClickHouse has no triggers in the classic sense. MV is the only built-in mechanism for reacting to data insertion. With it, you can:

• Aggregate data on the fly (hourly totals).
• Denormalize data (look up sport names from a dictionary).
• Distribute the same raw data to multiple tables with different structures (aggregates, deduplication, full archive).

2. Simple MV: Summing Bets by Hour

Let's start with the most common scenario: you have a bets table with bets, and you need hourly statistics by sport.

Step 1: Create the target table (where data will be aggregated)

CREATE TABLE hourly_sport_stats
(
    hour        DateTime,           -- Start of hour (2025-06-01 14:00:00)
    sport_id    UInt8,              -- Sport type
    bets_count  UInt64,             -- Number of bets per hour
    total_amount Decimal(18,2)      -- Total bet amount
)
ENGINE = SummingMergeTree()         -- Will sum by hour + sport_id
ORDER BY (hour, sport_id);

Step 2: Create the MV that will populate this table

CREATE MATERIALIZED VIEW mv_hourly_stats TO hourly_sport_stats AS
SELECT 
    toStartOfHour(created_at) AS hour,   -- Round time to start of hour
    sport_id,
    count() AS bets_count,                -- Number of bets in group
    sum(amount) AS total_amount           -- Sum of bets in group
FROM bets
GROUP BY hour, sport_id;

What happens line by line:

• TO hourly_sport_stats — where to insert the result. You can omit it if the MV structure matches the SELECT structure, but it's better to specify explicitly.
• AS SELECT ... — the query executed over each inserted batch of data in the bets table. Not over the entire table, only over new rows.
• GROUP BY hour, sport_id — aggregation within the batch. If one insert has 1000 rows for the same hour, the MV calculates one summary row for that hour.

How to use it:

-- Insert 100 bets for 14:00 and 50 for 15:00
INSERT INTO bets (created_at, sport_id, amount) VALUES 
    ('2025-06-01 14:15:00', 1, 100),
    ('2025-06-01 14:30:00', 1, 200),
    ('2025-06-01 15:00:00', 2, 50),
    ... (147 more rows) ...;

-- MV automatically adds or updates rows in hourly_sport_stats
-- For hour 14:00, sport_id=1: bets_count increases by the number of bets from the insert
-- For hour 15:00, sport_id=2: similarly

-- Now read aggregates instantly, without GROUP BY!
SELECT * FROM hourly_sport_stats 
WHERE hour = '2025-06-01 14:00:00';

Important limitation: MV in ClickHouse cannot update existing rows in the target table on repeated inserts for the same hour. But since we use SummingMergeTree, during background merges rows with the same ORDER BY (hour, sport_id) will be summed. Therefore, SELECT * FROM hourly_sport_stats may return multiple rows per group — always wrap in SUM with GROUP BY, as with SummingMergeTree.

3. MV with AggregatingMergeTree — For Complex Aggregations

If you need not only sums but also, for example, unique users (uniq), average bets (avg), you need to use AggregatingMergeTree and *State / *Merge functions.

-- Target table with AggregateFunction columns
CREATE TABLE hourly_sport_advanced
(
    hour            DateTime,
    sport_id        UInt8,
    bets_count      AggregateFunction(count, UInt64),      -- State for count
    total_amount    AggregateFunction(sum, Decimal(18,2)), -- State for sum
    unique_users    AggregateFunction(uniq, UInt64),       -- State for uniq
    avg_amount      AggregateFunction(avg, Decimal(18,2))  -- State for avg
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);

-- MV with *State functions
CREATE MATERIALIZED VIEW mv_hourly_advanced TO hourly_sport_advanced AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    countState(user_id) AS bets_count,        -- Not just count, but countState
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users,       -- Approximate uniq
    avgState(amount) AS avg_amount
FROM bets
GROUP BY hour, sport_id;

Why this way: AggregatingMergeTree stores not final values but intermediate states. This allows correctly merging aggregations from different inserts (e.g., from two batches for the same hour). uniqState stores a hash table of unique values, not just a number.

Reading data:

SELECT 
    hour,
    sport_id,
    countMerge(bets_count) AS bets_count,
    sumMerge(total_amount) AS total_amount,
    uniqMerge(unique_users) AS unique_users,
    avgMerge(avg_amount) AS avg_amount
FROM hourly_sport_advanced
GROUP BY hour, sport_id;

4. MV with SummingMergeTree for Simple Counters

For simple sums and counters, SummingMergeTree is simpler and faster than AggregatingMergeTree. The example from Section 2 is ideal for this.

Another example — counters by user:

-- Target table: how many bets each user made per day
CREATE TABLE user_daily_counts
(
    user_id     UInt64,
    day         Date,
    bets_count  UInt64,        -- will be summed
    total_amount Decimal(18,2) -- will be summed
)
ENGINE = SummingMergeTree()
ORDER BY (user_id, day);

-- MV
CREATE MATERIALIZED VIEW mv_user_daily TO user_daily_counts AS
SELECT 
    user_id,
    toDate(created_at) AS day,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM bets
GROUP BY user_id, day;

Advantage of SummingMergeTree: Simplicity, no need for *Merge functions when reading. A simple SUM with GROUP BY is enough for safety (if data hasn't merged).

Disadvantage: Only sums and counters. No unique users, max, average.

5. MV Chain: Event → Minute Aggregation → Hourly → Daily

This is a powerful pattern for gradually reducing granularity. Instead of aggregating from raw data directly into daily statistics (which would require recalculating billions of rows), you build a chain.

Schema:

• Raw bets (table raw_bets) — store 7 days.
• Minute aggregation (table minute_stats) — store 30 days.
• Hourly aggregation (table hourly_stats) — store 365 days.
• Daily aggregation (table daily_stats) — store 5 years.

-- Level 1: minute aggregation from raw data
CREATE TABLE minute_stats
(
    minute      DateTime,      -- rounded to minute
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (minute, sport_id);

CREATE MATERIALIZED VIEW mv_minute_from_raw TO minute_stats AS
SELECT 
    toStartOfMinute(created_at) AS minute,
    sport_id,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM raw_bets
GROUP BY minute, sport_id;

-- Level 2: hourly aggregation from minute
CREATE TABLE hourly_stats
(
    hour        DateTime,
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id);

CREATE MATERIALIZED VIEW mv_hourly_from_minute TO hourly_stats AS
SELECT 
    toStartOfHour(minute) AS hour,   -- round minute to hour
    sport_id,
    sum(bets_count) AS bets_count,    -- sum minute counters
    sum(total_amount) AS total_amount
FROM minute_stats
GROUP BY hour, sport_id;

-- Level 3: daily aggregation from hourly
CREATE TABLE daily_stats
(
    day         Date,
    sport_id    UInt8,
    bets_count  UInt64,
    total_amount Decimal(18,2)
)
ENGINE = SummingMergeTree()
ORDER BY (day, sport_id);

CREATE MATERIALIZED VIEW mv_daily_from_hourly TO daily_stats AS
SELECT 
    toDate(hour) AS day,
    sport_id,
    sum(bets_count) AS bets_count,
    sum(total_amount) AS total_amount
FROM hourly_stats
GROUP BY day, sport_id;

Advantages of the chain:

• Each level works with a smaller volume of data.
• You can delete old data from lower levels (TTL) and store aggregates for years.
• When querying a monthly report, you read daily_stats (365 rows per year), not raw_bets (billions).

Analogy: It's like retelling a book's plot: first you read the book (raw data), then write a chapter summary (minute), then a part summary (hourly), then an annotation (daily). To recall the main idea, you don't need to reread the entire book.

6. Null + MV Pattern for Kafka: Distribute Data to Multiple Tables

This is a professional pattern for high-load systems. Instead of writing to one table, you create a Null table (black hole) and several MVs that read from it and write to different target tables.

-- Step 1: Receiver table (Null, stores nothing)
CREATE TABLE raw_events_null
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = Null;

-- Step 2: Target table 1 — all raw events (for investigations)
CREATE TABLE raw_events_archive
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, user_id);

-- Step 3: Target table 2 — deduplicated bets (without IP for GDPR)
CREATE TABLE bets_dedup
(
    user_id     UInt64,
    sport_id    UInt8,
    amount      Decimal(18,2),
    created_at  DateTime,
    bet_id      String
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id);

-- Step 4: Target table 3 — hourly aggregates
CREATE TABLE hourly_agg
(
    hour        DateTime,
    sport_id    UInt8,
    total_amount AggregateFunction(sum, Decimal(18,2))
)
ENGINE = AggregatingMergeTree()
ORDER BY (hour, sport_id);

-- Step 5: MV — raw archive (all rows)
CREATE MATERIALIZED VIEW mv_archive TO raw_events_archive AS
SELECT * FROM raw_events_null;

-- Step 6: MV — deduplicated bets (generate bet_id on the fly)
CREATE MATERIALIZED VIEW mv_dedup TO bets_dedup AS
SELECT 
    user_id,
    sport_id,
    amount,
    created_at,
    concat(toString(user_id), '_', toString(sipHash64(created_at))) AS bet_id
FROM raw_events_null
WHERE amount != 0;

-- Step 7: MV — hourly aggregates
CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    sumState(amount) AS total_amount
FROM raw_events_null
GROUP BY hour, sport_id;

How it works in practice:

-- Kafka consumer inserts into raw_events_null (fast, no disk operations)
INSERT INTO raw_events_null VALUES (123, 1, 100.00, now(), '192.168.1.1');

-- Three MVs in parallel receive this data and write to their tables:
-- 1. mv_archive → raw_events_archive (full copy, including IP)
-- 2. mv_dedup → bets_dedup (deduplicated bets, without IP)
-- 3. mv_hourly_agg → hourly_agg (updates aggregates)

Why this is brilliant:

• One insert — three different transformations.
• The source table stores nothing (Null), no write overhead.
• Each MV can be enabled/disabled independently.
• Easy to add a fourth MV (e.g., for export to another format).

7. The Problem: MV Only Processes New Data After Creation

A critical limitation: MV does not see data that was already in the source table at the time of its creation. MV starts working only from the moment of creation and processes only new INSERTs.

-- We already have a billion rows in bets
SELECT count() FROM bets;  -- 1,000,000,000

-- Create MV
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats AS ...;

-- Insert a new bet
INSERT INTO bets VALUES (now(), 1, 100);

-- Only this new bet will appear in hourly_stats.
-- The previous billion remain unprocessed!

How to solve the problem — backfilling historical data:

-- Method 1: Manual INSERT using the same SELECT as in the MV
INSERT INTO hourly_stats
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    count() AS bets_count,
    sum(amount) AS total_amount
FROM bets
WHERE created_at < '2025-06-01'   -- old data
GROUP BY hour, sport_id;

-- Now hourly_stats contains both old and new data

Method 2: Recreate MV with POPULATE (dangerous!)

-- DANGEROUS: with CREATE ... POPULATE, MV processes the ENTIRE existing table
-- During processing, the table may be locked, and data may be lost
CREATE MATERIALIZED VIEW mv_hourly TO hourly_stats POPULATE AS
SELECT ... FROM bets ...;

Why POPULATE is dangerous in production:

• During the SELECT execution, the source table may be locked.
• If new data is inserted into the source table during POPULATE, it may be lost (not captured by either the old or new MV).
• On large tables, POPULATE can take hours.

Correct approach: Create the MV without POPULATE, and backfill historical data manually via INSERT with the same SELECT as in the MV. This is safe, predictable, and does not block inserts.

8. Monitoring and Debugging MVs

Check if MVs are active

-- List all MVs and their target tables
SELECT 
    name,
    engine,
    total_rows,
    formatReadableSize(total_bytes) AS size
FROM system.tables
WHERE engine = 'MaterializedView'
AND database = 'default';

Check if MV fires on INSERT

-- Enable detailed log (debug only)
SET log_queries = 1;

-- Insert one row into source table
INSERT INTO bets (created_at, sport_id, amount) VALUES (now(), 1, 100);

-- Find queries related to MV
SELECT 
    query,
    query_duration_ms,
    read_rows,
    written_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query LIKE '%MV%'
  AND event_time > now() - INTERVAL 1 MINUTE
ORDER BY event_time DESC;

Check data in target table

-- Compare number of unique groups in source and target tables
-- (should match after backfill)

-- Source: how many unique (hour, sport)
SELECT count(*) FROM (
    SELECT toStartOfHour(created_at) AS hour, sport_id 
    FROM bets 
    GROUP BY hour, sport_id
) AS src;

-- Target:
SELECT count(*) FROM hourly_stats;

Identify TYPE issues

If MV doesn't fire, often the problem is type incompatibility:

-- Error: Column `sport_id` type UInt8 in source table, but Int32 in MV target
-- Solution: explicitly cast types in SELECT
SELECT 
    toStartOfHour(created_at) AS hour,
    toUInt8(sport_id) AS sport_id,   -- explicit cast
    count() AS bets_count
FROM bets
GROUP BY hour, sport_id;

9. Real Schema for a Gambling Platform

Let's assemble a complete architecture for an online casino with multiple requirements:

• Detailed events for investigations (store 7 days).
• Deduplicated bets for analytics (store 90 days).
• Hourly aggregates for dashboard (store 2 years).
• Daily aggregates for financial reports (store 10 years).

-- ===== LEVEL 0: Receiver (Null) =====
CREATE TABLE raw_stream
(
    user_id     UInt64,
    sport_id    UInt8,
    bet_id      String,
    amount      Decimal(18,2),
    created_at  DateTime,
    ip          String
)
ENGINE = Null;

-- ===== TARGET TABLES =====
-- 1. Archive for 7 days (MergeTree + TTL)
CREATE TABLE raw_archive
(
    user_id UInt64, sport_id UInt8, bet_id String,
    amount Decimal(18,2), created_at DateTime, ip String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)
ORDER BY created_at
TTL created_at + INTERVAL 7 DAY DELETE;

-- 2. Deduplicated bets (ReplacingMergeTree)
CREATE TABLE bets_dedup
(
    user_id UInt64, sport_id UInt8, bet_id String,
    amount Decimal(18,2), created_at DateTime
)
ENGINE = ReplacingMergeTree(created_at)
ORDER BY (user_id, bet_id)
TTL created_at + INTERVAL 90 DAY DELETE;

-- 3. Hourly aggregates (SummingMergeTree)
CREATE TABLE hourly_agg
(
    hour DateTime, sport_id UInt8, total_amount Decimal(18,2), bet_count UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (hour, sport_id)
TTL hour + INTERVAL 2 YEAR DELETE;

-- 4. Daily aggregates (AggregatingMergeTree for uniq)
CREATE TABLE daily_agg
(
    day Date, sport_id UInt8, total_amount AggregateFunction(sum, Decimal(18,2)),
    unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (day, sport_id)
TTL day + INTERVAL 10 YEAR DELETE;

-- ===== MATERIALIZED VIEWS =====
CREATE MATERIALIZED VIEW mv_raw_archive TO raw_archive AS
SELECT * FROM raw_stream;

CREATE MATERIALIZED VIEW mv_bets_dedup TO bets_dedup AS
SELECT user_id, sport_id, bet_id, amount, created_at
FROM raw_stream
WHERE bet_id != '';

CREATE MATERIALIZED VIEW mv_hourly_agg TO hourly_agg AS
SELECT 
    toStartOfHour(created_at) AS hour,
    sport_id,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM raw_stream
GROUP BY hour, sport_id;

CREATE MATERIALIZED VIEW mv_daily_agg TO daily_agg AS
SELECT 
    toDate(created_at) AS day,
    sport_id,
    sumState(amount) AS total_amount,
    uniqState(user_id) AS unique_users
FROM raw_stream
GROUP BY day, sport_id;

Data flow:

1. Application writes to raw_stream (Null) — milliseconds.
2. Four MVs process each insert in parallel.
3. Each target table receives data in its aggregated/transformed form.
4. TTL automatically deletes outdated data at each level.

Advantages over a single MERGETREE:

• Maximum insert speed (Null + MV work in memory).
• Different storage levels (7 days, 90 days, 2 years, 10 years).
• Different engines for different tasks (MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree).
• No performance loss when querying old aggregates.

What's Next

Now you know how to build complex data processing pipelines using MVs. Next topics:

• MV with external source updates — how to combine dictionaries and MVs for enrichment.
• Debugging complex MV chains — how to understand why data didn't reach the target table.
• MV replication in a cluster — how MVs behave on distributed tables.

Summary: Materialized views in ClickHouse are not just a cache. They are a powerful incremental data processing mechanism that allows building real event-sourcing architectures inside the database. The rule is simple: if you need to aggregate, transform, or duplicate data on insert — use MV. And never forget to backfill historical data manually, not via POPULATE in production.

---

← Previous: Special ClickHouse Engines: When MergeTree Doesn't Fit
→ Next: Secondary (Skipping) Indexes in ClickHouse: When ORDER BY Index Is Not Enough

— Editorial Team