Real-time analytics, end to end.

One minute of this pipeline: every source arrives in batches, and each batch becomes a new part.

Live events (Kafka)

A batch every 7.5 s by default (stream_flush_interval_ms); on ClickHouse Cloud, ClickPipes batches for you.

Database changes (PostgreSQL)

ClickPipes (PeerDB) copies the table once, then adds each change as a new row, every 60 s by default.

History files (S3)

Loaded once with INSERT INTO events SELECT … FROM s3(…), a few days per insert: one insert may touch at most 100 partitions.

Small app writes

Asynchronous inserts batch them on the server, on by default since 26.2 (2026-02) (Guide 03 ↗).

Each table engine decides what a background merge does with rows that share a sorting key. Until merges run, read with FINAL or aggregate (Guide 05 ↗).

What counts as a duplicate

Rows combine only when their whole ORDER BY matches, so tenants is sorted by its key alone.

Other engines

AggregatingMergeTree for distinct counts and percentiles, CollapsingMergeTree (both in Guide 02 ↗); Null stores nothing but still feeds views.

Fixing a few rows

UPDATE writes a small patch part when the table has enable_block_number_column and enable_block_offset_column.

A materialized view runs on every insert, so the hourly totals are ready as soon as the data lands. The figure sets this against a join refreshed every hour.

The view behind the hourly totals

CREATE TABLE usage_hourly (tenant_id UInt32, hour DateTime, …)ENGINE = SummingMergeTreePARTITION BY toYYYYMM(hour)ORDER BY (tenant_id, hour, device_type) CREATE MATERIALIZED VIEW usage_mv TO usage_hourly ASSELECT tenant_id, toStartOfHour(timestamp) AS hour, device_type,  count() AS events, sum(revenue) AS revenueFROM events GROUP BY tenant_id, hour, device_type

Partial totals add up

SummingMergeTree adds up the partial rows when parts merge, and queries still sum(). For distinct counts, use AggregatingMergeTree.

Joins inside a view

They use the other table as it was at insert time. For the current plan, join when a chart asks, in the metrics layer.

One row per tenant

The figure's second way: two views fill one CoalescingMergeTree row, which keeps each column's latest value.

When a schedule still fits

Work that needs all the data at once, such as rankings: a refreshable view, REFRESH EVERY 1 HOUR.

Backfill

A new view sees only new inserts: fill the past with one INSERT … SELECT per tenant (Guide 06 ↗).

Today's chart for tenant 42 needs at most 120 totals: 24 hours × 5 devices. Each cut in the figure leaves less to read.

Check the cuts

EXPLAIN indexes = 1 prints them: 4 of 7 parts, then 4 of 1,280 granules (Overview ↗).

Pre-summed

Once merged, usage_hourly has at most 720 k rows a day, against 151 M events.

All tenants at once

The figure's projection, by_hour, keeps that rollup inside the table; on SummingMergeTree it needs deduplicate_merge_projection_mode = 'rebuild'.

Repeated charts

The query condition cache is on by default; the query cache is opt-in and keeps results for 60 s (Guide 07 ↗).

The metrics layer turns a chart into SQL, adds the customer's filter and caches answers. system.query_log records how long ClickHouse took.

Charts for customers

Take the tenant from the signed-in user, on the server; never trust a filter sent by the browser.

Dashboards for your team

Give the tool its own database user, with limits. Power BI stays live in DirectQuery mode.

Speed: the slowest charts

SELECT normalized_query_hash, any(query) AS chart,  quantile(0.95)(query_duration_ms) AS p95_ms,  max(read_rows) AS max_rows, count() AS runsFROM system.query_logWHERE type = 'QueryFinish' AND user = 'cube'  AND event_time > now() - INTERVAL 1 HOURGROUP BY normalized_query_hashORDER BY p95_ms DESC LIMIT 10

p95 shows the slow runs an average hides. Cache hits never reach ClickHouse, so also time the page in the browser.

Freshness

A new event reaches a chart in about 18 s (7.5 s batch, then up to 10 s in the cache), a plan change in about 70 s. Alert when now() - max(timestamp) grows past that.