bi-dashboard

How it is built

Architecture overview

A BI dashboard tool does not store the data it shows. It stores a small model of the data (which tables, which columns, which named measures), a set of saved questions and charts built on that model, and layouts that arrange the charts. When someone opens a dashboard, the tool turns every chart into one query, sends it to a separate warehouse or database, and draws the rows that come back. Three things make this feel interactive on warehouses that take seconds per query: a semantic layer that makes the query a pure function of a chart definition plus filters, a result cache keyed on exactly that query, and a background worker tier that runs slow queries, refreshes caches and sends reports. The product is therefore a thin web application in front of someone else's database; the engineering is in generating safe SQL, not running the same SQL twice, and not letting one user's heavy chart starve everyone.

Open BI projects follow this shape. Superset documents a Flask backend with React frontend, a metadata database, a Redis-style cache that doubles as a message broker, Celery workers and a Celery beat scheduler (Superset architecture). Redash's compose file lists the same roles: server, scheduler, worker, Redis, PostgreSQL (compose.yaml). Metabase and Grafana are described below from their own docs.

Parts:

• Semantic layer — names tables, columns, joins, measures and types so charts never contain raw SQL — semantic-layer (no page yet), Name resolution, relational-algebra (no page yet)
• Query compiler and executor — turns a chart spec into SQL, runs it on the warehouse with pooled connections — connection-pooling (no page yet), request-coalescing (no page yet), Columnar storage, Vectorized execution (these two live in the warehouse, not in the tool)
• Result cache — stores result sets by query key, with timeouts and warm-up — LRU and CLOCK, Cache invalidation
• Pre-aggregation — rollup tables or maintained views so dashboards read thousands of rows, not billions — Rollup aggregation, hash-aggregation (no page yet), Time partitioning, incremental-view-maintenance (no page yet), Change data capture
• Scheduler and workers — async queries, cache warm-up, alert and report delivery — cron-scheduling (no page yet), fair-queuing (no page yet), Durable job queue
• Chart data shaping — downsampling and bucketing so a browser never receives a million points — lttb-downsampling (no page yet), hdr-histogram (no page yet), List virtualization
• Access control — who may see which dashboard, dataset and rows — Authorization models, Row-level security, Mutual TLS
• Metadata store — dashboards, charts, users, schedules — Primary-backup replication, Write-ahead log, Point-in-time recovery, ARIES recovery, Object storage
• Operations and verification — Process supervision, Graceful draining, Health checking, Leases, Deterministic simulation testing, Coverage-guided fuzzing

 browser ──HTTPS──► web tier ──► authz ──► chart spec + filters
   ▲                  │                          │
   │                  │                   semantic layer
   │                  │                   (dataset, measures)
   │                  │                          ▼
   │                  │                   SQL compiler ──► cache key
   │ rows/series      │                          │ miss          │ hit
   └──── shaping ◄────┤                          ▼               │
                      │                  queue ► worker ► pool ► warehouse
                      │                          │   ▲            (rollups,
          metadata DB ◄┘                      result cache ◄───────  views)
    (dashboards, users)                           ▲
                                      scheduler: warm-up, reports, alerts

How the parts fit together

Semantic layer

The semantic layer (semantic-layer, no page yet) is what separates a BI tool from a SQL editor. Redash is the SQL-editor end: the query is the artifact, the tool stores text and results. Superset sits in the middle: its dataset API exposes datasets with their own columns and metrics, so a chart refers to a named metric rather than an expression (Superset datasets API). Metabase syncs the database schema into its own metadata and assigns each column a semantic type, and an admin can edit that metadata (Syncing and scanning). Looker is the model-first end: its semantic modelling language is a product feature (Looker; proprietary).

Why bother: a measure such as "revenue" is defined once, so every chart agrees, and the compiler can add joins and filters mechanically. The contract is that a chart spec (dataset, dimensions, measures, filters, time grain, limit) plus the viewer's identity fully determines the SQL. That determinism is what makes caching correct. Resolving the names in a spec against the model is name resolution in its plain form: scoping, ambiguity, type checks. The compiler's output is relational algebra (select, project, join, group) before it becomes dialect SQL; relational-algebra (no page yet) is the missing block.

