SQL Agents Need Business Context, Not Just Schema

A SQL agent sees a table called orders with columns promised_date, delivery_date, and status. You ask it for the on-time delivery rate. It generates a query, the query runs, and you get a number. The number is wrong because the agent guessed the formula.

The real definition lives in a Confluence page, a Looker dashboard, or someone’s head. Schema alone does not encode business semantics. This is the core problem every data team hits when deploying text-to-SQL agents in production.

The LiveSQLBench Example

LiveSQLBench includes a disaster relief database with a distributionhubs table. The benchmark asks:

Analyze all distribution hubs based on their Resource Utilization Ratio. Show hub registry ID, calculated RUR value, and Resource Utilization Classification. Sort by RUR descending.

The schema shows three columns: hubregistry, hubutilpct, storecapm3, storeavailm3. No column is named “Resource Utilization Ratio.” The formula and thresholds live in a separate business rules file:

RUR = (hubutilpct / 100) × (storecapm3 / (storeavailm3 + 1))

High Utilization:     RUR > 5
Moderate Utilization: 2 ≤ RUR ≤ 5
Low Utilization:      RUR < 2

An agent that sees only the schema will invent a formula. The query executes without error. The result looks plausible. The answer is wrong, and nothing in the output flags it as a guess.

Why Schema Is Not Enough

Database schemas encode structure, not meaning. They tell you:

• Column names and types
• Primary and foreign keys
• Nullability and constraints

They do not tell you:

• Which foreign key path is the canonical join between two entities
• How to calculate “revenue” (gross vs. net, recognized vs. booked)
• Which date field represents the official timestamp for a business event
• What “active customer” means this quarter

When multiple foreign key paths exist between the same tables, the schema does not say which one represents the business relationship you care about. When three date columns exist on an order, the schema does not say which one defines “order date” for reporting.

The Operational Knowledge Framework Approach

The OKF (Operational Knowledge Framework) series proposes a knowledge graph that sits alongside the schema. The graph encodes:

1. Metric definitions: Formulas, aggregation rules, filters
2. Entity relationships: Canonical join paths, relationship cardinality
3. Business glossary: Term definitions, synonyms, ownership
4. Calculation context: Time grain, currency, regional rules

The agent queries this graph before generating SQL. The workflow looks like this:

1. User asks a question in natural language
2. Agent extracts entities and metrics from the question
3. Agent queries the knowledge graph for relevant definitions
4. Agent generates SQL using both schema and business rules
5. Agent executes query and returns result

Knowledge Graph Structure

The knowledge graph uses a triple store or property graph format. Each metric becomes a node with properties:

metric:
  id: "on_time_delivery_rate"
  label: "On-Time Delivery Rate"
  formula: "COUNT(CASE WHEN delivery_date <= promised_date THEN 1 END) / COUNT(*)"
  filters:
    - "status IN ('delivered', 'completed')"
    - "promised_date IS NOT NULL"
  tables: ["orders"]
  owner: "logistics_team"
  updated: "2026-09-15"

Join paths become edges with weights or priority flags:

join_path:
  from: "orders"
  to: "customers"
  via: "orders.customer_id = customers.id"
  canonical: true
  cardinality: "many_to_one"
  description: "Primary customer relationship for order attribution"

Agent Orchestration Flow

The agent needs a two-phase retrieval strategy:

Phase 1: Semantic Lookup

• Embed the user question
• Query the knowledge graph for similar metric definitions
• Retrieve top-k relevant business rules
• Extract table names and join paths

Phase 2: SQL Generation

• Combine schema metadata with retrieved business rules
• Generate SQL with explicit metric formulas
• Validate that all referenced columns exist in schema
• Add comments to SQL explaining which business rules were applied

The agent prompt includes both schema DDL and retrieved knowledge graph entries. This gives the LLM enough context to generate correct SQL without inventing formulas.

Implementation Architecture

A production SQL agent with business context needs these components:

Component	Purpose	Technology Options
Knowledge Graph Store	Store metric definitions and join rules	Neo4j, TigerGraph, RDF triple store
Embedding Service	Semantic search over business terms	OpenAI embeddings, Sentence Transformers
Schema Metadata Store	Cache table DDL and column stats	SQLAlchemy reflection, dbt metadata
SQL Generator	Combine context and generate queries	LangChain SQL agent, custom LLM chain
Validation Layer	Check generated SQL before execution	sqlparse, query plan analysis
Audit Log	Track which rules were used per query	Postgres JSONB, Elasticsearch

The knowledge graph does not replace the schema. It augments it. The agent still needs column types and foreign keys from the schema to generate syntactically valid SQL.

Versioning Business Logic

Business rules change. “Active customer” might mean “purchased in last 90 days” this quarter and “purchased in last 60 days” next quarter. The knowledge graph needs versioning:

