Text-to-SQL analytics assistant

Least privilege first: a read-only role limited to approved tables or views, statement timeouts and row limits, and a read replica. Validate the SQL before running it (parse it, block anything but SELECT), and never let model output become part of a command without those checks.

The user asks a question; the assistant retrieves relevant tables, columns, business definitions and example queries from a semantic layer, generates SQL with the schema in context, validates it (parse, allow only SELECT, check tables and columns, add limits), runs it on a read replica with a read-only role and timeouts, then summarises results with the SQL shown and a chart, and asks for clarification when the question is ambiguous.

Do not paste the whole warehouse: retrieve only relevant tables by embedding table and column descriptions, include column types, keys, sample values and join paths, and add curated example question–SQL pairs for common metrics.

Detect ambiguity (which revenue, which date, which region) and ask a clarifying question or state assumptions explicitly, using business definitions from the semantic layer to pick standard interpretations.

Provide a semantic layer or glossary: what 'revenue', 'active user' or 'region' mean, with example queries for common questions. Retrieve the relevant definitions with the schema. Curated views that already encode the definitions work even better.

A curated layer (dbt metrics, Cube, LookML) defining business metrics, dimensions and joins once. Generating queries against it reduces errors because 'active customer' or 'net revenue' have one agreed definition.

Parse it with a SQL parser, reject anything other than SELECT, check referenced tables and columns exist and are allowed, enforce row limits and timeouts, and optionally run EXPLAIN to catch very expensive plans.

Return the error message to the model to fix the query, limit retries, and if it still fails, ask the user to rephrase or show the closest working query with explanation.

Run queries as a role that only sees permitted data (row-level security policies, secure views), never rely on the prompt to hide columns, and mask sensitive columns in the warehouse.

Show the SQL, the definitions used, the time range and filters in plain language, the result table and a suitable chart, and let users open the query in their BI tool.

Use LIMIT and partition filters, prefer aggregated tables, set query cost or byte limits (BigQuery maximum bytes billed), cache results for repeated questions and monitor per-user usage.

Collect real business questions with gold SQL written by analysts, include ambiguous and tricky joins, measure execution accuracy (results match gold), and track performance per question category.

Index table and column descriptions, sample values and common joins, retrieve the most relevant tables for each question with semantic search plus keyword matching, then give the model only those schemas. Learn from which tables successful past queries used.

Define them once in the semantic layer or as approved SQL snippets, have the assistant use those definitions instead of inventing calculations, and show which definition was applied in the answer.

Build a set of questions with correct SQL or expected results. Run the generated SQL and compare results (execution accuracy) rather than the SQL text, since many queries are equivalent. Track failures by type: wrong table, wrong join, wrong filter.

Different SQL can be correct, so execution accuracy compares result sets from the generated and gold queries rather than exact string matches.

Define a standard business calendar and time zone in the semantic layer, resolve relative dates ('last quarter') in code to explicit ranges, and show them in the answer.

Keep conversation state with the previous query and filters, so 'and by region?' modifies the last query instead of starting over.

Pick from the result's shape: time series as line charts, category comparisons as bar charts, parts of a whole only with few categories, and tables for detailed data.

Treat query results as data, never execute instructions found in them, keep SQL execution read-only, and restrict which tables can be queried.

Log questions, generated SQL, results and user feedback, have analysts correct failures, add corrected pairs to examples and the evaluation set, and update descriptions of confusing columns.

When data needed is not available, the user lacks permission, or the question needs judgement beyond data; it should say so and suggest who to ask.

Write clear descriptions of each table and column (meaning, units, allowed values, gotchas like soft-deleted rows), and keep them in the catalogue alongside lineage.

Translate the SQL into a short plain-language description of what was counted, filtered and grouped, list the tables and date range used, and let the user expand to see the full SQL.

Answer only if both are joined in the warehouse with known keys, otherwise say the data lives in separate systems. Never join on fuzzy matches like names without telling the user about the risk.

Cache generated SQL by normalised question and user permissions, cache query results with short TTLs for dashboards, and suggest saved queries for common questions instead of regenerating SQL each time.

Let them flag the answer and edit the filters or the SQL, run the corrected query, store the pair as a new evaluation example after review, and use it as a few-shot example for similar questions.

Strong reasoning and code models generally do best; test candidates on your own evaluation set because schema complexity varies widely.

Aggregate in SQL, return only the top rows with a count, and offer a download or BI link for full results.

Question, user, retrieved schema, SQL, validation result, execution time, rows returned, cost and feedback.

Start with one well-modelled domain (sales or support), a group of analysts as early users, and expand domains as accuracy is proven.

Explain that the assistant reports historical data, offer the trend or a simple forecast only if an approved forecasting model or table exists, and label any projection clearly as an estimate.

1. ALower temperature
2. BA longer schema description
3. CA polite system prompt
4. DA read-only database roleCorrect answer

Why: The database refuses writes, whatever the model generates.

1. AThe entire warehouse
2. BNo schema
3. CRandom tables
4. DOnly relevant tables retrieved by descriptionCorrect answer

