Low-Latency Serving Layers: Tinybird, Cube & ClickHouse APIs for Sub-Second Product Analytics

Low-latency serving is the layer that finally lets a product surface — a usage dashboard, an in-app metric tile, a live counter next to a user's name — read a fresh analytical number in tens of milliseconds, instead of waiting on a batch job, a BI export, or a warehouse query that was never built to answer ten thousand tiny concurrent lookups a second. The hard problem was never "compute the aggregate"; it was the serving gap. A data warehouse or lakehouse is tuned for scanning billions of rows in a scheduled pipeline, not for returning a single pre-aggregated row under a sub-100-millisecond budget while thousands of users refresh their screens, so every team that wanted to ship product analytics in-app ended up either melting the warehouse or hand-rolling yet another caching backend nobody had time to keep correct.

This guide is the senior-data-engineering walkthrough for closing that gap — for building the sub-second analytics serving tier as a governed, reusable API layer rather than a pile of bespoke caches — framed the way interviewers actually probe it: why the warehouse is not itself a serving engine, how Tinybird ingests a stream into a managed ClickHouse and publishes a chained SQL pipe as a real-time API endpoint, how Cube defines measures and dimensions once and serves them sub-second through pre-aggregations behind REST, GraphQL, and SQL, how ClickHouse itself uses materialized views, MergeTree ordering, and projections to make an OLAP API cheap at scale, and how the surrounding serving architecture — pre-aggregate versus query-live, caching tiers, concurrency, and cost — keeps the engine from falling over. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the API integration practice library →, rehearse serving patterns on the real-time analytics practice library →, and sharpen the architecture axis with the system design practice library →.

---

On this page

• Why a low-latency serving layer exists
• Tinybird — pipes, data sources & published API endpoints
• Cube — semantic layer, pre-aggregations & the OLAP API
• ClickHouse — MergeTree, materialized views & the HTTP interface
• The serving architecture — pre-aggregation, caching, concurrency & cost
• Cheat sheet — low-latency serving
• Frequently asked questions
• Practice on PipeCode

---

1. Why a low-latency serving layer exists

The serving gap — a warehouse answers batch questions; a serving layer answers sub-second ones

The one-sentence invariant: a low-latency serving layer is a governed request/response tier in front of a purpose-built OLAP store, and the reason it exists is that a warehouse or lakehouse is built for high-throughput batch scans over enormous tables, not for the high-concurrency, sub-100-millisecond point and aggregate reads a product surface needs — so the serving layer's whole job is to bridge that gap by deciding what to pre-aggregate versus query live, where freshness comes from, and how concurrency stays cheap, while turning a metric into a reusable API instead of a per-team caching backend. Point a thousand in-app tiles straight at the warehouse and you get slow queries, a runaway bill, and batch-job contention; put a serving layer in between and the OLAP store serves the API, and the API serves the product.

The four axes interviewers actually probe.

• Latency budget. What is the response-time target, and does it force pre-aggregation? A warehouse cold query is seconds; a product tile is often budgeted at tens of milliseconds. The senior answer names the budget first and lets it decide whether the number is pre-aggregated into a rollup or scanned live. Reaching for "just query the warehouse per request" without stating the budget is the tell of someone who has not served real product traffic.
• Concurrency. How many simultaneous requests, and what stops them from crushing the engine? Product analytics means thousands of users each refreshing a dashboard. The senior answer talks about max_concurrent_queries, pre-aggregation to shrink rows-scanned-per-request, and caching as first-class parts of the serving layer, not an afterthought.
• Freshness. How fresh must the number be, and where does that freshness come from? The senior answer distinguishes ingest-time pre-aggregation (a materialized view maintained on every insert — seconds fresh) from scheduled rollups (minutes fresh) from live queries (real-time but expensive), and matches each to the product's tolerance.
• Cost. What does a single request cost in rows scanned and compute, and how do you amortise it? A live scan over raw events costs O(rows) per request; a pre-aggregation costs O(1) index read plus a periodic rebuild. The senior answer frames cost as rows-scanned-per-request and pre-aggregation amortisation.

The 2026 reality — the serving layer is a small stack of purpose-built tools.

• ClickHouse is the OLAP engine underneath most of this: a column store whose MergeTree family, ORDER BY sparse index, partitioning, materialized views, and projections make aggregate and point reads sub-second, and whose HTTP interface can itself be an app-facing API.
• Tinybird is a managed serving platform over ClickHouse: you ingest a stream into a data source, transform it through chained SQL pipes, pre-aggregate with materialized-view nodes, and publish the last node as a real-time API endpoint with scoped token auth — no backend to write.
• Cube is the semantic layer: you define measures, dimensions, and joins once as a data model, add pre-aggregations that Cube's query router hits for sub-second reads, and every consumer speaks the same semantics over REST, GraphQL, or SQL with a multi-tenant security context.
• Materialized views are the shared engine of all three: a query maintained incrementally so the expensive aggregation runs once at write time and the read is a cheap lookup — the single most important primitive in sub-second serving.

What interviewers listen for.

• Do you say the warehouse is not a serving engine and explain the serving gap unprompted? — senior signal.
• Do you frame the choice as pre-aggregate vs query-live driven by a stated latency and freshness budget? — required answer.
• Do you name materialized views / pre-aggregations as where sub-second comes from, not "add more compute"? — senior signal.
• Do you treat concurrency and rows-scanned-per-request as first-class cost drivers? — required answer.
• Do you describe a served metric as a governed API with an owner, an SLO, and scoped access? — senior signal.

Worked example — the pre-aggregation-vs-query-live decision table

Detailed explanation. The single most useful artifact for a serving-layer interview is a memorised mapping of access pattern → serving strategy. Every senior discussion converges on it: given a product surface and a latency/freshness budget, do you query the OLAP store live, pre-aggregate a rollup, or serve a cached response? Walk through building the table for a product that exposes a "daily active users by feature" metric to an in-app dashboard.

• The consumers. An in-app usage tile (20k req/min, p95 < 100 ms), an analyst ad-hoc explorer (low volume, freshness-tolerant), a live ops counter (sub-second freshness).
• The tension. Live scans over raw events are fresh but slow and costly at high QPS; pre-aggregated rollups are fast and cheap but only as fresh as their refresh.
• The rule. Match the strategy to the access pattern's tolerance for latency, freshness, and cost.

Question. For each consumer, name the serving strategy and where the number physically comes from when the request arrives.

Input.

Consumer	Latency budget	Freshness need	Strategy
In-app usage tile	< 100 ms	minutes OK	pre-aggregated rollup + result cache
Analyst ad-hoc explorer	seconds OK	live	live query over the OLAP store
Live ops counter	< 500 ms	seconds	ingest-time materialized view
Nightly finance export	minutes OK	daily	scheduled rollup + pagination

Code.

-- Pre-aggregate a rollup the serving API reads from, maintained on ingest.
-- The API NEVER scans the raw events table on the hot path.
CREATE MATERIALIZED VIEW usage_daily_mv
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, feature, day)
AS
SELECT
    tenant_id,
    feature,
    toDate(ts)                       AS day,
    uniqState(user_id)               AS dau_state,   -- incremental distinct-count state
    countState()                     AS events_state
FROM events
GROUP BY tenant_id, feature, day;

-- The serving read merges the incremental state — a tiny grouped lookup, not a scan.
SELECT feature,
       uniqMerge(dau_state)   AS dau,
       countMerge(events_state) AS events
FROM usage_daily_mv
WHERE tenant_id = {tenant:String} AND day = today()
GROUP BY feature;

Step-by-step explanation.

1. The materialized view usage_daily_mv is the serving store: the expensive uniqState/countState aggregation runs incrementally as events are inserted, not once per request. The in-app tile reads a tiny pre-aggregated rollup, so its p95 is a grouped index read, not an events scan.
2. AggregatingMergeTree with ORDER BY (tenant_id, feature, day) turns the tile's filtered read (WHERE tenant_id = ... AND day = today()) into a sparse-index range read over a handful of rows — the sort order is the index that makes it sub-second.
3. The read uses uniqMerge/countMerge to combine the partial aggregate states — so a running distinct-user count is maintained without ever re-scanning raw events, the essence of ingest-time pre-aggregation.
4. The analyst explorer, by contrast, is freshness-sensitive and low-volume, so it queries the OLAP store live — pre-aggregating every ad-hoc slice they might want is impossible and wasteful.
5. The mistake is a single strategy for all consumers: querying live for the high-QPS tile melts the engine, and pre-aggregating for the ad-hoc explorer builds rollups nobody reads. The table is the antidote — strategy follows the access pattern.

Output.

Access pattern	Right strategy	Wrong strategy (common mistake)
High-QPS, freshness-tolerant	pre-aggregated rollup + cache	live scan per request
Low-volume, freshness-critical	live query over OLAP store	pre-aggregate every possible slice
Sub-second live counter	ingest-time materialized view	scheduled batch rollup
Bulk nightly	scheduled rollup + paginate	one giant unpaginated scan

Rule of thumb. State the latency and freshness budget first, then let it choose the strategy: pre-aggregate a rollup for hot, freshness-tolerant reads; maintain an ingest-time materialized view when you need sub-second freshness; query live only for cold, ad-hoc paths. The OLAP store serves the rollup; the rollup serves the request.

Worked example — what interviewers actually probe

Detailed explanation. The senior serving-layer interview has a predictable escalation: an ambiguous opener ("put this metric in the app"), then progressive narrowing to test whether you understand the serving gap, pre-aggregation, and concurrency. The candidates who name pre-aggregate-vs-live, materialized views, and rows-scanned-per-request score highest.

• Ambiguous opener. "The product team wants a live usage dashboard on the events data. Point them at the warehouse?"
• Follow-up 1. "Twenty thousand requests a minute hit it. Now what?" — probes pre-aggregation + caching.
• Follow-up 2. "It must be fresh to the last few seconds. Now what?" — probes ingest-time materialized views.
• Follow-up 3. "The OLAP store hits its concurrency limit. Why?" — probes rows-scanned + concurrency caps.
• Follow-up 4. "Tinybird, Cube, or raw ClickHouse?" — probes tool fit.

Question. Draft a 5-minute senior serving answer that pre-empts all four follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Access	"give them a warehouse connection"	"put a serving layer over an OLAP store; the warehouse isn't a serving engine"
High QPS	"add warehouse compute"	"pre-aggregate a rollup; cache repeats"
Freshness	"run the batch more often"	"an ingest-time materialized view, fresh in seconds"
Concurrency	"raise the limit"	"shrink rows-scanned-per-request; cap concurrency"
Tool	"whatever's trendy"	"Tinybird to publish, Cube for a semantic layer, ClickHouse under both"

Code.

Senior low-latency serving answer template (5 minutes)
======================================================

Minute 1 — name the gap up front
  "The warehouse is for batch scans, not sub-second serving. I'd serve
   from an OLAP store built for it — ClickHouse — and put a serving
   layer in front, not point the app at raw fact tables."

Minute 2 — pre-aggregate vs live
  "For the high-QPS tile I pre-aggregate a rollup (a materialized view)
   and cache repeats; for the analyst's ad-hoc explorer I query live.
   The latency and freshness budget picks the strategy per consumer."

Minute 3 — where freshness comes from
  "If it must be fresh to seconds, the rollup is a ClickHouse
   materialized view maintained on INSERT, so the aggregate updates
   incrementally — no scheduled batch lag, no per-request scan."

Minute 4 — concurrency + cost
  "Concurrency is bounded by rows-scanned-per-request: pre-aggregation
   turns an O(rows) scan into an O(1) index read, so a small pool of
   query slots serves thousands of tiles. I cap max_concurrent_queries
   and let a result cache absorb the hot keys."

Minute 5 — tool fit
  "Tinybird to ingest and publish a pipe as an endpoint with token auth;
   Cube when many consumers need one governed semantic model over
   REST/GraphQL/SQL; raw ClickHouse HTTP when I want the thinnest API.
   Either way it's a governed metric with an owner and an SLO."

Step-by-step explanation.

1. Minute 1 frames the whole answer around the serving gap. Weak candidates hand out a warehouse connection; naming "the warehouse isn't a serving engine, serve from an OLAP store" signals you understand the architecture, not just the tools.
2. Minute 2 shows you serve high-QPS traffic from a pre-aggregated rollup and reserve live queries for cold, ad-hoc paths — the single most senior thing you can say about serving.
3. Minute 3 pre-empts the freshness follow-up. Naming an ingest-time materialized view — incremental on INSERT, not a scheduled batch — is the difference between "run the job more often" and "the aggregate is always current by construction."
4. Minute 4 pre-empts the concurrency follow-up. Volunteering that concurrency is bounded by rows-scanned-per-request, and that pre-aggregation turns O(rows) into O(1), shows you have run a serving layer under real load.
5. Minute 5 closes on tool fit and the governed API framing — owned, SLO-backed, token-scoped — the sentence that separates a platform engineer from someone bolting on a cache.

Output.

Grading criterion	Weak score	Senior score
Names the serving gap	rare	mandatory
Pre-aggregate vs live by budget	occasional	mandatory
Freshness via ingest-time MV	rare	senior signal
Concurrency = rows-scanned	rare	senior signal
Frames it as a governed API	rare	senior signal

Rule of thumb. The senior serving answer is a 5-minute monologue covering the serving gap, pre-aggregate-vs-live, where freshness comes from, concurrency-as-rows-scanned, and tool fit without waiting for the follow-ups. Rehearse it once; deploy it every interview.

Worked example — the sub-second latency budget breakdown

Detailed explanation. "Sub-second" is not one number — it is a budget you spend across cache, pre-aggregation, and the network. A senior engineer decomposes the p95 target into where the milliseconds go and where each strategy claws them back. Break down a p95 < 100 ms budget for an in-app tile.

