Introduction
We built an agent to answer business questions from a Snowflake database. The obvious approach was prompt engineering: give the LLM the schema of the analytical DB and some examples, and let it generate SQL. The first version worked surprisingly well on simple queries. Then a product manager asked: "What was the DAU over the last 3 months?" The agent confidently returned results. They were completely wrong. It had joined tables incorrectly and missed a critical filter. Not the agent's fault: nothing it could see said that this question meant filtering users on a particular attribute, or that you were only allowed to count someone as 'active' if certain conditions were met.
That gap is where production Text-to-SQL fails. LLMs are remarkably good at generating syntactically correct SQL, and syntactically correct SQL can still be the wrong answer. After a number of iterations on that client project, we found the challenges go far deeper than prompt engineering. Real-world databases have cryptic column names that no LLM can interpret without help. Users expect consistent results, and LLMs are inherently non-deterministic. Multi-tenant platforms serve customers who use identical schemas in completely different ways. What fixed all three was a semantic layer, a memory of pinned queries, and per-tenant context. Better prompts never would have.
Interestingly, power users can even instinctively catch that the answer is incorrect. The existing dashboards from the big data era were built with multiple dev cycles between the business user and the data analyst, probably over many painstaking months if not years, but they had one thing going for them: they were correct in their answer and they were consistent in their data. Text-to-SQL indeed has a high bar to meet.
This guide is what we learned building Text-to-SQL for real business users: where naive implementations break, what a semantic layer actually contains, how pinned queries fix non-determinism, and what changes when every tenant uses the same schema differently. If you're deciding whether to build one of these systems, or debugging one that keeps being confidently wrong, this is the map we wish we'd had.
