Real-Time Analytics

What it is

Real-time analytics refers to OLAP query systems capable of answering aggregation queries over billions of rows with sub-second latency, where the underlying data is updated in seconds or minutes rather than hours. Traditional OLAP databases (Hive, Spark SQL over S3) have query latencies of minutes to hours — acceptable for overnight reports, not for user-facing dashboards, operational monitoring, or fraud detection that requires fresh results in real time.

The leading open-source real-time OLAP systems — Apache Pinot, Apache Druid, and ClickHouse — achieve sub-second queries at scale through columnar storage, aggressive pre-computation and rollup at ingestion time, vectorised query execution, and inverted indexes on high-cardinality dimensions.

Why it exists

Two use case classes drive real-time OLAP: (1) user-facing analytics — embedding analytics dashboards in products (e.g., an advertising platform showing campaign metrics to advertisers), where query latency is a product quality metric; and (2) operational monitoring — dashboards and alerting for engineering and operations teams that must respond to anomalies within seconds (error rate spikes, fraud pattern detection). Traditional BI tools backed by data warehouses are too slow and too expensive for these access patterns.

Typical architecture

REAL-TIME OLAP INGESTION PIPELINE
───────────────────────────────────
  Event stream (Kafka) ──────────────────────┐
  Batch history (S3 Parquet) ────────────────┤
                                             ↓
                                   OLAP Cluster
                              (Pinot / Druid / ClickHouse)
                                             │
                              Distributed Query Layer
                                             │
                         REST / SQL / gRPC API
                                             │
                    ┌────────────────────────┤
                    │                        │
             User-facing              Internal dashboards
             product dashboard        (Grafana, Superset)

APACHE DRUID
─────────────
  Core design: roll up (aggregate) data at ingestion time

  Rollup: multiple raw events sharing same dimensions → one row
  Example: 1 million impression events at minute-level granularity
  → 50,000 rolled-up rows (dimension cardinality × time granularity)

  Segment structure:
  - Time-partitioned segments (day/hour)
  - Each segment: sorted by time, columnar, compressed
  - Loaded into Historical nodes (disk-backed), Realtime nodes (memory)

  Query types: timeseries, topN, groupBy, scan
  Indexing: bitmap inverted indexes on all dimension columns
  Strengths: user-facing analytics, high concurrency (100s of QPS),
             time-series aggregations with rollup
  Weaknesses: limited ad-hoc query flexibility; joins are restricted;
              mutability is complex (segment compaction required)

APACHE PINOT
─────────────
  Core design: low-latency lookup at massive scale

  Designed at LinkedIn for user-facing analytics with SLA < 100ms
  Supports both real-time (Kafka) and offline (batch) tables
  Star-tree index: pre-materialises aggregations for specific
                   dimension combinations → sub-millisecond GROUP BY

  Star-tree example:
  Dimensions: country, device_type, ad_campaign
  Pre-aggregated: impressions, clicks, spend
  Query: WHERE country='US' AND device_type='mobile'
  → Star-tree lookup (no scan), O(1) time

  Strengths: fastest P99 latency for fixed-dimension dashboards,
             upsert support (update records by primary key),
             multi-valued columns (arrays of tags)
  Weaknesses: complex operational model; steep learning curve;
              less flexible than ClickHouse for ad-hoc queries

CLICKHOUSE
───────────
  Core design: columnar storage + vectorised query execution

  MergeTree engine family:
  - MergeTree: base engine; sorted by primary key; range queries fast
  - ReplacingMergeTree: deduplication (upserts by primary key) via background merge
  - AggregatingMergeTree: pre-aggregated materialized views
  - CollapsingMergeTree: efficient deletes via sign column

  Materialized views in ClickHouse (automatic pre-aggregation):
  CREATE MATERIALIZED VIEW mv_hourly_clicks
  ENGINE = AggregatingMergeTree()
  ORDER BY (hour, campaign_id)
  AS SELECT
    toStartOfHour(event_ts) AS hour,
    campaign_id,
    countState() AS clicks_state
  FROM raw_clicks GROUP BY hour, campaign_id;

  Query materialized view (10x–100x faster than scanning raw):
  SELECT hour, campaign_id, countMerge(clicks_state) AS clicks
  FROM mv_hourly_clicks
  WHERE hour >= today() - INTERVAL 7 DAY
  GROUP BY hour, campaign_id;

  Strengths: most SQL-compatible of the three; excellent for ad-hoc
             analytics; fast INSERT throughput; widely adopted (cloud-
             hosted as ClickHouse Cloud); active development
  Weaknesses: single-shard transactions not supported; not designed
              for high-concurrency transactional workloads

CHOOSING BETWEEN DRUID / PINOT / CLICKHOUSE
─────────────────────────────────────────────
  Need user-facing analytics with < 100ms SLA, fixed dimensions:
    → Apache Pinot (star-tree index)

  Need time-series analytics, Kafka-native ingestion, rollup:
    → Apache Druid

  Need flexible ad-hoc SQL, easy setup, materialized views:
    → ClickHouse

  Need managed, minimal ops overhead:
    → ClickHouse Cloud / StarRocks Cloud / Tinybird (Pinot-based)

Pros and cons

Implementation notes

Rollup granularity trade-off: Druid's rollup and Pinot's star-tree dramatically reduce storage and query time but lose the ability to query individual raw events. Choose the minimum time granularity that satisfies query requirements: minute-level for operational monitoring, hour-level for user-facing dashboards. If raw event access is occasionally needed, archive raw data to S3 Parquet and use Athena for historical ad-hoc queries alongside the real-time OLAP store for dashboards.

High-cardinality dimensions: Bitmap inverted indexes work well for dimensions with up to ~100,000 unique values. For high-cardinality dimensions (user IDs with millions of values), use a different lookup strategy: store in the OLAP system indexed by time range + filter, not user ID range.

Common failure modes

• Using ClickHouse/Druid as a transactional database: High-frequency small writes (1 row at a time) destroy performance; batch inserts (minimum 1,000–10,000 rows) are required for all real-time OLAP engines.
• Over-relying on materialized views without testing: Materialized view logic bugs produce silently wrong numbers with no error — test materialized view output against raw data before going live.

Decision checklist

• Are inserts batched (not one-row-at-a-time) to maintain write throughput?
• Is rollup/pre-aggregation granularity sufficient for the intended query patterns?
• Is raw event data archived separately (S3 Parquet) for occasional ad-hoc access?
• Have materialized view outputs been validated against raw data?

Example use cases

• Advertising analytics (Pinot): LinkedIn-style: advertisers query impressions, clicks, CTR by campaign/date/device type; 100M events/day; Pinot star-tree on (campaign_id, date, device_type) delivers P99 < 50ms at 1,000 QPS.
• Fraud monitoring (ClickHouse): Payment transactions → Kafka → ClickHouse (AggregatingMergeTree); Grafana dashboard on velocity metrics (transactions per card per hour) refreshes every 30 seconds; alert when velocity > threshold.

Related patterns

• Data Pipeline Patterns — Medallion Gold layer feeds into real-time OLAP stores via Kafka or batch load.
• Lambda Architecture — Real-time OLAP systems serve as the serving layer in Lambda-style architectures.
• Data Governance for Analytics — Column-level access controls and PII handling are essential in user-facing analytics.

Further reading

• Apache Druid Architecture Documentation
• Apache Pinot Documentation
• ClickHouse Documentation