SQL & data validation

Chapter 3 · Phase 2 · practice in Week 4 and Week 5

1. SQL as source of truth

LLMs are fluent writers but unreliable accountants. When a model states "revenue grew 23% in Q3," that number came from training data pattern-matching — not from running a query on your database. It may be a hallucination, outdated, or from a different company entirely.

The correct mental model: LLM writes the query → you run it → database returns the number. The LLM is a translator, not an oracle.

2. Text-to-SQL — how it works

Text-to-SQL converts a natural language question into a SQL query. The model needs two things in the prompt: the question and enough schema context to write a valid query.

Minimal text-to-SQL prompt

You are a SQL expert. Convert the question to a valid SQLite query.
Return ONLY the SQL. No explanation.

Schema:
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  created_at TIMESTAMP,
  plan TEXT,  -- 'free' | 'pro' | 'enterprise'
  last_active TIMESTAMP
);

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  event_type TEXT,
  created_at TIMESTAMP
);

Question: How many Pro plan users were active in the last 30 days?

Expected output

SELECT COUNT(DISTINCT u.id)
FROM users u
JOIN events e ON u.id = e.user_id
WHERE u.plan = 'pro'
  AND e.created_at >= datetime('now', '-30 days');

What the model needs to produce good SQL

• Full schema — table names, column names, data types, foreign keys
• Example data (optional but helpful) — sample rows clarify ambiguous column semantics
• Glossary — business term definitions ("active user = logged in within 30 days")
• Dialect — SQLite, PostgreSQL, BigQuery, Snowflake all have different functions

3. Where text-to-SQL breaks

4. Threat model — SQL injection via prompt

When users can influence the question that becomes a query, there is a prompt-injection risk where a malicious user crafts a natural language question that tricks the LLM into generating a destructive or data-exfiltrating query.

Mitigations

5. Validation patterns for LLM-generated SQL

Pattern A — Sanity check with known values

-- After running generated query:
-- 1. Check result is in expected range
SELECT COUNT(*) FROM users;  -- should be ~50,000 for our DB
-- LLM-generated MAU query returning 2M users is clearly wrong

-- 2. Check denominator separately
SELECT COUNT(DISTINCT user_id) FROM events
WHERE created_at >= '2024-01-01';
-- Compare to LLM's numerator to validate fraction

Pattern B — Dry-run with EXPLAIN

-- Before running an unfamiliar generated query:
EXPLAIN QUERY PLAN
SELECT ...  -- paste LLM-generated query here

-- Review the query plan for:
-- - Unexpected full table scans on large tables
-- - Missing index usage
-- - Surprising join order

Pattern C — Result count heuristic

-- Wrap any generated query in a count first
SELECT COUNT(*) FROM (
  -- LLM-generated query here
) t;

-- If count is 0 or unexpectedly large, investigate before returning to user