• The budget. 100 ms p95, end to end, from the app's request to the rendered number.
• The pieces. Network + TLS, gateway/auth, cache lookup, and (on a miss) the OLAP read.
• The lever. Pre-aggregation and caching shrink the OLAP read from a scan to a lookup.

Question. Given a p95 < 100 ms budget, allocate the milliseconds and show how pre-aggregation and a result cache keep the OLAP read inside budget.

Input.

Stage	Cache HIT	Cache MISS (pre-aggregated)	Cache MISS (live scan)
Network + TLS	~30 ms	~30 ms	~30 ms
Gateway + auth	~5 ms	~5 ms	~5 ms
Cache lookup	~5 ms	~5 ms	~5 ms
OLAP read	—	~15 ms (index read)	~1,500 ms (scan)
Total p95	~40 ms	~55 ms	~1,540 ms

Code.

p95 budget = 100 ms  (in-app usage tile)

Cache HIT (most requests)
  network 30 + gateway 5 + cache 5              = ~40 ms   ✓ well under budget

Cache MISS, PRE-AGGREGATED rollup
  network 30 + gateway 5 + cache 5 + read 15    = ~55 ms   ✓ under budget
  (read = sparse-index lookup over a rollup — a few rows)

Cache MISS, LIVE SCAN over raw events
  network 30 + gateway 5 + cache 5 + scan 1500  = ~1540 ms ✗ 15x over budget

Takeaway:
  - Caching absorbs the repeat traffic (most requests are HITs).
  - Pre-aggregation makes the MISS affordable (15 ms, not 1500 ms).
  - A live scan blows the budget even once — never on the hot path.

Step-by-step explanation.

1. The fixed costs — network, gateway, cache lookup — total ~40 ms and are the same whatever the backend; they are the floor, so the OLAP read is the only variable you control against the 100 ms budget.
2. On a cache HIT (the common case for a hot tile) the request never reaches the OLAP store at all — ~40 ms total — which is why a result cache is the first line of defence for repeat traffic.
3. On a MISS against a pre-aggregated rollup, the read is a sparse-index lookup over a few rows (~15 ms), so the total (~55 ms) stays inside budget — pre-aggregation is what makes the miss affordable.
4. On a MISS against a live scan over raw events, the read alone (~1,500 ms) is 15× the entire budget — a single such request blows the SLO, which is why live scans never belong on the hot path.
5. The senior framing: sub-second is a budget you spend, and the two levers that keep you inside it are caching (kill the repeat) and pre-aggregation (make the miss cheap) — never raw compute.

Output.

Path	p95	Verdict
Cache HIT	~40 ms	comfortably in budget
MISS, pre-aggregated	~55 ms	in budget
MISS, live scan	~1,540 ms	15× over — never hot-path
No cache, always live	seconds	SLO violated

Rule of thumb. Treat sub-second as a millisecond budget: fixed costs (network, gateway, cache) are the floor, so protect the budget with a result cache for repeats and pre-aggregation for misses. A live scan over raw events blows the budget on the first request — keep it off the hot path entirely.

Senior interview question on low-latency serving strategy

A senior interviewer often opens with: "A product team wants a live in-app usage dashboard — daily active users and event counts by feature — for thousands of concurrent users, fresh to within a minute, at p95 < 100 ms. You currently have only a batch warehouse. Design the serving layer: what you pre-aggregate versus query live, where freshness comes from, how you keep the OLAP store from being crushed by concurrency, and why it's a governed API, not a one-off cache."

Solution Using a pre-aggregated serving store, a MergeTree engine, and a per-consumer contract

-- Step 1 — an OLAP store fronts the warehouse; the API reads THIS, not raw fact tables.
-- MergeTree sorted for the serving read pattern (tenant, feature, time).
CREATE TABLE events
(
    tenant_id  String,
    user_id    UInt64,
    feature    LowCardinality(String),
    ts         DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, feature, ts);

-- Step 2 — pre-aggregate on ingest: a materialized view maintains the rollup incrementally.
CREATE MATERIALIZED VIEW usage_daily_mv
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, feature, day)
AS
SELECT tenant_id, feature, toDate(ts) AS day,
       uniqState(user_id) AS dau_state, countState() AS events_state
FROM events
GROUP BY tenant_id, feature, day;

-- Step 3 — the serving read: a tiny grouped lookup over the rollup, scoped to the tenant.
SELECT feature,
       uniqMerge(dau_state)     AS dau,
       countMerge(events_state) AS events
FROM usage_daily_mv
WHERE tenant_id = {tenant:String} AND day = today()
GROUP BY feature;

# Step 4 — the contract, per consumer, as a governed metric API.
metric_api: feature_usage
owner: product-analytics-platform
slo: { availability: 99.9%, p95_latency_ms: 100, freshness: "<= 60s" }
contracts:
  - consumer: in-app-tile       # high QPS, cache-friendly
    serving: materialized-view-rollup + result-cache
    freshness: ingest-time (seconds)
  - consumer: analyst-explorer
    serving: live-query over the OLAP store
    freshness: real-time
  - consumer: finance-export
    serving: scheduled-rollup + keyset-pagination
    freshness: daily

Step-by-step trace.

Decision	Before (warehouse only)	After (serving layer)
Hot tile reads	ad-hoc scan of raw fact tables	index lookup on a rollup + cache
Freshness	scheduled batch (hours)	ingest-time MV (seconds)
Concurrency	scan per request → limit hit	O(1) rollup reads, small pool
Contract	one warehouse connection	governed metric API per consumer
Ownership	"some table"	versioned metric API with an SLO
Cost	O(rows) per request	O(1) read + periodic MV merge

After the rollout, the in-app tile reads the pre-aggregated usage_daily_mv through the serving layer with the query scoped to the caller's tenant_id; the materialized view keeps the rollup fresh to seconds by aggregating on every INSERT; a result cache absorbs the repeat traffic; the analyst explorer queries the OLAP store live; and the whole thing is described by one metric-API contract with an owner and an SLO. The warehouse is touched only to backfill the OLAP store — never on the request hot path.

Output:

Metric	Before	After
Hot-tile p95 latency	1.5–4 s (warehouse scan)	< 100 ms (rollup + cache)
Freshness	hours (batch)	seconds (ingest-time MV)
Rows scanned per request	millions	a handful (rollup lookup)
Max concurrent tiles	~ warehouse slots	thousands (cheap reads + cache)
Contract clarity	tribal knowledge	versioned, owned, SLO-backed

Why this works — concept by concept:

• Serving store, not the warehouse — a ClickHouse MergeTree table sorted for the serving read pattern absorbs the request traffic, so the batch warehouse never handles the hot path. The OLAP store serves the API; the warehouse only backfills it.
• Ingest-time materialized view — an AggregatingMergeTree view maintains the rollup incrementally on every INSERT, so the aggregate is fresh to seconds without a scheduled batch and without a per-request scan.
• Pre-aggregation shrinks rows-scanned — the serving read is a grouped sparse-index lookup over a rollup (a handful of rows) instead of a scan over millions, turning O(rows) per request into O(1) and letting a small query pool serve thousands of tiles.
• Per-consumer contract — a cached rollup for the hot tile, a live query for the analyst, a scheduled rollup for the export, each with its own freshness, all under one versioned metric-API contract with an owner and an SLO.
• Cost — one incremental merge per insert batch, an O(1) index read per request, and a cache absorbing hot keys, versus a per-request warehouse scan. The eliminated cost is the warehouse bill and the outage risk of pointing product traffic at a batch engine — O(1) cached/index reads versus O(scan) per request.

Design
Topic — design
Design problems on low-latency serving layers

Practice →

Real-time analytics Topic — real-time-analytics Real-time analytics problems on sub-second serving

Practice →

---

2. Tinybird — pipes, data sources & published API endpoints

Ingest a stream, pre-aggregate in a materialized node, publish the last node as a real-time API

The mental model in one line: Tinybird is a managed serving platform over ClickHouse that turns four primitives into a real-time API — a data source ingests streaming or batch rows into a managed MergeTree table, a pipe is a chain of named SQL nodes that transform and query that data, a materialized-view node pre-aggregates on ingest so the read is a rollup lookup rather than a raw scan, and the last node of an endpoint pipe is published as an HTTP API with query parameters and scoped-token auth — so you build a governed, parametrised, sub-second endpoint entirely in SQL without writing or deploying a backend. You describe the ingest, the transformation, and the query in SQL; Tinybird provisions the ClickHouse tables, the materialization, and the API.

Data sources — the ingest side.

• What they are. A data source is a managed ClickHouse MergeTree table plus an ingest endpoint; you define its schema, sorting key, and (optionally) partitioning, and Tinybird accepts rows via the Events API (streaming), file uploads (batch), or connectors (Kafka, S3, and others).
• The sorting key is the index. As with raw ClickHouse, the data source's ENGINE_SORTING_KEY decides which reads are fast — put the columns your endpoints filter on first (tenant_id, time) so serving reads are sparse-index range scans.
• Streaming ingest. The high-frequency-event Events API accepts NDJSON at high throughput and makes rows queryable within seconds — the freshness floor for live product analytics.
• Schema as code. Data sources are declared in .datasource files (schema + engine settings) and version-controlled, so the ingest contract is reviewable, not clicked together in a UI.

Pipes — the transformation side.

• Chained SQL nodes. A pipe is an ordered list of nodes; each node is a SELECT, and later nodes reference earlier ones by name (SELECT ... FROM previous_node) — a readable, testable decomposition of a query instead of one giant statement.
• Node types. A pipe can end as an endpoint (published as an API), a materialized view (its result maintained incrementally into a target data source), or a copy (scheduled snapshot) — the last node's type is what the pipe does.
• Parameters. Endpoint nodes use a templating syntax ({{String(tenant_id)}}, {{Date(day)}}) so the published API takes query-string parameters, bound safely into the SQL — the parametrised contract the app calls.
• Testability. Because each node is named SQL, you can inspect intermediate results node by node — the debugging affordance a hand-written serving backend rarely gives you.

Materialized-view pipes — where sub-second comes from.

• Pre-aggregate on ingest. A materialized-view pipe reads from a landing data source and writes an aggregated result into a target data source on every ingest, so the endpoint that reads the target sees a rollup that is always current — the Tinybird form of the ClickHouse ingest-time materialized view.
• State columns. For distinct counts and complex aggregates, the target uses AggregatingMergeTree with -State/-Merge functions, exactly as in raw ClickHouse — the endpoint merges partial states cheaply at read time.
• The rollup shrinks the read. The endpoint scans the small pre-aggregated target (rows per tenant per day) instead of the raw event stream (millions), which is what keeps the published API sub-second under load.

Token auth — the governance side.

• Scoped tokens. Every request carries an auth token; a token is scoped to specific pipes/endpoints with READ rights, so a leaked token cannot reach data or endpoints it was not granted.
• Row-level filters in the token. A token can carry a SQL filter (e.g. tenant_id = 'acme') that Tinybird ANDs onto every query the token runs — the multi-tenant analogue of row-level security, enforced by the platform, not the app.
• Least privilege. You mint a narrow token per consumer (per tenant, per surface) so blast radius is bounded and access is attributable.

The failure modes senior engineers pre-empt.

• Serving from the raw data source. Publishing an endpoint that scans the landing stream per request re-creates the warehouse problem. Mitigation: pre-aggregate with a materialized-view pipe; the endpoint reads the rollup.
• Unparametrised or unbounded endpoints. An endpoint with no filter or LIMIT lets a caller pull everything. Mitigation: require the tenant/time parameters, add a LIMIT, and scope the token's row filter.
• A wide-open admin token. Reusing one privileged token everywhere means a leak exposes all data. Mitigation: one narrow read token per consumer with a row filter; rotate.

Common interview probes on Tinybird.

• "What are the four primitives?" — data source (ingest), pipe (chained SQL), materialized-view node (pre-aggregate on ingest), published endpoint (parametrised API).
• "How does it stay sub-second?" — a materialized-view pipe pre-aggregates on ingest; the endpoint reads a small rollup, not the raw stream.
• "How is multi-tenancy enforced?" — scoped tokens carrying a row-level SQL filter that is AND-ed onto every query.
• "Where does the backend go?" — there is none; the pipe is the API, provisioned over managed ClickHouse.

Worked example — a data source and a pipe published as a parametrised endpoint

Detailed explanation. The canonical Tinybird setup: a data source ingests events, and an endpoint pipe filters and aggregates them with query parameters, published as an HTTP API. Build a "daily usage by feature" endpoint the app calls with a tenant and a date.

• Data source. events (tenant, user, feature, ts), sorted for the serving read.
• Endpoint pipe. filters by tenant and day, aggregates by feature, takes parameters.
• The call. GET /v0/pipes/daily_usage.json?tenant=acme&day=2026-08-26.

Question. Define an ingest data source and publish a parametrised endpoint that returns per-feature usage for one tenant and one day.

Input.

Piece	Value
Data source	events (tenant_id, user_id, feature, ts)
Sorting key	(tenant_id, feature, ts)
Endpoint pipe	daily_usage
Parameters	tenant (String), day (Date)

Code.

-- events.datasource — schema + engine (declared as code, version-controlled).
SCHEMA >
    `tenant_id` String,
    `user_id`   UInt64,
    `feature`   LowCardinality(String),
    `ts`        DateTime

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(ts)"
ENGINE_SORTING_KEY   "tenant_id, feature, ts"

-- daily_usage.pipe — a parametrised endpoint (the last node is published as an API).
NODE daily_usage_endpoint
SQL >
    %
    SELECT
        feature,
        uniq(user_id) AS dau,
        count()       AS events
    FROM events
    WHERE tenant_id = {{ String(tenant, required=True) }}
      AND toDate(ts) = {{ Date(day, required=True) }}
    GROUP BY feature
    ORDER BY dau DESC