class MetricVersion:
    metric_id: str
    version: int
    effective_date: datetime
    formula: str
    deprecated: bool
    superseded_by: Optional[str]

When the agent retrieves a metric definition, it checks the effective date against the query date. For historical queries, it uses the version that was active at that time. For current queries, it uses the latest non-deprecated version.

This prevents the agent from applying today’s business rules to last year’s data, which would produce incorrect trend analysis.

Code Example: Knowledge Graph Query

Here is how the agent queries the knowledge graph before generating SQL:

from neo4j import GraphDatabase

class BusinessKnowledgeRetriever:
    def __init__(self, uri, user, password):
        self.driver = GraphDatabase.driver(uri, auth=(user, password))
    
    def get_metric_definition(self, metric_name, effective_date=None):
        query = """
        MATCH (m:Metric {name: $metric_name})
        WHERE m.effective_date <= $effective_date
          AND (m.deprecated = false OR m.deprecated IS NULL)
        RETURN m.formula, m.filters, m.tables, m.joins
        ORDER BY m.effective_date DESC
        LIMIT 1
        """
        with self.driver.session() as session:
            result = session.run(
                query,
                metric_name=metric_name,
                effective_date=effective_date or datetime.now()
            )
            record = result.single()
            if record:
                return {
                    "formula": record["m.formula"],
                    "filters": record["m.filters"],
                    "tables": record["m.tables"],
                    "joins": record["m.joins"]
                }
            return None
    
    def get_canonical_join(self, table_a, table_b):
        query = """
        MATCH (a:Table {name: $table_a})-[j:JOINS]->(b:Table {name: $table_b})
        WHERE j.canonical = true
        RETURN j.condition, j.cardinality
        """
        with self.driver.session() as session:
            result = session.run(query, table_a=table_a, table_b=table_b)
            record = result.single()
            if record:
                return {
                    "condition": record["j.condition"],
                    "cardinality": record["j.cardinality"]
                }
            return None

The agent calls get_metric_definition("on_time_delivery_rate") and receives the formula, filters, and required tables. It then calls get_canonical_join("orders", "customers") to get the correct join condition.

Failure Modes

This architecture introduces new failure points:

Stale Knowledge Graph

If the business logic changes but the knowledge graph is not updated, the agent generates queries using outdated rules. Mitigation: Treat the knowledge graph as code. Store definitions in YAML files, version them in Git, and deploy updates through CI/CD.

Ambiguous Metric Names

If the user asks for “revenue” and the knowledge graph has “gross_revenue,” “net_revenue,” and “recognized_revenue,” the agent must disambiguate. Mitigation: Use synonym mappings and prompt the user when multiple definitions match.

Missing Definitions

If the user asks about a metric that is not in the knowledge graph, the agent falls back to schema-only mode and risks generating incorrect SQL. Mitigation: Log all missing lookups and prioritize adding them to the graph. Return a warning to the user when a metric definition is not found.

Graph Query Latency

If the knowledge graph query takes 2 seconds and the SQL query takes 500ms, the user perceives the agent as slow. Mitigation: Cache frequently accessed metric definitions in Redis. Preload common definitions at agent startup.

Observability Requirements

You need visibility into which business rules the agent used for each query:

• Log the retrieved metric definitions alongside the generated SQL
• Tag each query with the knowledge graph version that was active
• Track cache hit rate for metric lookups
• Alert when the agent generates SQL without finding a matching business rule

This audit trail lets you debug incorrect results and understand which business logic is actually being used in production.

When to Use This Architecture

Use a knowledge graph for SQL agents when:

• Your data team maintains a semantic layer or metrics catalog
• Business users ask questions using domain terms, not table names
• Multiple teams define the same metric differently
• You need to enforce consistent metric definitions across tools
• Historical queries must use the business rules that were active at that time

Skip the knowledge graph when:

• Your schema is simple and self-explanatory
• Users are SQL-literate and prefer writing queries manually
• Business logic rarely changes
• You are prototyping and do not need production-grade accuracy yet

Technical Verdict

Schema metadata is necessary but not sufficient for SQL agents. Business logic (metric formulas, canonical joins, entity definitions) must be encoded in a queryable format that the agent can retrieve before generating SQL.

A knowledge graph provides this semantic layer. It separates business rules from schema structure, allows versioning, and gives the agent enough context to generate correct queries from ambiguous questions.

The trade-off is operational complexity. You now maintain two sources of truth: the database schema and the knowledge graph. They must stay synchronized. The knowledge graph must be versioned, deployed, and monitored like any other production system.

For data teams already maintaining a metrics catalog or semantic layer, this architecture formalizes what they already do manually. For teams without one, building the knowledge graph is the hard part. The agent orchestration is straightforward once the business knowledge is encoded.

---

Source Links

• Primary Article: Your Schema Isn’t Enough (OKF Series, Part 4)