What’s the best way to run real-time product analytics on streaming event data?

Most teams discover the hard way that “real-time product analytics on streaming event data” is actually three problems: getting a clean events schema, landing high-volume streams into a warehouse that doesn’t fall over, and serving millisecond queries to product and growth teams without exploding cost. The best way to do this in 2026 is to pair a streaming pipeline (Kafka, Kinesis, Pub/Sub, Redpanda, or a managed event collector) with a real-time OLAP engine like ClickHouse that’s built to analyze billions of events with sub-second latency.

Quick Answer: The best way to run real-time product analytics on streaming event data is to stream events into a columnar OLAP database like ClickHouse using batched inserts, then expose that data via SQL-based aggregations and precomputed materialized views. This architecture gives you millisecond queries on billions of events, supports complex funnels and retention analysis, and stays cost-efficient as your traffic and concurrency grow.

Why This Matters

If your product or growth team can’t trust real-time metrics—activation funnels, feature adoption, experiment results—they stop using them. Delayed dashboards and 30–60 second query spinners kill iterative experimentation, and the usual fixes (bigger warehouse, more caches, more rollups) become expensive and brittle.

A streaming-first, OLAP-native approach lets you:

• Ingest events continuously from your apps and services.
• Query them in seconds or less, even at billions of rows.
• Scale to high-cardinality workloads (users, devices, A/B variants) without daily schema redesigns.

ClickHouse is built precisely for this: column-oriented storage, vectorized execution, and aggressive compression that keep queries fast and storage lean, even on dense event streams.

Key Benefits:

• Millisecond queries at scale: Analyze billions of events in real time using columnar storage, vectorized execution, and compression optimized for OLAP workloads.
• Cost-effective real-time analytics: Store granular event history with best‑in‑class compression instead of aggregating everything away, so you retain investigative power without runaway cloud bills.
• Developer-friendly and flexible: Use simple SQL to build funnels, retention, experiments, and AI-powered product analytics on top of the same event stream.

Core Concepts & Key Points

Concept	Definition	Why it's important
Streaming event ingestion	Continuously pushing product events (page views, clicks, signups, feature usage) from clients and services into a durable event bus (e.g., Kafka, Kinesis) and then into ClickHouse in batched inserts.	Ensures you can track user behavior in near real time without overloading the database with tiny writes.
Columnar OLAP storage	Data stored by column rather than by row, optimized for aggregations, filters, and scans across billions of records. ClickHouse’s MergeTree family is the core storage engine.	Makes real-time dashboards, funnels, and cohort analysis fast because queries touch only the columns they need and benefit from compression and vectorized execution.
Materialized views & rollups	ClickHouse objects that automatically derive and store aggregated forms of your event data (e.g., per-minute metrics, user-day summaries) as new data arrives.	Offloads heavy aggregations from every query, enabling millisecond responses for common product analytics workloads while still keeping raw event detail.

How It Works (Step-by-Step)

Running real-time product analytics on streaming event data involves four layers: event schema, ingestion pipeline, storage model, and query patterns. At a high level:

1. Define a robust events schema
2. Ingest and batch events into ClickHouse
3. Model tables and views for fast product analytics

1. Define a robust events schema

a. Standardize your event model

Aim for a small, predictable set of event types with shared fields:

• user_id / anonymous_id
• session_id
• event_name
• event_timestamp
• source / platform (web, ios, android…)
• properties (JSON or flattened key columns for hot fields)
• context (device, experiment variants, referrers)

Avoid ultra-wide schemas with hundreds of sparse columns. Instead:

• Put frequently queried properties into first-class columns.
• Keep long-tail/niche fields in a JSON / String column with JSONExtract* in queries as needed.

Example events table (MergeTree):

CREATE TABLE events
(
    event_date       Date DEFAULT toDate(event_time),
    event_time       DateTime64(3, 'UTC'),
    user_id          String,
    session_id       String,
    event_name       LowCardinality(String),
    source           LowCardinality(String),
    platform         LowCardinality(String),
    experiment_key   LowCardinality(String),
    experiment_variant LowCardinality(String),
    properties       JSON,            -- with JSON data type in newer CH, or String
    -- often-used derived fields:
    url              String,
    feature_flag     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)         -- data management / retention
