Serving Real-Time Analytics

A stream job ends by writing results to a sink. But people do not read a sink. They read a dashboard, an app screen or an API.

The serving layer is the store that answers those reads. This chapter shows how to choose it for data that must be fresh.

What you will learn

1. What a serving layer must do, and how to measure it.
2. When the warehouse is enough, and when it is not.
3. Why you add up rows before you serve them.
4. What Apache Pinot, Apache Druid and ClickHouse are for.
5. How a fast store handles updates and repeats.
6. How late events and freshness work at the serving layer.
7. What each step of freshness costs.

The example is an operations dashboard for a shop with many stores. It shows orders and revenue per store, per minute. All numbers are made up.

Before you choose a store, ask four questions. Write the answers down, because they decide the design:

1. Freshness: how old may the newest number be? One day, 15 minutes, one minute?
2. Speed: how long may one query take? Ten seconds for an analyst, under one second for a screen.
3. Load: how many queries per second, at the busiest time?
4. Shape: are the queries the same few each time, or new ones each time?

Three kinds of reader give very different answers:

The warehouse fits the first reader well. The third reader almost always needs a different store. The second is the common interview case, and the answer depends on the numbers.

Start with the warehouse you already have. A stream or a micro-batch job writes there every minute or so, and the dashboard reads it.

Freshness is usually fine. The problem is load: many people, refreshing at once.

Count the queries. Use this arithmetic for the operations dashboard:

The last line is a simple rule: queries at once = queries per second × seconds per query.

By default, one Snowflake warehouse cluster runs at most 8 queries at once. The others wait in a queue. So 80 at once needs about 10 clusters, or a long queue.

Before you buy a faster store, try three cheaper fixes:

1. Make each query cheap. Read a small table of totals, not raw orders. The next section shows how.
2. Share one answer. All 200 people see the same tiles, so run each tile once per refresh and send the result to everyone. That is 6 queries every 30 seconds, not 40 per second.
3. Refresh less often. A tile that changes once a minute does not need a refresh every 30 seconds.

With all three fixes, the warehouse is often enough for an internal dashboard.

A dashboard tile rarely needs single orders. It needs totals per store and per minute. So compute the totals once, and serve them.

This is called pre-aggregation, or a rollup. This query turns eight orders into five rows:

SELECT date_trunc('minute', placed_at) AS minute,
       store_id,
       COUNT(*)    AS orders,
       SUM(amount) AS revenue
FROM   orders
GROUP  BY 1, 2;

Here eight rows become five. At real scale the gap is much bigger.

Say the shop has 3,000 orders per second and 500 stores. That is 180,000 orders a minute, but at most 500 rows a minute in store_minute.

You can compute the totals in three places:

Some reads need under one second at thousands of queries per second. For those, teams use a real-time OLAP store. OLAP means online analytical processing: queries that filter and add up many rows.

These stores read straight from Kafka. New rows can be queried within seconds. They keep data in columns, with indexes built for filters and totals.

The three common open-source ones started inside companies with this exact problem:

All three are also sold as managed services. Running one yourself is a real cost: another cluster to size, upgrade and watch.

So a fast store is a step you take for a reason. Name the reason: the load, the speed, or numbers inside your product.

Totals change. A late order arrives, or a stream job restarts and sends a total twice. The serving store must end with the right number.

Fast stores are built for adding rows, not changing them. Each one handles this in its own way.

ClickHouse: the newest version wins, later

In ClickHouse, a ReplacingMergeTree table keeps one row per sort key in the end. With a version column, it keeps the highest version.

Here a late order, 209, adds 6.00 to store A at 10:01. The stream job sends a new total with version 2:

CREATE TABLE store_minute (
  minute   DateTime,
  store_id String,
  orders   UInt32,
  revenue  Decimal(10, 2),
  version  UInt64
) ENGINE = ReplacingMergeTree(version)
ORDER BY (store_id, minute);

-- Two queries for store A at 10:01:
SELECT orders, revenue FROM store_minute
WHERE store_id = 'A' AND minute = '2026-01-22 10:01:00';

SELECT orders, revenue FROM store_minute FINAL
WHERE store_id = 'A' AND minute = '2026-01-22 10:01:00';

FINAL gives the right answer, but each query does more work. Another way is to pick the newest row in the query, for example with argMax(revenue, version).

Pinot and Druid

Pinot can upsert by primary key on a real-time table. The newest record wins, chosen by the time column unless you set another column.

Pinot needs the Kafka topic to be partitioned by that primary key. Then every version of one key reaches the same server.

Druid has no update of one row by key. To correct data, you rewrite the data for a time range, such as one hour, from the source.

Order 209 was placed at 10:01:40 but arrived at 10:03. By then the dashboard already showed 10:01 for store A as 15.00.

So a minute on the dashboard is not final when it first appears. It can change while late events arrive.

You choose how long a minute may change. The chapter "Stream Processing" calls this the watermark. Here is the store A tile at three moments:

Show freshness on the screen. A line such as "Data as of 10:05:40" tells people how old the numbers are.

Measure it as now minus the newest event time loaded into store_minute. Alert when it passes the promise, for example 2 minutes.

Each step to fresher data adds parts that must run all the time. This table shows what each step usually needs:

Ask what people do with a fresher number. If nobody acts within a minute, a 15-minute dashboard is cheaper and enough.

Build each scenario on the pipeline canvas with the chapter closed. In each one, write the freshness promise, the load, and the store you chose.