Batch processing collects data over a window (hourly, nightly, weekly), then transforms and loads it in scheduled jobs. The design is optimized for throughput per job, not for time-to-insight. Every downstream consumer, from fraud rules to executive dashboards, inherits the schedule’s latency.

Real-time analytics inverts the contract. Events are ingested continuously, become queryable within seconds, and are served to many concurrent users and applications against a data set that is always current. The workload profile is different in every dimension: many small inserts instead of a few large loads, continuous background merging instead of a maintenance window, and interactive query concurrency instead of a handful of long-running reports.

The engine you pick therefore has to be built for that profile. Bolting streaming onto a batch warehouse produces micro-batches with warehouse-scale cost and warehouse-scale latency. ClickHouse was designed from the start for continuous ingestion and sub-second aggregation over columnar data, which is why it has become the default open-source engine for real-time analytics.

The single most important decision in a real-time ClickHouse deployment is the ORDER BY key of the raw table, because it defines both the sparse primary index and the physical sort order inside each part. Lead with the low-cardinality columns that every query filters on, then the time column. Partition by a coarse time bucket (day or month) so that TTL moves and drops operate on whole partitions and so that insert traffic does not spray parts across many partitions.

The example below is a minimal but production-shaped pattern: a replicated raw events table, an hourly rollup, and the materialized view that keeps the rollup current on every insert. Engine parameters are explicit by ChistaDATA convention; never rely on defaults in customer-facing schemas.

Freshness is then a function of three things you can measure: Kafka consumer lag, the insert-to-part latency reported in system.part_log, and replication delay in system.replicas. Query latency on the serving path is a function of how much the rollup reduces the scanned row count, visible directly in read_rows in system.query_log.

-- Raw immutable events (ClickHouse 24.x+ syntax)
CREATE TABLE analytics.events_raw
(
    event_time   DateTime64(3, 'UTC'),
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    user_id      UInt64,
    amount       Decimal(18, 4),
    attrs        Map(String, String)
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/analytics/events_raw',
    '{replica}')
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (tenant_id, event_type, event_time)
TTL toDateTime(event_time) + INTERVAL 90 DAY
    TO VOLUME 'cold'
SETTINGS index_granularity = 8192;

-- Hourly rollup served to dashboards
CREATE TABLE analytics.events_1h
(
    hour         DateTime('UTC'),
    tenant_id    UInt32,
    event_type   LowCardinality(String),
    events       UInt64,
    amount_sum   Decimal(18, 4),
    users        AggregateFunction(uniq, UInt64)
)
ENGINE = ReplicatedAggregatingMergeTree(
    '/clickhouse/tables/{shard}/analytics/events_1h',
    '{replica}')
PARTITION BY toYYYYMM(hour)
ORDER BY (tenant_id, event_type, hour);

-- Maintained on every insert into events_raw
CREATE MATERIALIZED VIEW analytics.mv_events_1h
TO analytics.events_1h
AS
SELECT
    toStartOfHour(event_time) AS hour,
    tenant_id,
    event_type,
    count()                   AS events,
    sum(amount)               AS amount_sum,
    uniqState(user_id)        AS users
FROM analytics.events_raw
GROUP BY hour, tenant_id, event_type;

A migration is finished when the numbers say so. ChistaDATA reports three metrics for every real-time analytics engagement, each sourced from a ClickHouse system table rather than an estimate.

End-to-end freshness. The gap between event timestamp and the moment the row is visible to SELECT. Instrument by comparing now() to max(event_time) on the serving table, and correlate with Kafka consumer lag and system.part_log insert events.

Serving latency. p95 and p99 of query_duration_ms from system.query_log per dashboard or API user, together with read_rows and read_bytes to show how much the data model reduced scan volume. See the p99 latency playbook.

Ingestion health. Parts per partition and merge backlog from system.parts and system.merges; too-many-parts errors are the classic symptom of under-batched inserts. Replication delay from system.replicas bounds the freshness of read replicas.

Illustrative only: a well-modeled hourly rollup typically reduces read_rows by two to four orders of magnitude versus scanning raw events, which is where sub-second p95 comes from. Actual ratios depend on cardinality and query shape; ChistaDATA measures them per workload during validation.

-- Serving latency by dashboard user, last 24 h
SELECT
    user,
    count()                                   AS queries,
    quantile(0.95)(query_duration_ms)         AS p95_ms,
    quantile(0.99)(query_duration_ms)         AS p99_ms,
    avg(read_rows)                            AS avg_read_rows
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 24 HOUR
  AND query_kind = 'Select'
GROUP BY user
ORDER BY p99_ms DESC;

-- Ingestion health: parts per active partition
SELECT
    database,
    table,
    partition,
    count()          AS active_parts,
    sum(rows)        AS rows,
    max(modification_time) AS last_part_written
FROM system.parts
WHERE active
  AND database = 'analytics'
GROUP BY database, table, partition
ORDER BY active_parts DESC
LIMIT 20;

-- Freshness: how far behind is the serving table?
SELECT
    now() - max(hour) AS freshness_lag_seconds
FROM analytics.events_1h;

ChistaDATA is a full-stack ClickHouse infrastructure operations company: consulting, 24×7×365 consultative support, platform engineering, and managed services on 100% open-source ClickHouse. Every real-time analytics engagement is led by principal ClickHouse engineers and documented in decision-grade written deliverables.

The portfolio covers strategic ClickHouse consulting, 24×7 enterprise support, fully managed ClickHouse services, migration engineering from Redshift, Snowflake, BigQuery, Druid, Pinot, Vertica, Teradata, Hadoop, and Elasticsearch, performance audits, performance tuning, ClickHouse Cloud and DBaaS cost optimization, and Data SRE.

Support is governed by an enterprise SLA: Severity 1 response in 15 minutes, Severity 2 in 12 hours, Severity 3 in 24 hours, Severity 4 in 48 hours. Standing caveat for every recommendation on this page: test before applying to production, and maintain a robust, drilled disaster-recovery posture.