Curious builder0 XP earned · 300 to level 2

0 daysFinish a lesson to begin

Badge collection0 of 6 unlocked

51 small wins to finish your pathNext question →

Q21IntermediateSystem design

Design a natural-language analytics assistant (text-to-SQL) over a company data warehouse.

30-second answerSay your answer out loud first, then reveal.

Ground the model in a curated semantic layer: table and column descriptions, business metric definitions, join paths, example queries. Retrieve the relevant schema snippets for each question, generate SQL with an LLM, validate it (parse, read-only, allowed tables, row-level security, cost limits), execute it with a timeout, and return results with the SQL shown and a chart. Handle ambiguity with clarifying questions. Build an eval set of question → correct-result pairs, and learn from analysts' corrections.

Text-to-SQL flow: a business question and the semantic layer feed schema retrieval; ambiguous questions get a clarifying question, otherwise the LLM writes SQL that is validated (with a repair loop on errors), executed on a read replica, returned as an answer with table, chart and the SQL, and analyst corrections become new examples.

Key challenges

Schema scale: warehouses have thousands of tables. Retrieve relevant tables via embeddings and metadata; maintain a curated "gold" subset for common questions.
Business semantics: "active user", "revenue", "churn" have company-specific definitions. Encode them in a semantic layer (dbt metrics, LookML-style), or have the LLM generate metric queries instead of raw SQL.
Correctness: queries can be syntactically valid but semantically wrong (wrong join, double counting). Mitigations: verified example queries (few-shot retrieval), showing the SQL and assumptions, result sanity checks.
Security: read-only roles, row and column-level security per user, PII column masking, query cost limits.
Evaluation: execution accuracy (does the result match the gold query's result?) on 100–300 real questions.

UX: show the interpretation ("I interpreted 'last quarter' as Jul–Sep 2026, revenue = net revenue") so users can catch mistakes.

PreviousDesign a meeting summarisation system: audio → transcript → summary → action items.

Little by little, you're building something great.

Design a natural-language analytics assistant (text-to-SQL) over a company data warehouse.

Key challenges

Related