[hypequery](https://hypequery.com/) › [Examples](https://hypequery.com/examples) › ClickHouse materialized view rollups: incremental vs refreshable
Index for agents: https://hypequery.com/llms.txt

# ClickHouse materialized view rollups: incremental vs refreshable (https://hypequery.com/examples/materialized-view-rollups)

Pre-aggregate a million events two ways, incremental and refreshable, and query both through one typed hypequery dataset.

## Run it

```bash
npx @hypequery/cli create materialized-view-rollups -y
cd materialized-view-rollups
pnpm install
pnpm dev
```

- Packages: @hypequery/datasets, @hypequery/clickhouse (tested from hypequery 2.14.0)
- Stack: node, clickhouse
- Source: https://github.com/hypequery/hypequery/tree/main/examples/materialized-view-rollups
- Test: `pnpm test`
- Verify: `node dist/query.js --json`

| Env var | Required | Description |
| --- | --- | --- |
| CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) |
| CLICKHOUSE_USERNAME | no | ClickHouse user (default: default) |
| CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) |
| CLICKHOUSE_DATABASE | no | Database holding the seed tables (default hypequery_examples) |

## Demo

```text
Events per day, 2026-10-25 to 2026-10-31

Raw events table (62 ms):            Incremental rollup, events_daily (20 ms):
period      events  uniqueUsers       period      events  uniqueUsers
2026-10-26  5551    1186              2026-10-26  5551    1186
2026-10-27  5548    1175              2026-10-27  5548    1175
...                                   ...

Refreshable rollup by plan, tenant_daily_summary (13 ms):
plan        events  errorRate  worstP95LatencyMs
enterprise  26424   0.0244     3057
growth      5175    0.0230     2723
```

Same numbers, three sources. The rollups answer in a fraction of the time and the
smoke test proves they match hand-written SQL over the raw table exactly.

## Install

```bash
npx @hypequery/cli create materialized-view-rollups -y
cd materialized-view-rollups
docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed)   # or point env vars at your own ClickHouse
pnpm install
pnpm dev
```

`pnpm dev` runs `sql/*.sql` to create both rollups over the seed's `events`
table, waits for the first refresh, then prints the comparison above.

## What it shows

- **An incremental materialized view** (`sql/01-incremental.sql`): an INSERT trigger that writes aggregate states into an `AggregatingMergeTree`, backfilled with `INSERT … SELECT`, never `POPULATE`.
- **A refreshable materialized view** (`sql/02-refreshable.sql`): a full recompute every hour, with a `JOIN` to `tenants` and an exact p95, which an incremental view cannot do cheaply.
- **One dataset per target** (`src/datasets.ts`): `measure.merge('uniqExact', 'users')` finalizes the stored state, so distinct users stay exact whether you ask by day, week or month.
- **The same semantic query against raw and rolled-up data** (`src/query.ts`): identical results, with timings from `result.meta`.
- **A smoke test as ground truth** (`src/smoke.test.ts`): compares every rollup against raw SQL over all one million rows.

## Key file

`src/datasets.ts` defines three datasets over the same facts: the raw table, the
incremental target and the refreshable target. Only the source and the way
uniques are read differ.

```ts
export const EventsDaily = dataset('eventsDaily', {
  source: 'events_daily',
  timeKey: 'day',
  timeGrains: ['day', 'week', 'month', 'quarter', 'year'],
  dimensions: {
    day: dimension.timestamp(),
    tenantId: dimension.number({ column: 'tenant_id' }),
    eventType: dimension.string({ column: 'event_type' }),
  },
  measures: {
    events: measure.sum('events'),                      // SimpleAggregateFunction(sum)
    errors: measure.sum('errors'),
    uniqueUsers: measure.merge('uniqExact', 'users'),   // uniqExactMerge(users)
    errorRate: measure.derived({
      uses: { errors: 'errors', events: 'events' },
      formula: ({ errors, events }) => divide(errors, nullIfZero(events)),
    }),
  },
});
```

## Architecture

```text
events (1M rows, seed)
  ├─ events_daily_mv  ──INSERT trigger──▶  events_daily (AggregatingMergeTree, states)
  │                                           └─ dataset EventsDaily  ── measure.merge
  └─ tenant_daily_summary_rmv ──REFRESH EVERY 1 HOUR──▶ tenant_daily_summary (MergeTree, values)
       JOIN tenants                                       └─ dataset TenantDailySummary
```

`analytics.execute(dataset, { by: 'day', measures: [...] })` is the same call
for all three datasets. hypequery generates `sum(...)`, `uniqExactMerge(...)`
or `count()` as the source requires.

## Adapt it

