Typorithm · hard

Materialized View

materialized-viewclickhouseincrementalinsert-trigger

Saved on this device

Full lesson · 8 learning steps Open any exercise for focused practice

Step 1 of 8 · intro card

Learn Materialized View

Aggregation Engines

A materialized view runs its SELECT on every insert into the source table, writing the aggregated result into the TO target — an incrementally maintained rollup.

A ClickHouse materialized view is not a cached query; it is an insert trigger attached to a source table.

On each insert into the source, the view runs its SELECT (often a GROUP BY aggregate) over just the new block and writes the result to the TO target table.

Because only the new block is processed, the target rollup is maintained incrementally — pair it with SummingMergeTree or AggregatingMergeTree to fold the per-block results.

MV is an insert trigger on the source table.

TO writes results into a target table.

Each insert is aggregated incrementally.

CREATE MATERIALIZED VIEW daily_revenue_mv
TO daily_revenue
AS
SELECT
    toDate(event_time) AS event_date,
    product_id,
    sum(amount)        AS revenue,
    count()            AS orders
FROM raw_orders
GROUP BY event_date, product_id;

Read the implementation

Connect each part of the code to the concept before you type.

1. Define the view

A materialized view is an insert trigger that runs its SELECT on each new block.

CREATE MATERIALIZED VIEW daily_revenue_mv

2. Target table

TO routes the view's output into an existing target table.

TO daily_revenue

3. Source table

The view fires on each insert into the source table named in FROM.

FROM raw_orders

4. Incremental aggregate

Each inserted block is aggregated and written, maintaining the rollup incrementally.

GROUP BY event_date, product_id

A complete learning loop

Understand

Read the concept map and follow the code breakdown.

Reconstruct

Type the implementation, repair mistakes and work through every recall exercise.

Verify

Use the final check without hints; revisit weak concepts in short practice sessions.