Query compiler and executor

The compiler renders SQL in the warehouse's dialect, and the executor runs it. The tool is a client, so it needs connection pooling (connection-pooling, no page yet): warehouses limit concurrent connections, and opening one costs a TLS handshake and a login. It also needs request coalescing (request-coalescing, no page yet): when twenty viewers open the same morning dashboard at 09:00, twenty identical queries should become one running query and twenty waiters. Without it a cache only helps after the first answer lands, and the cold start is the worst moment.

The heavy lifting (scan, filter, group) belongs to the warehouse, which is why column layouts and vectorized operators matter for BI even though the tool itself contains neither: Columnar storage and Vectorized execution explain why a grouped aggregate over one column of a wide table is cheap.

Result cache

Superset's cache section is the most explicit of the four. It names separate caches for dashboard filter state, explore form data, metadata, chart data, SQL Lab results and thumbnails, with Redis recommended and Memcached, S3-compatible storage and a filesystem as other backends (Superset caching). Chart-data timeouts resolve in order chart, dataset, database, global default, and -1 disables caching. A value-size cap skips oversized results. Metabase offers duration, schedule and adaptive policies at question, dashboard and database level; "adaptive" multiplies a query's average execution time by a factor, so expensive queries live longer in the cache (Metabase caching). Grafana's query cache is documented as an Enterprise and Cloud feature with in-memory, Redis and Memcached backends; its key combines data source, query and time range, and time-series ranges are rounded to the panel interval to raise hit rates (Grafana data sources).

The mechanics under these features are eviction (LRU and CLOCK) and invalidation (Cache invalidation). BI caches mostly use time-to-live invalidation because the tool cannot see warehouse writes. The exact alternative is to invalidate on data change, which needs a signal from the warehouse; that is where Change data capture enters. Note the one design choice Superset documents explicitly: a configured backend that becomes unavailable causes failures rather than silent degradation.

Pre-aggregation

When the warehouse is slow, move the work earlier. A rollup (Rollup aggregation) stores sum(amount) per day and region instead of per order, computed with hash-aggregation (no page yet) and organised by Time partitioning so recent days can be rebuilt without touching old ones. A materialised view kept fresh incrementally (incremental-view-maintenance, no page yet) goes further: apply only the delta of changed rows, which the source emits as a change stream (Change data capture). The compiler's job then includes rollup matching: if a chart asks for monthly revenue by region and a daily-by-region rollup exists, rewrite the query to read it.

Scheduler and workers

Slow queries must not hold a web request open. Superset's documentation shows the standard split: Celery workers execute asynchronous SQL Lab queries, report snapshots and emails; beat is the single scheduler that triggers periodic tasks, and running two beats duplicates reports (Superset async queries). Results are stored in a separate results backend and serialised with MessagePack and PyArrow by default. Cache warm-up is itself a scheduled task with strategies such as top-N dashboards, and it needs a persistent cache backend and a schedule at least as frequent as the cache timeout (Superset caching). Redash's compose file has the same separation of scheduler and worker processes.

The primitives: cron-scheduling (no page yet) for "every weekday at 07:00 in the viewer's timezone"; a Durable job queue so a restarted worker does not lose accepted queries; and fair-queuing (no page yet) so one analyst's thousand-query script does not delay the CEO's dashboard. Superset's example configuration rate-limits the SQL Lab task per worker, which is a crude form of the same idea.

Chart data shaping

A line chart over a year of one-second data is 31 million points; a screen has about two thousand pixel columns. Grafana's docs show the standard answer: "max data points" defaults to the panel width, extra points are aggregated server-side if the data source supports it, and the $__interval variable lets queries group by a bucket that grows when the user zooms out (Grafana query options). The page also notes that resizing a panel can change computed values, which is a real pitfall: set a fixed value for stable numbers. When the source cannot aggregate, a shape-preserving downsampler such as lttb-downsampling (no page yet) picks points that keep peaks visible, and an hdr-histogram (no page yet) summarises latency distributions without storing raw samples. Tables use List virtualization so only visible rows enter the DOM.