- **Pick the view type by question.** Freshness in seconds and a single source table: incremental. Joins, exact percentiles, top-N or anything you want to recompute wholesale: refreshable. Many teams run both over the same facts, as here.
- **Watch the trigger fire.** Insert a row into `events` and the incremental target updates on the same insert; `tenant_daily_summary` changes only after `SYSTEM REFRESH VIEW tenant_daily_summary_rmv`. Clean up with `ALTER TABLE events DELETE WHERE event_id > 1000000` so the seed checksum still matches.
- **Approximate uniques are fine for most dashboards.** Swap `uniqExactState`/`uniqExact` for `uniqState`/`uniq` to shrink the target table; `measure.merge('uniq', 'users')` reads it and the catalog marks the measure approximate.
- **Scheduling.** `REFRESH EVERY 1 HOUR` can become `REFRESH EVERY 1 DAY OFFSET 2 HOUR`, or `REFRESH AFTER 10 MINUTE` for an interval since the last run. `DEPENDS ON` chains refreshable views.
- **Tenant scoping.** Add `tenantKey: 'tenant_id'` to any of the datasets to require a tenant in every query. See the multi-tenant example.

## Related recipes

- p95 latency (coming in this series)

## Related examples

- Next.js dashboard (coming in this series)

Tested on ClickHouse 25.8 with hypequery `@hypequery/datasets` 0.29 and `@hypequery/clickhouse` 2.14.

## Key file: src/datasets.ts

```typescript
import { dataset, dimension, divide, gte, measure, nullIfZero } from '@hypequery/datasets';

const DAY_GRAINS = ['day', 'week', 'month', 'quarter', 'year'] as const;

/** The raw fact table: a million rows, aggregated at query time. */
export const Events = dataset('events', {
  source: 'events',
  timeKey: 'occurred_at',
  dimensions: {
    eventId: dimension.number({ column: 'event_id' }),
    occurredAt: dimension.timestamp({ column: 'occurred_at' }),
    tenantId: dimension.number({ column: 'tenant_id' }),
    userId: dimension.number({ column: 'user_id' }),
    eventType: dimension.string({ column: 'event_type' }),
    statusCode: dimension.number({ column: 'status_code' }),
  },
  measures: {
    events: measure.count('eventId'),
    uniqueUsers: measure.countDistinct('userId'),
    errors: measure.count('statusCode', { filters: [gte('statusCode', 500)] }),
    errorRate: measure.derived({
      uses: { errors: 'errors', events: 'events' },
      formula: ({ errors, events }) => divide(errors, nullIfZero(events)),
    }),
  },
});

/**
 * Incremental rollup (sql/01-incremental.sql). Reads aggregate states, so
 * `measure.merge` applies `uniqExactMerge` and uniques stay exact at any grain.
 */
export const EventsDaily = dataset('eventsDaily', {
  source: 'events_daily',
  timeKey: 'day',
  timeGrains: [...DAY_GRAINS],
  dimensions: {
    day: dimension.timestamp(),
    tenantId: dimension.number({ column: 'tenant_id' }),
    eventType: dimension.string({ column: 'event_type' }),
  },
  measures: {
    events: measure.sum('events'),
    errors: measure.sum('errors'),
    uniqueUsers: measure.merge('uniqExact', 'users'),
    errorRate: measure.derived({
      uses: { errors: 'errors', events: 'events' },
      formula: ({ errors, events }) => divide(errors, nullIfZero(events)),
    }),
  },
});

/**
 * Refreshable rollup (sql/02-refreshable.sql). Final values per tenant-day,
 * including a JOIN to `tenants` and an exact p95, recomputed every hour.
 */
export const TenantDailySummary = dataset('tenantDailySummary', {
  source: 'tenant_daily_summary',
  timeKey: 'day',
  timeGrains: [...DAY_GRAINS],
  dimensions: {
    day: dimension.timestamp(),
    tenantId: dimension.number({ column: 'tenant_id' }),
    tenantName: dimension.string({ column: 'tenant_name' }),
    plan: dimension.string(),
  },
  measures: {
    events: measure.sum('events'),
    errors: measure.sum('errors'),
    worstP95LatencyMs: measure.max('p95_latency_ms', { label: 'Worst daily p95 latency (ms)' }),
    errorRate: measure.derived({
      uses: { errors: 'errors', events: 'events' },
      formula: ({ errors, events }) => divide(errors, nullIfZero(events)),
    }),
  },
});
```

## Related

- [All examples](https://hypequery.com/examples)
- [/clickhouse-typescript](https://hypequery.com/clickhouse-typescript)
- [/clickhouse-nodejs](https://hypequery.com/clickhouse-nodejs)
- [/clickhouse-time-series](https://hypequery.com/clickhouse-time-series)
- [/clickhouse-semantic-layer](https://hypequery.com/clickhouse-semantic-layer)