04. SQL RAG¶
Category: Advanced RAG Architecture
Module: Part V — Advanced Retrieval-Augmented Generation
Difficulty: Advanced
📖 Overview¶
Traditional RAG systems primarily retrieve information from unstructured content such as:
Vector retrieval works particularly well when the user asks:
"What does the refund policy say?"
"Explain the authentication architecture."
"Summarize the incident report."
However, enterprise data is also heavily structured.
Examples include:
Customers
Accounts
Transactions
Orders
Products
Employees
Invoices
Payments
Inventory
Subscriptions
Metrics
This information typically lives inside relational databases.
For these questions:
"How many customers signed up last month?"
"Which products generated the highest revenue?"
"Show the top 10 customers by transaction volume."
"How many failed payments occurred yesterday?"
semantic vector retrieval is usually not the right primary retrieval mechanism.
SQL RAG combines:
Natural Language
+
Query Understanding
+
SQL Generation
+
Database Execution
+
Result Validation
+
LLM Generation
to allow an LLM-powered application to retrieve precise information from structured databases.
🎯 Learning Objectives¶
After completing this chapter, you will be able to:
- Understand SQL RAG
- Understand why SQL databases complement vector RAG
- Understand Text-to-SQL
- Understand NL2SQL pipelines
- Understand database schema discovery
- Understand schema-aware prompting
- Generate SQL safely
- Validate generated SQL
- Execute read-only SQL queries
- Handle joins and aggregations
- Handle multi-table reasoning
- Combine SQL retrieval with vector retrieval
- Build hybrid SQL + Vector RAG systems
- Understand semantic layers
- Handle enterprise database metadata
- Apply SQL security controls
- Implement query authorization
- Prevent SQL injection through generated queries
- Control query cost and execution time
- Validate SQL results
- Handle ambiguous questions
- Evaluate Text-to-SQL systems
- Design production SQL RAG architectures
- Implement observability and governance
🧠 1. What Is SQL RAG?¶
SQL RAG is a RAG architecture where the retrieval layer uses structured databases.
Instead of:
SQL RAG uses:
User Query
↓
Query Understanding
↓
Schema Retrieval
↓
SQL Generation
↓
SQL Validation
↓
Database Execution
↓
Structured Result
↓
LLM
↓
Response
🔎 2. Traditional RAG vs SQL RAG¶
Traditional Vector RAG¶
SQL RAG¶
Hybrid RAG¶
User Query
│
┌────────┴────────┐
▼ ▼
Vector Retrieval SQL Retrieval
│ │
▼ ▼
Documents Structured Data
│ │
└────────┬────────┘
▼
Evidence Fusion
│
▼
LLM
📊 3. Structured vs Unstructured Knowledge¶
Enterprise AI applications usually need both.
| Data Type | Example | Typical Retrieval |
|---|---|---|
| Unstructured | Policy PDF | Vector Search |
| Unstructured | Architecture Document | Vector Search |
| Semi-structured | JSON | Metadata / Search |
| Structured | Customer Table | SQL |
| Structured | Transactions | SQL |
| Structured | Product Catalog | SQL |
| Relationship-heavy | Dependencies | Knowledge Graph |
| Mixed | Customer + Policy | SQL + Vector |
This leads to an important architectural principle:
🧩 4. What Is Text-to-SQL?¶
Text-to-SQL converts natural-language questions into SQL.
Example:
Generated SQL:
SELECT COUNT(*)
FROM customers
WHERE registration_date >= '2026-01-01'
AND registration_date < '2027-01-01';
The database executes the SQL.
The LLM then converts the result into a user-friendly answer.
🔄 5. Basic Text-to-SQL Pipeline¶
flowchart LR
A["Natural Language Query"] --> B["Schema Retrieval"]
B --> C["SQL Generation"]
C --> D["SQL Validation"]
D --> E["Database"]
E --> F["Query Result"]
F --> G["Answer Generation"]
G --> H["Response"]🧠 6. Why SQL RAG?¶
LLMs are excellent at:
Databases are excellent at:
SQL RAG combines these strengths:
📈 7. Example¶
Question:
The LLM identifies:
Possible SQL:
SELECT SUM(amount) AS total_revenue
FROM transactions
WHERE product = 'Payments'
AND transaction_date >= '2026-07-01'
AND transaction_date < '2026-08-01';
Database:
LLM:
🏗️ 8. Production SQL RAG Architecture¶
flowchart TD
A["User"] --> B["API"]
B --> C["RAG Orchestrator"]
C --> D["Intent Detection"]
D --> E["Query Planner"]
E --> F["Schema Retriever"]
F --> G["Relevant Schema"]
G --> H["SQL Generator"]
H --> I["SQL Validator"]
I --> J{"Valid?"}
J -->|No| K["SQL Repair"]
K --> I
J -->|Yes| L["Read-Only Database"]
L --> M["Query Result"]
M --> N["Result Validator"]
N --> O["Answer Generator"]
O --> P["Response"]🧩 9. Database Schema¶
The LLM cannot reliably generate SQL without understanding the database schema.
Example:
customers
---------
id
name
email
registration_date
country
orders
------
id
customer_id
order_date
total_amount
status
products
--------
id
name
category
price
The schema provides:
📚 10. Schema Retrieval¶
For a large enterprise database, sending the complete schema to the LLM is inefficient.
Instead:
User Query
↓
Schema Retrieval
↓
Relevant Tables
↓
Relevant Columns
↓
Relevant Relationships
↓
SQL Generation
🔎 11. Schema-Aware Retrieval¶
Question:
Relevant schema might be:
customers
---------
id
name
orders
------
customer_id
total_amount
product_id
products
--------
id
name
Instead of providing hundreds of unrelated tables.
🧠 12. Schema as Retrieval Context¶
The prompt can contain:
Relevant Tables:
customers
- id
- name
orders
- customer_id
- total_amount
- product_id
products
- id
- name
Relationships:
orders.customer_id → customers.id
orders.product_id → products.id
Then ask the model to generate SQL.
🏷️ 13. Database Metadata¶
Useful metadata includes:
Table Name
Column Name
Data Type
Description
Primary Key
Foreign Key
Relationships
Business Meaning
Sample Values
Sensitivity
Owner
Example:
{
"table": "transactions",
"column": "amount",
"type": "DECIMAL",
"description": "Transaction monetary amount",
"sensitive": false
}
🧠 14. Business Metadata¶
Technical schema alone may not be sufficient.
For example:
could mean:
Business metadata helps the LLM understand the semantic meaning.
🧩 15. Semantic Layer¶
A semantic layer maps technical database structures to business concepts.
Example:
Another:
This reduces ambiguity in SQL generation.
🏢 16. Enterprise Semantic Layer¶
flowchart TD
A["Business Question"] --> B["Semantic Layer"]
B --> C["Business Concepts"]
C --> D["Physical Tables"]
D --> E["Columns"]
E --> F["SQL Generator"]
F --> G["Database"]This creates a separation between:
and:
🔗 17. Schema Relationships¶
SQL generation often requires joins.
Example:
The model needs to understand:
🔎 18. Join Reasoning¶
Question:
Requires:
SELECT DISTINCT c.name
FROM customers c
JOIN orders o
ON o.customer_id = c.id
JOIN products p
ON p.id = o.product_id
WHERE p.name = 'Payments';
This is a multi-table reasoning problem.
🧠 19. SQL Generation Prompt¶
A controlled prompt might look like:
You are an SQL generation system.
Generate a read-only SQL query.
Rules:
- Use only provided tables and columns.
- Do not modify data.
- Do not access unauthorized tables.
- Do not use SELECT * unless required.
- Return SQL only.
Schema:
customers(
id,
name,
registration_date
)
orders(
id,
customer_id,
order_date,
total_amount
)
Question:
"How much did each customer spend?"
Expected:
SELECT
c.id,
c.name,
SUM(o.total_amount) AS total_spend
FROM customers c
JOIN orders o
ON o.customer_id = c.id
GROUP BY c.id, c.name;
🛡️ 20. SQL Generation Must Be Constrained¶
Never treat generated SQL as automatically trusted.
The architecture should be:
Not:
🚨 21. Dangerous SQL¶
A model might generate:
or:
or:
A production SQL RAG system should prevent these operations.
🔐 22. Read-Only Database Access¶
Prefer a dedicated read-only database user.
Conceptually:
RAG Application
│
▼
Read-Only DB User
│
├── SELECT ✓
├── INSERT ✗
├── UPDATE ✗
├── DELETE ✗
├── DROP ✗
└── ALTER ✗
Defense should exist at the database permission layer, not only in prompts.
🧩 23. SQL Validation¶
SQL validation should check:
Syntax
+
Statement Type
+
Allowed Tables
+
Allowed Columns
+
Authorization
+
Query Complexity
+
Resource Limits
Example:
def validate_sql(sql):
parsed = parse_sql(sql)
assert is_select_statement(parsed)
assert only_allowed_tables(parsed)
assert only_allowed_columns(parsed)
assert no_write_operations(parsed)
return True
This is illustrative architecture rather than a complete SQL security implementation.
🧠 24. SQL AST Validation¶
A stronger approach parses SQL into an Abstract Syntax Tree.
The validator can inspect:
🔐 25. Allowlist Approach¶
Instead of trying to block every dangerous operation:
and explicitly control:
Example:
This is safer than relying solely on blacklist rules.
🧩 26. Row-Level Security¶
Enterprise databases may contain:
A user from Tenant A should not retrieve:
Use database-level or trusted application-level controls such as:
🏢 27. Multi-Tenant SQL RAG¶
flowchart TD
A["User"] --> B["Identity"]
B --> C["Tenant Context"]
C --> D["Authorization"]
D --> E["Schema Filtering"]
E --> F["SQL Generation"]
F --> G["SQL Validation"]
G --> H["Tenant-Aware Database"]
H --> I["Result"]The tenant boundary should be enforced outside the LLM.
🛡️ 28. Sensitive Data¶
Enterprise databases may contain:
The SQL RAG layer should know which columns are sensitive.
Example:
may require additional authorization.
🔒 29. Column-Level Access¶
A user may be allowed:
but not:
The schema retriever should therefore expose only authorized schema information.
🧠 30. Authorized Schema Retrieval¶
Do not expose unauthorized columns to the model unnecessarily.
📊 31. Aggregations¶
SQL is particularly powerful for aggregation.
Examples:
Question:
Possible SQL:
This is a strong SQL RAG use case.
📅 32. Time-Based Queries¶
Natural language dates can be ambiguous.
Example:
The query planner should resolve temporal semantics explicitly.
Example:
The resolved date range should be visible in the generated query or execution trace.
⚠️ 33. Ambiguous Questions¶
Question:
Potential meanings:
A production system should ask for clarification when the semantic layer cannot resolve the ambiguity safely.
🧠 34. Query Planning¶
Before generating SQL, a planner can extract:
Example:
Question:
"Top 10 products by revenue in July"
Metric:
Revenue
Dimension:
Product
Time:
July
Aggregation:
SUM
Sort:
DESC
Limit:
10
🔄 35. Query Planning Pipeline¶
flowchart LR
A["Natural Language"] --> B["Intent"]
B --> C["Entities"]
C --> D["Metrics"]
D --> E["Filters"]
E --> F["Time Range"]
F --> G["Aggregation"]
G --> H["SQL"]🧩 36. SQL Generation with Intermediate Representation¶
Instead of going directly:
use:
Example:
{
"metric": "revenue",
"dimensions": ["product"],
"filters": {
"month": "2026-07"
},
"sort": {
"field": "revenue",
"direction": "desc"
},
"limit": 10
}
This intermediate representation can be validated before SQL generation.
🏗️ 37. Safer SQL RAG Pipeline¶
User Query
↓
Intent
↓
Query Plan
↓
Authorization
↓
Schema Retrieval
↓
SQL Generation
↓
SQL Parsing
↓
SQL Policy Validation
↓
Cost Validation
↓
Database Execution
↓
Result Validation
↓
Answer Generation
🔁 38. SQL Self-Correction¶
SQL generation can fail.
Example:
but the actual table is:
A controlled repair loop can be:
⚠️ 39. SQL Repair Must Be Bounded¶
Do not allow:
Use:
Example:
🧠 40. Query Result Validation¶
A successful SQL execution does not guarantee a correct answer.
Example:
may execute successfully but answer the wrong business question.
Therefore validate:
📊 41. Result Shape Validation¶
Question:
Expected result:
If the query returns:
something may be wrong.
🧠 42. Semantic Result Validation¶
The system can verify:
For example:
The query may be syntactically valid but semantically incorrect.
🧩 43. Null Handling¶
Generated SQL should consider:
For example:
may be appropriate in some business contexts.
However, business semantics should determine whether:
and:
are equivalent.
📉 44. Empty Results¶
An empty result does not always mean:
It could mean:
A production system should distinguish:
from:
🔎 45. SQL + Vector RAG¶
Many enterprise questions require both:
Example:
SQL:
Vector:
🔀 46. Hybrid SQL + Vector Architecture¶
flowchart TD
A["User Query"] --> B["Query Planner"]
B --> C["SQL Retrieval"]
B --> D["Vector Retrieval"]
C --> E["Structured Results"]
D --> F["Document Evidence"]
E --> G["Evidence Fusion"]
F --> G
G --> H["LLM"]
H --> I["Response"]🧠 47. SQL + Knowledge Graph + Vector¶
A mature enterprise system may use all three:
Example:
Question:
"Which customers were affected by the payment
incident, which services were involved, and what
was the root cause?"
Possible routing:
🏢 48. Enterprise Knowledge Retrieval¶
flowchart TD
A["User Query"] --> B["Query Understanding"]
B --> C["Query Router"]
C --> D["SQL Retriever"]
C --> E["Graph Retriever"]
C --> F["Vector Retriever"]
D --> G["Structured Evidence"]
E --> H["Relationship Evidence"]
F --> I["Document Evidence"]
G --> J["Evidence Fusion"]
H --> J
I --> J
J --> K["Context Engineering"]
K --> L["LLM"]
L --> M["Validation"]
M --> N["Citation"]
N --> O["Enterprise Response"]🧩 49. SQL RAG with Semantic Layer¶
The semantic layer can define:
Each business metric maps to:
Example:
Business Metric:
Active Customer
Definition:
Customer with at least one successful transaction
within the active period.
The SQL generator can then use the governed definition.
🧠 50. Governed Metrics¶
Without a semantic layer:
could be interpreted differently by different queries.
With a governed metric:
The definition becomes consistent.
🏢 51. Metric Layer¶
This can dramatically improve consistency in enterprise analytics.
🧠 52. Schema RAG¶
Schema itself can be treated as retrieval data.
Store metadata such as:
Table descriptions
Column descriptions
Relationship descriptions
Business terms
Metric definitions
Example queries
Then:
This is often called a schema-aware or metadata-aware Text-to-SQL architecture.
📚 53. Few-Shot SQL Examples¶
Example pairs can improve SQL generation.
Question:
"How many customers are active?"
SQL:
SELECT COUNT(*)
FROM customers
WHERE status = 'ACTIVE';
Another:
The system can retrieve examples relevant to the current query.
🔎 54. Example Retrieval¶
flowchart LR
A["User Question"] --> B["Example Retriever"]
B --> C["Relevant SQL Examples"]
C --> D["Schema Context"]
D --> E["SQL Generator"]
E --> F["Validated SQL"]This can reduce repeated prompt design and improve consistency when examples are well curated.
🧠 55. SQL RAG Prompt Assembly¶
A production prompt may contain:
SYSTEM RULES
+
AUTHORIZED SCHEMA
+
BUSINESS DEFINITIONS
+
RELEVANT SQL EXAMPLES
+
USER QUESTION
+
QUERY CONSTRAINTS
Example:
SYSTEM:
Generate read-only SQL.
AUTHORIZED TABLES:
customers
orders
products
BUSINESS DEFINITION:
Revenue = successful order total.
EXAMPLE:
...
QUESTION:
What were the top 10 products by revenue last month?
🔐 56. SQL Injection Considerations¶
There are two different concerns:
Traditional SQL Injection¶
User-controlled strings are inserted into SQL unsafely.
LLM-Generated SQL Risk¶
The LLM itself generates an unsafe or unauthorized query.
Both require defense.
Use:
Parameterized Queries
+
Read-Only Credentials
+
SQL Parsing
+
Allowlisting
+
Authorization
+
Database Policies
🛡️ 57. Prompt Injection¶
User input may contain:
The system should not allow the model to bypass:
Security must be enforced outside the model.
🚨 58. Database Resource Exhaustion¶
A generated query may be technically valid but expensive.
Example:
This could create an enormous intermediate result.
Therefore enforce:
where supported by the database platform.
⚡ 59. Query Cost Control¶
A production pipeline can use:
Conceptually:
The exact cost mechanism depends on the database engine.
📏 60. LIMIT and Pagination¶
For exploratory queries:
may protect the system.
However, blindly adding:
can produce incorrect answers for aggregation queries.
For example:
would not mean:
Therefore limits must be applied according to query semantics.
🧠 61. Exactness Matters¶
SQL is particularly useful because databases provide deterministic computation.
For:
the database should perform the computation.
Do not ask the LLM to calculate:
from retrieved text.
Use:
🧩 62. LLM Should Not Become the Database¶
Bad architecture:
Better:
This improves:
📊 63. Result-to-Text Generation¶
Database result:
The LLM can generate:
Payments generated the highest revenue
at ₹12.45 million, followed by Loans at
₹9.8 million and Cards at ₹7.4 million.
The LLM is primarily performing:
rather than database computation.
🧠 64. SQL RAG Response Contract¶
A useful internal response model might contain:
{
"question": "...",
"sql": "...",
"columns": [],
"rows": [],
"execution_time_ms": 42,
"source": "analytics-db",
"schema_version": "v12"
}
The final response generator can consume this structured result.
🔍 65. Citation for SQL RAG¶
SQL answers need a different type of attribution from document RAG.
Instead of:
the system may expose:
For sensitive systems, expose only the level of SQL/database detail appropriate for the user.
🧾 66. Data Provenance¶
SQL RAG should preserve:
This enables auditing.
🧠 67. SQL Query Logging¶
A production trace may include:
Trace ID
User
Tenant
Question
Schema Retrieved
Generated SQL
Validation Result
Execution Time
Rows Returned
Database
Error
Final Response
Sensitive values should be redacted from logs where appropriate.
👀 68. SQL RAG Observability¶
Track:
SQL Generation Latency
Schema Retrieval Latency
SQL Validation Latency
Database Execution Latency
Total Latency
SQL Error Rate
Repair Rate
Empty Result Rate
Query Cost
Rows Returned
📊 69. SQL RAG Dashboard¶
| Metric | Purpose |
|---|---|
| SQL Success Rate | Query reliability |
| SQL Repair Rate | Generation quality |
| Query Latency | Performance |
| DB Latency | Database performance |
| Empty Result Rate | Query quality |
| Unauthorized Query Rate | Security |
| Query Cost | Resource usage |
| Rows Returned | Result size |
| Schema Retrieval Accuracy | Context quality |
| Answer Accuracy | End-to-end quality |
🧪 70. Text-to-SQL Evaluation¶
Evaluation should measure more than whether SQL executes.
Possible metrics:
SQL Syntax Accuracy
SQL Execution Accuracy
Exact Match
Component Match
Result Accuracy
Answer Accuracy
🔎 71. Execution Accuracy¶
Suppose two SQL queries are structurally different:
and:
Both may return the correct answer.
Therefore execution/result correctness can be more useful than literal SQL string comparison.
🧠 72. Query Result Accuracy¶
The ultimate question is:
Evaluation dataset:
The generated SQL can be executed against a controlled evaluation database.
🧪 73. SQL Evaluation Dataset¶
A strong dataset should include:
Simple Queries
Filters
Joins
Aggregations
Nested Queries
Time-Based Queries
Ambiguous Questions
Multi-Table Questions
Business Metrics
Security Cases
🚨 74. Security Evaluation¶
Test cases should include:
Unauthorized Table
Unauthorized Column
Write Operation
Cross-Tenant Query
Sensitive Column Access
Expensive Query
Prompt Injection
Schema Injection
Example:
Expected:
🧠 75. SQL RAG Failure Modes¶
Common failures:
Wrong Table
Wrong Column
Wrong Join
Wrong Filter
Wrong Aggregation
Wrong Date Range
Wrong Metric Definition
Unauthorized Access
Expensive Query
Empty Result
Incorrect Result Interpretation
🚨 76. Wrong Join¶
Suppose:
is joined incorrectly to:
The query may execute successfully but return incorrect results.
This is a semantic failure, not a syntax failure.
📅 77. Date Errors¶
Question:
Potential interpretations:
The system should resolve the time semantics explicitly.
🧠 78. Metric Definition Errors¶
Question:
Possible definitions:
A governed semantic layer can reduce these ambiguities.
🏢 79. Enterprise SQL RAG Architecture¶
USER
│
▼
Query Understanding
│
▼
Query Planner
│
┌──────────────┼──────────────┐
│ │ │
▼ ▼ ▼
Schema Semantic Example
Retrieval Layer Retrieval
│ │ │
└──────────────┼──────────────┘
▼
SQL Generation
│
▼
SQL Validation
│
┌──────┴──────┐
│ │
Valid Invalid
│ │
▼ ▼
Cost Analysis Repair
│ │
▼ │
Authorization │
│ │
└──────┬──────┘
▼
Read-Only DB
│
▼
SQL Result
│
▼
Result Validation
│
▼
Answer Generator
│
▼
Response
🔀 80. Full Multi-Source Enterprise RAG¶
flowchart TD
A["User Query"] --> B["Query Planner"]
B --> C["Vector Search"]
B --> D["Knowledge Graph"]
B --> E["SQL Database"]
C --> F["Document Evidence"]
D --> G["Relationship Evidence"]
E --> H["Structured Evidence"]
F --> I["Evidence Fusion"]
G --> I
H --> I
I --> J["Re-ranking"]
J --> K["Context Engineering"]
K --> L["LLM"]
L --> M["Response Validation"]
M --> N["Citation / Attribution"]
N --> O["Enterprise Response"]🧠 81. When to Use SQL RAG¶
Use SQL RAG when questions require:
Exact Counts
Aggregations
Filtering
Sorting
Joins
Transactions
Time-Series Analysis
Structured Business Metrics
Examples:
How many customers?
What is total revenue?
Which product sold the most?
Which customers placed more than 10 orders?
What were failed payments yesterday?
🚫 82. When Not to Use SQL RAG¶
Do not force SQL when the information is primarily in:
For:
vector retrieval is usually more appropriate.
🔀 83. Decision Framework¶
Question
│
▼
What type of knowledge?
│
├── Unstructured → Vector
│
├── Structured → SQL
│
├── Relationship-heavy → Graph
│
└── Mixed → Hybrid
This simple routing model becomes increasingly useful as enterprise RAG systems grow.
🧩 84. SQL RAG + Graph RAG¶
Some questions require both.
Example:
Possible flow:
Then combine the results.
🏗️ 85. SQL + Graph Architecture¶
flowchart LR
A["Query"] --> B["Planner"]
B --> C["SQL"]
B --> D["Graph"]
C --> E["Customers / Products"]
D --> F["Dependencies / Hosting"]
E --> G["Fusion"]
F --> G
G --> H["LLM"]🤖 86. SQL RAG + Agentic RAG¶
An agent can use SQL as a tool:
Example:
Agent
↓
"What is the current transaction volume?"
↓
SQL Tool
↓
Result
↓
"Why did it change?"
↓
Vector Search
↓
Incident Documentation
This allows iterative retrieval.
🧠 87. SQL Tool Interface¶
A controlled tool might expose:
Notice that the interface can accept:
instead of arbitrary SQL from the agent.
This provides another policy boundary.
🛡️ 88. Query Plan as a Security Boundary¶
Instead of:
prefer:
This can reduce the attack surface.
🧠 89. Query Plan Example¶
{
"table": "transactions",
"metrics": [
{
"name": "count"
}
],
"filters": [
{
"field": "status",
"operator": "=",
"value": "FAILED"
}
],
"time_range": {
"from": "2026-08-10",
"to": "2026-08-11"
}
}
The SQL generator can convert this controlled representation into SQL.
⚡ 90. Performance Optimization¶
Key optimization areas:
Schema Retrieval
SQL Generation
SQL Validation
Database Execution
Result Serialization
LLM Generation
Database-side optimization remains especially important.
🗄️ 91. Database Optimization¶
Generated SQL should leverage:
The RAG layer should not attempt to replace database engineering.
📉 92. Minimize Data Returned¶
Avoid:
when unnecessary.
Prefer:
This reduces:
🧠 93. Push Computation to Database¶
Prefer:
over:
The database should perform structured computation.
🔄 94. Query Result Compression¶
For large results:
Do not pass unnecessary raw data into the model.
🧩 95. Result Summarization¶
Example database output:
Country | Transactions | Revenue
--------|--------------|---------
India | 120000 | 4.2M
Germany | 90000 | 3.8M
UK | 70000 | 2.9M
The LLM can summarize:
🧪 96. Practical Exercise¶
Create a sample database:
Example:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(200),
country VARCHAR(100)
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(200),
category VARCHAR(100)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
product_id INTEGER,
order_date DATE,
amount DECIMAL(18,2)
);
🔎 97. Practice Questions¶
Implement:
1. Count customers.
2. Count customers by country.
3. Calculate total revenue.
4. Calculate revenue by product.
5. Find top 10 customers by spending.
6. Find the most popular product.
7. Find customers with more than 10 orders.
8. Find revenue for the previous month.
9. Find products with no orders.
10. Find average order value.
🧪 98. Add Natural Language¶
Convert:
into:
Then:
into:
SELECT
country,
COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC
LIMIT 1;
🧠 99. Add Schema Retrieval¶
For a larger schema:
Experiment with:
Observe how schema retrieval affects SQL generation.
🧪 100. Add Security¶
Test:
Expected:
Test:
Expected:
Test:
when the user belongs to Tenant A.
Expected:
📊 101. Evaluate the System¶
Measure:
Compare:
🚨 102. Common Mistakes¶
Mistake 1 — Giving the Entire Database Schema to the LLM¶
Large schemas create:
Prefer schema retrieval.
Mistake 2 — Trusting Generated SQL¶
Generated SQL must be validated.
Mistake 3 — Allowing Write Access¶
Use read-only credentials.
Mistake 4 — Ignoring Business Definitions¶
Technical column names are not always sufficient.
Mistake 5 — Asking the LLM to Perform Large Calculations¶
Use the database.
Mistake 6 — Ignoring Query Cost¶
A valid SQL query can still be operationally dangerous.
Mistake 7 — Ignoring Multi-Tenancy¶
Tenant isolation must be enforced independently of the model.
Mistake 8 — Treating Empty Results as Truth¶
Investigate whether the query itself is wrong.
📋 103. Production Checklist¶
☐ Identify SQL RAG use cases
☐ Identify structured data sources
☐ Identify database owners
☐ Document database schemas
☐ Document relationships
☐ Document business definitions
☐ Build schema metadata
☐ Build schema retrieval
☐ Build semantic layer
☐ Define governed metrics
☐ Curate SQL examples
☐ Implement query planning
☐ Implement SQL generation
☐ Implement SQL parsing
☐ Implement SQL validation
☐ Implement allowlists
☐ Implement query cost controls
☐ Use read-only database credentials
☐ Implement authentication
☐ Implement authorization
☐ Implement tenant isolation
☐ Implement column-level security
☐ Protect sensitive data
☐ Implement bounded retries
☐ Implement SQL repair
☐ Implement result validation
☐ Handle empty results
☐ Handle ambiguous questions
☐ Optimize database queries
☐ Apply appropriate indexes
☐ Limit result size
☐ Push computation to database
☐ Avoid SELECT * where unnecessary
☐ Integrate Vector RAG
☐ Integrate Knowledge Graph
☐ Implement hybrid routing
☐ Implement evidence fusion
☐ Preserve query provenance
☐ Log database source
☐ Log schema version
☐ Log query execution metadata
☐ Measure SQL accuracy
☐ Measure execution accuracy
☐ Measure result accuracy
☐ Measure answer accuracy
☐ Measure security violations
☐ Monitor latency
☐ Monitor query cost
☐ Monitor error rate
☐ Monitor repair rate
☐ Monitor empty-result rate
☐ Build regression dataset
☐ Test ambiguous queries
☐ Test security scenarios
☐ Test tenant isolation
☐ Load test database access
📚 104. Key Takeaways¶
- SQL RAG connects natural-language questions to structured enterprise databases.
- Text-to-SQL is a core component of SQL RAG.
- Databases are better than LLMs at exact structured computation.
- SQL is particularly useful for filtering, joins, aggregation, sorting, and counting.
- Schema understanding is critical for reliable SQL generation.
- Large enterprise schemas should be retrieved selectively rather than blindly passed to the LLM.
- Business metadata is often as important as technical schema metadata.
- A semantic layer can map business concepts to physical database structures.
- Governed metric definitions can reduce inconsistent interpretations of business terms.
- Query planning can provide an intermediate representation between natural language and SQL.
- SQL should be parsed and validated before execution.
- Read-only credentials should be used for RAG database access.
- Database permissions should provide a security boundary independent of the LLM.
- Allowlisting authorized tables and columns is safer than relying only on prompt instructions.
- Tenant isolation must be enforced outside the model.
- Sensitive columns require additional authorization controls.
- SQL injection and LLM-generated unsafe SQL are related but distinct security concerns.
- Query cost controls are necessary because syntactically valid queries can still be operationally expensive.
- Result validation is necessary because successful SQL execution does not guarantee semantic correctness.
- Empty results should be investigated rather than automatically treated as authoritative.
- SQL repair loops should be bounded.
- Database computation should remain inside the database whenever possible.
- SQL RAG and Vector RAG complement each other.
- SQL can provide exact structured facts while vector retrieval provides unstructured evidence.
- Knowledge Graphs can provide relationship-based retrieval.
- A mature enterprise RAG system may combine SQL, Graph, Vector, and other retrieval mechanisms.
- Agents can use SQL through controlled tools rather than arbitrary database access.
- Production SQL RAG requires evaluation, observability, security, governance, and cost controls.
🧠 Final Mental Model¶
USER QUESTION
│
▼
QUERY UNDERSTANDING
│
▼
QUERY PLANNER
│
┌─────────────┼─────────────┐
▼ ▼ ▼
Intent Metrics Filters
│ │ │
└─────────────┼─────────────┘
▼
AUTHORIZATION
│
▼
SCHEMA RETRIEVAL
│
┌────────────────┼────────────────┐
▼ ▼ ▼
Tables Columns Relationships
│ │ │
└────────────────┼────────────────┘
▼
SEMANTIC LAYER
│
▼
SQL GENERATION
│
▼
SQL PARSING
│
▼
POLICY VALIDATION
│
┌─────┴─────┐
▼ ▼
Valid Invalid
│ │
▼ ▼
COST CHECK REPAIR
│ │
▼ │
READ-ONLY DB ◄────┘
│
▼
SQL EXECUTION
│
▼
RESULT VALIDATION
│
▼
STRUCTURED DATA
│
┌───────────┼───────────┐
▼ ▼ ▼
SQL Vector Graph
Evidence Evidence Evidence
│ │ │
└───────────┼───────────┘
▼
EVIDENCE FUSION
│
▼
CONTEXT ENGINEERING
│
▼
LLM
│
┌───────┴───────┐
▼ ▼
Validation Citation
│ │
└───────┬───────┘
▼
ENTERPRISE RESPONSE
The core principle is:
SQL RAG does not turn the LLM into a database. It gives the LLM a controlled mechanism for asking the database precise questions, while the database remains responsible for authoritative structured computation.
The strongest enterprise architecture is therefore:
Natural Language
↓
Query Planning
↓
Schema + Semantic Retrieval
↓
Controlled SQL Generation
↓
Validation + Authorization
↓
Database
↓
Exact Structured Result
↓
Evidence Fusion
↓
LLM
↓
Validated Enterprise Response
And when the question spans multiple knowledge types:
Enterprise Question
│
┌────────────┼────────────┐
▼ ▼ ▼
SQL Graph Vector
│ │ │
▼ ▼ ▼
Facts Relationships Documents
│ │ │
└────────────┼────────────┘
▼
Evidence Fusion
│
▼
LLM
This makes SQL RAG an important component of a broader enterprise knowledge retrieval architecture, rather than a replacement for Vector RAG or Graph RAG.
🧭 Chapter Navigation¶
Part V — Advanced Retrieval-Augmented Generation¶
Previous:
03. Knowledge Graphs for RAG
Next:
05. Multimodal RAG
Section:
05 — Advanced RAG Architecture
Advanced RAG Architecture Path¶
01 Advanced RAG Architecture
↓
02 Graph RAG
↓
03 Knowledge Graphs for RAG
↓
04 SQL RAG
↓
05 Multimodal RAG
↓
06 Agentic RAG
Enterprise AI Engineering Handbook
Building Production-Grade Enterprise AI Systems — One Chapter at a Time.