Lab: Answer a Metric Question With Text-to-SQL

This is one of the hands-on labs that run alongside these posts. The full lab, with the database and the read-only guard, is in lab-08-text-to-sql.zip.

Before your first lab, do the one-time, once-per-account setup: run the zip’s preflight.sh to confirm your account is ready, then deploy the lab reaper, a standing backstop that auto-deletes any lab you forget to tear down after 24 hours.

The scenario

“What is the total monthly value by region?” has no passage to retrieve. The answer is a SUM over a GROUP BY, and semantic search cannot compute it, no matter how good the embeddings are. This is where text-to-SQL wins: give the model the schema, have it write a query, run the query, and turn the rows into a sentence. The lab bakes a small SQLite table into the function so the only thing you build is the generation; a real system would point the same pattern at Athena, Redshift, or RDS.

What you’re given

A Lambda that can call Bedrock, a subscriptions table with a schema description written for the model, a read-only guard that refuses anything but a single SELECT, and a summary step that turns the result rows into a plain answer. The gap is generate_sql().

Your task

Turn the question into a safe query. generate_sql(question) declares a run_query tool whose input schema is a single sql string, hands it to _bedrock.converse in toolConfig alongside SCHEMA_DESCRIPTION and the question at temperature 0, and reads the SQL out of the toolUse block that comes back. Asking for bare SQL in the prompt and hoping is what leaves you stripping markdown fences off the reply; a schema means there is no prose to strip it from.

The guard runs whatever comes back, but only if it is a lone SELECT, so a wrong or unsafe query fails loudly instead of touching data. The schema carries the shape and the guard carries the authority, and neither substitutes for the other: a well-formed query can still be the wrong one.

Deploy and prove it

cd lab-08-text-to-sql
./scripts/deploy.sh
./scripts/test.sh
./scripts/teardown.sh

“How many active subscriptions?” returns 8; “total monthly value by region” returns east 260, south 250, north 240, west 184; each answer carries the SQL the model wrote and a one-line summary. The run finishes by handing the guard a DELETE directly, and the reply is "error": "only a SELECT query is allowed" with the rows still there.

When you want the reference answer, deploy it with SRC=solution ./scripts/deploy.sh, or unfold it here:

SQL_TOOL = {
    "toolSpec": {
        "name": "run_query",
        "description": "Run a single read-only SELECT against the subscriptions database.",
        "inputSchema": {
            "json": {
                "type": "object",
                "properties": {
                    "sql": {
                        "type": "string",
                        "description": "One read-only SELECT statement, and nothing else.",
                    },
                },
                "required": ["sql"],
            }
        },
    }
}


def generate_sql(question):
    resp = _bedrock.converse(
        modelId=MODEL_ID,
        system=[{"text": "You are a SQLite expert. Answer the question by "
                         "calling the run_query tool with a single read-only "
                         "SELECT. Do not reply in prose."}],
        messages=[{"role": "user", "content": [
            {"text": f"Schema:\n{SCHEMA_DESCRIPTION}\n\nQuestion: {question}"}
        ]}],
        inferenceConfig={"maxTokens": 300, "temperature": 0},
        toolConfig={"tools": [SQL_TOOL]},
    )
    for block in resp["output"]["message"]["content"]:
        if "toolUse" in block:
            return block["toolUse"]["input"]["sql"].strip()
    raise ValueError("model did not call run_query")

The ideas that carry over

• Metric questions need SQL, not similarity. Any scenario asking for a count, sum, average, ranking, or join over structured data is a text-to-SQL scenario; embedding rows as text is the distractor. A managed Bedrock Knowledge Base can generate and run SQL over Redshift or Athena for exactly this.
• Schema grounding is the accuracy lever. The model writes correct SQL only when it knows the tables, columns, and allowed values. A vague or stale schema description produces confidently wrong queries.
• Generated SQL is untrusted input. Run it read-only, as a single statement, under a least-privilege database identity, with row and cost limits. The model proposes; your guard and your database permissions dispose. A tool schema does nothing for this: it constrains the shape of what comes back, never the authority of what you do with it.
• Constrain the output with a schema, not a plea. “Return only the SQL, no markdown” is a request the model is free to ignore, and the tell that you are relying on one is defensive parsing downstream. A tool schema moves the shape into the contract, and the reply arrives as parsed arguments instead of text you have to clean up.
• Split the two jobs. One model call writes the query; another turns the rows into a sentence. Each stays simple, and you can test the SQL independently of the phrasing.

What’s worth remembering

1. Counts, sums, averages, and rankings over structured data are text-to-SQL problems; vector retrieval cannot compute them.
2. Ground the model in an accurate schema; that description is what makes the generated SQL correct.
3. Treat generated SQL as untrusted: read-only role, single statement, row limits, so a bad or adversarial query cannot mutate or exfiltrate data.
4. A managed Bedrock Knowledge Base can do structured-data retrieval (NL to SQL) over sources like Redshift and Athena, the managed version of this lab.
5. Separate query generation from result summarisation; two simple calls beat one that tries to do both.
6. Route documents to RAG and metrics to text-to-SQL, and a system that must do both simply picks per question.