How to model an analytics table in ClickHouse

What and why

ClickHouse is a columnar OLAP database built for fast aggregations over huge datasets. Its MergeTree engine sorts data by an ordering key and stores it in compressed parts, so well-chosen keys and partitions make scans extremely fast. This tutorial models an events table and queries it.

Prerequisites

• A running ClickHouse server reachable via clickhouse-client or HTTP.
• Event or metric data to load.
• Basic SQL knowledge.

Steps

1. Start ClickHouse

docker run -d --name ch -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server

2. Choose an ordering key

The ORDER BY key determines how data is sorted on disk and which queries are fast. Order by the columns you filter and group on most, typically coarse-to-fine, for example (event_date, customer_id).

3. Create a MergeTree table

CREATE TABLE events (
  event_date Date,
  customer_id UInt64,
  event_type LowCardinality(String),
  amount Float64
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, customer_id);

LowCardinality compresses repetitive strings; partitioning by month enables pruning.

4. Load data

Insert in large batches for efficiency, or load files:

INSERT INTO events VALUES ('2026-06-01', 42, 'purchase', 79.99);

ClickHouse rewards big inserts; avoid many tiny ones.

5. Run aggregations

SELECT event_date, sum(amount) AS revenue
FROM events
WHERE event_date >= '2026-06-01'
GROUP BY event_date ORDER BY event_date;

The ordering key and partition pruning keep this fast even on billions of rows.

6. Add a materialized view

Precompute rollups as data arrives:

CREATE MATERIALIZED VIEW events_daily
ENGINE = SummingMergeTree ORDER BY (event_date)
AS SELECT event_date, sum(amount) AS revenue FROM events GROUP BY event_date;

Verification

Run an aggregation with a date filter and confirm it returns quickly; EXPLAIN shows partition pruning. Query the materialized view and confirm it matches the on-the-fly aggregation while scanning far less data.

Next Steps

Use OPTIMIZE and TTL for data lifecycle, choose appropriate codecs per column for better compression, and shard with the Distributed engine for horizontal scale.