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. Text-to-SQL is the pattern for it: 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 rejects 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. Ask for bare SQL in the prompt instead, and the reply arrives inside a markdown fence often enough that you write code to strip it. A tool schema hands you parsed arguments, with no fence to strip.

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 constrains the shape of the reply; the guard constrains what executes. Neither substitutes for the other, because 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 request for a count, sum, average, ranking, or join over structured data is a text-to-SQL problem; embedding the rows as text does not get you a total. Amazon Bedrock Knowledge Bases does this as a managed service, with Amazon Redshift as the query engine over Redshift tables or the default AWS Glue Data Catalog.
• Schema grounding drives the accuracy. The model writes correct SQL only when the prompt names the tables, columns, and allowed values. A vague or stale schema description produces queries that parse cleanly and return the wrong number.
• Generated SQL is untrusted input. Run it read-only, as a single statement, under a least-privilege database identity, with row and cost limits. AWS gives the same advice for its own managed text-to-SQL: restricted roles, read-only databases, sandboxing, and no CREATE, UPDATE or DELETE grant. A tool schema does nothing for this, because it constrains the shape of what comes back and not what you then run.
• Constrain the output with a tool schema. “Return only the SQL, no markdown” still comes back fenced often enough that you end up writing a stripper, and that defensive parsing downstream is the tell. A tool schema moves the shape into the request, 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. Metrics need SQL, not similarity. Counts, sums, averages and rankings over structured data are text-to-SQL problems; vector retrieval cannot compute them.
2. Ground the model in the schema. An accurate description of tables, columns and allowed values is what makes the generated SQL correct.
3. Treat generated SQL as untrusted. Use a read-only role, a single statement and row limits, so a bad query cannot mutate or exfiltrate data.
4. Managed text-to-SQL exists. Amazon Bedrock Knowledge Bases translates natural language to SQL through an Amazon Redshift query engine; this lab is the hand-built version.
5. Split generation from summarising. One call writes the query, another summarises the rows; test the SQL independently of phrasing.
6. Route by question type. Document questions go to RAG, metric questions to text-to-SQL; a system serving both routes per question.