ClickHouse Cheatsheet 18 — Use Cases

ClickHouse use cases.

1. Product analytics

Event tracking. Schemas:

CREATE TABLE events (
    ts DateTime,
    user_id UInt32,
    session_id String,
    event LowCardinality(String),
    properties Map(String, String)
) ENGINE = MergeTree() ORDER BY (ts, user_id);

Funnels, retention, cohorts.

2. Log analytics (Loki alternative)

CREATE TABLE logs (
    ts DateTime64(3),
    level LowCardinality(String),
    service LowCardinality(String),
    msg String,
    fields Map(String, String)
) ENGINE = MergeTree() ORDER BY (service, ts) TTL ts + INTERVAL 30 DAY;

Grep-like queries fast.

3. Metrics

CREATE TABLE metrics (
    ts DateTime,
    name LowCardinality(String),
    labels Map(String, String),
    value Float64
) ENGINE = MergeTree() ORDER BY (name, ts);

Prometheus alternative at huge scale.

4. Click streams

Tracking clicks, ad impressions, recommendations.

5. Network telemetry

Pcap data, NetFlow analysis.

6. Financial / trading

Tick data, latency-sensitive aggregations.

7. IoT / sensors

CREATE TABLE sensor_data (
    ts DateTime CODEC(DoubleDelta, LZ4),
    sensor_id UInt32,
    temp Float64 CODEC(Gorilla, LZ4)
) ENGINE = MergeTree() ORDER BY (sensor_id, ts);

Compressed time-series.

8. Data warehouse

Hold all historic data, query at scale.

9. Business intelligence

OLAP cubes via materialized views.

10. Real-time dashboards

Streaming inserts + live queries.

11. Cohort / retention

SELECT
    toStartOfMonth(first_seen) AS cohort,
    toStartOfMonth(ts) AS month,
    count(DISTINCT user_id) AS active
FROM events_with_cohort
GROUP BY cohort, month;

12. A/B test analysis

Mass aggregations on experiment buckets.

13. SaaS multi-tenant analytics

Embedded analytics per tenant.

14. Customer 360

SELECT user_id, argMax(name, ts), argMax(email, ts)
FROM customer_events GROUP BY user_id;

15. Search / facets

Not a search engine but can serve faceted queries.

16. Fraud detection

Window functions + aggregations on transactions.

17. Telecom CDR

Call records analytics.

18. Game analytics

Player events, monetization.

19. Real-time recommendations

Aggregated user signals.

20. Compliance / audit

Long retention with low cost.

Read this next

• Cheatsheet 19 — Common Pitfalls

If you want my CH templates per use case, they’re at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .