Survey Text-to-SQL

What it is

Answering questions across thousands of community survey responses meant writing SQL by hand or waiting on an analyst. This pipeline takes a plain-English question, generates a read-only SQL query against a normalized database, runs it behind privacy guardrails, and narrates the result in two or three sentences. The answers are exact counts and percentages, not approximations, because that is what the decisions behind them actually run on.

It runs on several thousand respondents across multiple communities, and scores perfectly against a golden set of questions with human-authored SQL.

The problem

The raw material is the hard part. The surveys arrived as Excel files, one per community, and no two were the same:

• Headers mixed English, local scripts, transliterations, and typos.
• The same underlying question was phrased differently by each team, with 19 to 52 columns per file.
• A single cell often held a compound answer ("Yes, I have X; I am aware of Y").
• Some answers existed only in the native script, with no English at all.

Decisions get made on these numbers, so "roughly" is not good enough. Every count has to be exact, and no individual respondent can be identifiable in an answer.

Why SQL, not vectors

A retrieval-augmented chatbot would embed the rows and answer from the nearest matches. That is the wrong tool here. This is structured data, and the questions are aggregates: counts, percentages, breakdowns by group. The query itself is the retrieval, and it computes over every row exactly rather than sampling the most similar few. So there are no embeddings, no chunking, and no vector store anywhere in the system.

Architecture

Ingestion happens once; everything else happens per question.

Ingest

• Column mapping. Each incoming header is matched to a canonical question: an exact match first, then fuzzy matching with RapidFuzz (token-set ratio at or above 88) for near-duplicates, then an optional LLM fallback, and finally an overflow slot for anything genuinely new.
• Answer normalization. Compound cells are split, native-script-only answers are kept raw and excluded from the normalized columns, and free-form values are standardized (a spread of "yes" spellings collapses to one).
• Hybrid schema. Common questions live in a wide respondents table; rare or community-specific ones spill into an entity-attribute-value (EAV) table. New question shapes never require a schema migration.
• Idempotent loads. Rows are keyed by a content hash, so re-running an import skips duplicates.

Every mapping decision is written to an auditable log, so a reviewer can trace exactly how a raw header became a database column.

Query

• A schema prompt is built from the live database: table and column names, the distinct categorical values, and the EAV keys, so the model knows what it can query.
• The model returns a single read-only SELECT. Nothing else is allowed to reach the database.
• If the SQL is invalid, one automatic repair attempt runs before the system fails gracefully.
• A short narration turns the result set into two or three plain sentences, with a program recommendation where it fits.

Privacy guardrails

This is the part I care most about, because the data is real people. Three layers stand between a question and an answer:

1. Read-only validation. The generated SQL must be a single SELECT; writes, joins to system tables, and multiple statements are rejected outright.
2. Aggregate-only. Queries that would return identifying columns or row-level records are refused. The system answers with counts and percentages, not names.
3. k-anonymity. Small groups are suppressed (k = 5), so an answer can never single out a handful of respondents.

Exact and private are not in tension here; SQL gives you both.

Stack

What I'd do differently

The interesting engineering was in the boring-sounding parts: fuzzy header matching, the hybrid schema that never needs a migration, and the guardrails. The LLM is a small piece, well fenced in. If I extended it, the next steps are richer eval coverage as new communities come in, and a tighter feedback loop so a corrected answer teaches the mapping registry. The full write-up on the decisions and tradeoffs is available on request.