Why: Smaller context, fewer mistakes.

1. AAsk for clarification or state assumptionsCorrect answer
2. BGuess silently
3. CRefuse always
4. DRun every interpretation

Why: Avoid confident wrong answers.

1. AAny statement
2. BDELETE with a WHERE
3. CDROP in staging
4. DSELECT onlyCorrect answer

Why: Analytics must stay read-only.

1. AThey need no indexes
2. BReplicas are free
3. CSo heavy queries do not slow the production primaryCorrect answer
4. DReplicas allow writes

Why: It isolates analytical load from the transactional database.

1. AAgreed definitions of metrics, dimensions and joinsCorrect answer
2. BA database backup
3. CA UI theme
4. DA cache

Why: One definition of revenue.

1. AAsking the model to filter
2. BHiding columns in the prompt
3. CTrusting users
4. DA database role with RLS or secure viewsCorrect answer

Why: Prompts are not security.

1. AModel settings
2. BNothing
3. CThe SQL and the definitions usedCorrect answer
4. DOnly a number

Why: Users can verify the logic themselves.

1. ARetry forever
2. BDrop the table
3. CReturn it to the model to fix, with limited retriesCorrect answer
4. DShow a blank page

Why: Then ask the user to rephrase.

1. ALIMITs, partition filters and byte capsCorrect answer
2. BLarger queries
3. CNo caching
4. DSELECT * everywhere

Why: Monitor usage per user.

1. ALeft as text
2. BResolved in code to explicit rangesCorrect answer
3. CGuessed by the model
4. DIgnored

Why: Show them in the answer.

1. ANo schema
2. BRandom tables
3. COnly the retrieved relevant schemasCorrect answer
4. DEvery table's schema

Why: Focused context improves accuracy and cost.

1. AThe model's own calculation
2. BWhatever the user types
3. CThe largest number
4. DThe approved semantic-layer definitionCorrect answer

Why: One definition keeps answers consistent.

1. ASQL text character by character
2. BThe results of running the generated and reference queriesCorrect answer
3. CQuery length
4. DToken counts

Why: Different SQL can give the same correct result, so results are compared.

1. AWord cloud
2. BScatter of IDs
3. CLine chartCorrect answer
4. DPie chart

Why: Line charts show trends over time.

1. AIgnore context
2. BAsk the user to repeat everything
3. CModify the previous query from conversation stateCorrect answer
4. DStart over each time

Why: Keeps conversations natural.

1. AUI bugs
2. BNetwork issues
3. CVery expensive query plansCorrect answer
4. DSpelling errors

Why: Before running the query.

1. ANothing
2. BAs screenshots
3. CAggregated with top rows and a BI linkCorrect answer
4. DAll rows in chat

Why: Keeps answers readable.

1. AThe author's name
2. BRandom notes
3. CNothing
4. DMeaning, units and gotchasCorrect answer

Why: Prevents misread columns.

1. AFor every question
2. BWhen the query is short
3. CWhen data or permission is missingCorrect answer
4. DNever

Why: Say who to ask instead.

1. AIt cannot
2. BBy raising temperature
3. CBy deleting logs
4. DAnalysts correct failures, added to examples and evalsCorrect answer

Why: This closes the feedback loop.

1. AExecuted
2. BSent to admins
3. CTreated as data and ignored as commandsCorrect answer
4. DFollowed

Why: This prevents prompt injection.

1. AOnly the raw SQL
2. BThe database password
3. CThe model's name
4. DWhat was counted, filtered and the date rangeCorrect answer

Why: Plain language builds trust.

1. ARequired
2. BFaster and safer
3. CRisky and must be disclosedCorrect answer
4. DAlways exact

Why: Name matches create false joins.

1. AOnly the chart colours
2. BNothing
3. CUser passwords
4. DSQL and results scoped to permissionsCorrect answer

Why: Caches must respect who may see the data.

1. ADeleted logs
2. BReviewed evaluation and few-shot examplesCorrect answer
3. CIgnored notes
4. DProduction SQL without review

Why: Corrections improve future answers.

1. ASlots per user
2. BMaximum bytes billedCorrect answer
3. CMax rows
4. DTimeout only

Why: Rejects expensive scans.

1. AAll domains at once
2. BThe messiest data
3. CExecutives only
4. DOne well-modelled domainCorrect answer

Why: Expand as accuracy is proven.

1. Adbt metrics or CubeCorrect answer
2. BPhotoshop
3. CSlack
4. DExcel macros only

Why: Shared metric definitions.

1. AOnly the question
2. BNothing
3. COnly errors
4. DSQL, validation, rows, cost and feedbackCorrect answer

Why: These logs are needed to improve.

1. ABy name
2. BTested on your own evaluation setCorrect answer
3. CBy popularity
4. DBy release date

Why: Warehouse schemas vary widely.

1. AReturn last year's total as fact
2. BBe answered only with a labelled, approved forecastCorrect answer
3. CGet an invented number
4. DRun a random query

Why: Projections must be clearly marked.