Lab 08 — Answer a metric question with text-to-SQL

Scaffold: 2/5. The database, the read-only guard, and the summary are done. You write the step that turns a question into SQL.

The scenario

“What is the total monthly value by region?” cannot be answered by semantic search. You cannot embed your way to a SUM or a GROUP BY; retrieval finds passages, not computed figures. The answer is text-to-SQL: give the model the schema, have it write a query, run the query, and summarise the result. Here the data is a small SQLite table baked into the function (a real system would point at Athena, Redshift, or RDS), so the only moving part is the generation.

The requirement

• A metric question (“how many active subscriptions?”, “total value by region”) returns the right number, with the SQL the model wrote and a one-sentence answer.
• A query that is not a plain SELECT never runs; the guard rejects it.

What’s provided

• template.yaml — a Lambda allowed to call Bedrock.
• data.py — the subscriptions table, its rows, and a SCHEMA_DESCRIPTION string for the model.
• src/handler.py — the read-only guard (_run_readonly), the result summary, and the orchestration. The gap is generate_sql(). It answers a prompt, and it also takes a raw sql field so you can point the guard at a statement directly.
• solution/handler.py — the reference answer.
• scripts/ — deploy, test (three metric questions, then the guard refusing a DELETE), teardown.

Your task

Implement generate_sql(question) in src/handler.py:

1. Prompt the model with SCHEMA_DESCRIPTION and the question, asking for a single read-only SELECT and nothing else.
2. Call the model at temperature 0.
3. Strip any stray markdown fence and return the SQL.

The guard runs whatever you produce, but only if it is a lone SELECT, so a bad query fails loudly instead of touching data.

Run it

# Prerequisite: Model access enabled for your model, in your region. The default
# model id, amazon.nova-lite-v1:0, is on-demand in us-east-1; elsewhere use the
# inference profile: MODEL_ID=us.amazon.nova-lite-v1:0 ./scripts/deploy.sh
./scripts/deploy.sh
./scripts/test.sh
./scripts/teardown.sh

What success looks like

“How many active subscriptions?” returns 8; “total monthly value by region” returns east 260, south 250, north 240, west 184, highest first; each response includes the generated SQL and a plain-English answer. The last case sends DELETE FROM subscriptions ... straight at the guard, and the run prints the refusal: "error": "only a SELECT query is allowed", with the rows untouched.

If it fails

• only a SELECT query is allowed on a metric question — the model wrapped the SQL in prose or a fence, or wrote an explanation. Tighten the prompt (“return only the SQL”) and strip the fence. On the DELETE case that message is the point: the guard is doing its job.
• Wrong numbers — the schema description and the real schema disagree, or the model guessed a column. Keep SCHEMA_DESCRIPTION accurate; that grounding is the whole game.
• AccessDeniedException — enable Model access for the model in this region.
• ValidationException about on-demand throughput — the region has no on-demand capacity for that base model id, so it needs an inference profile. Redeploy with MODEL_ID=us.amazon.nova-lite-v1:0 (or the eu. profile in Europe), which routes across the region group.

Reveal the solution

SRC=solution ./scripts/deploy.sh && ./scripts/test.sh

What you just learned

• Metric and aggregate questions want SQL, not similarity. Embedding a row as text cannot compute a sum; text-to-SQL is the right pattern for structured data, and a managed Bedrock Knowledge Base can do this over Athena or Redshift.
• Schema grounding is the accuracy lever. The model writes good SQL only when it is told the tables, columns, and allowed values; a vague schema gives wrong queries.
• Generated SQL is untrusted. Run it read-only, as a single statement, with a least-privilege database identity, so a bad or adversarial query cannot mutate or exfiltrate data. The guard here is the minimum; a real system also uses a read-only role and row limits.
• Two model calls, two jobs: one writes the query, one turns the rows into a sentence. Keep them separate so each is simple and testable.

Next

Lab 09 — Evaluate the pipeline. You stop building features and start measuring one: a small golden set, an automated judge, and a score you can trust, so a change is a number, not a hunch.