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 earns its place: 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) builds a prompt that hands the model SCHEMA_DESCRIPTION and the question and asks for a single read-only SELECT with no explanation and no markdown fences, then calls _bedrock.converse at temperature 0 and takes the text of the first content block. Before returning it, strip any stray backtick fence the model wrapped around the SQL; models fence SQL out of habit, prompt or no prompt.

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. Without the fence strip, a model that wraps its SQL in a markdown fence turns every good query into a rejected 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:

def generate_sql(question):
    prompt = (
        "You are a SQLite expert. Given this schema:\n"
        f"{SCHEMA_DESCRIPTION}\n\n"
        "Write a single read-only SELECT query that answers the question. "
        "Return only the SQL, with no explanation and no markdown fences.\n\n"
        f"Question: {question}"
    )
    resp = _bedrock.converse(
        modelId=MODEL_ID,
        messages=[{"role": "user", "content": [{"text": prompt}]}],
        inferenceConfig={"maxTokens": 300, "temperature": 0},
    )
    sql = resp["output"]["message"]["content"][0]["text"].strip()
    # Models fence SQL out of habit, prompt or no prompt.
    if sql.startswith("```"):
        sql = sql.strip("`")
        if sql.lower().startswith("sql"):
            sql = sql[3:]
    return sql.strip()

The ideas that carry over

• Metric questions want 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. This is the same principle as a tool call: the model never gets more authority than the code that executes for it.
• 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.