Access control

Dashboards expose data, so every query runs for someone. The model is typically roles for objects (dashboard, dataset, database) plus filters on rows; see Authorization models for role-, attribute- and relationship-based variants, and Row-level security for enforcing the row filter in the data layer so a mistyped chart cannot bypass it. The cache must include the identity or the row-filter in its key; otherwise user B receives user A's cached rows. Superset's chart cache timeout hierarchy documented above says nothing about identity, so verify this per product. The tool-to-warehouse link is credential-based in most deployments; where the warehouse supports it, Mutual TLS authenticates the tool itself.

Metadata store

Everything the user authored lives in the metadata database: Superset recommends PostgreSQL or MySQL, standalone for production, and says workers and web servers must share it (architecture). It is small and precious, so it gets the full durability stack: Write-ahead log, Primary-backup replication (with Leases and Health checking for failover), Point-in-time recovery built on ARIES recovery and base backups in Object storage. Losing the cache is an inconvenience; losing dashboards is not.

Operations and verification

Web and worker processes run under Process supervision and drain in-flight requests on deploy (Graceful draining). The SQL compiler is the part worth fuzzing: it builds SQL from user-controlled strings (column names, filters, custom expressions), so Coverage-guided fuzzing with a dialect parser as oracle finds injection and quoting bugs. Deterministic simulation testing fits the scheduler and cache invalidation logic, which are order-sensitive and hard to reproduce.

Data and control flow

One dashboard view (cold cache).

1. The browser requests the dashboard; the web tier loads layout and chart specs from the metadata store (small reads, milliseconds).
2. Authorization checks the viewer against the dashboard and each dataset.
3. Each chart spec plus filters and viewer-derived row filters is compiled to SQL; the SQL text (or the spec) hashed with the identity forms the cache key.
4. A cache miss registers the key with request coalescing; the first caller submits the query to a worker queue, the rest wait on it.
5. A worker takes a pooled warehouse connection, runs the query, applies the row cap and stores the result under the key with a timeout. Time here is the warehouse's, order of seconds on a large scan.
6. The web tier shapes the rows (bucketing, limits) and returns them; the browser draws. Charts render independently, so the slowest chart does not block the others.

Warm view. Steps 1-3 as above; the key hits; shaping and return. This is the path caching exists for. Cache warm-up (step 5 run ahead of time by beat) moves the cold view out of business hours for the most-visited dashboards.

Scheduled report. Beat fires at the cron time; a worker renders the dashboard or runs its queries as the report owner, stores a snapshot, and sends email or chat. The worker needs the same code and configuration as the web server (Superset states this explicitly).

Scaling

1. Warehouse cost and latency limit first. Levers in order: cache with sensible TTLs; coalesce identical queries; push work into rollups; then buy warehouse capacity.
2. Concurrency on the warehouse. Limit the pool per datasource and queue the rest; apply per-user or per-dashboard fairness.
3. Cache memory. Cap value size, use an eviction policy suited to scans (frequency-aware policies resist one-off large results), and consider tiering to object storage for big results.
4. Worker throughput. Add workers; keep one scheduler; give long and short queries separate queues so short ones are not stuck behind long ones.
5. Web tier. Stateless apart from the metadata store and cache; add instances behind a load balancer.
6. Metadata store. Rarely first, but a replica handles read-heavy dashboard loads.
7. Browser. Cap points and rows; virtualise tables.

Failure modes

Real implementations

Sources

• Apache Superset admin docs: architecture, caching, async queries and Celery, datasets API
• Metabase docs in the repo: Caching query results, Syncing and scanning databases
• Grafana docs: data source management, query and transform data
• Redash: compose.yaml
• Licences read from each repo's LICENSE file.