TYPE endpoint

### The published API — parameters become the query string; token gates it.
GET /v0/pipes/daily_usage.json?tenant=acme&day=2026-08-26
Authorization: Bearer <scoped-read-token>

Step-by-step explanation.

1. The events.datasource declares a managed MergeTree table; the ENGINE_SORTING_KEY puts tenant_id and feature first, so the endpoint's filter is a sparse-index range read rather than a full scan — the sort order is the index.
2. The daily_usage.pipe has one node whose SQL uses templated parameters: {{ String(tenant, required=True) }} and {{ Date(day, required=True) }} bind the query-string values safely into the SQL, so tenant and day become required API parameters.
3. TYPE endpoint publishes the node as an HTTP API at /v0/pipes/daily_usage.json — no controller, no route handler, no deployment; the pipe is the endpoint.
4. The % at the top of the node marks it as a templated (parameterised) query, and required=True means a call missing tenant or day is rejected — the endpoint cannot be invoked unbounded.
5. The request carries a scoped read token; Tinybird runs the compiled SQL against managed ClickHouse and returns JSON — a governed, parametrised, sub-second API built entirely in SQL.

Output.

Element	Compiles to / behaviour
WHERE tenant_id = {{String(tenant)}}	sparse-index range read (sort key)
{{ Date(day, required=True) }}	required parameter; missing → 400
TYPE endpoint	published at /v0/pipes/daily_usage.json
response	JSON rows: feature, dau, events

Rule of thumb. Declare the data source with a sorting key that matches the endpoint's filter, write the endpoint as a parametrised pipe node with required=True parameters, and let TYPE endpoint publish it — a governed API with no backend to deploy. Never publish an unparametrised node over a raw stream.

Worked example — a materialized-view pipe that pre-aggregates on ingest

Detailed explanation. The endpoint above still scans events per request. For high QPS, add a materialized-view pipe that pre-aggregates into a target data source on ingest, so the endpoint reads a small rollup. Convert daily_usage to read a pre-aggregated target.

• Target data source. usage_daily on AggregatingMergeTree, sorted by (tenant_id, feature, day).
• Materialized pipe. reads events, writes aggregate states to usage_daily on every ingest.
• Endpoint. now reads usage_daily and merges states — a rollup lookup, not a scan.

Question. Pre-aggregate events into a rollup on ingest and point the endpoint at the rollup so it reads a handful of rows.

Input.

Piece	Value
Target	usage_daily (AggregatingMergeTree)
State columns	uniqState(user_id), countState()
Sorting key	(tenant_id, feature, day)
Endpoint reads	usage_daily (merges states)

Code.

-- usage_daily.datasource — the pre-aggregated TARGET (states, not final values).
SCHEMA >
    `tenant_id`     String,
    `feature`       LowCardinality(String),
    `day`           Date,
    `dau_state`     AggregateFunction(uniq, UInt64),
    `events_state`  AggregateFunction(count)

ENGINE "AggregatingMergeTree"
ENGINE_SORTING_KEY "tenant_id, feature, day"

-- mv_usage_daily.pipe — MATERIALIZED: runs on every ingest into `events`.
NODE mv_usage_daily_node
SQL >
    SELECT
        tenant_id, feature, toDate(ts) AS day,
        uniqState(user_id) AS dau_state,   -- partial distinct-count state
        countState()       AS events_state
    FROM events
    GROUP BY tenant_id, feature, day

TYPE materialized
DATASOURCE usage_daily          -- write the rollup here, incrementally

-- daily_usage.pipe (endpoint) — now reads the ROLLUP and merges states.
NODE daily_usage_endpoint
SQL >
    %
    SELECT feature,
           uniqMerge(dau_state)     AS dau,
           countMerge(events_state) AS events
    FROM usage_daily
    WHERE tenant_id = {{ String(tenant, required=True) }}
      AND day = {{ Date(day, required=True) }}
    GROUP BY feature
    ORDER BY dau DESC
TYPE endpoint

Step-by-step explanation.

1. The usage_daily target is declared on AggregatingMergeTree and stores partial aggregate states (AggregateFunction(uniq, ...), AggregateFunction(count)) — not final numbers — so distinct counts can be combined later without re-scanning raw rows.
2. The mv_usage_daily.pipe is TYPE materialized with DATASOURCE usage_daily: Tinybird runs its SELECT on every batch ingested into events and appends the aggregated states to the target, so the rollup is maintained incrementally, seconds fresh.
3. The endpoint now reads usage_daily and calls uniqMerge/countMerge to combine the partial states into final dau/events — a grouped read over a few rows per tenant/day instead of a scan over the raw stream.
4. Because the target is sorted by (tenant_id, feature, day), the endpoint's filter is a sparse-index range read, so the pre-aggregated endpoint is both small and well-indexed — the two properties that make it sub-second at high QPS.
5. The invariant: the expensive aggregation happens once, at ingest, amortised across all future reads — the endpoint's cost per request drops from O(events) to O(rollup rows), which is what lets it serve thousands of concurrent tiles.

Output.

Approach	Rows read per request	Freshness
Endpoint over raw events	millions (scan)	real-time
Endpoint over usage_daily rollup	a handful (merge)	seconds (ingest-time)
Aggregation cost	per request	once, at ingest
QPS ceiling	low (scan-bound)	high (lookup-bound)

Rule of thumb. For any high-QPS endpoint, add a TYPE materialized pipe that pre-aggregates into an AggregatingMergeTree target on ingest, then point the endpoint at the target and merge states at read time. The aggregation runs once at write time; the endpoint reads a rollup, not the stream.

Worked example — a scoped token with a row-level filter

Detailed explanation. A multi-tenant endpoint must never return another tenant's rows, even if the caller omits or forges a tenant parameter. A Tinybird token can carry a SQL filter that Tinybird ANDs onto every query — enforcing tenant isolation at the platform, not in the app. Mint a per-tenant read token.

• The endpoint. daily_usage, which also takes a tenant parameter.
• The token. scoped to READ on daily_usage, carrying tenant_id = 'acme'.
• The guarantee. even ?tenant=globex returns nothing but acme's rows.

Question. Create a read token scoped to the daily_usage endpoint that enforces tenant_id = 'acme' on every query, so no parameter value can widen the caller's scope.

Input.

Piece	Value
Token scope	READ on daily_usage
Row filter	tenant_id = 'acme'
Effect	filter AND-ed onto every query
Result	cross-tenant reads impossible

Code.

# Mint a scoped read token carrying a row-level SQL filter (Tinybird CLI / API).
tb token create \
  --name "acme-read" \
  --scope "PIPES:READ:daily_usage" \
  --resource "daily_usage" \
  --filter "tenant_id = 'acme'"     # AND-ed onto EVERY query this token runs

### Even if the caller forges ?tenant=globex, the token's filter wins.
GET /v0/pipes/daily_usage.json?tenant=globex&day=2026-08-26
Authorization: Bearer <acme-read-token>

# Effective SQL executed by Tinybird:
#   ... WHERE (tenant_id = {{String(tenant)}})   -- = 'globex' from the param
#         AND (tenant_id = 'acme')               -- from the TOKEN filter
#   -> tenant_id = 'globex' AND tenant_id = 'acme'  -> empty result. No leak.

Step-by-step explanation.

1. The token is scoped PIPES:READ:daily_usage, so it can only call that endpoint — a leaked token cannot reach other pipes, data sources, or admin operations; scope bounds the blast radius.
2. The --filter "tenant_id = 'acme'" attaches a row-level predicate to the token; Tinybird ANDs it onto every query the token runs, exactly like a row-level-security policy, but enforced by the serving platform.
3. When a caller passes ?tenant=globex, the endpoint's own WHERE tenant_id = {{String(tenant)}} becomes tenant_id = 'globex', but the token filter adds AND tenant_id = 'acme' — the conjunction is unsatisfiable, so the result is empty; the forged parameter cannot leak globex's data.
4. The correct usage is one narrow token per tenant (or per consumer), so access is least-privilege and attributable — a compromised token exposes one tenant's read surface, not the platform.
5. The senior framing: never trust the client-supplied tenant parameter for isolation; put the authoritative tenant predicate in the token, so isolation holds regardless of what the caller sends.

Output.

Request (token / param)	Effective filter	Rows returned
acme token, ?tenant=acme	acme AND acme	acme's rows
acme token, ?tenant=globex	globex AND acme	none (leak blocked)
globex token, ?tenant=globex	globex AND globex	globex's rows
no token	—	denied (401)

Rule of thumb. Enforce tenant isolation with a token-level row filter (tenant_id = '...') that Tinybird ANDs onto every query, and mint one narrow read token per consumer — never rely on a client-supplied parameter for isolation. The token, not the caller, is the source of truth for which rows are visible.

Senior interview question on Tinybird pipes and published endpoints

A senior interviewer might ask: "Stand up a sub-second product-analytics API on Tinybird over a high-volume event stream, with no backend. Cover how you ingest and sort the data, how you pre-aggregate so the endpoint stays sub-second under 20k req/min, how you publish a parametrised endpoint, and how you enforce multi-tenant isolation so no request can read another tenant's rows."

Solution Using a data source, a materialized pipe, a published endpoint, and a scoped token

-- 1. Ingest data source — sorted for the serving read pattern.
-- events.datasource
SCHEMA >
    `tenant_id` String, `user_id` UInt64,
    `feature` LowCardinality(String), `ts` DateTime
ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(ts)"
ENGINE_SORTING_KEY   "tenant_id, feature, ts"

-- 2. Pre-aggregate on ingest into an AggregatingMergeTree target.
-- usage_daily.datasource
SCHEMA >
    `tenant_id` String, `feature` LowCardinality(String), `day` Date,
    `dau_state` AggregateFunction(uniq, UInt64),
    `events_state` AggregateFunction(count)
ENGINE "AggregatingMergeTree"
ENGINE_SORTING_KEY "tenant_id, feature, day"

-- mv_usage_daily.pipe (TYPE materialized -> DATASOURCE usage_daily)
NODE mv
SQL >
    SELECT tenant_id, feature, toDate(ts) AS day,
           uniqState(user_id) AS dau_state, countState() AS events_state
    FROM events GROUP BY tenant_id, feature, day
TYPE materialized
DATASOURCE usage_daily

-- 3. Published parametrised endpoint reading the ROLLUP (sub-second).
-- daily_usage.pipe
NODE endpoint
SQL >
    %
    SELECT feature, uniqMerge(dau_state) AS dau, countMerge(events_state) AS events
    FROM usage_daily
    WHERE tenant_id = {{ String(tenant, required=True) }}
      AND day = {{ Date(day, required=True) }}
    GROUP BY feature ORDER BY dau DESC
    LIMIT {{ Int32(limit, 100) }}      -- bounded: no unbounded pull
TYPE endpoint

# 4. Multi-tenant isolation via a token-level row filter (one token per tenant).
tb token create --name "acme-read" \
  --scope "PIPES:READ:daily_usage" --filter "tenant_id = 'acme'"

Step-by-step trace.

Layer	Component	Purpose
Ingest	events data source	managed MergeTree, sorted for reads
Pre-aggregate	mv_usage_daily (materialized)	rollup maintained on every ingest
Rollup store	usage_daily (AggregatingMergeTree)	states, sparse-indexed
Endpoint	daily_usage (parametrised)	reads the rollup, LIMIT-bounded
Isolation	token row filter	tenant_id AND-ed onto every query
Freshness	ingest-time materialization	seconds fresh, no batch lag

After deployment, events stream into the events data source; the mv_usage_daily materialized pipe pre-aggregates them into usage_daily on every ingest; the daily_usage endpoint reads that rollup, merges the uniq/count states, and returns JSON in tens of milliseconds; the LIMIT bounds every response; and the acme-read token's tenant_id = 'acme' filter is AND-ed onto every query so no parameter can leak another tenant. There is no backend — the pipe is the API.

Output:

Metric	Naive (scan endpoint)	Tinybird (materialized + token)
Rows scanned per request	millions	a handful (rollup)
p95 latency at 20k req/min	seconds	< 100 ms
Freshness	—	seconds (ingest-time MV)
Cross-tenant leak risk	parameter-dependent	zero (token filter)
Backend code	a bespoke service	none (SQL pipes)

Why this works — concept by concept:

• Data source with a serving sort key — declaring the MergeTree sorting key as (tenant_id, feature, ts) makes the endpoint's filter a sparse-index range read, so even the raw-scan fallback is well-indexed rather than a full table scan.
• Materialized-view pipe — a TYPE materialized pipe pre-aggregates events into an AggregatingMergeTree target on every ingest, so the expensive aggregation runs once at write time and the endpoint reads a small, always-current rollup.
• Parametrised published endpoint — templated required=True parameters plus a LIMIT make the endpoint a bounded, governed API published straight from SQL, with no controller or deployment to maintain.
• Token-level row filter — a per-tenant token carrying tenant_id = '...' AND-ed onto every query enforces isolation at the platform, so a forged or omitted parameter cannot widen the caller's scope.
• Cost — one incremental aggregation per ingest batch and an O(rollup-rows) merge per request, versus an O(events) scan per request and a hand-written backend. The eliminated cost is an entire serving service per metric — O(SQL) to publish a governed endpoint instead of O(engineers) to build one.

Real-time analytics
Topic — real-time-analytics
Real-time analytics problems on streaming ingest and endpoints

Practice →

API integration Topic — api-integration API integration problems on published data endpoints

Practice →

---

3. Cube — semantic layer, pre-aggregations & the OLAP API

Define measures and dimensions once; pre-aggregations and caching serve every API sub-second

