Natural language to SQL
Ask a scoped question, inspect planning scope, and judge query suggestions against known source data.
The practical problem and model
A business analyst wants to ask which product generated the most revenue without remembering every table name. Natural-language-to-SQL translates that request into a database query, using the schema and scope available to the caller.
The hard part is often the business definition. Here amount is already the order's total revenue; multiplying it by quantity would answer a different question. The prompt explicitly requests all dates, avoiding a hidden current-period assumption.
The fixture is small enough to compute the answer by hand. Use that as a check on both the generated SQL and the returned answer.
Before you run
Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.
Studio: Tutorial: Natural Language to SQL (ASK / SUGGEST QUERIES). Download the complete SQL.
ASK and SUGGEST QUERIES use Gnok's AI provider under your organization's AI Governance. An organization administrator opens Operations → AI Governance in Studio, saves a monthly token budget as Available, and enables a rollout policy that allows provider anthropic and model claude-haiku-4-5-20251001, with egress and retention approved. EXPLAIN ASK makes no provider call. See AI Governance. Scope selection narrows discovery; it does not grant new data permissions.
Step 1: Create five known orders
The fixture contains three products across four regions. All dates are fixed so the expected aggregate does not depend on when you run the example.
CREATE CATALOG IF NOT EXISTS tutorial;
CREATE SCHEMA IF NOT EXISTS tutorial.sales;
CREATE OR REPLACE TABLE tutorial.sales.orders (
order_id BIGINT,
region VARCHAR,
product VARCHAR,
quantity INT,
amount DOUBLE,
placed_at TIMESTAMP
);
INSERT INTO tutorial.sales.orders VALUES
(1, 'us-west', 'widget', 3, 89.97, TIMESTAMP '2026-04-01 10:00'),
(2, 'us-east', 'gadget', 1, 49.99, TIMESTAMP '2026-04-02 11:00'),
(3, 'eu', 'widget', 5, 149.95, TIMESTAMP '2026-04-02 14:00'),
(4, 'apac', 'gizmo', 2, 199.98, TIMESTAMP '2026-04-03 09:00'),
(5, 'us-west', 'gadget', 4, 199.96, TIMESTAMP '2026-04-03 12:00');
Step 2: Ask a precise question within a schema
WITH SCOPE tutorial.sales narrows schema discovery. EXPLAIN ASK helps inspect that scope; it does not substitute for reviewing the generated query and result.
ASK 'What is the top-selling product by total revenue across all orders? Use all dates.'
WITH SCOPE tutorial.sales;
EXPLAIN ASK 'top selling product by revenue'
WITH SCOPE tutorial.sales;
Step 3: Create conversation state and request suggestions
BEGIN CONVERSATION returns an ID for later conversation commands. The subsequent suggestion statement is scoped explicitly; it does not reference that returned ID. LIMIT 5 is an upper bound, and the output budget can yield fewer suggestions.
BEGIN CONVERSATION WITH SCOPE tutorial.sales;
SUGGEST QUERIES
ABOUT 'revenue trends and product mix'
LIMIT 5
WITH SCOPE tutorial.sales;
What to check in the results
The correct revenue ranking is:
| Product | Total revenue |
|---|---|
| gadget | 249.95 |
| widget | 239.92 |
| gizmo | 199.98 |
The ASK answer should identify gadget. Check every suggested title and description against its SQL, then inspect any results you choose to execute. The recorded constrained-output run returned three complete suggestions; five is not a required row count.
Keep the conversation ID if continuing the conversation and close it with the documented END CONVERSATION command when finished. Separate lifecycle checks verified follow-up context, closure, restart persistence, and provider-usage accounting.
Adapt it to real data
Maintain clear metric definitions and schema descriptions. Use the narrowest useful scope and the caller's actual data permissions. Review generated SQL for time filters, joins, grouping, and units before treating its answer as a business fact.
Budget schema context and output size, and handle incomplete provider responses explicitly. A syntactically valid suggestion can still answer the wrong question; language output needs semantic review alongside SQL execution checks.