Designing Component APIs for Analytics That Support High‑Cardinality Data (ClickHouse Use Cases)

Ship dashboards and tables that scale: API patterns for high‑cardinality analytics with ClickHouse

Hook: If your charts and tables choke when you point them at tens of millions of distinct users, events, or session IDs, the problem is usually not React or D3 — it's the API contract between your component and the OLAP engine. In 2026, with ClickHouse adoption surging after a major late‑2025 funding round, engineering teams need component APIs that expose efficient query patterns (aggregates, sampling, pre‑aggregation) so UI code can request accurate, fast results without blowing up cluster resources.

Why API design matters for high‑cardinality analytics

High cardinality means group keys explode cardinality (think user_id, session_id, URL path). Naively running GROUP BY on those fields for charts or row tables ruins latency, memory, and cost. Well‑designed component APIs do two things:

• Constrain the query surface — only allow operations that are safe and predictable for high cardinality (top‑N, approximate distinct, sampled aggregates).
• Encapsulate strategy — hide the sampling/aggregation/pre‑aggregation decisions in server‑side endpoints while giving the UI a declarative descriptor to request drilldowns, re‑aggregation, or higher accuracy.

2026 trends that make this urgent

• ClickHouse maturity and enterprise adoption spiked after a late‑2025 investment round; teams rely on ClickHouse for real‑time analytics and observability.
• Demand for interactive analytics in apps (sub‑second charts, paged tables) increases pressure on query APIs to provide fast approximations then refine on demand.
• Standardized pre‑aggregation tooling (materialized views, AggregatingMergeTree, projections) is mainstream, so component APIs should offer both live and pre‑aggregated modes.

Design goals for component APIs

Every JS component (table, chart, heatmap) should be able to request data using a compact, typed descriptor. The server turns the descriptor into efficient ClickHouse queries using aggregates, sampling, and pre‑aggregations. Aim for these goals:

• Predictable latency — first response in 200–800ms for top‑N; optionally refine to full accuracy.
• Safety — prevent unbounded GROUP BYs and disallowed SQL via whitelisted templates and typed parameters.
• Composability — let components request top‑N, totals, breakdowns and drilldowns using the same descriptor shape.
• Observability — include execution metrics (rows scanned, query time) in the response to tune UX strategies.

Core API patterns

1) Declarative query descriptor (recommended)

Let components send a JSON descriptor instead of raw SQL. The server maps the descriptor to optimized ClickHouse queries. Example descriptor shape:

{
  "dataset": "events_v1",
  "type": "aggregate",          // aggregate | topn | drilldown
  "groupBy": ["page_path"],
  "metrics": [{"name":"pageviews","agg":"sum","expr":"count()"}],
  "filters": [{"field":"ts","op":">=","value":"2026-01-01"}],
  "limit": 50,
  "sampling": {"mode":"deterministic","rate":0.1},
  "approximate": true,
  "mode": "fast_first_then_precise"  // strategy hints
}

Server behavior:

• When mode = fast_first_then_precise, issue a sampled/topN query returning quick results and a task id for a precise background query.
• If approximate=true, use ClickHouse approximate functions (uniqCombined, topK, quantiles) and return accuracy metadata.

2) Two‑phase queries: fast approximation + optional refine

Pattern: the component first requests a low‑cost approximation for fast UI paint, then triggers a precise run when the user drills or after idle time. Use these ClickHouse levers:

• Sampling — deterministic SAMPLE BY to preserve grouping distribution across panels (use a stable hash on user_id or another key).
• Approx aggregate functions — uniqCombined, topK, approximate quantiles reduce cost.
• Pre‑aggregations — AggregatingMergeTree / materialized views for known rollups.

Example client flow:

1. Component requests top50 (sampling rate 10%). Server executes sampled query and returns results + metadata.
2. Component displays the chart/table with a subtle precision badge. If user drills, component requests a precise query for the selected slice.
3. Server runs precise query (no sampling) or answers from a pre‑aggregation if available.

3) Top‑N + "Others" pattern

High‑cardinality charts should show the top N keys plus an aggregated "Other" bucket to avoid exploding legends. Implement as two server calls or one two‑step server plan:

1. TopN query to get keys: SELECT page_path, sum(count) AS s FROM events WHERE ... GROUP BY page_path ORDER BY s DESC LIMIT N.
2. Aggregate using a CASE to collapse all non‑top keys into 'Other': SELECT CASE WHEN page_path IN (...) THEN page_path ELSE 'Other' END AS bucket, sum(count) FROM events WHERE ... GROUP BY bucket.

Two small roundtrips are often cheaper than a single giant GROUP BY. Below is a practical example implemented in the API server and called by a chart.

ClickHouse query templates and examples

