Design a Near-Real-Time OLAP Serving Layer

Every dashboard is a promise about latency. A seller opening their PhoenixWorld analytics page expects the chart to be there before they finish reading the heading, and they expect it to reflect the click that happened ten seconds ago. Doing that once, for one analyst, is easy. Doing it for thousands of sellers at once, each slicing tens of billions of rows down to their own campaigns, is a different kind of system. That system is a near-real-time OLAP serving layer, and it exists because the tools you already have cannot make that promise.

Rafael has the raw material. He built the ad-click aggregates and the enriched order stream, and both land in the lakehouse. So his first instinct is the obvious one: point the seller dashboard at Trino over the Iceberg tables. It works beautifully in the demo, with him as the only user. Then the product team turns it on for a thousand sellers, each dashboard auto-refreshing every ten seconds, and every refresh scans billions of rows through a query engine priced by the byte. Latency climbs into seconds, the cluster saturates, and the bill for one day of dashboard traffic is a rounding error away from the entire analytics budget.

The problem is not that the lakehouse is slow. It is that a general-purpose query engine is the wrong tool for a narrow, high-concurrency, latency-critical workload. A warehouse stays general and pays for it on every query. This chapter is about the specialized store that gives it up on purpose - it pre-declares the queries, denormalizes the data, pre-aggregates, and indexes for exactly those shapes - and gets sub-second answers at thousands of queries per second in return.

Step 1 - Understand the Problem and Establish Design Scope

"Make the dashboard fast" hides the two questions that decide everything: how narrow is the query set, and how fresh does it need to be. The narrower and the fresher, the more you can specialize.

Candidate: Who queries this, and how many at once?

Interviewer: Sellers, looking at their own performance. Thousands of them, and the dashboard auto-refreshes, so assume hundreds of queries per second at peak. Each seller only ever sees their own data.

Candidate: How fresh does the data need to be?

Interviewer: A click or an order should show up on the dashboard within a few seconds. This is a live view of how a campaign is doing right now, not a daily report.

Candidate: What do the queries actually look like? Is it arbitrary SQL, or a fixed set of shapes?

Interviewer: A bounded set. Metrics - clicks, orders, revenue, conversion rate - grouped by campaign, product, and a time bucket, always filtered to one seller and a time range. Sellers don't write SQL; they click date pickers and filters.

Candidate: That's a big deal, so let me confirm it. We know the queries in advance. We're not building a general analytics engine, we're serving a known set of aggregations.

Interviewer: Correct. Optimize for those.

Candidate: How much history, and how exact does it have to be?

Interviewer: Ninety days of interactive history; older than that can be slower or archived. And these are dashboards, so approximately right within the freshness window is fine. The exact billing numbers come from a separate reconciled pipeline, not this.

Candidate: Good. That relaxes the correctness bar and lets me pre-aggregate aggressively.

Interviewer: That's the intent.

Two answers shape the whole design: the query set is bounded, and the data can be pre-aggregated because dashboards tolerate approximation.

Functional requirements

• Serve interactive aggregate queries (metrics grouped by campaign, product, and time bucket), always filtered to one seller and a time range, with sub-second latency.
• Continuously ingest the enriched order stream and ad-click aggregates, making each event queryable within seconds.
• Retain ~90 days of data at interactive speed; keep older data available at lower cost and slower speed.
• Sustain high concurrency: thousands of sellers, hundreds of queries per second.