Inside the NL2SQL Pipeline: A Full Walkthrough

The earlier post explained why this system matches questions against validated queries instead of always generating fresh SQL. This one is the map: every stage a question actually moves through, in order, and what each one is responsible for. If you want to understand the codebase or reason about where a given behavior lives, start here.

The system has two halves. The first is a pre-pipeline analysis phase that runs inside the Streamlit app before any graph executes. The second is a LangGraph agent pipeline with two routes through it — a fast path for matched queries and a full path for generation. Execution and interpretation happen after the graph, triggered by the user.

Before anything: the input guard

Not every string typed into a question box is a question worth answering. Before the app spends a single LLM call, two cheap checks run on the raw text:

• Profanity check — inappropriate language is rejected with a request to rephrase.
• Database-relevance check — the question has to plausibly be about the TPC-H domain (orders, customers, suppliers, parts, and so on). A question about the weather gets turned away here, not three agents deep into the pipeline.

This is a guardrail, not intelligence. Its only job is to stop obviously-bad input from consuming resources downstream.

Phase one: analysis before generation

When the user clicks Analyze, three independent operations fire in parallel through a thread pool. They are independent by design — none depends on another's output — so running them concurrently cuts the perceived latency to the slowest of the three rather than their sum.

Temporal analysis (LLM). The question goes to an LLM that detects date and time references and proposes concrete dates. "Last quarter" is meaningless without an anchor; the same phrase resolves differently in April than in January. Rather than guess, the system surfaces what it detected and asks the user to confirm a date range, an as-of date, or no date filter at all.

Geography analysis (LLM). In parallel, a second LLM call detects whether the question implies a geographic scope — a region, a nation, a US state, a city, or a ZIP code — and whether that geography is a filter (one specific place) or a group-by (broken down across places). Catching this before generation means the right JOINs and WHERE clauses can be planned up front instead of discovered through a failed retry.

Question matching (Qdrant). At the same time, the user's question is embedded and searched against the store of validated NL→SQL pairs. The top matches come back with similarity scores. This is the heart of the fast path: if a curated query already answers this question, the user can reuse it instead of asking the model to write a new one.

After those three return, two more lightweight steps run:

Intent parsing. The question parser extracts a structured representation — metric, dimensions, temporal references, aggregation type. This structured intent is both an input to the clarifier and the thing the skip logic inspects.

Related BI Topic lookup. The question is matched against curated business-intelligence topics. When a strong topic match is found, its description, business terms, and example queries become available as optional context — the user can toggle whether that context feeds generation, and a dashboard or S3 link is surfaced alongside.

The clarifier: closing ambiguity before generating

A question can be database-relevant, temporally clear, and still ambiguous about what it actually wants. The clarifier agent handles that.

Its first move is a skip decision: if the Qdrant question match is strong enough, clarification is skipped entirely — a high-confidence match already encodes the right interpretation, so there's nothing to clarify. Otherwise the clarifier proposes a small set of multiple-choice questions, each tied to a specific dimension of ambiguity, each with a sensible default.

Clarification runs for up to two rounds. Round one asks its questions; if the agent still isn't confident, round two asks follow-ups while showing the round-one answers as locked-in context. The user can refine, confirm, or simply proceed — unanswered questions fall back to their defaults. The committed answers travel into generation as explicit context.

Phase two: the LangGraph pipeline

Everything above produces a QuestionState — the Pydantic object threaded through every node of the graph. The graph's entry node inspects it and routes to one of two paths.

The fast path (matched SQL)

When the user chose to reuse a matched query, the curated SQL is already correct in shape — it just needs to be adapted to this question's specifics. The entry node does that adaptation directly, without any LLM call:

• Geography substitution — placeholders like {REGION} or {STATE} are replaced with the confirmed value; if the curated SQL has no placeholder, the required JOINs and WHERE condition are injected in the right position relative to any existing WHERE / GROUP BY / ORDER BY.
• Temporal substitution — placeholders like {START_DATE}, {YEAR}, {QUARTER} are filled from the confirmed dates, wrapped in TO_DATE(...).
• Temporal override — dynamic expressions a curated query might carry (for example BETWEEN DATEADD('month', -1, CURRENT_DATE) AND CURRENT_DATE) are rewritten to the user's confirmed literal dates.

The adapted SQL is promoted straight to validation. The retry counter is set to its ceiling so a matched query is never silently regenerated — if it fails validation, the system tells the user to generate fresh instead of quietly substituting AI output for a curated query.

The full path (generation)

When there's no strong match, or the user asked for a fresh query, the full chain runs:

1. Parse — structured intent extracted from the natural-language question (the same parser used in analysis; the node guarantees the state carries it).
2. Retrieve schema — the schema-retrieval agent searches Qdrant for the relevant tables, columns, business terms, and example queries. The retrieval is targeted — not a full schema dump — which keeps the generation prompt focused and shrinks the surface for mistakes.
3. Enrich ontology — a TPC-H ontology agent adds domain context: how the entities relate, which keys join which tables. This step is full-path only; a matched query already encodes those relationships.
4. Process temporal — the confirmed temporal information from phase one is converted into the concrete date expressions the SQL will use. Date handling is one of the most error-prone parts of SQL generation, so it's isolated in its own node.
5. Generate SQL — the generator takes the structured intent, retrieved schema context, temporal expressions, geography info, ontology context, clarification answers, topic context, and the original question, then produces Snowflake SQL using few-shot examples pulled from Qdrant. Even here, any user-confirmed dates are re-applied over the model's output, because the LLM will sometimes read a date straight from the question text instead of from the temporal info.
6. Validate — the validator checks syntax, table and column references, and security constraints.

The retry loop

The edge out of validation is conditional. If validation fails — or, on execution, if the query errors — the graph routes back to generation for another attempt, up to two iterations. After that it stops with an explicit error rather than looping forever. This loop is full-path only; the fast path opts out of it deliberately.

After the graph: execution and interpretation

The graph stops at a validated query. Execution is not automatic — it's a deliberate, user-triggered step, which keeps a human between the generated SQL and the database.

When the user clicks Run SQL, the text is screened one more time at the boundary: only a single SELECT or WITH statement is allowed, and any DELETE / DROP / ALTER / INSERT / UPDATE and similar operations are blocked outright. A read-only system stays read-only no matter what the model emitted or the admin edited.

If the query runs and returns rows, two agents process the result:

• Result validator — a semantic, data-quality pass over the returned data. It flags issues the SQL validator can't see because they only appear in the actual results, surfacing them as warnings or informational notes rather than hard failures.
• Result interpreter — takes the question, the SQL, and the dataset and writes a plain-English summary of what the data shows, so the user gets an answer rather than just a table to decode.

The trust badge

Every result carries a trust label, derived from how the SQL was produced and whether it validated:

• Trusted — a high-confidence match with a curated query that also passed validation. The strongest signal the system offers.
• Semi-trusted — a moderate-confidence match that passed validation. Usable, but review before relying on it.
• Untrusted — AI-generated SQL that passed validation. Correct-looking, but it was reasoned from scratch, so review it.
• Untrusted (failed) — validation failed, regardless of source.

The badge is the point of the whole architecture. A curated query that has already executed and returned correct results cannot hallucinate; a generated query can fail in dozens of quiet ways. By showing the user which kind of answer they're looking at, the system makes the confidence level part of the result instead of hiding it.

Closing the loop: growing the trusted set

An admin who confirms a generated query is good can save it back to the knowledge base, with a duplicate check to keep the store clean. That query is now available to the fast path. Every save is one more question the system never has to generate again — and the more the store covers, the more questions hit the fast path, and the fewer chances the model gets to produce something wrong. The collection isn't a cache of examples; it's accumulated institutional knowledge about how this specific database should be queried, written in SQL that has already been proven to work.