Build a columnar OLAP store (ClickHouse / Druid style) (13 scenes)
Scene 11

Materialized views are INSERT triggers

A column-store materialized view is not a periodic refresh — it is a trigger that runs the MV's SELECT against the incoming block only and appends the result to a tiny rollup table, so direct INSERT into the target table silently bypasses it and the rollup drifts.

Previously

Skip indexes prune at read time. The bigger lever is to PRE-COMPUTE the answer at write time — turn a billion-row scan into a two-hundred-row scan by materializing the aggregation as data lands.

Scene 11

Materialized views are INSERT triggers

  1. Watch
  2. Try it
  3. Predict
  4. Capture
SOURCETARGET (rollup)eventsraw rows · wide partsINSERT block · 1.0M rowsMATERIALIZED VIEW · INSERT TRIGGERruns SELECT against incoming block ONLY:
CREATE MATERIALIZED VIEW events_by_country_mv
TO events_by_country_hourly AS
SELECT toStartOfHour(ts) AS hour,
       country,
       count() AS events
FROM events
GROUP BY hour, country;
events_by_country_hourlypre-aggregated · narrow partsINSERT → events fires MV → SELECT over incoming block → appends rollup → target.
What to watch for

An INSERT lands in events. The MV's SELECT runs against the just-arrived 1M-row block and appends ~5 rollup rows to events_by_country_hourly. The dashboard reads the rollup in 2 ms instead of scanning the source for 30 s.

Continue unlocks when the animation finishes.
Implementation

Highlighted lines are the ones running in the diagram right now.

Source.on_insert_to_source
INSERT into source fires every attached MV against the incoming block
def on_insert_to_source(source, block):
    writePart(source, block)            # immutable part on source
    for mv in source.attached_mvs:      # post-INSERT trigger
        rollup = mv.select.run(block)   # block-only, not whole table
        writePart(mv.target, rollup)    # appends to target rollup
Backfill.direct_insert_to_target
writes straight into the target — the trigger never fires
def direct_insert_to_target(target, rows):
    # writes a part on the TARGET table
    writePart(target, rows)
    # nothing inspects target.attached_mvs — MVs trigger off SOURCE
    # rollup now contains rows the source has no record of
    return  # silent drift: target > source
MaterializedView.create
trigger attaches NOW — only future inserts pass through it
def create_mv(source, select_stmt, target, populate=False):
    mv = Mv(select_stmt, target)
    source.attached_mvs.append(mv)  # active from this moment on
    if populate:                    # opt-in one-shot backfill
        mv.select.run(scan(source)) >> writePart(target)
    # rows already in `source` were inserted BEFORE mv existed —
    # they never ran through select_stmt. Backfill by hand:
    #   INSERT INTO target SELECT ... FROM source WHERE ts < cutoff
    return mv
Sources

Where this sits in Build a columnar OLAP store (ClickHouse / Druid style)

Scene 11 of 13, in the Patterns act — Materialized views and the OLAP answer to JOIN — denormalize or dictGet.. A column-store MV is not a refresh — it's a trigger over the incoming block. Direct INSERT into the target silently bypasses it and drifts.

Up next. Materialized views collapse one big scan into a tiny one. But the reader's other OLTP instinct — JOIN across normalized tables — hits a different kind of wall on a column store, and the answer feels wrong.

All 13 scenes in Build a columnar OLAP store (ClickHouse / Druid style) · Every curriculum

Built with Arqly
Every scene in Build a columnar OLAP store (ClickHouse / Druid style) builds on the one before it.All 13 Build a columnar OLAP store (ClickHouse / Druid style) scenes