Building Real-Time Analytics with ClickHouse

“Real-time analytics” is often used as if it were one feature. In production, it is a chain: the application emits an event, ingestion accepts it, storage makes it queryable, a dashboard refreshes, and an alert reaches a person. The system is only as real time as its slowest stage.

ClickHouse is a strong foundation for this workload because it combines continuous ingestion with fast analytical scans. Building a reliable system still requires decisions about batching, table order, duplicates, late events, and query cost.

Define the freshness target first#

Not every product needs sub-second results.

Use case	Reasonable freshness
Executive KPI dashboard	5–15 minutes
Product operations	10–60 seconds
API error monitoring	Seconds
Automated fraud response	Often sub-second, with a streaming decision system

Tighter targets cost more and complicate the architecture. If a dashboard refreshes every minute, reducing ingestion latency from two seconds to 100 milliseconds produces no visible benefit.

Write the service-level objective as a complete path: “95% of accepted events appear in the dashboard within 20 seconds.” That is more useful than saying the database is real time.

Keep ingestion simple and observable#

ClickHouse performs best when inserts arrive in batches. Tiny synchronous inserts create too many parts and waste merge work. A common pipeline is:

application
  → HTTP collector
  → durable queue or batch buffer
  → ClickHouse
  → dashboards and alerts

The collector should acknowledge only after reaching the durability level your product promises. A fire-and-forget request is fast but can lose data during a restart. A durable queue adds moving parts but isolates producers from database maintenance and traffic spikes.

Track at least:

• accepted events per second
• rejected events and validation reasons
• queue depth and oldest-message age
• insert batch size and latency
• ClickHouse parts and merge pressure
• end-to-end event freshness

Without the last metric, every component can look healthy while the customer sees stale charts.

Design the table around time-range reads#

An event table usually includes a tenant boundary, collection, event timestamp, ingestion timestamp, and payload:

CREATE TABLE events
(
  account_id String,
  collection LowCardinality(String),
  event_timestamp DateTime64(3),
  ingested_at DateTime64(3) DEFAULT now64(3),
  event_id String,
  json String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_timestamp)
ORDER BY (account_id, collection, event_timestamp, event_id);

The exact ordering key depends on your filters. Put high-value equality filters before time when most queries begin with an account and collection. Avoid partitioning by a high-cardinality field such as customer id; too many partitions create operational overhead.

Keep both event time and ingestion time. Event time answers “when did this happen?” Ingestion time answers “when did our analytics system learn about it?” You need both to diagnose delays and handle backfills.

Make retries idempotent#

Networks fail after the server accepts a request but before the client receives the response. A correct client retries, which means duplicates are normal.

Give every event a stable event_id and decide where deduplication happens:

• before insertion in the collector
• with a ClickHouse table engine and ordering strategy
• at query time for the small set of metrics that require exact uniqueness

There is no free universal deduplication setting. Exact query-time deduplication can be expensive; asynchronous table behavior may not remove duplicates immediately. Document the guarantee your users actually receive.

Treat late and out-of-order events as normal#

Mobile clients reconnect. Queues retry. Backfills intentionally send old timestamps. A dashboard that assumes arrival order equals event order will eventually be wrong.

Use event_timestamp for business analysis and ingested_at for operational analysis. Pick a lateness policy for materialized aggregates: can yesterday’s count change today? If yes, queries or rollups must accommodate it. If no, reject or route late events explicitly instead of silently corrupting a closed period.

GraphJSON accepts explicit event timestamps for exactly this reason. The time and time zones guide covers seconds versus milliseconds, UTC, and backfills.

Precompute only the hot path#

ClickHouse materialized views can update aggregate tables as data arrives. They are useful for a small number of high-volume, repeatedly queried metrics:

CREATE MATERIALIZED VIEW requests_per_minute_mv
TO requests_per_minute
AS
SELECT
  account_id,
  toStartOfMinute(event_timestamp) AS minute,
  countState() AS requests
FROM events
WHERE JSONExtractString(json, 'event') = 'api_request'
GROUP BY account_id, minute;

Do not precompute every possible slice. That recreates a rigid cube and makes event evolution painful. Keep raw events available for ad hoc questions, then materialize the queries whose cost and frequency justify it.

Protect ingestion from dashboard queries#

A real-time system must keep accepting events while someone opens a 12-month dashboard.

Use query limits, timeouts, and sensible defaults. Cache repeated embed and dashboard queries. Separate workloads or replicas when scale requires it. Watch memory-heavy group-bys and unbounded high-cardinality dimensions.

The best dashboard query is often not the cleverest SQL. It is the one with a clear time range, a selective tenant filter, and only the columns the chart needs.

Alert on the result and the pipeline#

Business alerts—payment failures, error-rate spikes, missing jobs—are only trustworthy if the ingestion pipeline is healthy. Pair them with system alerts for queue lag, rejected events, and freshness.

Otherwise “zero errors” may mean the product is healthy, or it may mean no events have arrived for ten minutes.

The managed path#

GraphJSON uses ClickHouse for continuously queryable JSON events and supplies the rest of the workflow: HTTP ingestion, collections, exploration, SQL notebooks, dashboards, alerts, and cached embeds.

If you want the product outcome without operating the pipeline, follow the logging quickstart. If you are designing the events themselves, start with event tracking best practices.