The mental model in one line: Cube is a semantic layer that sits over an OLAP store — ClickHouse, and also warehouses like Snowflake or BigQuery — where you define measures (aggregations), dimensions (grouping/filter fields), and joins once as a versioned data model, add pre-aggregations (materialized rollups) that Cube's query router transparently routes matching queries to for sub-second reads, and expose the whole model through three interchangeable APIs — REST, GraphQL, and a Postgres-wire SQL API — with a security context that injects a multi-tenant filter into every query, so consistent metric definitions, fast reads, and governed access all come from one model instead of being re-implemented per dashboard. You describe the metrics and the rollups; Cube compiles queries, routes them to a pre-aggregation when one fits, and serves them over any API.

The data model — define semantics once.

• Cubes. A cube maps to a table (or a SQL query) and declares its measures, dimensions, and joins; every consumer queries the cube, never the raw table, so a metric means the same thing everywhere.
• Measures. Aggregations with an explicit type — count, sum, count_distinct, avg — defined once (e.g. revenue: sum(amount)), so no dashboard can compute "revenue" a different way.
• Dimensions. The fields you group and filter by (region, feature, a time dimension), including derived ones (CASE-based buckets), so slicing is consistent across surfaces.
• Joins. Relationships between cubes with a cardinality (one_to_many, etc.); Cube generates the correct SQL join and avoids fan-out double-counting — the semantic-layer job an ad-hoc query gets wrong.

Pre-aggregations — where sub-second comes from.

• What they are. A pre-aggregation is a materialized rollup of a cube at a chosen granularity (measures × dimensions × time grain), stored in Cube's store or the source, and refreshed on a schedule or incrementally.
• Transparent routing. Cube's query planner checks whether an incoming query can be answered by an existing pre-aggregation; if the requested measures/dimensions/grain are a subset, it rewrites the query to hit the rollup — the consumer's query does not change, only where it lands.
• Additivity matters. A rollup can serve a coarser query only when its measures are additive over the extra dimensions (sums/counts roll up; count_distinct needs special handling like HLL), so the grain and measure types decide what a pre-aggregation can answer.
• Partitioned + incremental. Time-partitioned pre-aggregations refresh only the recent partition, so freshness is cheap — the analogue of an ingest-time rollup at the semantic layer.

The three APIs — one model, many contracts.

• REST API. A JSON query (measures, dimensions, filters, timeDimensions) over HTTP — the default for web dashboards; cache-friendly and simple.
• GraphQL API. The same model as a GraphQL schema, for consumers that prefer a typed graph and field selection.
• SQL API. A Postgres-wire endpoint so BI tools and notebooks can SELECT ... FROM cube in plain SQL — the semantic model exposed as a queryable database.
• One definition, three shapes. Because all three compile through the same model, a measure defined once is identical across REST, GraphQL, and SQL — no drift between a dashboard and a notebook.

Caching and security.

• Two-tier caching. Pre-aggregations are the first tier (materialized rollups); an in-memory result cache is the second (identical queries served from memory for a short TTL) — together they keep the source engine quiet.
• Security context. A signed token carries claims (tenant, role); queryRewrite (or COMPILE_CONTEXT) uses them to inject a mandatory filter into every query, so multi-tenant isolation is enforced in the model, not the client.
• Refresh alignment. Pre-aggregation refresh cadence sets the freshness floor; the result-cache TTL is aligned below it so served numbers are bounded-stale, never stale-forever.

The failure modes senior engineers pre-empt.

• Querying live without pre-aggregations. Every dashboard query hitting the source engine re-creates the scan-per-request problem. Mitigation: define pre-aggregations for the hot query shapes and confirm the planner routes to them.
• A pre-aggregation that never matches. A rollup at the wrong grain/measure set is never used, so you pay to build it and still scan live. Mitigation: build rollups from the actual query shapes and verify routing.
• Security context omitted. Forgetting the tenant filter in queryRewrite exposes every tenant. Mitigation: a mandatory tenant filter injected for every request; default-deny if the claim is absent.

Common interview probes on Cube.

• "What problem does the semantic layer solve?" — one consistent definition of measures/dimensions across every API and dashboard.
• "How does it stay sub-second?" — pre-aggregations (materialized rollups) that the query router transparently hits.
• "How is multi-tenancy enforced?" — a security context injecting a mandatory filter into every query via queryRewrite.
• "What APIs does it expose?" — REST, GraphQL, and a Postgres-wire SQL API over the same model.

Worked example — define a cube with measures and dimensions

Detailed explanation. The core Cube artifact is the data model: a cube declaring what a metric means. Define a Usage cube over an events table with additive measures and the dimensions dashboards slice by, so every API computes usage identically.

• Source. an events table (tenant, user, feature, ts, amount).
• Measures. count, distinctUsers (count_distinct), revenue (sum).
• Dimensions. feature, region, and a ts time dimension.

Question. Define a Usage cube whose measures and dimensions give dashboards a single, consistent definition of active users, events, and revenue by feature and time.

Input.

Piece	Value
Cube	Usage (over events)
Measures	count, distinctUsers, revenue
Dimensions	feature, region, ts (time)
Consumers	REST / GraphQL / SQL, same model

Code.

// model/cubes/Usage.js — the semantic definition (versioned as code).
cube(`Usage`, {
  sql_table: `events`,

  measures: {
    count:         { type: `count` },
    distinctUsers: { sql: `user_id`, type: `count_distinct` },
    revenue:       { sql: `amount`,  type: `sum`, format: `currency` },
  },

  dimensions: {
    feature: { sql: `feature`, type: `string` },
    region:  { sql: `region`,  type: `string` },
    ts:      { sql: `ts`,      type: `time` },     // the time dimension
    tenantId:{ sql: `tenant_id`, type: `string`, shown: false },
  },
});

// A REST query against the model — same measure names, any consumer.
{
  "measures":   ["Usage.distinctUsers", "Usage.count", "Usage.revenue"],
  "dimensions": ["Usage.feature"],
  "timeDimensions": [{ "dimension": "Usage.ts", "granularity": "day",
                       "dateRange": "Today" }]
}

Step-by-step explanation.

1. The Usage cube maps to the events table and declares three measures with explicit types: count, a count_distinct on user_id (active users), and a sum on amount (revenue) — so "active users" and "revenue" have one authoritative definition no dashboard can override.
2. The dimensions (feature, region, and the ts time dimension) are the fields consumers group and filter by; declaring them once means every surface slices usage the same way.
3. tenantId is a hidden dimension (shown: false) used by the security context for isolation, not something a dashboard selects — the model carries the isolation field without exposing it.
4. The REST query names measures and dimensions from the model (Usage.distinctUsers, Usage.feature) and a timeDimensions grain (day), so Cube compiles it to the correct SQL aggregation — the client never writes GROUP BY or count(DISTINCT ...) itself.
5. The same query works verbatim over the GraphQL and SQL APIs because all three compile through this one model — the semantic layer's core guarantee that a metric is identical everywhere.

Output.

Query element	Resolves to	Governed by
Usage.distinctUsers	count(DISTINCT user_id)	measure definition
Usage.revenue	sum(amount)	measure definition
dimensions: [feature]	GROUP BY feature	dimension definition
timeDimensions: day	toStartOfDay(ts) bucket	time dimension

Rule of thumb. Define every metric once as a typed measure and every slice as a dimension in the cube, and let consumers name them — never let a dashboard hand-write the aggregation. One model means a measure computes identically across REST, GraphQL, and SQL.

Worked example — a pre-aggregation the query router hits

Detailed explanation. The cube above still queries the source live. Add a pre-aggregation — a materialized rollup at the day/feature grain — and Cube's planner will transparently route matching queries to it, turning a live scan into a rollup lookup. Add and verify a rollup.

• The rollup. measures × feature × day, time-partitioned, refreshed hourly.
• The routing. a day/feature query is a subset → planner rewrites to the rollup.
• The win. sub-second reads without changing the consumer's query.

Question. Define a pre-aggregation for the hot "usage by feature by day" query shape and explain how Cube routes matching queries to it.

Input.

Aspect	Value
Grain	feature × day
Measures	count, distinctUsers, revenue
Partition	by month (ts)
Refresh	every 1 hour (incremental)

Code.

// Add a pre_aggregations block to the Usage cube.
cube(`Usage`, {
  sql_table: `events`,
  // ... measures & dimensions as before ...

  pre_aggregations: {
    byFeatureDay: {
      measures:   [Usage.count, Usage.distinctUsers, Usage.revenue],
      dimensions: [Usage.feature],
      time_dimension: Usage.ts,
      granularity: `day`,                 // the rollup's grain
      partition_granularity: `month`,     // partition → refresh only recent months
      refresh_key: { every: `1 hour` },   // freshness cadence
    },
  },
});

# Query routing (what Cube's planner does):
Incoming query: measures [distinctUsers, revenue], dimension [feature],
                time grain [day], range [last 30 days]

Planner check:
  - requested measures  ⊆ rollup measures?      yes
  - requested dimension ⊆ rollup dimensions?    yes (feature)
  - requested grain     = rollup grain (day)?   yes  (or coarser & additive)
  => REWRITE the query to read `byFeatureDay` rollup, NOT the raw events table.

Result: a lookup over ~ (features × days) rows, not a scan over raw events.

Step-by-step explanation.

1. The byFeatureDay pre-aggregation materialises the three measures at the feature × day grain — a rollup with roughly (features × days) rows per month, tiny compared to the raw event stream.
2. partition_granularity: month time-partitions the rollup so a refresh only rebuilds the current month's partition, and refresh_key: { every: '1 hour' } sets the freshness cadence — cheap incremental maintenance rather than a full rebuild.
3. When a dashboard query arrives, Cube's planner checks whether its measures, dimensions, and grain are a subset of the rollup's; the day/feature query matches, so the planner rewrites it to read byFeatureDay instead of events — transparently, without the consumer changing anything.
4. Routing works for coarser queries too when the measures are additive: a "revenue by month" query can sum the daily rollup, but a count_distinct cannot be rolled up naively, which is why measure types and grain decide what a rollup can answer.
5. The payoff is that the same REST/GraphQL/SQL query that used to scan the source now hits a small materialized rollup — sub-second — and you verify it by checking Cube's query plan shows the pre-aggregation was used, not the raw table.

Output.

Query	Routes to	Rows read
usage by feature by day (30d)	byFeatureDay rollup	~features × 30
revenue by month	rollup (additive sum)	~features × months
distinctUsers by minute	source (grain too fine)	live scan
a new dimension not in rollup	source (not a subset)	live scan

Rule of thumb. Build pre-aggregations from the actual hot query shapes (measures × dimensions × grain), time-partition them for cheap incremental refresh, and verify the planner routes to them. A rollup only helps if queries match it — additive measures roll up to coarser grains, count_distinct does not.

Worked example — a REST query with a multi-tenant security context

Detailed explanation. A single Cube deployment serving many tenants must scope every query to the caller's tenant, regardless of what the client requests. The security context reads signed token claims and injects a mandatory filter via queryRewrite. Wire tenant isolation.

• The token. a signed JWT carrying tenant_id.
• The rewrite. queryRewrite appends Usage.tenantId = <claim> to every query.
• The guarantee. no query, however crafted, escapes its tenant.

Question. Configure a security context so every Cube query — over any API — is filtered to the caller's tenant_id from a signed token, unbypassable by the client.

Input.

Piece	Value
Token claim	tenant_id
Injection	queryRewrite adds a filter
Applies to	REST, GraphQL, SQL — all APIs
Absent claim	default-deny

Code.

// cube.js — inject a mandatory tenant filter into EVERY query.
module.exports = {
  // The security context is the verified token payload.
  checkAuth: (req, auth) => {
    // verify JWT; attach claims as the security context
    req.securityContext = verifyJwt(auth);       // { tenant_id: "acme", role: "user" }
  },

  queryRewrite: (query, { securityContext }) => {
    if (!securityContext || !securityContext.tenant_id) {
      throw new Error("Forbidden: no tenant in context");   // default-deny
    }
    query.filters = query.filters || [];
    query.filters.push({
      member: "Usage.tenantId",
      operator: "equals",
      values: [securityContext.tenant_id],        // mandatory, unbypassable filter
    });
    return query;
  },
};

// The client's REST query does NOT mention tenant — the context injects it.
{
  "measures": ["Usage.distinctUsers"],
  "dimensions": ["Usage.feature"],
  "timeDimensions": [{ "dimension": "Usage.ts", "granularity": "day",
                       "dateRange": "Today" }]
}
// Effective query Cube runs: the above + filter Usage.tenantId = "acme".

Step-by-step explanation.

1. checkAuth verifies the request's JWT and attaches its payload as securityContext, so downstream code has trustworthy claims (tenant_id, role) rather than anything the client can set in the query body.
2. queryRewrite runs for every query on every API (REST, GraphQL, SQL); it pushes a filter Usage.tenantId = securityContext.tenant_id onto the query before compilation — the injection point that makes isolation universal.
3. If the context has no tenant_id, queryRewrite throws — default-deny — so a missing or unverified token can never fall through to an unscoped query that returns every tenant's data.
4. Because the client's query never mentions tenant and cannot remove the injected filter, there is no query shape — over any API — that escapes its tenant; isolation lives in the model, not in fragile client code.
5. The senior framing: the security context is the semantic-layer analogue of row-level security — one central rewrite enforces multi-tenancy for the REST dashboard, the GraphQL app, and the SQL-API notebook alike, instead of each re-implementing the filter.

Output.

Caller (token)	Client query mentions tenant?	Effective filter
acme	no	tenantId = 'acme'
globex	no	tenantId = 'globex'
acme, tries to add tenantId=globex	yes (ignored)	still tenantId = 'acme'
no/invalid token	—	Forbidden (default-deny)

