Schema as Context

For the SQL Agent to generate correct queries, it needs to know the database schema. We provide this as a detailed text prompt that describes all tables and columns.

text2sql_agent.py
python
SCHEMA_INFO = """
Database Schema for E-commerce System:

1. customers
   - customer_id (TEXT): Unique customer identifier
   - customer_unique_id (TEXT): Unique customer identifier across datasets
   - customer_zip_code_prefix (INTEGER): Customer zip code
   - customer_city (TEXT): Customer city
   - customer_state (TEXT): Customer state

2. orders
   - order_id (TEXT): Unique order identifier
   - customer_id (TEXT): Foreign key to customers
   - order_status (TEXT): Order status (delivered, shipped, etc.)
   - order_purchase_timestamp (TEXT): When the order was placed
   - order_approved_at (TEXT): When payment was approved
   - order_delivered_carrier_date (TEXT): When order was handed to carrier
   - order_delivered_customer_date (TEXT): When customer received the order
   - order_estimated_delivery_date (TEXT): Estimated delivery date

3. order_items
   - order_id (TEXT): Foreign key to orders
   - order_item_id (INTEGER): Item sequence number within order
   - product_id (TEXT): Foreign key to products
   - seller_id (TEXT): Foreign key to sellers
   - shipping_limit_date (TEXT): Shipping deadline
   - price (REAL): Item price
   - freight_value (REAL): Shipping cost
...
"""

This shows the key tables. The full SCHEMA_INFO variable includes all 9 tables from the Brazilian E-commerce dataset (customers, orders, order_items, products, sellers, payments, reviews, geolocation, and categories).

Why detailed schema matters

Notice how we include data types, descriptions, and relationships. The more context we give the LLM, the better SQL it generates. This is why RAG (Retrieval-Augmented Generation) is so powerful!

Exactly right! The R in RAG stands for Retrieval. In traditional RAG you retrieve documents. Here we retrieve schema information and inject it into the prompt. The "Agentic" part comes from having multiple specialized agents that coordinate to answer the question, not just a single LLM call.

Schema Context flow

How schema information flows into the SQL Agent prompt.

AI prompt: Try it with AI

Loading practice…

Quiz: Quiz

Loading practice…

The schema context is the secret sauce of our system. Next, we will initialize the actual SQLite database from the CSV data files.