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, 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 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 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:
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 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:
ALTER USER metabase_user SETTINGS max_execution_time = 30;
Looker Studio
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 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:
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, materialize hourly or daily rollups alongside your raw event table:
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:
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:
-- 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:
-- 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 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:
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.