Rule of thumb. Enforce multi-tenancy in queryRewrite off verified security-context claims, injecting a mandatory tenant filter into every query and defaulting to deny when the claim is absent. The client's query is never trusted for isolation — the context is, and it covers REST, GraphQL, and SQL from one place.

Senior interview question on Cube's semantic layer and pre-aggregations

A senior interviewer might ask: "Serve product-analytics metrics to a web dashboard, a mobile app, and analysts' notebooks from one Cube deployment over an OLAP store. Cover how you define metrics once so they're consistent across APIs, how you make the hot dashboard queries sub-second, how you keep multi-tenant data isolated across every API, and how you keep the source engine from being scanned on every request."

Solution Using a semantic model, a rollup pre-aggregation, the REST API, and a security context

// 1. One semantic model: measures/dimensions defined once, consistent everywhere.
cube(`Usage`, {
  sql_table: `events`,
  measures: {
    count:         { type: `count` },
    distinctUsers: { sql: `user_id`, type: `count_distinct` },
    revenue:       { sql: `amount`,  type: `sum`, format: `currency` },
  },
  dimensions: {
    feature:  { sql: `feature`, type: `string` },
    ts:       { sql: `ts`, type: `time` },
    tenantId: { sql: `tenant_id`, type: `string`, shown: false },
  },

  // 2. Pre-aggregation: the hot query shape, materialized + incrementally refreshed.
  pre_aggregations: {
    byFeatureDay: {
      measures: [Usage.count, Usage.distinctUsers, Usage.revenue],
      dimensions: [Usage.feature],
      time_dimension: Usage.ts, granularity: `day`,
      partition_granularity: `month`, refresh_key: { every: `1 hour` },
    },
  },
});

// 3. Security context: a mandatory tenant filter on EVERY query, every API.
module.exports = {
  checkAuth: (req, auth) => { req.securityContext = verifyJwt(auth); },
  queryRewrite: (query, { securityContext }) => {
    if (!securityContext?.tenant_id) throw new Error("Forbidden");
    (query.filters ||= []).push({
      member: "Usage.tenantId", operator: "equals",
      values: [securityContext.tenant_id],
    });
    return query;
  },
};

// 4. The SAME query served over REST, GraphQL, and SQL — one definition, three shapes.
{ "measures": ["Usage.distinctUsers", "Usage.revenue"],
  "dimensions": ["Usage.feature"],
  "timeDimensions": [{ "dimension": "Usage.ts", "granularity": "day",
                       "dateRange": "Last 30 days" }] }
// -> planner routes to `byFeatureDay` rollup; context injects tenant filter.

Step-by-step trace.

Layer	Component	Purpose
Semantics	Usage cube	one definition of every measure/dimension
Consistency	REST / GraphQL / SQL	same model, three API shapes
Speed	byFeatureDay pre-aggregation	hot queries hit a rollup, sub-second
Freshness	refresh_key: 1 hour, partitioned	cheap incremental refresh
Isolation	queryRewrite tenant filter	every query scoped, every API
Safety	default-deny on missing claim	no unscoped query ever runs

After deployment, all three consumers query the same Usage model, so a measure means one thing everywhere; the hot "usage by feature by day" query is transparently rewritten by the planner to read the byFeatureDay rollup, refreshed hourly and partitioned by month; the security context injects tenantId = <claim> into every query on every API and denies requests without a tenant claim; and the source engine is scanned only when the pre-aggregation refreshes, not per request.

Output:

Metric	Ad-hoc dashboards	Cube (model + rollup + context)
Metric consistency	drifts per dashboard	one definition, all APIs
Hot-query latency	seconds (live scan)	< 100 ms (rollup)
Multi-tenant isolation	client-dependent	injected filter, every API
Source engine load	every request	pre-aggregation refresh only
Freshness	ad hoc	bounded (hourly, incremental)

Why this works — concept by concept:

• Semantic model — defining measures and dimensions once in a cube means "active users" and "revenue" compile to the same SQL across REST, GraphQL, and the SQL API, eliminating the metric drift that plagues per-dashboard definitions.
• Routed pre-aggregation — a materialized rollup at the hot query's grain, which the planner transparently rewrites matching queries to, turns a live source scan into a sub-second rollup lookup without the consumer changing a thing.
• Partitioned incremental refresh — time-partitioning plus a refresh key rebuilds only the recent partition, so the rollup stays fresh cheaply instead of via a full rebuild.
• Security context — a queryRewrite injecting a mandatory tenant filter off verified claims (default-deny when absent) enforces multi-tenancy in the model, so no query on any API escapes its tenant.
• Cost — one incremental rollup refresh per cadence and an O(rollup-rows) read per request, versus a live source scan per dashboard query per consumer. The eliminated cost is per-dashboard re-implementation and per-request scanning — O(model) to define a governed metric layer instead of O(dashboards) to rebuild it everywhere.

API integration
Topic — api-integration
API integration problems on semantic-layer APIs

Practice →

Optimization Topic — optimization Optimization problems on pre-aggregations and query routing

Practice →

---

4. ClickHouse — MergeTree, materialized views & the HTTP interface

MergeTree stores it, a materialized view pre-aggregates it, the HTTP interface serves it as an API

The mental model in one line: ClickHouse is the column-store OLAP engine under Tinybird and often under Cube, and four features make it a sub-second serving engine — the MergeTree family stores data physically sorted by an ORDER BY key whose sparse primary index turns filtered reads into small range scans, materialized views pre-aggregate at INSERT time into an AggregatingMergeTree so a rollup is maintained incrementally rather than scanned, projections keep an alternate sort order or aggregation inside the same table so different query shapes each hit an index, and the HTTP interface answers parametrised SQL over HTTP so ClickHouse can itself be the app-facing OLAP API — meaning you can build the whole serving layer directly, without a separate serving tool, when you want the thinnest possible stack. The ORDER BY key is the single most important design decision: it is the index.

MergeTree — the storage and index model.

• ORDER BY is the primary index. MergeTree stores rows sorted by the ORDER BY key and keeps a sparse index (one entry per granule of ~8,192 rows); a filter on a prefix of the key reads only the matching granules, so put the columns your serving reads filter on first (tenant_id, time).
• Sparse, not per-row. The index marks granule boundaries, not every row, so it is tiny and cache-resident — the read jumps to the right granules and scans a few thousand rows, not the table.
• Partitioning. PARTITION BY (e.g. toYYYYMM(ts)) splits the table into parts you can drop or refresh independently and prunes whole partitions from a time-bounded query — coarser than the index, a maintenance and pruning tool.
• LowCardinality and codecs. LowCardinality(String) dictionary-encodes repetitive columns (feature, region) and per-column codecs (Delta, ZSTD) shrink storage and I/O — less to scan means faster reads.

Materialized views — pre-aggregate at INSERT.

• Insert-time trigger. A ClickHouse materialized view is a trigger: when rows are inserted into the source table, the view's SELECT runs on that block and writes the result into a target table — it is not a cached query, it is an incremental pipeline.
• AggregatingMergeTree target. The target stores partial aggregate states (sumState, uniqState, countState); background merges combine states for the same key, and reads finalise with -Merge — so distinct counts and sums are maintained without ever re-scanning source rows.
• The rollup shrinks the read. The serving query reads the small aggregated target (rows per key per grain) instead of the raw fact table — the same O(rows) → O(rollup) win, native to the engine.
• POPULATE vs backfill. Creating the view with POPULATE seeds it from existing data once; for large tables you instead backfill with an INSERT ... SELECT to control load — a senior operational detail.

Projections — alternate indexes inside one table.

• What they are. A projection is an extra copy of a table's data, stored in a different ORDER BY (or pre-aggregated), maintained inside the same table and chosen automatically by the optimizer when it fits a query.
• Why they help. One MergeTree can be optimally sorted for only one access pattern; a projection gives a second pattern its own sorted/aggregated copy, so "by tenant+time" and "by feature+time" queries both hit an index — without a separate table to keep in sync.
• Trade-off. Projections cost extra storage and write amplification (each insert maintains them), so add them for genuinely hot secondary shapes, not speculatively.

The HTTP interface — ClickHouse as an API.

• Parametrised SQL over HTTP. POST /?query=... (or GET) runs SQL and returns the chosen FORMAT (JSON, JSONEachRow, CSV); query parameters ({name:Type}) bind values safely — a thin app-facing endpoint with no extra service.
• Read-only, quota'd users. Serve app traffic through a dedicated user with readonly = 2, max_execution_time, max_rows_to_read, and max_concurrent_queries_for_user — guardrails that stop a single endpoint from monopolising the engine.
• Output formats for apps. FORMAT JSONEachRow streams newline-delimited JSON that app code consumes directly — the OLAP API without a translation layer.

The failure modes senior engineers pre-empt.

• Wrong ORDER BY. A sort key that does not match the serving filter turns every read into a full scan. Mitigation: order by the serving filter prefix (tenant_id, time); verify with EXPLAIN.
• Serving from the raw fact table. Point-reading raw events per request scans granules unnecessarily. Mitigation: a materialized view into an AggregatingMergeTree rollup; read the rollup.
• Unbounded HTTP queries. An open SQL-over-HTTP endpoint lets a caller run anything. Mitigation: parametrised queries only, a readonly quota'd user, max_rows_to_read/max_execution_time, and LIMIT.

Common interview probes on ClickHouse serving.

• "Why is ClickHouse fast for serving?" — column store + a sparse primary index on the ORDER BY key that reads only matching granules.
• "How do you keep a rollup fresh?" — a materialized view that pre-aggregates at INSERT into an AggregatingMergeTree.
• "One table, two access patterns?" — a projection with a second ORDER BY, chosen automatically.
• "How does ClickHouse become an API?" — the HTTP interface running parametrised SQL through a read-only, quota'd user.

Worked example — a MergeTree table sorted for point reads

Detailed explanation. The foundational decision is the ORDER BY key: it determines which serving reads are sub-second. Design an events table sorted and partitioned so a per-tenant, time-bounded read touches a handful of granules. Show why the key order matters.

• Serving read. WHERE tenant_id = ? AND ts BETWEEN ? AND ?.
• The key. ORDER BY (tenant_id, ts) — tenant first (equality), time second (range).
• Partition. toYYYYMM(ts) prunes months outside the range.

Question. Create an events MergeTree whose sort key and partitioning make a per-tenant, time-ranged serving read a small granule scan rather than a full table scan.

Input.

Aspect	Choice	Why
ORDER BY	(tenant_id, ts)	filter prefix first
PARTITION BY	toYYYYMM(ts)	prune months
feature type	LowCardinality(String)	dictionary-encode
Read	tenant_id = ? AND ts range	granule range scan

Code.

CREATE TABLE events
(
    tenant_id  String,
    user_id    UInt64,
    feature    LowCardinality(String),
    amount     Decimal(12, 2),
    ts         DateTime CODEC(Delta, ZSTD)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, ts);          -- the ORDER BY key IS the primary index

-- A serving read: tenant equality + time range -> a small granule range scan.
SELECT feature, count() AS events, sum(amount) AS revenue
FROM events
WHERE tenant_id = {tenant:String}
  AND ts >= {from:DateTime} AND ts < {to:DateTime}
GROUP BY feature;

-- Prove it reads few granules, not the table:
EXPLAIN indexes = 1
SELECT count() FROM events
WHERE tenant_id = 'acme' AND ts >= '2026-08-01' AND ts < '2026-08-27';

Step-by-step explanation.

1. ORDER BY (tenant_id, ts) physically sorts rows by tenant, then time, and builds a sparse primary index over that order — so a query filtering tenant_id = ? jumps straight to that tenant's contiguous granules instead of scanning everyone's rows.
2. Putting tenant_id first (an equality predicate) and ts second (a range predicate) matches the classic index rule: equality columns before range columns, so both parts of the WHERE use the index.
3. PARTITION BY toYYYYMM(ts) lets ClickHouse prune entire months outside the time range before it even consults the index — a coarse first-pass filter that shrinks the candidate granules further.
4. LowCardinality(String) on feature dictionary-encodes it and CODEC(Delta, ZSTD) on ts compresses monotonic timestamps, so the granules that are read carry less I/O — smaller reads, faster response.
5. EXPLAIN indexes = 1 shows how many granules survive index and partition pruning; a well-ordered table reports a handful of granules read, proving the serving read is a range scan, not a full scan — the verification a senior engineer always does.

Output.

Read	Without index match	With ORDER BY (tenant_id, ts)
one tenant, one month	full table scan	a few granules (range)
granules read	all	handful (pruned)
p95	seconds	milliseconds
verified by	—	EXPLAIN indexes = 1

Rule of thumb. Order a serving MergeTree by the read's filter prefix — equality columns (tenant) before range columns (time) — and partition by the time bucket to prune, then confirm with EXPLAIN indexes = 1 that reads touch a handful of granules. The ORDER BY key is the index; get it wrong and every read is a full scan.

Worked example — an AggregatingMergeTree materialized view

Detailed explanation. Even a well-sorted table scans granules per request. For the hottest metric, pre-aggregate at INSERT with a materialized view into an AggregatingMergeTree, so the serving read is a merge over a rollup. Build the incremental DAU/revenue rollup.

• Target. usage_daily on AggregatingMergeTree, storing states.
• View. on INSERT into events, aggregate the block into usage_daily.
• Read. -Merge finalises the states — a rollup lookup.

Question. Pre-aggregate events into a daily per-feature rollup maintained on every INSERT, and serve distinct users and revenue from the rollup.

Input.

Piece	Value
Target engine	AggregatingMergeTree
State columns	uniqState(user_id), sumState(amount)
Trigger	materialized view on INSERT
Read	uniqMerge / sumMerge

Code.

