AI Analytics Dashboard

Ask your data in plain English

Design an analytics product where business users ask questions in plain English, get checked SQL, a chart and an explanation, and receive automatic alerts when a key metric moves.

Overview

Most people in a company cannot write SQL, so their questions wait for an analyst. An AI analytics dashboard lets them type 'What was revenue last quarter by region?' and get a number, a chart and a sentence explaining how it was computed.

The core step is text-to-SQL: a language model reads the question and a description of the database and writes a query. It fails in predictable ways. Business words do not match column names ('revenue' may mean the sum of paid order amounts, not a column called revenue). Dates are relative ('last quarter'). Joins have more than one plausible path. And the model can be talked into writing a query it should not, because the question itself is untrusted input.

So the design has two jobs beyond calling a model. It must make answers correct, by giving the model governed business definitions and showing users what was computed. And it must make them safe, by running every query with the user's own permissions inside the database, where no prompt can change them.

Questions to ask first

Where is the data? — In each customer's warehouse or PostgreSQL; we connect with a read-only account
Who are the users? — 50,000 business users across 1,000 companies, busiest in office hours
What must they not see? — Other companies' data, and rows their role does not allow
How fast? — SQL and explanation in 3 s at p95 (our target); the query itself takes as long as the warehouse takes
Follow-ups? — Yes: 'now by region', 'only in Europe'
Proactive alerts? — Yes: scheduled checks on key metrics, with a short written summary

What you will be able to do

Explain why sending the whole schema to the model fails, and how schema retrieval and a semantic layer fix it.
Make generated SQL safe with a read-only role, row-level security and timeouts in the database.
Handle follow-up questions by editing the previous query plan.
Pick a chart from the shape of the result, and cache results without leaking them across users.
Build metric alerts where the numbers come from SQL and the model only writes the summary.

Key concepts

Text-to-SQL
Schema retrieval
Semantic layer
Row-level security
Read-only execution
Query explanation
Conversation context
Chart selection
Anomaly detection
Result caching

Functional requirements

Turn a question into SQL for the customer's warehouse, and show the SQL with a plain-English explanation
Use business definitions (metrics, dimensions, synonyms) that the data team maintains
Run the query with the asking user's permissions and show the result as a chart or table
Answer follow-up questions by changing the previous query
Ask a clarifying question when a term is unknown or ambiguous
Run scheduled checks on key metrics and send alerts with a short summary

Non-functional requirements

SQL and explanation shown within 3 s at p95 (a target we chose); long warehouse queries stream a progress state
No generated query can read another tenant's data or rows outside the user's role
Every generated query is read-only and has a time limit and a row limit
Every answer is logged with the question, the SQL and the definitions used, for audit
Scheduled checks must not slow down interactive questions on the same warehouse

Interview questions

Why not just send the whole schema and the question to the model?
How do you stop generated SQL from leaking data?
How do you make the answers correct, not just plausible?
How do follow-up questions work?
How do proactive insights avoid made-up numbers?
How do you cache without leaking results?
What happens when a query is slow or expensive?