What you will build
You will trace a natural-language question through clarification, generation, structural validation and read-only execution—then see unsafe SQL stopped before it reaches the database.
“Show our best customers” is not ready for SQL
Does best mean revenue, margin, number of orders or on-time payment? Generating SQL before resolving that ambiguity can produce a valid query that answers the wrong question.
The safe workflow has gates
- Understand the question and authorised user context.
- Clarify ambiguous metrics and time ranges.
- Retrieve approved schema and business definitions.
- Generate a bounded query.
- Parse and validate structure, tables, columns and operations.
- Execute with a read-only role, timeout, cost and row limits.
- Explain the result with query and source context.
- Log the request, decision, SQL and execution metadata.
The agent must pass these gates in order. It should clarify before generation, validate before execution and stop before any action outside its permission.
A governed query template
User question: “Which five customers generated the highest paid revenue in September 2026?”
01SELECT c.customer_id,02c.name,03SUM(o.total_amount) AS paid_revenue04FROM analytics.customers AS c05JOIN analytics.orders AS o06ON o.customer_id = c.customer_id07WHERE o.status = 'PAID'08AND o.ordered_at >= DATE '2026-09-01'09AND o.ordered_at < DATE '2026-10-01'10GROUP BY c.customer_id, c.name11ORDER BY paid_revenue DESC, c.customer_id12LIMIT 5;
The request defines the metric, state, time range and result size. A business definition layer can supply those meanings, but a SQL view alone is only one implementation building block—not a complete semantic governance system.
Example result returned to the agent
| customer_id | name | paid_revenue |
|---|---|---|
| C101 | Meena | ₹48,900 |
| C208 | Farah | ₹41,250 |
| C145 | Arun | ₹38,600 |
The agent should explain that this ranking uses paid revenue during September 2026, rather than presenting “best customer” as an undefined fact.
Permissions enforce what prompts cannot
01CREATE ROLE reporting_agent NOLOGIN;02GRANT USAGE ON SCHEMA analytics TO reporting_agent;03GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO reporting_agent;04ALTER DEFAULT PRIVILEGES IN SCHEMA analytics05GRANT SELECT ON TABLES TO reporting_agent;
Production configuration needs an appropriate login/session role and must follow the organisation’s database policy. Crucially, a prompt saying “do not delete” is not access control. Database privilege should make prohibited actions impossible.
Real-world application: finance question
An executive asks an agent for overdue invoice totals. The agent may prepare and execute an approved read-only query. Changing an invoice, issuing a refund or sending a collection notice crosses into consequential action and requires a separate workflow with explicit approval.
Validate before execution
String matching alone is fragile. Parse the SQL and apply an allowlist:
- Permit only approved statement types, schemas, tables, columns and functions.
- Reject multiple statements, comments used for evasion and unbounded operations.
- Apply statement timeout, maximum rows and resource controls.
- Consider
EXPLAINor a database-specific dry run before costly queries. - Keep an audit trail and redact sensitive values from logs.
Common text-to-SQL mistakes
Common mistakes
- Treating a syntactically valid query as a semantically correct answer.
- Allowing direct access to every production table.
- Depending on the model prompt instead of database permissions.
- Executing before clarifying vague metrics or dates.
- Returning a number without showing the definition, query context or freshness.
Practice the safety decision
For the request “Remove customers who have not purchased this year,” list the clarifications required, explain why read-only execution must block it and design a human-approved operational workflow instead.
Choose an answer, inspect the explanation and explain the idea in your own words.
What is the strongest control against a generated DELETE statement?
Interview-ready explanation
Why is prompt-only SQL safety insufficient?
Model instructions are probabilistic and can be misunderstood or bypassed. Safety needs independent enforcement: parsed-query allowlists, least-privilege database roles, execution limits, approval boundaries and audit logs.
Questions beginners usually ask
Should every generated query require approval? Not necessarily. Low-risk, read-only queries can run inside strict controls; high-cost, sensitive or consequential requests should stop or require review.
What should the answer show? Include the interpreted metric, time range, data freshness and enough query or source context for a person to verify the result.
What to remember
Key takeaways
- Text-to-SQL begins with business meaning, not generation.
- Validation should understand SQL structure and enforce explicit allowlists.
- Read-only privilege, timeouts and row limits provide independent protection.
- High-impact actions belong behind a separate human approval boundary.
- Audit evidence makes results explainable and incidents diagnosable.
Project complete: your Business Data Analyst can retrieve exact facts, analyse trends, search meaning, build time-safe ML features and answer natural-language questions within governed database boundaries.