---
title: "ClickHouse BI dashboards"
excerpt: "ClickHouse delivers sub-second query results to BI tools at a concurrency that traditional warehouses can't match. Here's how to connect your dashboards, optimize for interactivity, and avoid the patterns that make dashboards slow."
authors: "Tinybird"
categories: "AI Resources"
createdOn: "2026-05-28 00:00:00"
publishedOn: "2026-05-28 00:00:00"
updatedOn: "2026-05-28 00:00:00"
status: "published"
---

Most BI tools were designed for data warehouses that run batch queries against day-old data. ClickHouse® was designed for real-time analytical workloads where data is seconds old and dashboards refresh on demand. The gap between those two assumptions is where most dashboard performance problems live.

According to the [ClickHouse 2025 State of ClickHouse Survey](https://clickhouse.com/resources/engineering/real-time-analytics-platforms-a-practical-comparison), 72% of ClickHouse users rely on a separate BI or visualization layer for dashboarding, and 38% report that their current BI tool doesn't fully leverage ClickHouse's real-time capabilities. The bottleneck is rarely ClickHouse. It's usually the schema design, the connection method, or the query pattern the BI tool generates.

This post covers how to connect popular BI tools to ClickHouse, what to optimize for dashboard workloads specifically, how to cache and scale under concurrent users, and how Tinybird's API layer removes the BI tool bottleneck entirely for customer-facing dashboards.

## Why ClickHouse works well for dashboards

BI dashboards make many small, fast queries in parallel, often with different filter combinations from different users. ClickHouse handles this workload well for three reasons:

**Columnar storage.** Dashboard queries typically filter on a time range and group by a few dimensions. ClickHouse reads only the columns referenced, skipping everything else. A query touching 5 of 50 columns reads roughly 10% of what a row-store database would read. That reduction in I/O directly translates into lower query latency and less resource contention when multiple users are querying simultaneously.

**Vectorized execution.** ClickHouse processes data in batches rather than row by row, which maps well to the aggregation-heavy patterns dashboards run: `GROUP BY`, `COUNT`, `SUM`, `uniq()`. This vectorized approach lets modern CPUs process more data per instruction cycle, which is why ClickHouse can aggregate billions of rows in milliseconds on commodity hardware.

**High query concurrency.** ClickHouse can handle hundreds of simultaneous queries from different dashboard users without degrading. The [ClickHouse Cloud architecture](https://clickhouse.com/resources/engineering/top-5-cloud-data-warehouses) delivers sub-second responses at 1,000+ queries per second, which is the kind of throughput customer-facing dashboards need. Traditional warehouses that optimize for a few heavy batch queries tend to degrade badly when 50 users load dashboards simultaneously.

## Connecting BI tools

### Grafana

[Grafana](https://www.tinybird.co/blog/clickhouse-grafana-example) is the most widely deployed open-source dashboarding platform with an official ClickHouse data source plugin maintained jointly by ClickHouse and Grafana Labs. It excels at real-time operational dashboards with sub-second refresh on time-series data.

Connect via the native ClickHouse plugin rather than JDBC, which supports parameterized SQL macros like `$__timeFilter(timestamp)` that translate Grafana's time picker into efficient ClickHouse WHERE clauses:

```sql
SELECT
    toStartOfMinute(timestamp) AS time,
    count()                    AS requests,
    avg(duration_ms)           AS avg_latency,
    quantile(0.99)(duration_ms) AS p99_latency
FROM api_requests
WHERE $__timeFilter(timestamp)
  AND endpoint = '$endpoint'
GROUP BY time
ORDER BY time
```

Set the Grafana panel refresh to 10–30 seconds for operational dashboards. For streaming data, use Grafana's live streaming with a WebSocket data source if near-real-time push is needed. Use Grafana for engineering-led operational monitoring and infrastructure metrics. For business users who need self-service exploration, a dedicated BI tool is a better fit.

### Metabase

[Metabase](https://www.tinybird.co/blog/clickhouse-integration-metabase) connects to ClickHouse via its native driver. It works well for internal analyst dashboards where non-technical users need to explore data without writing SQL.

Key settings for ClickHouse with Metabase: disable the Metabase query cache and let ClickHouse's own performance handle latency. Metabase's cache adds staleness without meaningful speed improvement when the underlying database already returns in milliseconds. Also set `max_execution_time` on your ClickHouse user profile to prevent runaway Metabase queries from impacting other workloads:

```sql
ALTER USER metabase_user SETTINGS max_execution_time = 30;
```

### Looker Studio

[Looker Studio](https://www.tinybird.co/blog/clickhouse-integration-looker-studio) connects via the ClickHouse JDBC driver or a community connector. It generates SQL that can be aggressive on full table scans, so always define calculated fields and filters in the data source settings rather than relying on Looker Studio to push them down efficiently. Pre-aggregate the data Looker Studio queries into a dedicated rollup table and point the connector at that, not the raw event table.

### Tableau and Power BI

Both Tableau and [Power BI](https://www.tinybird.co/blog/clickhouse-integration-power-bi) support ClickHouse via ODBC/JDBC. Performance varies significantly based on whether the generated SQL uses your sort key efficiently. Always inspect the queries these tools send to ClickHouse using `system.query_log` and verify they're not triggering full table scans. Both tools have a tendency to generate `SELECT *` or wrap filter columns in functions, both of which defeat ClickHouse's pruning.

## Schema design for dashboard workloads

**Partition by time, sort by your most common filter dimensions.** Most dashboard queries filter on a date range and then by campaign, org, region, or user. Put those columns in the `ORDER BY` key in that order:

```sql
CREATE TABLE events
(
    timestamp   DateTime,
    org_id      String,
    event_type  LowCardinality(String),
    user_id     String,
    revenue     Nullable(Float64)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
ORDER BY (org_id, timestamp, event_type);
```

A dashboard querying `WHERE org_id = 'acme' AND timestamp >= ...` uses both levels of the sort key to skip irrelevant data. Without `org_id` first, ClickHouse scans all rows in the partition before filtering by org.

**Use `LowCardinality` on dimension columns.** Event types, status values, region codes, plan names, any string column with a bounded set of values compresses and filters much faster with `LowCardinality(String)`. At scale, this can reduce memory usage for GROUP BY operations by 2-4x.

**Pre-aggregate into rollup tables.** For [real-time dashboards on high-volume time-series data](https://www.tinybird.co/blog/real-time-dashboards-high-volume-time-series-data), materialize hourly or daily rollups alongside your raw event table:

```sql
CREATE MATERIALIZED VIEW events_daily_mv
TO events_daily AS
SELECT
    toDate(timestamp)            AS day,
    org_id,
    event_type,
    count()                      AS events,
    uniqState(user_id)           AS unique_users,
    sumState(revenue)            AS revenue
FROM events
GROUP BY day, org_id, event_type;
```

Dashboard queries that cover more than a few days hit the pre-aggregated table. Queries drilling into the last few hours hit the raw table. Both stay fast regardless of how many total rows are in the base table.

## Query patterns that kill dashboard performance

**Avoid functions on indexed columns.** `WHERE toDate(timestamp) = '2026-05-28'` wraps the sort key in a function, preventing ClickHouse from using it for pruning. Write `WHERE timestamp >= '2026-05-28' AND timestamp < '2026-05-29'` instead. The same applies to any expression: `WHERE lower(event_type) = 'click'` defeats the LowCardinality index.

**Avoid `SELECT *`.** Every column the query touches is read from disk. BI tools that generate `SELECT *` then discard most columns are wasting I/O proportional to your table width. Name only the columns the visualization actually needs.

**Use `uniq()` instead of `COUNT(DISTINCT)`.** Most BI tools generate `COUNT(DISTINCT user_id)` which is exact but slow at scale. On ClickHouse, `uniq()` uses HyperLogLog with ~2% error and is 10-100x faster. For dashboards showing weekly active users or unique visitors, the approximation is imperceptible.

**Check your concurrency settings.** For high-concurrency dashboard workloads with many simultaneous users, tune `max_threads` and `max_concurrent_queries` in your user profile to prevent any single dashboard query from monopolizing resources:

```sql
ALTER USER dashboard_user SETTINGS
    max_threads = 4,
    max_memory_usage = 4000000000;
```

This ensures that a slow analytical query from one user doesn't block the fast widget queries from fifty others.

## Caching for high-traffic dashboards

For dashboards that many users load simultaneously with the same filters, a cache layer in front of ClickHouse dramatically reduces load without sacrificing freshness. The best place to cache is at the query result level, keyed on the exact parameters:

```sql
-- Enable ClickHouse's built-in query cache (v23.5+)
SELECT
    day, event_type, count() AS events
FROM events_daily
WHERE org_id = 'acme'
  AND day >= today() - 30
GROUP BY day, event_type
SETTINGS use_query_cache = 1,
         query_cache_ttl = 60;  -- 60-second TTL
```

ClickHouse's built-in query cache stores results in memory and returns them for identical subsequent queries within the TTL. For external caching, a Redis layer in front of your API endpoints with a 30–60 second TTL typically cuts ClickHouse load by 70-90% for shared public dashboards.

## In-product and customer-facing dashboards

Embedding analytics directly in your product, dashboards your customers see inside your app, has different requirements than internal BI tools. The BI tool layer becomes a liability: it adds latency, requires per-user licensing, and can't handle per-tenant data isolation cleanly.

The pattern that works at scale is publishing ClickHouse queries as parameterized HTTP endpoints that your frontend calls directly. Tinybird handles this with Pipes: write SQL once, add `{{ String(org_id, required=True) }}` for tenant isolation, and the endpoint enforces that the query always scopes to the requesting org:

```sql
-- dashboard_metrics.pipe
NODE metrics
SQL >
  %
  SELECT
    toStartOfDay(timestamp)               AS day,
    event_type,
    count()                               AS events,
    uniq(user_id)                         AS unique_users,
    round(sumMerge(revenue), 2)           AS revenue
  FROM events_daily
  WHERE org_id    = {{ String(org_id, required=True) }}
    AND timestamp >= now() - INTERVAL {{ Int32(days, 30) }} DAY
  GROUP BY day, event_type
  ORDER BY day

TYPE ENDPOINT
```

Your frontend calls `/dashboard_metrics.json?org_id=acme&days=7`. ClickHouse runs the query. No BI tool license, no extra service, no per-tenant query routing code. For [in-product dashboards](https://www.tinybird.co/blog/inproduct-dashboards) this is significantly simpler than running a separate BI platform per tenant.

## Monitoring dashboard query performance

ClickHouse's `system.query_log` table records every query with timing, rows read, and bytes read. Use it to identify which dashboard queries are scanning too much:

```sql
SELECT
    query,
    read_rows,
    read_bytes,
    query_duration_ms,
    user
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time >= now() - INTERVAL 1 HOUR
  AND query_duration_ms > 1000
ORDER BY read_bytes DESC
LIMIT 20;
```

Any query scanning unexpectedly high `read_rows` for its duration is either missing a sort key hit or running against the wrong table (raw instead of rollup). The fix is almost always one of: add the dimension to the sort key, rewrite the filter to avoid wrapping indexed columns, or route the query to the pre-aggregated table.

{% cta
  title="Real-time dashboards without the infrastructure"
  text="Tinybird is managed ClickHouse with a built-in HTTP API layer. Connect any BI tool in minutes."
  button={href: "https://cloud.tinybird.co/signup", target: "_blank", text: "Try Tinybird free"}
/%}