Design a Business Intelligence "Self-Service" Tool

Problem statement

Design a web-based self-service business intelligence platform where non-technical business users explore governed data, drag and drop dimensions and measures to build charts, assemble dashboards, subscribe to scheduled deliveries, and trust that every number they see is computed from a single, certified definition — all without writing SQL and without filing a ticket with the data team.

The warehouse already exists. The BI platform does not own the raw data; it sits above a cloud warehouse such as Snowflake, BigQuery, Redshift, or Synapse and compiles business questions into governed SQL. That positioning defines the entire design. The platform owns four things the warehouse does not provide: a semantic layer mapping business terms to physical columns, a compilation and governance path that enforces security inside every generated query, a caching and concurrency system that makes interactive exploration affordable, and a metadata backbone that keeps definitions consistent across thousands of users.

Why the problem is distinctive

A consumer web system optimizes for high QPS and small requests. A BI platform is the opposite profile: low query volume, extremely expensive queries, and correctness and security properties that are far more valuable than raw speed. One drag-and-drop action can compile into a scan of two billion fact rows joined against six dimension tables. The expensive resource is not application CPU; it is warehouse concurrency and scan budget. Therefore the design separates mission-critical correctness (security predicates, metric definitions, version pinning) from best-effort performance (result caches, thumbnails, usage counters).

The second distinctive property is trust. When finance reports revenue from the BI tool and the data team reports a different revenue number from a notebook, the organization stops trusting both. A self-service tool therefore needs a metrics contract: every measure has exactly one certified definition, versioned like code, with lineage from dashboard tile back to source table. This is the gap that separates a serious answer from a drawing of boxes labeled chart builder plus database.

The core split: authoring plane versus query plane

The authoring plane is where analysts and data stewards create and govern meaning: semantic models, dimensions, measures, joins, row-level security policies, certification states, dashboards, and chart specifications. It is low-throughput, strongly consistent, and versioned.

The query plane is where users consume meaning: it takes a chart specification, resolves it against a pinned semantic model version, compiles SQL with row-level security injected, checks caches, obtains a warehouse slot from the governor, executes, materializes a bounded result, and renders it. It is read-heavy, cache-driven, and must degrade gracefully when the warehouse is slow.

A strong interview answer keeps these planes separate. The authoring plane may pause for minutes during an incident; the query plane must keep serving cached and pinned results. And nothing a user drags onto a canvas may ever bypass the security and governance path.

Public operating baseline versus design assumptions

The category is operationally mature. Looker publicly documents its in-database architecture and the LookML modeling language, including Git-backed model versioning and aggregate awareness. Airbnb publicly described Minerva, its metrics layer providing a single source of truth for metric definitions across the company. LinkedIn open-sourced DataHub and publicly described its generalized metadata platform for datasets, dashboards, and lineage. Tableau publicly documents VizQL, its visual query language that compiles drag-and-drop actions into queries, and the Hyper data engine. These are cited public descriptions, not requirements for our fictional system.

For capacity planning, this answer explicitly assumes a large enterprise deployment: 60,000 registered users, 6,000 daily active analysts, 240,000 query executions per day with a five-times morning peak, 2,500 governed semantic models, and 20,000 published dashboards. Unless a number is tied to a named company description, it is a stated design assumption, target, or budget — not a claim about any company's private architecture.