How AI Writes SQL Queries

Introduction

Ever typed a question like “Show me the top 10 customers by revenue last quarter” and watched an AI assistant churn out a SELECT statement? Behind that seemingly magical moment is a layered pipeline of intent detection, schema grounding, and query generation. In this guide you will learn how AI produces SQL you can trust, practice the workflow yourself, and discover how Galaxy’s AI copilot raises the accuracy bar by weaving your data context directly into the generation loop.

Learning Objectives

• Describe the key steps an AI system follows to translate text into SQL.
• Compare template-based, statistical, and large-language-model (LLM) approaches.
• Walk through a complete example that goes from user prompt to validated query.
• Identify common failure modes and debugging strategies.
• Use Galaxy to generate, tweak, and share AI-assisted SQL queries.

1. Fundamentals of Natural Language → SQL

1.1 Intent & Entity Extraction

The first step is transforming prose into structured pieces the model can reason about:

User Prompt: "List active users who upgraded in June."
8 Intent: RETRIEVE
8 Entities: {table: users, status: 'active', event: 'upgrade', month: '2023-06'}


Classical NLP pipelines use tokenisation and part-of-speech tagging; LLMs implicitly learn these patterns during pre-training.

1.2 Schema Grounding

AI must map concepts like “active users” to concrete columns in your database (users.last_active_at). Without grounding, an LLM might hallucinate fields that do not exist.

• Static grounding: A pre-defined dictionary maps business terms to columns.
• Dynamic grounding: The model is fed your live schema (table names, column names, comments) via the prompt—Galaxy’s default approach.

1.3 Query Planning

Once entities align with the schema, the model assembles clauses (SELECT, FROM, WHERE, GROUP BY, ORDER BY). Some systems build an abstract syntax tree (AST) first, others generate raw SQL tokens directly.

2. AI Techniques for Generating SQL

2.1 Template-Based Systems

Early “AI” solutions relied on manually curated templates:

Template: "SELECT {{columns}} FROM {{table}} WHERE {{condition}}"


Pros: fast and predictable. Cons: brittle—new question types require new templates.

2.2 Statistical Machine Translation (Seq2Seq)

Neural nets treat SQL as another language to translate into. Sequence-to-sequence (seq2seq) models with attention outperform templates but still struggle with unseen schemas.

2.3 Large Language Models (LLMs)

Models like GPT-4 or open-source Llama 3 have ingested vast code corpora. When prompt-engineered with your schema and examples, they can generate complex joins and window functions in one shot.

3. Step-by-Step Example: From Prompt to SQL

We will convert the question “Which five products generated the highest revenue in Q1 2024?” into SQL.

1. Load schema context // Galaxy automatically fetches
Tables: orders (id, product_id, total_price, created_at)
products (id, name, category)

2. Extract intent & entities Intent: aggregation (TOP N)
Entities: product, revenue, Q1 2024 (date range)

3. Generate query (LLM draft) SELECT p.name,
SUM(o.total_price) AS revenue
FROM orders o
JOIN products p ON p.id = o.product_id
WHERE o.created_at BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 5;

4. Validate
   • Run in EXPLAIN mode.
   • Check for missing indices or type mismatches.
5. Refine (optional) // Copilot prompt: "Rewrite using CTEs & include product category"
WITH q1_orders AS (
SELECT product_id,
total_price
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-03-31'
)
SELECT p.category,
p.name,
SUM(q.total_price) AS revenue
FROM q1_orders q
JOIN products p ON p.id = q.product_id
GROUP BY p.category, p.name
ORDER BY revenue DESC
LIMIT 5;


4. Hands-On Practice with Galaxy

1. Open Galaxy Editor (desktop or web).
2. Connect to your database.
   • Galaxy supports Postgres, MySQL, Snowflake, and more.
3. Prompt the Copilot (Cmd + J shortcut) with: "Find daily sign-ups and 7-day rolling average for the last month." Watch the Copilot fetch your users table schema, propose a window-function query, and insert it into the editor.
4. Run & Inspect
   • Hit Cmd + Enter. Results appear in the grid. Toggle the Chart tab for a quick line graph.
5. Endorse & Share
   • Click “Add to Collection → Growth Metrics”. Team-mates now reuse the vetted query instead of asking you again.

5. Common Pitfalls & Troubleshooting

• Hallucinated Columns – Mitigate by always feeding explicit schema. Galaxy auto-injects table DDL into the prompt.
• Ambiguous Business Terms – Clarify inputs (e.g., define “active”). Maintain a semantic layer or glossary.
• Performance Regressions – AI may generate N+1 joins. Use EXPLAIN, indexes, and Copilot prompts like “optimize for Postgres”.
• Compliance & Permissions – Non-technical users pasting AI SQL can be risky. Galaxy enforces role-based access and query endorsement to curb this.

6. Best Practices

1. Few-Shot Examples – Provide 2-3 correct queries in the prompt to steer the LLM.
2. Incremental Generation – Ask for SELECT clause only first, then add filters. Easier to debug.
3. Version Control – Sync Galaxy Collections to GitHub so regenerated queries are peer-reviewed.
4. Schema Comments – Document columns; Copilot uses comments to pick the right fields.
5. Fallback Templates – For mission-critical paths, keep deterministic templates as a safety net.

7. Exercises

1. Write a natural-language prompt for: “Quarter-over-quarter retention by cohort”. Generate SQL in Galaxy, then refactor it to use WITH CTEs.
2. Intentionally omit a join condition in the prompt and see how the AI responds. Fix the error and record the diff.
3. Create a Collection called “Endorsed Marketing Queries” and save three AI-generated statements. Invite a teammate as Viewer; confirm they can run but not edit.

Key Takeaways & Next Steps

• AI SQL generation works through intent extraction, schema grounding, and query assembly.
• LLMs excel when supplied with up-to-date schema and examples—exactly what Galaxy automates.
• Always validate, optimize, and version AI output before production use.
• Continue learning: explore window functions, common table expressions, and query optimisation modules in Galaxy’s library.