-- 1. The rollup target: stores partial aggregate STATES, sorted for the read.
CREATE TABLE usage_daily
(
    tenant_id     String,
    feature       LowCardinality(String),
    day           Date,
    dau_state     AggregateFunction(uniq, UInt64),
    revenue_state AggregateFunction(sum, Decimal(12, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, feature, day);

-- 2. The materialized view: a TRIGGER that runs on every INSERT into events.
CREATE MATERIALIZED VIEW usage_daily_mv TO usage_daily AS
SELECT tenant_id, feature, toDate(ts) AS day,
       uniqState(user_id) AS dau_state,
       sumState(amount)   AS revenue_state
FROM events
GROUP BY tenant_id, feature, day;

-- 3. The serving read: merge the states -> final numbers, over a tiny rollup.
SELECT feature,
       uniqMerge(dau_state)   AS dau,
       sumMerge(revenue_state) AS revenue
FROM usage_daily
WHERE tenant_id = {tenant:String} AND day = today()
GROUP BY feature;

Step-by-step explanation.

1. usage_daily is declared on AggregatingMergeTree and stores AggregateFunction states, not final values — so background merges can combine states for the same (tenant, feature, day) key without seeing raw rows, and reads finalise them later.
2. usage_daily_mv ... TO usage_daily is a trigger: on each INSERT block into events, its SELECT aggregates just that block (uniqState, sumState) and writes the partial states into the target — incremental maintenance, not a scheduled rebuild.
3. Multiple inserts produce multiple partial-state rows for the same key; AggregatingMergeTree's background merges combine them, and the serving read's uniqMerge/sumMerge finalises whatever states currently exist — always correct, always current to the last insert.
4. The serving read groups over usage_daily (a few rows per tenant/day), sorted by (tenant_id, feature, day), so it is both tiny and index-matched — the two properties that make it sub-second at high QPS.
5. The invariant is amortisation: the uniq/sum work happens once per insert block and is shared across every future read, so a metric that would cost an O(events) scan per request costs an O(rollup) merge instead.

Output.

Read	Raw events scan	usage_daily rollup
DAU + revenue today	scan all of today's events	merge a few state rows
rows read	millions	handful
aggregation	per request	once, at INSERT
freshness	real-time	to the last insert (seconds)

Rule of thumb. For the hottest metric, pre-aggregate at INSERT with a materialized view into an AggregatingMergeTree storing -State columns, and finalise with -Merge at read time. The aggregation runs once per insert block and is shared by every read — an O(events) scan becomes an O(rollup) merge.

Worked example — the HTTP interface serving a parametrised query

Detailed explanation. ClickHouse can be the app-facing API directly: the HTTP interface runs parametrised SQL and returns JSON, through a locked-down read-only user. Serve the rollup over HTTP as an OLAP API. Wire the endpoint and its guardrails.

• The query. the usage_daily read, parametrised on tenant/day.
• The transport. POST /?... with bound param_* values, FORMAT JSONEachRow.
• The user. readonly = 2, row/time/concurrency quotas.

Question. Serve the daily-usage rollup as a parametrised HTTP OLAP API, safely bound and quota-limited so no caller can run an unbounded or arbitrary query.

Input.

Concern	Setting
Binding	{tenant:String}, {day:Date}
Format	JSONEachRow
User	readonly = 2
Quotas	max_rows_to_read, max_execution_time, concurrency

Code.

<!-- users.xml — a locked-down serving user for the app-facing API. -->
<clickhouse><users>
  <serving_api>
    <profile>serving_profile</profile>
    <networks><ip>::/0</ip></networks>
  </serving_api>
</users>
<profiles>
  <serving_profile>
    <readonly>2</readonly>                          <!-- SELECT + settings only -->
    <max_rows_to_read>5000000</max_rows_to_read>    <!-- cap scan size -->
    <max_execution_time>2</max_execution_time>      <!-- seconds -->
    <max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
  </serving_profile>
</profiles></clickhouse>

# The app-facing API call: parametrised SQL over HTTP, JSON out, bound values.
curl "https://ch-host:8443/?param_tenant=acme&param_day=2026-08-26" \
  --user 'serving_api:...' \
  --data-binary "
    SELECT feature,
           uniqMerge(dau_state)    AS dau,
           sumMerge(revenue_state) AS revenue
    FROM usage_daily
    WHERE tenant_id = {tenant:String} AND day = {day:Date}
    GROUP BY feature
    ORDER BY dau DESC
    LIMIT 100
    FORMAT JSONEachRow"
# -> {"feature":"search","dau":"8421","revenue":"12043.50"}
# -> {"feature":"upload","dau":"5310","revenue":" 8830.00"}

Step-by-step explanation.

1. The {tenant:String} and {day:Date} placeholders are bound from the param_tenant/param_day query-string values, so user input is parameterised, never string-concatenated into SQL — the injection-safe way to serve HTTP queries.
2. FORMAT JSONEachRow returns newline-delimited JSON objects that app code consumes directly, so ClickHouse is the API and there is no translation service in between.
3. The request authenticates as the serving_api user, whose serving_profile sets readonly = 2 — it can run SELECT and adjust settings but cannot write or perform DDL, so a compromised endpoint cannot mutate data.
4. The profile's quotas — max_rows_to_read, max_execution_time, and max_concurrent_queries_for_user — cap how much any single request or user can consume, so one heavy or runaway query is aborted rather than allowed to monopolise the engine.
5. Reading the pre-aggregated usage_daily (not raw events) with a LIMIT keeps each request a tiny, bounded, index-matched rollup read — the combination of pre-aggregation, parameterisation, and quotas that makes the HTTP interface a safe, sub-second OLAP API.

Output.

Guardrail	Effect
{tenant:String} binding	injection-safe parameters
readonly = 2	no writes/DDL from the API
max_rows_to_read	over-large scans aborted
max_concurrent_queries_for_user	one caller can't hog slots
reads usage_daily + LIMIT	bounded, sub-second response

Rule of thumb. Expose ClickHouse over HTTP as an OLAP API only through a readonly, quota'd user running parametrised SQL against a pre-aggregated rollup with a LIMIT. Parameter binding stops injection, the profile quotas stop runaway queries, and reading the rollup keeps every request sub-second.

Senior interview question on ClickHouse serving internals

A senior interviewer might ask: "Build a sub-second product-analytics serving layer directly on ClickHouse, no extra serving tool. Cover how you sort and partition the raw table for fast reads, how you keep a hot metric pre-aggregated and fresh without scheduled batches, how you serve a second access pattern from the same table, and how you expose it as a safe app-facing API."

Solution Using MergeTree, an AggregatingMergeTree materialized view, a projection, and the HTTP interface

-- 1. Raw events, sorted for the primary serving read (tenant + time).
CREATE TABLE events
(
    tenant_id String, user_id UInt64,
    feature LowCardinality(String), amount Decimal(12,2),
    ts DateTime CODEC(Delta, ZSTD)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, ts);

-- 2. A projection for a SECOND access pattern (by feature+time) inside the same table.
ALTER TABLE events ADD PROJECTION by_feature
(
    SELECT tenant_id, feature, ts, amount
    ORDER BY (tenant_id, feature, ts)
);
ALTER TABLE events MATERIALIZE PROJECTION by_feature;

-- 3. Pre-aggregate the hot metric on INSERT into an AggregatingMergeTree rollup.
CREATE TABLE usage_daily
(
    tenant_id String, feature LowCardinality(String), day Date,
    dau_state AggregateFunction(uniq, UInt64),
    revenue_state AggregateFunction(sum, Decimal(12,2))
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, feature, day);

CREATE MATERIALIZED VIEW usage_daily_mv TO usage_daily AS
SELECT tenant_id, feature, toDate(ts) AS day,
       uniqState(user_id) AS dau_state, sumState(amount) AS revenue_state
FROM events GROUP BY tenant_id, feature, day;

# 4. Serve it over HTTP as a parametrised OLAP API (readonly, quota'd user).
curl "https://ch-host:8443/?param_tenant=acme&param_day=2026-08-26" \
  --user 'serving_api:...' --data-binary "
    SELECT feature, uniqMerge(dau_state) AS dau, sumMerge(revenue_state) AS revenue
    FROM usage_daily
    WHERE tenant_id = {tenant:String} AND day = {day:Date}
    GROUP BY feature ORDER BY dau DESC LIMIT 100 FORMAT JSONEachRow"

Step-by-step trace.

Layer	Component	Purpose
Storage	events MergeTree	sorted (tenant_id, ts), partitioned
Second pattern	by_feature projection	index for feature+time, same table
Pre-aggregate	usage_daily_mv on INSERT	incremental DAU/revenue rollup
Rollup store	usage_daily (AggregatingMergeTree)	states, sparse-indexed
API	HTTP interface	parametrised SQL → JSON
Safety	readonly + quotas	no writes, no runaway queries

After deployment, events is sorted for the primary tenant+time read and carries a by_feature projection so a feature+time query also hits an index without a second table; the usage_daily_mv materialized view maintains a daily DAU/revenue rollup on every INSERT; and the HTTP interface serves the rollup as a parametrised JSON API through a read-only, quota'd user. The raw fact table is scanned only by the projection/view maintenance, never on the request hot path.

Output:

Metric	Naive single MergeTree	Full ClickHouse serving layer
Primary read (tenant+time)	granule range scan	granule range scan
Secondary read (feature+time)	full scan	projection index scan
Hot metric per request	O(events) scan	O(rollup) merge
Freshness	—	to the last insert (seconds)
API safety	ad hoc	parametrised + readonly + quotas

Why this works — concept by concept:

• MergeTree ORDER BY — sorting by (tenant_id, ts) makes the primary serving read a sparse-index granule range scan instead of a full table scan; the sort key is the index, so the read touches a handful of granules.
• Projection — an alternate ORDER BY (tenant_id, feature, ts) copy maintained inside the same table lets a second access pattern hit an index too, chosen automatically by the optimizer, without a separate table to keep in sync.
• AggregatingMergeTree materialized view — pre-aggregating at INSERT into -State columns and finalising with -Merge maintains the hot metric incrementally, turning an O(events) scan per request into an O(rollup) merge that is fresh to the last insert.
• HTTP interface with guardrails — parametrised SQL over HTTP through a readonly, quota'd user makes ClickHouse itself the app-facing OLAP API, injection-safe and protected from runaway queries, with no extra service.
• Cost — incremental view/projection maintenance at write time and O(rollup) reads at request time, versus O(events) scans per request and extra sync'd tables. The eliminated cost is a separate serving backend and a second copy to reconcile — one engine serves storage, pre-aggregation, and the API.

Optimization
Topic — optimization
Optimization problems on indexing, sorting, and pre-aggregation

Practice →

Time series Topic — time-series Time-series problems on partitioning and rollups

Practice →

---

5. The serving architecture — pre-aggregation, caching, concurrency & cost

Pre-aggregate the hot path, cache the repeat, cap concurrency; the OLAP store serves the API, not the warehouse

The mental model in one line: a production low-latency serving architecture is a short pipeline — ingest (stream or batch) feeds an OLAP store (ClickHouse), a serving tool (Tinybird or Cube) or the store's own HTTP interface turns requests into pre-aggregated reads, an API gateway authenticates and rate-limits, and app tiles read the result — wrapped in caching tiers (CDN edge, result cache, pre-aggregation) where the single most important decision is pre-aggregate-versus-query-live driven by the freshness/latency/cost triangle, and the single most important cost driver is rows-scanned-per-request bounded by concurrency caps. Every component exists to keep the raw warehouse off the request hot path and to survive the concurrency a product surface generates that a batch job never does.

The serving-layer anatomy.

• Ingest. A stream (Kafka, the Tinybird Events API, CDC) or batch load lands rows in the OLAP store, where a materialized view can pre-aggregate them on arrival — the freshness source for live product analytics.
• OLAP store. ClickHouse (managed by Tinybird, or standalone under Cube) built for sub-second aggregate and point reads via MergeTree ordering, materialized views, and projections — never the raw batch warehouse.
• Serving tool. Tinybird (pipes → published endpoints) or Cube (semantic model → REST/GraphQL/SQL), or the ClickHouse HTTP interface directly — the layer that turns a governed query into an API.
• Gateway + app. An API gateway does authn (token/JWT), per-tenant rate limits, and edge caching; app tiles consume JSON. The warehouse only backfills the OLAP store on a schedule.

Pre-aggregate vs query-live — the freshness/latency/cost triangle.

• Pre-aggregate (materialized rollup). Fast and cheap per request, but only as fresh as the rollup and costly to store/maintain. For hot, freshness-tolerant reads (usage tiles, dashboards).
• Query-live (raw OLAP read). Fresh and flexible, but scans more per request and costs more under load. For cold, ad-hoc, or freshness-critical low-volume reads.
• The triangle. You cannot maximise freshness, latency, and cost at once — pick two. A stated SLO (p95 < 100 ms, freshness ≤ 60 s) resolves the tension per metric.
• Ingest-time is the third option. When you need both fresh and fast, an ingest-time materialized view maintains the rollup incrementally — the point of the triangle that trades storage/write cost for both freshness and latency.

Caching tiers.

• CDN/edge cache. For public, cacheable GETs (parametrised endpoints with a bounded TTL) — absorbs the hottest keys before they reach your infrastructure.
• Result cache. At the serving tool or gateway — caches full query results keyed on the query + identity for a short TTL, so identical dashboard refreshes do not re-hit the store (Cube's in-memory cache, Tinybird's endpoint caching, ClickHouse's query cache).
• Pre-aggregation. The deepest tier — a materialized rollup so even a cache miss reads a rollup, not raw events. The three tiers stack: edge for repeats, result cache for identical queries, pre-aggregation for the miss.

Concurrency and cost.

• Concurrency is rows-scanned-bound. An OLAP store serves more concurrent requests when each scans fewer rows; pre-aggregation shrinks rows-scanned-per-request, so the same engine serves far more tiles. Cap with max_concurrent_queries and per-user quotas so a spike queues rather than collapses.
• Cost is rows-scanned × requests. A live scan costs O(rows) per request; a rollup read costs O(rollup) amortised against a periodic merge. Cost control is pre-aggregation plus caching, not bigger machines.
• Refresh alignment. Cache TTLs and pre-aggregation refresh cadence must align to the freshness SLO so served numbers are bounded-stale, and caches self-heal within a refresh window.
• Backpressure. Statement timeouts, max_execution_time, and rate limits ensure a spike or a heavy query degrades gracefully instead of cascading.

The failure modes senior engineers pre-empt.

• Serving from the raw warehouse. Ad-hoc scans on the batch engine per request → slow, expensive, and contends with batch jobs. Mitigation: an OLAP store fronts it, refreshed on a schedule.
• No pre-aggregation under load. Every request scanning raw events exhausts concurrency. Mitigation: pre-aggregate the hot shapes; reads become O(rollup).
• Stale-forever or leaky caches. A cache with no invalidation, or a shared key across tenants, serves wrong or cross-tenant data. Mitigation: TTL aligned to refresh cadence; identity in the cache key.

Common interview probes on serving architecture.

• "Where does authentication vs authorization live?" — authn at the gateway; authorization in the serving layer (token filters / security context / row policies).
• "How do you serve thousands of concurrent tiles?" — pre-aggregate to shrink rows-scanned-per-request, cache repeats, cap concurrency.
• "Pre-aggregate or query live?" — by the SLO: pre-aggregate hot/freshness-tolerant; ingest-time MV when you need both; live for cold/ad-hoc.
• "How do you keep caches correct?" — align TTL to refresh cadence; scope keys by identity; invalidate on refresh.

Worked example — pre-aggregation vs live query under an SLO

Detailed explanation. The architecture's central choice is per-metric: pre-aggregate or query live. An SLO — a latency target and a freshness target — resolves it. Walk three metrics through the freshness/latency/cost triangle and place each.

• Metric A. Exec usage tile: p95 < 100 ms, freshness ≤ 15 min.
• Metric B. Live ops counter: p95 < 500 ms, freshness ≤ 10 s.
• Metric C. Analyst explorer: p95 < 5 s, freshness = live.

Question. For each metric, choose pre-aggregate, ingest-time materialized view, or query-live, and the store it reads, justified by its SLO.

Input.

Metric	Latency SLO	Freshness SLO	Choice
A: exec tile	< 100 ms	≤ 15 min	scheduled rollup + result cache
B: live counter	< 500 ms	≤ 10 s	ingest-time materialized view
C: analyst	< 5 s	live	live query over the OLAP store

Code.

Freshness / Latency / Cost — pick two; the SLO decides which.

Metric A (exec tile)   p95<100ms, freshness<=15m
  -> PRE-AGGREGATE: rollup refreshed every 15m + result cache (TTL 60s)
     fast + cheap; trades freshness (bounded 15m). OLAP store touched only on refresh.

Metric B (live counter) p95<500ms, freshness<=10s
  -> INGEST-TIME MV: an AggregatingMergeTree materialized view maintained on INSERT
     fresh + fast; trades cost (write amplification + storage). Not a scheduled batch.

Metric C (analyst)      p95<5s, freshness=live
  -> QUERY LIVE: raw OLAP read, no rollup
     fresh + flexible; trades latency (seconds) & per-query cost. Low volume makes it fine.

Anti-pattern for all three: ad-hoc queries against the raw batch warehouse on the hot path.

-- Metric A serving store: a scheduled rollup aligned to the 15-min SLO.
CREATE TABLE exec_tile_daily
( tenant_id String, metric String, value Decimal(18,2), as_of DateTime )
ENGINE = ReplacingMergeTree(as_of)
ORDER BY (tenant_id, metric);
-- Refreshed every 15 min by INSERT ... SELECT; result cache TTL 60s < 15 min (self-heals).

Step-by-step explanation.

1. Metric A's tight latency SLO (< 100 ms) rules out a live scan, and its loose freshness SLO (≤ 15 min) permits a scheduled rollup — so a rollup refreshed every 15 minutes plus a short result cache is the cheapest way to hit the target.
2. Metric B needs both low latency and near-real-time freshness (≤ 10 s), which a scheduled rollup cannot give and a live scan cannot serve fast — so an ingest-time materialized view (maintained on INSERT) is the only point of the triangle that satisfies it, at the cost of write amplification.
3. Metric C is freshness-critical but latency-tolerant (< 5 s) and low-volume, so a live OLAP read is correct — pre-aggregating every ad-hoc slice an analyst might want is impossible and pointless.
4. The triangle is the reasoning tool: you cannot maximise freshness, latency, and cost simultaneously, so each SLO names which two to optimise and which one to trade — and the trade is explicit, not accidental.
5. The invariant across all three is that none queries the raw batch warehouse on the hot path: A reads a scheduled rollup, B reads an ingest-time MV, C reads the OLAP store live — the warehouse only ever backfills those stores.

Output.

Metric	Serving store	Optimises	Trades
A: exec tile	scheduled rollup + cache	latency, cost	freshness (≤15m)
B: live counter	ingest-time MV	freshness, latency	cost (write amp)
C: analyst	live OLAP read	freshness, flexibility	latency, per-query cost
all	never the raw warehouse hot path	—	—

Rule of thumb. Resolve pre-aggregate-vs-live per metric with its SLO: a scheduled rollup when latency is tight and freshness is loose, an ingest-time materialized view when you need both, a live OLAP read only for freshness-critical low-volume access. Whatever you choose, the OLAP store serves the request and the warehouse only backfills it.

Worked example — caching tiers and refresh alignment

Detailed explanation. Sub-second at scale is a stack of caches, each catching a different kind of repeat, with TTLs aligned to the pre-aggregation cadence so nothing serves stale-forever. Configure the three tiers for a hot usage endpoint and align their TTLs.

• Edge. CDN caches the parametrised GET for public, non-viewer-specific tiles.
• Result. the serving tool caches identical queries per identity for a short TTL.
• Pre-aggregation. the rollup makes even a full miss a rollup read.

Question. Configure edge, result, and pre-aggregation caching for a usage endpoint and align every TTL to a 15-minute rollup refresh so served data is bounded-stale.

Input.

Tier	Catches	TTL
CDN edge	repeat public GETs	60 s
Result cache	identical query + identity	60 s
Pre-aggregation	the cache miss	refresh 15 min
Alignment	all TTLs ≤ refresh cadence	≤ 15 min

Code.

Request path with three cache tiers (hot usage tile):

  app tile
    -> CDN edge cache        (TTL 60s)  HIT? serve, 0 backend cost
       MISS
    -> result cache          (TTL 60s, key = query + tenant)  HIT? serve
       MISS
    -> pre-aggregated rollup (refreshed every 15m)  read a few rows
       (raw OLAP store scanned ONLY by the 15-min refresh, never per request)

TTL alignment rule:
  cache TTL (60s)  <=  pre-aggregation refresh (15m)
  -> a cache entry can be at most 60s staler than the rollup,
     and the rollup is at most 15m stale -> bounded total staleness.

# Serving-tool + gateway cache policy (illustrative).
endpoint: daily_usage
cache:
  edge:   { ttl: 60s, cache_when: "Cache-Control: public" }
  result: { ttl: 60s, key: "${query}:${jwt.tenant_id}" }   # identity in the key
pre_aggregation:
  refresh: 15m           # rollup cadence = freshness floor
  # invalidate/rebuild only the current partition (incremental)

Step-by-step explanation.

1. The CDN edge tier catches repeated public GETs — the hottest, non-viewer-specific tiles — and serves them with zero backend cost; it is the cheapest hit and the first line of defence.
2. The result cache catches identical query + identity repeats that miss the edge (e.g. per-tenant tiles), keyed on the query and the tenant_id so one tenant's cached result is never served to another — identity in the key is the multi-tenant safety rule.
3. On a full miss, the request reads the pre-aggregated rollup (a few rows), not raw events, so even the miss is sub-second; the raw OLAP store is scanned only by the 15-minute refresh, never per request.
4. Every cache TTL (60 s) is set below the rollup refresh cadence (15 min), so a served value is at most 60 s staler than the rollup and the rollup at most 15 min stale — total staleness is bounded and the caches self-heal within a refresh window.
5. The senior discipline is that the three tiers are complementary, not redundant: edge kills public repeats, the result cache kills per-identity repeats, and pre-aggregation makes the residual miss cheap — together they keep the store handling only refreshes and cold misses.

Output.

Request	Served from	Backend cost
repeat public tile	CDN edge	none
repeat per-tenant tile	result cache	none
first/expired tile	pre-aggregated rollup	small (rollup read)
raw store	refresh only	once per 15 min

Rule of thumb. Stack three cache tiers — CDN edge for public repeats, a result cache keyed on query + identity for per-tenant repeats, and pre-aggregation so even the miss is a rollup read — and align every TTL below the refresh cadence. The tiers are complementary, and identity in the key keeps a multi-tenant cache safe.

Worked example — concurrency and cost control at scale

Detailed explanation. A product surface generates concurrency a batch job never does; the OLAP store survives it only if each request is cheap and the total is capped. Show how rows-scanned-per-request and concurrency limits together bound cost. Reason about a 20k-req/min tile.

• The load. 20k req/min across thousands of users on one metric.
• The lever. pre-aggregation drops rows-scanned-per-request from millions to a handful.
• The cap. max_concurrent_queries + per-user quotas queue a spike, not collapse it.

Question. Show how pre-aggregation plus concurrency caps let one OLAP store serve 20k req/min without exhausting its query slots, and how cost scales.

Input.

Aspect	Live scan	Pre-aggregated + capped
Rows scanned / request	millions	a handful (rollup)
Query time / request	~1.5 s	~15 ms
Slots to sustain 20k/min	impossible	small pool
Behaviour at spike	collapse	queue (429 / backpressure)

Code.

Why concurrency is rows-scanned-bound:

  sustainable QPS  ≈  (query_slots × 1000ms) / query_time_ms

  LIVE SCAN:   query_time ≈ 1500 ms
    with 100 slots -> ~66 queries/sec -> ~4k/min   ✗ can't serve 20k/min
  PRE-AGGREGATED: query_time ≈ 15 ms
    with 100 slots -> ~6600 queries/sec -> ~400k/min  ✓ 20k/min is easy
    (and a cache absorbs most of it before it even reaches a slot)

Cost model:
  cost ≈ rows_scanned_per_request × requests
  live:  millions × requests           -> unbounded, melts the store
  rollup: a-few-rows × requests + one periodic merge  -> amortised, cheap

<!-- Concurrency + cost guardrails on the serving user. -->
<serving_profile>
  <max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
  <max_execution_time>2</max_execution_time>
  <max_rows_to_read>5000000</max_rows_to_read>   <!-- a live scan that blows this is aborted -->
</serving_profile>
<!-- Gateway: per-tenant rate limit so one noisy tenant can't starve others. -->
<!-- rate_limit: key=jwt.tenant_id, limit=600/min -> 429 over the limit -->

Step-by-step explanation.

1. Sustainable QPS is roughly query-slots divided by query-time, so the per-request cost (query time) is the lever: a 1.5 s live scan lets 100 slots serve only ~4k/min, nowhere near 20k, while a 15 ms rollup read lets the same 100 slots serve ~400k/min.
2. Pre-aggregation is therefore not just a latency trick but a concurrency trick: by dropping rows-scanned-per-request from millions to a handful, it multiplies the QPS the same hardware can sustain by ~100×.
3. A result/edge cache absorbs most of the 20k/min before it reaches a query slot at all, so the store only sees cold misses — the cache and pre-aggregation compound.
4. max_concurrent_queries_for_user, max_execution_time, and max_rows_to_read are the safety net: a runaway or accidental heavy query is aborted or queued rather than allowed to consume every slot, so a spike degrades gracefully.
5. Cost is rows-scanned × requests: a live scan is unbounded and melts the store, while a rollup read is a-few-rows × requests plus one periodic merge — amortised and cheap, which is why cost control is pre-aggregation plus caching, never bigger machines.

Output.

Setup	Sustainable QPS (100 slots)	Behaviour at 20k/min
Live scan (1.5 s)	~4k/min	slots exhausted, collapse
Pre-aggregated (15 ms)	~400k/min	comfortable
+ result/edge cache	most served from cache	store sees only misses
over per-tenant limit	—	429, others unaffected

Rule of thumb. Bound concurrency and cost by shrinking rows-scanned-per-request with pre-aggregation, absorbing repeats with caches, and capping with max_concurrent_queries/per-user quotas and per-tenant rate limits. Concurrency scales with per-request cheapness, and cost is rows-scanned × requests — so the fix is always pre-aggregation and caching, not more compute.

Senior interview question on end-to-end serving architecture

A senior interviewer might ask: "Design the full low-latency serving architecture for a multi-tenant product-analytics API on top of a warehouse. Cover the request path from ingest to app, where authentication and authorization each live, how you decide pre-aggregate versus query-live per metric, your caching tiers and how you keep them correct and safe for multi-tenant data, and how you survive product-scale concurrency — all tied to an SLO."

Solution Using an ingest path, a pre-aggregated store, tiered caches, concurrency caps, and SLO-driven routing

# 1. Request path + where each concern lives.
#    ingest (stream/CDC) -> ClickHouse OLAP store (MergeTree + materialized views)
#      client
#        -> API gateway   : authn (JWT), per-tenant rate limit, edge/result cache (identity-keyed)
#        -> serving tool   : Tinybird endpoint / Cube model -> pre-aggregated read; authz via token filter / security context
#        -> OLAP store     : rollup lookup (NOT the raw warehouse)
#      <- warehouse only BACKFILLS the OLAP store on a schedule (never on the hot path)

gateway:
  auth: { jwt: { required: true } }                       # authenticate here
  rate_limit: { key: "${jwt.tenant_id}", limit: 600, window: 60s }
  cache: { key_includes: [path, query, "jwt.tenant_id"], ttl: 60s }   # identity-scoped

-- 2. Ingest-time pre-aggregation in the OLAP store (freshness source).
CREATE MATERIALIZED VIEW usage_daily_mv TO usage_daily AS
SELECT tenant_id, feature, toDate(ts) AS day,
       uniqState(user_id) AS dau_state, sumState(amount) AS revenue_state
FROM events GROUP BY tenant_id, feature, day;   -- rollup maintained on INSERT

# 3. SLO-driven routing (per metric).
#   hot tile  (p95<100ms, fresh<=15m)  -> scheduled rollup + result cache
#   live KPI  (p95<500ms, fresh<=10s)  -> ingest-time MV (above)
#   analyst   (p95<5s,    live)        -> live OLAP read

<!-- 4. Concurrency + cost caps on the serving user (survive product-scale load). -->
<serving_profile>
  <readonly>2</readonly>
  <max_concurrent_queries_for_user>50</max_concurrent_queries_for_user>
  <max_execution_time>2</max_execution_time>
  <max_rows_to_read>5000000</max_rows_to_read>
</serving_profile>

Step-by-step trace.

Layer	Component	Responsibility
Ingest	stream / CDC → OLAP store	land rows; MV pre-aggregates on INSERT
Edge	CDN / gateway cache	absorb hot public GETs
Gateway	JWT authn + per-tenant rate limit	authenticate, throttle, cache (identity-keyed)
Serving tool	Tinybird / Cube	pre-aggregated read; authz (token filter / context)
OLAP store	ClickHouse rollup	sub-second reads; raw warehouse off the hot path
Safety	concurrency + row/time caps	spike queues, runaway query aborted

After deployment, a request is authenticated and rate-limited per tenant at the gateway, possibly served from an identity-scoped edge or result cache, otherwise compiled by the serving tool into a pre-aggregated read whose token filter / security context scopes it to the caller's tenant, executed against a ClickHouse rollup kept fresh by an ingest-time materialized view. Authentication lives at the edge, authorization in the serving layer, concurrency is bounded by cheap rollup reads plus caps, and the raw warehouse only backfills the OLAP store — so the whole path honours a p95 < 100 ms, freshness ≤ 60 s SLO.

Output:

Metric	Naive (warehouse-direct)	Serving architecture
Hot-path latency p95	seconds (scan)	< 100 ms (rollup + cache)
Freshness	batch (hours)	seconds (ingest-time MV)
Concurrency at 20k/min	exhausts slots	comfortable (cheap reads + cache)
Cross-tenant leak	app-dependent	zero (token filter / context + scoped cache)
Noisy-tenant blast radius	global	own 429 bucket
Warehouse load from serving	every request	scheduled backfill only

Why this works — concept by concept:

• OLAP store fronting the warehouse — a ClickHouse store built for sub-second reads handles the request path, and the batch warehouse only backfills it on a schedule, so product traffic never contends with batch jobs on the analytical engine.
• Ingest-time pre-aggregation — a materialized view maintaining the rollup on INSERT gives both freshness (to the last insert) and latency (a rollup lookup), the point of the triangle that a scheduled batch or a live scan cannot reach.
• Authn at the edge, authz in the serving layer — the gateway verifies identity and throttles per tenant, while a token filter or security context enforces which rows the identity may read, a defence-in-depth split that puts each concern where it is unbypassable.
• Identity-scoped, refresh-aligned tiered caching — edge and result caches keyed on tenant with a TTL below the refresh cadence make caching a multi-tenant API both safe (no cross-tenant leak) and correct (bounded staleness), while pre-aggregation makes the miss cheap.
• Cost — cheap rollup reads bounded by concurrency caps and absorbed by caches, plus one scheduled backfill and incremental MV merges, versus a per-request warehouse scan and a slot per client. The eliminated cost is the warehouse bill and outage risk of serving from a batch engine — O(rollup) cached reads versus O(scan) direct queries.

Design
Topic — design
Design problems on serving-layer and gateway architecture

Practice →

Streaming
Topic — streaming
Streaming problems on ingest and ingest-time rollups

Practice →

---

Cheat sheet — low-latency serving

• The serving gap. A batch warehouse/lakehouse is for high-throughput scans; a serving layer is request/response, high-concurrency, sub-second. Never serve product traffic from the raw warehouse — front it with an OLAP store (ClickHouse) refreshed on a schedule. The warehouse backfills the OLAP store; the OLAP store serves the request.
• Pre-aggregate vs query-live. Freshness/latency/cost — pick two, and let the SLO decide. Pre-aggregate a rollup for hot, freshness-tolerant reads; maintain an ingest-time materialized view when you need both fresh and fast; query live only for cold, freshness-critical, low-volume access. Sub-second is a millisecond budget: cache the repeat, pre-aggregate the miss, never live-scan on the hot path.
• Tinybird template. Ingest a stream into a .datasource (MergeTree, sort key = the serving filter prefix); chain SQL .pipe nodes; add a TYPE materialized node writing to an AggregatingMergeTree target to pre-aggregate on ingest; publish the last node with TYPE endpoint and required=True parameters + a LIMIT; gate it with a scoped read token carrying a tenant_id = '...' row filter. The pipe is the API — no backend.
• Cube template. Define measures/dimensions/joins once in a cube(...) model (one definition across REST, GraphQL, SQL); add pre_aggregations (materialized rollups, partition_granularity + refresh_key) the planner transparently routes matching queries to; enforce multi-tenancy in queryRewrite off verified security-context claims (default-deny when absent). Additive measures roll up to coarser grains; count_distinct does not.
• ClickHouse template. ORDER BY (tenant_id, ts) is the sparse primary index — equality columns before range columns; PARTITION BY toYYYYMM(ts) to prune; a MATERIALIZED VIEW ... TO AggregatingMergeTree with -State/-Merge to pre-aggregate on INSERT; a PROJECTION for a second access pattern in the same table; serve via the HTTP interface with parametrised {name:Type} SQL through a readonly=2, quota'd user (max_rows_to_read, max_execution_time, max_concurrent_queries_for_user).
• Materialized views are the shared engine. Tinybird materialized pipes, Cube pre-aggregations, and ClickHouse materialized views are the same idea — the expensive aggregation runs once at write/refresh time so the read is a rollup lookup. Ingest-time (on INSERT) is fresh to seconds; scheduled is fresh to the cadence.
• Concurrency = rows-scanned-per-request. Sustainable QPS ≈ query-slots ÷ query-time, so pre-aggregation (millions → a handful of rows) multiplies the concurrency the same hardware sustains by ~100×. Cap with max_concurrent_queries/per-user quotas so a spike queues; rate-limit per tenant so one noisy consumer hits its own 429.
• Caching tiers. CDN/edge (public cacheable GETs) → result cache (serving tool/gateway, keyed on query + identity) → pre-aggregation (even the miss reads a rollup). Keep them correct: identity in the key (no cross-tenant leak), TTL ≤ refresh cadence (bounded staleness), invalidate on refresh.
• Authn vs authz. Authentication (JWT/token) at the gateway; authorization in the serving layer — a Tinybird token row filter, a Cube security context queryRewrite, or a ClickHouse row policy — AND-ed onto every query. Authn at the edge, authz at the data, defence in depth.
• Tool fit. Tinybird to ingest a stream and publish a pipe as an endpoint with token auth and the least backend; Cube when many consumers need one governed semantic model over REST/GraphQL/SQL; raw ClickHouse HTTP for the thinnest possible API. ClickHouse is the engine under all three.
• Metric API framing. A served metric is a product: a versioned contract, an owner, an SLO (availability, p95 latency, freshness), and governed, token-scoped access — not a one-off cache bolted onto the warehouse.

Frequently asked questions

What is a low-latency serving layer for product analytics?

A low-latency serving layer is a governed request/response tier — usually a real-time HTTP API returning JSON — that sits in front of an OLAP store so a product surface can read a fresh analytical number in tens of milliseconds, without a bespoke caching backend and without touching the batch warehouse directly. It exists because a warehouse or lakehouse is optimised for high-throughput batch scans, not for the high-concurrency, sub-100-millisecond reads an in-app dashboard or live counter needs; the serving layer bridges that gap by reading from an OLAP store (ClickHouse) where materialized views pre-aggregate on ingest. Done well, each metric becomes a reusable API with a versioned contract, an owner, and an SLO — rather than a per-team cache every squad rebuilds.

Tinybird vs Cube vs ClickHouse — which do I pick?

They sit at different layers, and ClickHouse is usually underneath the other two. Pick ClickHouse directly when you want the thinnest stack and are comfortable running the engine: MergeTree ordering, materialized views, projections, and the HTTP interface give you a sub-second OLAP API with no extra service. Pick Tinybird when you want a managed platform over ClickHouse that turns a stream into a published API endpoint with almost no backend — data sources ingest, pipes transform, materialized nodes pre-aggregate, and scoped tokens enforce isolation. Pick Cube when many consumers need one consistent semantic model across REST, GraphQL, and SQL, with pre-aggregations and a multi-tenant security context. Many teams combine them — ClickHouse as the store, Cube as the semantic layer, or Tinybird as the ingest-and-serve platform.

How do materialized views make analytics sub-second?

A materialized view moves the expensive aggregation from read time to write time. Instead of scanning millions of raw event rows on every request, the view runs the aggregation incrementally as data is inserted and maintains a small rollup — so the serving read is a lookup over a handful of pre-aggregated rows, not a scan. In ClickHouse this is a materialized view writing partial aggregate states into an AggregatingMergeTree, finalised at read time with -Merge; Tinybird exposes the same thing as a TYPE materialized pipe, and Cube as a pre-aggregation the query planner routes to. Because the aggregation cost is paid once and amortised across all future reads, a metric that would cost an O(rows) scan per request costs an O(rollup) read — the core mechanism behind sub-second serving. Ingest-time views also stay fresh to the last insert, so you get both speed and freshness.

Do I pre-aggregate or query live?

Let the SLO decide via the freshness/latency/cost triangle — you cannot maximise all three, so pick two. Pre-aggregate a rollup (refreshed on a schedule) for hot, high-QPS reads whose freshness tolerance is minutes — usage tiles, dashboards — because it is fast and cheap per request at the cost of bounded staleness. Maintain an ingest-time materialized view when you need both fresh (to seconds) and fast, accepting the write-amplification and storage cost. Query live over the OLAP store only for freshness-critical, low-volume, or ad-hoc access where seconds of latency are acceptable, since pre-aggregating every possible slice is impossible. The one constant: the OLAP store serves the request and the batch warehouse only backfills it — product traffic against raw fact tables is slow, expensive, and contends with batch jobs.

How does ClickHouse serve thousands of concurrent in-app requests?

By making each request cheap and capping the total. Concurrency is bounded by rows-scanned-per-request: sustainable QPS is roughly query-slots divided by query-time, so pre-aggregation — which drops rows scanned from millions to a handful — multiplies the concurrency the same hardware sustains by around a hundredfold. On top of that, a result cache and CDN edge absorb repeat traffic so most requests never reach a query slot, and max_concurrent_queries_for_user, max_execution_time, and max_rows_to_read cap what any single user or query can consume so a spike queues or aborts rather than collapsing the engine. The MergeTree ORDER BY sparse index keeps even the reads that do run to a few granules. Together — pre-aggregation, caching, and concurrency caps — let one store serve product-scale traffic.

How do I keep a serving API from melting the OLAP store?

Layered defences, none of which involve the warehouse serving requests directly. First, front the warehouse with an OLAP store (ClickHouse) so the batch engine is only ever refreshed on a schedule. Second, pre-aggregate the hot metrics with materialized views so each read is a rollup lookup, not a scan — the single biggest lever, since it shrinks both latency and rows-scanned-per-request. Third, cache aggressively at tiered layers — CDN/edge for public GETs, a result cache keyed on identity, and pre-aggregation as the deepest tier — with TTLs aligned to your refresh cadence so hot keys never reach the store. Fourth, rate-limit per tenant at the gateway so one noisy consumer hits its own 429 wall, and cap max_concurrent_queries and per-query rows/time so runaway queries are aborted. Together these keep the store seeing only authenticated, throttled, cache-missed, bounded, pre-aggregated traffic.

Practice on PipeCode

• Drill the API integration practice library → for the published-endpoint, semantic-API, and parametrised-query problems that Tinybird, Cube, and the ClickHouse HTTP interface make concrete.
• Rehearse serving patterns on the real-time analytics practice library → for the ingest-time materialized view, live-counter, and freshness scenarios where the pre-aggregate-vs-live decision earns its keep.
• Sharpen the architecture axis with the system design practice library → for the ingest, caching-tier, concurrency, and authorization-placement trade-offs a sub-second serving layer must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the MergeTree-ordering, materialized-view, and pre-aggregation patterns against real graded inputs — ClickHouse, Tinybird, Cube, caching, and concurrency.

Lock in low-latency serving muscle memory

Docs explain Tinybird, Cube, and ClickHouse. PipeCode drills explain the decision — when the warehouse must not serve the request, when a `materialized view` turns an O(rows) scan into an O(rollup) read, when pre-aggregation has to beat a live query, and when a token filter or security context is the only safe place for tenant isolation. Pipecode.ai is Leetcode for Data Engineering — serving-layer practice tuned for the production trade-offs senior data engineers actually face.

Practice API integration problems →
Practice real-time analytics problems →