How I made AI Search “Whatever data you want to see…” for Find Me in Chicago.
If you try to run a “zero-shot” Text-to-SQL against a raw enterprise database with hundreds of tables and complex foreign key relations, off-the-shelf models fail spectacularly. Feeding hundreds of table schemas and relations into a prompt pollutes the attention window and causes hallucinated join chains.
Asking an enterprise database “Show me the most popular products in the Midwest last quarter” requires:
- Schema and join path discovery: if you are Traversing multiple tables a single wrong join could create a cartesian product potentially returning millions of garbage rows. Assuming you are even looking at the correct table.
- Disambiguation: The primary date field could be order_date, ship_date, or created_at and you still have to figure out whatever field corresponds to the “Midwest” region.
The search space is thus combinatorial, not linear, and not cheap.
Maintaining a “Golden Repository” vector database containing many human-audited questions to validated SQL pairs can help. But, wow, that is a lot of work to maintain and also an expensive compute. In this design the top most similar verified queries are injected into the prompt as context. The LLM copies a structural skeleton already proved correct. But good luck keeping that up to date, even if you use AI to help you the prompt window will grow and grow.
I also cannot get my head around why there is so much guidance to embed actual schemas into a vector database. Schemas are by their very nature deterministic. Inferring schemas from vectors seems a recipe to spend more to get less. Why not simplify the ask at the source?
Luckily Find Me in Chicago is constrained to single flat datasets hosted by Socrata for free access from the Chicago Data Portal. So the LLM only needs to answer: “Which columns and what filters?”
For a table with 40 columns, there is no join path. This reduces a highly combinatorial problem to a much simpler search space, allowing a single-shot prompt to deliver high accuracy at minimal token cost without heavy agentic frameworks, vector databases, or multi-turn loops.
The most cost-effective strategy is maybe just flatten your data, perhaps with a materialized view for efficiency. It is a starting point at least.