ORDER BY (event_date, user_id, event_name, event_time)
SETTINGS index_granularity = 8192;

Notes from running this in production:

• Partitioning: Month-based partitions (toYYYYMM(event_date)) strike a balance between lifecycle management (easy backfill and drops) and keeping partition counts low. Partitioning is primarily for retention and management, not a silver bullet for speeding up all queries.
• Order key: Order by event_date plus predicates you filter on most (frequently user_id, event_name). This improves data locality and secondary index usage.

2. Ingest and batch events into ClickHouse

a. Use a streaming bus as the source of truth

Most teams use:

• Kafka / Redpanda
• AWS Kinesis
• GCP Pub/Sub
• Azure Event Hubs

Your clients and backends publish events into topics/streams, then a connector or custom consumer pushes them to ClickHouse.

b. Batch inserts to keep merges healthy

ClickHouse’s MergeTree engine is optimized for fewer, larger inserts. Small frequent inserts create many parts, leading to “Too many parts” errors and stressed merges.

Operational guidance:

• Target 1,000–100,000 rows per insert.
• If you’re ingesting from Kafka, use clickhouse-kafka-connect or the built-in Kafka table engine plus a materialized view to batch.
• Tune batching by time (linger.ms) and size (bytes / records) in your producer to hit those row counts.

You can monitor part health via:

SELECT
    table,
    count()            AS parts,
    sum(rows)          AS total_rows,
    round(avg(rows),0) AS avg_rows_per_part
FROM system.parts
WHERE active AND database = 'events_db'
GROUP BY table
ORDER BY parts DESC;

If avg_rows_per_part is in the low hundreds, you’re under-batching.

c. Consider async inserts

For front-end–facing APIs or high-QPS services pushing directly into ClickHouse, enabling async_insert helps batch on the server side:

SET async_insert = 1;
SET wait_for_async_insert = 0;

This lets ClickHouse buffer small inserts and write them as bigger parts, smoothing ingestion without hurting perceived latency.

3. Model tables and views for fast product analytics

Raw events are necessary, but product analytics queries become much easier when you design for a few core patterns: funnels, retention, segmentation, and experimentation.

a. Create derived rollup tables via materialized views

Example: per-user-per-day activity summary:

CREATE TABLE daily_user_activity
(
    event_date      Date,
    user_id         String,
    sessions        UInt32,
    events          UInt32,
    distinct_events UInt32
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

CREATE MATERIALIZED VIEW mv_daily_user_activity
TO daily_user_activity
AS
SELECT
    event_date,
    user_id,
    uniqExact(session_id)  AS sessions,
    count()                AS events,
    uniqExact(event_name)  AS distinct_events
FROM events
GROUP BY event_date, user_id;

This pattern:

• Keeps your raw events for detailed investigations.
• Precomputes analytics that power most dashboards, enabling millisecond queries over days or months.

b. Build funnel and retention-friendly structures

Funnels and retention are primarily about order and time windows. ClickHouse’s vectorized engine and SQL window functions handle this well.

Example: simple 3-step signup funnel on the raw events table:

WITH
events_filtered AS (
    SELECT
        user_id,
        event_time,
        event_name
    FROM events
    WHERE event_name IN ('signup_start', 'signup_complete', 'first_use')
      AND event_date >= today() - 7
),
per_user AS (
    SELECT
        user_id,
        minIf(event_time, event_name = 'signup_start')    AS t_start,
        minIf(event_time, event_name = 'signup_complete') AS t_complete,
        minIf(event_time, event_name = 'first_use')       AS t_first_use
    FROM events_filtered
    GROUP BY user_id
)
SELECT
    count()                                         AS users_started,
    countIf(t_complete IS NOT NULL)                AS users_completed,
    countIf(t_first_use IS NOT NULL)               AS users_first_use,
    round(users_completed / users_started, 3)      AS start_to_complete,
    round(users_first_use / users_completed, 3)    AS complete_to_first_use
FROM per_user;

This runs interactively on billions of rows because:

• ClickHouse scans only relevant columns (user_id, event_time, event_name).
• Aggregations are fully vectorized, and data is stored in a MergeTree optimized for this access pattern.

For retention, you can bucket users by first-seen week and compute returning events using groupArray, windowFunnel, or date arithmetic in SQL.

c. Expose metrics to BI and apps

Because it’s “just SQL,” ClickHouse plugs into:

• BI tools: Superset, Metabase, Grafana, Tableau (via connectors).
• Product analytics frontends and internal tools.
• Your own application layer for embedding metrics and experimentation results.

In ClickHouse Cloud, you get a built-in SQL console and automatic scaling, so you can expose queries directly to product teams with permissions, then scale capacity as concurrency and event volume grow.

Common Mistakes to Avoid

• Over-partitioning by user or event name:
  Creating too many partitions (e.g., PARTITION BY user_id) leads to tiny partitions, high metadata overhead, and slower queries. Use date-based partitions with low-cardinality fields (100–1,000 distinct values) at most. Remember: partitioning is for lifecycle and manageability first, not per-user optimization.

• Tiny, frequent inserts that cause "Too many parts":
  Writing single events or small batches (tens of rows) directly to MergeTree tables causes part explosion and stresses merges. Always batch to at least 1,000 rows per insert (ideally 10,000–100,000) and monitor system.parts and system.merges to verify healthy behavior.

• Pre-aggregating away detail in upstream pipelines:
  Pushing only pre-aggregated metrics from Spark/Flink into ClickHouse removes your ability to run new funnels, retention views, or segment analyses later. Persist raw events in ClickHouse and add rollups via materialized views so you keep both performance and flexibility.

Real-World Example

At a previous company, we moved our product analytics from a row-based warehouse plus a custom event pipeline to ClickHouse after we hit a ceiling: hourly batch ETL, “real-time” dashboards lagging by 10–15 minutes, and funnel queries that routinely took 30–90 seconds—or timed out completely during traffic spikes.

We deployed a Kafka-based pipeline:

• Frontend and backend services emitted normalized events (user_id, session_id, event_name, properties) into Kafka.
• A dedicated consumer wrote into a ClickHouse events table with ~50,000 rows per insert, averaging a few thousand inserts per minute.
• We modeled a MergeTree table partitioned by month and ordered by (event_date, user_id, event_name, event_time).
• Materialized views maintained:
  • Daily user activity.
  • Experiment-level aggregates keyed by (experiment_key, experiment_variant, event_date).

Operationally:

• Query latency for typical funnels dropped from ~30–60 seconds to 20–200 ms, even during peak traffic.
• Storage shrank by ~4–6x due to columnar compression compared to our old row-store.
• Product managers started to trust “live” dashboards again because they reflected behavior within seconds of events occurring.

Using system.query_log, we could literally show PMs: “Here’s your funnel query; it scanned N billion rows in X ms,” and iterate on filters safely.

Pro Tip: Start with a single wide events table and a couple of high-value materialized views (daily user activity, experiment aggregates). Don’t pre-emptively shard or over-design; instead, watch system.parts, system.merges, and system.query_log in the first weeks to see your real bottlenecks, then evolve the schema and views based on actual workloads.

Summary

The best way to run real-time product analytics on streaming event data is to combine a robust events schema, a streaming ingestion layer, and a columnar OLAP engine like ClickHouse that’s built for millisecond queries over billions of rows. By batching inserts, using MergeTree tables with sane partitioning, and leaning on materialized views for common aggregations, you can power funnels, retention, and experimentation analytics in near real time without losing raw event detail or blowing up costs.

This architecture scales from early-stage products to petabyte-scale event backends, and it stays developer-friendly: everything is inspectable via SQL, and performance claims can be verified directly in system.parts, system.merges, and system.query_log.

Next Step

Get Started