RAG and search engineeringAdvancedLesson 2157 min read

Knowledge graphs and structured retrieval

Some questions are about counting, filtering and connecting facts, not finding a paragraph. For those, SQL or a graph can beat vector search.

✦ Ask the tutor🧪 Lab · 40 min📝 Lesson test
Lesson in motion

In 60 seconds

Knowledge graphs and structured retrieval

Some questions are about counting, filtering and connecting facts, not finding a paragraph. For those, SQL or a graph can beat vector search.

1/5
In simple words
If you want to know how many runs Virat Kohli scored in 2016, you do not read every match report. You look at a scorecard table and add up the numbers. Some questions need a table, not a story.
Vector and keyword search return passages. That is right for "what is the leave policy?". It is wrong for "how many orders from Pune were delayed last month?" or "which suppliers of Plant A also supply Plant B?". The answer to these is not written in any single paragraph. It must be computed from structured data.

Which tool for which question

Question typeExampleBest tool
Explain or describe"How do I apply for a refund?"Text retrieval (hybrid search)
Count, sum, average, top-N"Total sales in Kerala in March"SQL over a database
Exact filters on fields"Open tickets for customer 4412 older than 7 days"SQL or search filters
Connections across several steps"Which projects share a vendor with the delayed Chennai project?"A knowledge graph, or SQL joins
Mixed"Why were Pune orders delayed last week?"SQL for the numbers, text search for the explanations

Text-to-SQL, with care

Text-to-SQL means a model turns a user's question into a SQL query, your code runs it, and the model explains the result. It can be very useful, and it can go wrong in dangerous ways. SQL written by a model is untrusted input, just like SQL typed by a stranger.
  1. 1

    Give a small, clear schema

    Only the tables and columns needed, with plain descriptions and example values.
  2. 2

    Generate the query

    Ask for a single SELECT statement, with a row limit.
  3. 3

    Run it with a read-only connection

    The database user can only read, and only the allowed tables or views.
  4. 4

    Enforce limits

    Timeouts, row limits and the user's own data scope, all in code or in the database.
  5. 5

    Show the query and result

    Let people see the SQL and the numbers, not only the model's summary.
Python: running model-written SQL in a read-only session (sqlite3)python
import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER, city TEXT, status TEXT, amount REAL)")
conn.executemany("INSERT INTO orders VALUES (?, ?, ?, ?)", [
    (1, "Pune", "delayed", 450.0), (2, "Pune", "delivered", 1200.0),
    (3, "Kochi", "delayed", 300.0), (4, "Pune", "delayed", 800.0),
])
conn.execute("PRAGMA query_only = ON")  # from now on, writes are refused

def run_model_sql(sql: str, max_rows: int = 100):
    if ";" in sql.strip().rstrip(";"):
        raise ValueError("Only one statement is allowed")
    cur = conn.execute(sql)
    return cur.fetchmany(max_rows)

print(run_model_sql("SELECT city, COUNT(*) FROM orders WHERE status = 'delayed' GROUP BY city"))
try:
    run_model_sql("DELETE FROM orders")
except sqlite3.OperationalError as err:
    print("Blocked:", err)
The real protection here is the database refusing writes, not the text check. In a production database, use a separate user that has read-only rights on a few safe views, with row-level limits for the logged-in user's data. Checks on the SQL text alone are easy to get around.
Watch out
Text-to-SQL can return a wrong but valid query: the wrong join, a missed filter, a date range off by one day. The number looks precise and official. Show the SQL, test with a set of questions whose right answers you know, and be extra careful with money and personal data.

Knowledge graphs

A knowledge graph stores facts as entities (people, companies, products, places) and relations between them, such as "Vendor X supplies Plant A" or "Circular 14 replaces Circular 9". It is good at questions that hop across several links, and at keeping one clean record per entity even when documents spell names differently.
  • Build it from structured data when you can: your databases already hold many clean relations.
  • Extracting a graph from text with a model is possible, but the extraction makes mistakes. Keep a link from every fact back to its source passage, and review important facts.
  • Combine with text search: use the graph to find the right entities, then retrieve the passages about them for the explanation and the citations.

Putting it together: a router

Many systems add a first step that decides where each question goes: text search, SQL, the graph, or several. The router can be simple rules ("contains a number and a date range: try SQL") or a model call that picks from a fixed list. Keep the list short and log every choice, so you can see and fix wrong routes.
Risky structured retrieval
  • Model-written SQL runs as an admin user
  • Every table in the company is in the prompt
  • Only the model's summary is shown
  • Graph facts extracted by a model with no link to sources
Careful structured retrieval
  • Read-only user on a few safe views, with timeouts and row limits
  • A small described schema for the task
  • The SQL and the raw result are visible
  • Every graph fact linked to where it came from
Try this. Write five questions people ask at your work or college. Mark each one: passage, number, filter, or connection. How many would vector search alone get wrong?

Watch and read more

Lab

A small router that sends questions to text search or safe SQL, tested on your own questions.

~40 min

The problem

Use Python 3 and the standard library's sqlite3. No model is needed; you will write the SQL by hand where a model would.

  1. Create a table of 15–20 made-up orders with city, status, date and amount. Turn on PRAGMA query_only = ON.
  2. Write 8 questions: 4 that need numbers or filters, 4 that need explanations (for example "how do I return an item?").
  3. Write a rule-based route(question) that returns "sql" or "text". Use simple signals such as "how many", "total", "average", a city name, or a date.
  4. For each "sql" question, write the SQL by hand, run it, and check the result is right.
  5. Show that a DELETE or UPDATE fails.

You are done when

Hard questions

Try to answer before you reveal. If you can answer these, you understood the lesson.

Q1Why is checking the SQL text for words like DELETE or DROP not enough protection?Reveal

Text checks are easy to get around and easy to get wrong: odd spacing, comments, different statement types, database-specific functions with side effects, or a harmless-looking SELECT that reads data the user should not see. Real protection must come from the database itself: a read-only user, access only to safe views, row-level limits tied to the logged-in user, and timeouts. Text checks can be an extra layer, never the main one.

Q2A question is "Why did delays in Pune go up last week?" Describe how you would answer it with both SQL and text retrieval.Reveal

First, SQL computes the facts: the number of delayed Pune orders last week compared with the week before, maybe split by courier or area. Then text search retrieves passages that might explain it, such as incident notes, courier updates or weather notices for those dates. The model writes an answer that states the numbers (showing the query) and gives possible reasons with citations, marking them as possible causes, not proven ones.

Please sign in to continue.

Questions people ask

Do I need a graph database for a knowledge graph?

Not necessarily. A small graph can live in ordinary tables of entities and relations in the database you already use. Choose a dedicated graph database when you have many multi-step queries on a large graph.

Can the model do the maths itself from retrieved text?

For one or two numbers, maybe. For totals over many rows it often misses or miscounts, because the rows may not all be retrieved and models are not reliable calculators. Let the database compute, and let the model explain.

Is text-to-SQL safe for customers to use directly?

Only with strict limits: read-only access, only their own data enforced by the database, timeouts and row limits. Many teams start with internal staff use and a fixed set of safe views.

How do I test text-to-SQL?

Write questions with known correct results, run the generated SQL, and compare the results, not the SQL text, because different queries can be equally correct.

Lesson test

5 questions. Get 3 right (60%) to pass and complete this lesson.

Sign in to take the test and save your progress