These templates assume a time‑series events table with columns: ts DateTime, user_id UInt64, page_path String, event_name String, value UInt64.

Fast topN sampled (approximate)

-- sampled topN: deterministic SAMPLE BY (cityHash64(user_id))
  SELECT
    page_path,
    sum(1) AS views
  FROM events_v1
  WHERE ts >= toDateTime('2026-01-01')
    AND ts < toDateTime('2026-01-08')
  SAMPLE BY cityHash64(user_id) % 100 < 10
  GROUP BY page_path
  ORDER BY views DESC
  LIMIT 50
  FORMAT JSON

Notes:

• Deterministic SAMPLE BY preserves relative proportions across parallel panels when sampling by the same hash expression.
• Use modulo arithmetic on cityHash64 for percentage sampling.

Precise topN

SELECT page_path, sum(1) AS views
  FROM events_v1
  WHERE ts >= toDateTime('2026-01-01')
    AND ts < toDateTime('2026-01-08')
  GROUP BY page_path
  ORDER BY views DESC
  LIMIT 50
  FORMAT JSON

TopN + Others (server composes two queries)

-- 1) top keys
  SELECT page_path FROM (
    SELECT page_path, sum(1) AS views
    FROM events_v1
    WHERE ...
    GROUP BY page_path
    ORDER BY views DESC
    LIMIT 50
  )

  -- 2) aggregated with collapse
  SELECT
    if(page_path IN (), page_path, 'Other') AS bucket,
    sum(1) AS views
  FROM events_v1
  WHERE ...
  GROUP BY bucket
  ORDER BY views DESC
  FORMAT JSON

Approx distinct counts using HLL / uniqCombined

SELECT
    toStartOfHour(ts) AS h,
    uniqCombined64(user_id) AS approx_users
  FROM events_v1
  WHERE ts BETWEEN ...
  GROUP BY h
  ORDER BY h
  FORMAT JSON

Use uniqCombined64 for compact, configurable HyperLogLog‑style estimates with low memory. Return accuracy metadata so the UI can display confidence intervals.

Server‑side safety and templating

Never accept raw SQL from the browser. Build a server that:

• Accepts the JSON descriptor and maps to whitelisted templates.
• Performs sanitization, type checks and enforces limits (max limit, disallowed GROUP BY on high‑card fields unless sampling or pre‑agg enabled).
• Attaches query settings: max_execution_time, max_bytes_before_external_group_by, read_only=1, and format=JSONCompact or Arrow for faster transfer.

// pseudo-code: map descriptor to template
  const templates = {
    topn: (d) => `SELECT ${d.groupBy[0]} AS key, sum(1) AS value FROM ${d.dataset} WHERE ${filters} ${d.sampling ? samplingClause(d.sampling) : ''} GROUP BY key ORDER BY value DESC LIMIT ${d.limit}`
  }

Client examples: React table + progressive refinement

Below is a concise pattern (runnable with a simple server) using fetch to the server API. The component asks for a sampled topN, displays results, then requests a precise run for the selected bucket.

// client: query descriptor and fetch helper
  async function fetchDescriptor(descriptor) {
    const res = await fetch('/api/query', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(descriptor)
    });
    return res.json();
  }

  // usage in a React-like component
  async function loadChart() {
    const fast = await fetchDescriptor({ dataset: 'events_v1', type: 'topn', groupBy: ['page_path'], limit: 20, sampling: { mode: 'deterministic', rate: 0.05 } });
    renderChart(fast.rows, { approximate: true, meta: fast.meta });

    // background precise run
    const precise = await fetchDescriptor({ dataset: 'events_v1', type: 'topn', groupBy: ['page_path'], limit: 20, sampling: null });
    // replace chart when ready
    renderChart(precise.rows, { approximate: false, meta: precise.meta });
  }

Performance tuning and benchmarks

Measure two axes: latency and resource cost. Typical benchmarking steps:

1. Run sampled topN at 1%, 5%, 10% and record latency and error vs precise result.
2. Run uniqCombined vs uniqExact on distinct counts to quantify bias and memory usage.
3. Measure group by cardinality limits and set server safeguards.

// simple JS benchmark harness
  async function bench(desc, runs=3) {
    console.time(desc);
    for (let i=0;i

{
  rows: [...],
  stats: { elapsed: 210, bytes_scanned: 12345678 },
  approximate: true,
  accuracy: { type: 'hll', error_percent: 1.7 }
}

// sampled deterministic clause
SAMPLE BY cityHash64(user_id) % 100 < 10

// approximate distinct
SELECT uniqCombined64(user_id) FROM events_v1 WHERE ...

// topN + collapse (pseudo)
1) get top keys
2) SELECT if(page_path IN (), page_path, 'Other') AS bucket, sum(1) FROM events_v1 WHERE ... GROUP BY bucket