Building a Self-Improving Text-to-SQL Agent with Dynamic Context and Continuous Learning

If you've ever worked on a Text-to-SQL project, you know the common pitfalls. Most agents start from scratch every time, describing tables, columns, and finding join keys-essentially repeating every mistake over and over. This is in stark contrast to how experienced data engineers or analysts operate. They leverage tribal knowledge, past queries, and established usage patterns to get things done efficiently.

In this article, we'll explore how to build a self-improving Text-to-SQL agent using dynamic context and "poor-man's continuous learning." The goal is to make the agent more robust by providing it with the same kind of contextual information that human experts use. Here’s how we can break down the problem:

1. Why Text-to-SQL Fails in Practice

Most Text-to-SQL agents fail because they lack context and tribal knowledge. They start from scratch every time, which leads to several common issues:

• Repetition of Mistakes: The model describes tables, columns, and finds join keys repeatedly.
• Guessing Column Names: It often guesses column names or usage patterns incorrectly.
• Missing Domain Definitions: The model may not understand business-specific definitions (e.g., "active user," "churn," "ARR").
• Ignoring Business Rules: It might miss rules like "status lives in orders.state, not orders.status."
• Re-inventing Queries: The model re-creates queries that already exist in your organization's knowledge base.

The key to improvement is providing the agent with the same tribal knowledge that human engineers have. This allows it to re-use established queries and search for usage patterns at runtime.

2. What is "Dynamic Context"?

Dynamic context means the model has access to relevant information (schemas, joins, past queries, metric definitions, gotchas) at runtime. This can be achieved through techniques like Retrieval-Augmented Generation (RAG) or Agentic RAG, where the model retrieves and uses this context to generate more accurate SQL.

3. What is "Poor Man's Continuous Learning" (and Why It Works)

Continuous learning in this context means the agent learns from its successful runs and updates its knowledge base accordingly. This creates a self-improving feedback loop without requiring complex retraining processes. Here’s how it works:

• Online Path: The agent answers questions by retrieving schema and query patterns from the knowledge base.
• Offline Path: When the agent successfully generates a correct SQL query, it stores this result in its knowledge base for future use.

This approach keeps the online path stable while gradually improving the agent's performance over time.

4. Unified Agent Architecture

The architecture of our self-improving Text-to-SQL agent can be broken down into two main components:

• Text-to-SQL Agent (Online Path):
  • Context Retrieval: The agent retrieves relevant context from the knowledge base.
  • SQL Generation: It generates SQL queries based on the retrieved context.
  • Validation: The generated SQL is validated to ensure it is parseable and schema-correct.
• Continuous Learning (Offline Path):
  • Result Analysis: The agent analyzes the results of its SQL queries.
  • Knowledge Base Update: Successful queries are stored in the knowledge base for future use.

5. Knowledge Base Design

The knowledge base should be structured to store:

• Schemas and Tables: Definitions of tables and their columns.
• Join Patterns: Common join operations.
• Past Queries: Previously successful SQL queries.
• Metric Definitions: Business-specific definitions and rules.
• Gotchas: Common issues and how to handle them.

6. Production Harness (Deployable Anywhere)

To make the agent deployable, consider the following:

• Containerization: Use Docker or Kubernetes to containerize the application.
• API Endpoints: Expose RESTful API endpoints for querying the agent.
• Scalability: Ensure the system can handle multiple requests and scale horizontally.

7. Steps to Run Your Own Text-to-SQL Agent

1. Set Up the Knowledge Base:
   • Populate it with schemas, join patterns, past queries, metric definitions, and gotchas.
2. Develop the Online Path:
   • Implement context retrieval and SQL generation