A text to SQL agent turns a business question like "what was net revenue retention last quarter?" into a query against your warehouse and returns an answer. On a raw schema it will often return a confident, plausible and wrong number, because the model cannot know which of your four revenue columns is the real one. The fix is mostly data engineering: a semantic layer with agreed metric definitions, permissions passed through to every query, a golden set of real questions to test against, and an agent that shows its SQL and says "I do not know" when it should.
This guide is for data and analytics leaders setting Q1 plans while vendors push agents to every business user.
Why every warehouse vendor now ships a data agent
The major platforms all have a version of this. Snowflake describes Cortex Analyst as a fully managed, LLM-powered feature that lets business users ask questions in natural language without writing SQL, and its docs now point new projects toward Cortex Agents. Snowflake Intelligence is now Snowflake CoWork, which Snowflake calls a personal work agent that answers questions grounded in company data. IT Brief reported the rename in June. Databricks has Genie Agents (formerly Genie Spaces), which its docs describe as the natural-language chat interface where business users ask questions of their data.
The pitch is the same everywhere: business users ask their own questions instead of filing an analytics ticket. But a wrong answer can now reach a board deck unchecked. Every one of these products depends on well-modeled, governed data, so the vendor matters less than you think. Our Snowflake vs Databricks guide compares the two if you are still choosing.
Next question: Should we run our data agents on Snowflake or Databricks?
Why a text to SQL agent fails on a raw schema
Public benchmarks show the gap clearly. The Spider 2.0 benchmark, built from real enterprise databases that often have over 1,000 columns, reports that GPT-4o solved only 10.1% of its tasks, compared with 86.6% on the older Spider 1.0 benchmark. Real schemas are much harder than demo schemas.
In practice, failures cluster into a few patterns:
- Ambiguous metrics. "Revenue" might mean booked, billed, recognized or net of refunds. Finance, sales and product each use a different one, and the column names do not say which.
- Wrong joins. The model joins orders to customers on the wrong key, or joins through a many-to-many table and silently double counts.
- Stale and duplicate tables. The warehouse holds customers, customers_v2 and customers_final_old. The agent picks the one with the best name, not the one that is still refreshed.
- Hidden filters. Analysts know to exclude test accounts. The schema does not say so.
None of these are model problems. They are modeling problems your analysts work around from memory.
Next question: Who can clean up our warehouse before we add an agent?
The fix: a semantic layer with agreed metric definitions
A semantic layer is a governed description of your business on top of the warehouse: entities, approved joins, dimensions and metrics defined once in code, so the SQL comes from definitions your data team owns.
The evidence for this is strong, with a caveat. In a 2026 benchmark update from dbt Labs, which sells a semantic layer, Claude Sonnet 4.6 scored 90.0% with raw text-to-SQL and 98.2% through the semantic layer on modeled data, and GPT-5.3 Codex went from 84.1% to 100%. It used 11 questions on one dataset, so treat it as directional. The authors' most useful line was about failure modes: with text-to-SQL, failure looks like a plausible but incorrect answer, while with a semantic layer, failure looks like an error message.
The warehouse vendors have moved the same way. Snowflake's docs say its semantic views define business concepts, metrics and relationships, and recommend them over the older YAML files. Databricks Genie Agents accept metric views, example SQL queries and instructions. Either way, the work is to define each metric in code and give it an owner.
Next question: Where do we find senior data engineers who have built a semantic layer?
Pass permissions through to the agent, never around them
The fastest way to cause a data incident is to connect an agent through one powerful service account. Every user then sees whatever that account sees, salaries and customer PII included.
The better products run queries as the person asking. Databricks says data access in a Genie Agent is always evaluated using each end user's own Unity Catalog permissions, and that row filters and column masks are enforced per user. Snowflake says Cortex Analyst fully integrates with its role-based access control, and its CoWork page says the agent runs with each user's own privileges, applying row-level policies and masking automatically.
Read the fine print anyway. Snowflake's own docs note that a role with access to the stage holding a semantic model file can use that model even without table access, so they advise that roles have SELECT on every table the model references. Databricks notes that the author's compute credentials are attached to the agent for warehouse access. Have your security team review both before launch.
If you build your own agent, pass the user's identity to the warehouse on every call, keep row-level security in the warehouse rather than the prompt, and log each query against the person who asked. Our post on AI agent permissions and prompt injection covers the wider pattern.
Next question: Can we add a senior security engineer to review agent data access?
Evaluate with a golden set of real business questions
Build a golden set from the questions people actually ask, pulled from analytics tickets, Slack requests and recurring reports, each with correct SQL and an expected result. If you want help designing one, see our LLM evaluation services. Databricks has built this into Genie: its benchmarks feature holds up to 500 test questions per agent, compares the result set of your SQL with the one Genie generates, and recommends two to four phrasings of the same question. Snowflake offers Cortex Analyst evaluations that measure SQL accuracy against verified queries.
A practical golden set has four parts:
- Core metrics. The 20 to 50 numbers leadership looks at every week, each asked several ways.
- Hard joins. Questions that cross three or more entities, where double counting tends to appear.
- Permission tests. The same question asked by users with different access, checking that each sees only their rows.
- Questions with no answer. Things your data cannot answer, where the right response is a refusal.
Run the set in CI after every model, data or semantic layer change, and block releases that lower the pass rate. Let your analytics team own it.
Next question: How do we build an evaluation suite for an LLM agent?
Show the SQL, and teach the agent to say "I do not know"
Trust comes from being able to check the work. Every answer should show the SQL that produced it, the tables and metric definitions it used, and when that data was last refreshed.
Equally important is when the agent declines. Set clear rules for when it should stop and say so:
- The question uses a metric that is not in the semantic layer.
- The relevant table has not refreshed within its expected window.
- Two definitions match and the agent cannot tell which the user means. Ask a clarifying question instead of guessing.
- The user does not have access to the data needed to answer.
Snowflake's docs note that Cortex Analyst may struggle with follow-ups in long conversations where intent shifts, and suggest a reset. A clear "I do not know, here is who to ask" is far cheaper than a wrong number in a forecast.
Next question: How do you test an AI agent before business users rely on it?
The data engineering work that has to come first
Most teams find the agent was the easy part. The real work is on the warehouse, and it also makes dashboards trustworthy:
- Retire duplicate and dead tables so the agent only reaches current objects.
- Describe every table and column in scope, including units and grain.
- Model the core entities (customer, account, order, contract) with tested primary keys and documented joins.
- Define the top metrics in code, with one owner each, and remove conflicting versions from BI tools.
- Add freshness and quality tests so the agent can see when data is stale.
- Move row-level security into the warehouse if it lives only in your BI tool today.
Start narrow. Pick one domain, such as sales pipeline, with an eager business owner, model it well and launch there. Teams that point an agent at the whole warehouse on day one usually spend the next quarter explaining wrong numbers.
Next question: What does a senior data engineer cost per month?
Checklist: before you let an agent query the warehouse
- One business domain is in scope, with a named owner.
- Every table the agent can reach is current, described and has an owner.
- Core metrics are defined once in a semantic layer, and conflicting versions are retired.
- The agent queries as the end user, and row-level security and masking live in the warehouse.
- A golden set of real questions, with expected results, runs automatically and has a pass threshold.
- Every answer shows its SQL, sources and data freshness.
- The agent refuses or asks a clarifying question when a metric is missing, ambiguous or stale.
- Every query is logged against the person who asked, and someone reviews failures weekly.
If you cannot tick most of these, the agent is not ready. If you want a team to build it on your stack, see our AI agent development services.
Next question: Who can build a custom data agent on our own stack?
How Ryz can help
Ryz adds senior data engineers and AI pod teams to your team, working in your warehouse, repos and standups on US business hours, so the semantic layer, permission model and evaluation suite get built before the agent goes live. Only the top 1% make it through our vetting, most engineers ship in their first week, and senior engineers run $10,000 to $15,000 per month. See our data engineering services or hire data engineers to scope the work with us.
FAQ
What is a text to SQL agent?
It is an AI agent that turns a plain-English business question into SQL, runs it against your warehouse and returns the answer. Good ones use a semantic layer, respect user permissions and show their SQL.
Do we need a semantic layer for a text to SQL agent?
For anything leadership will act on, yes. Raw text-to-SQL can work for ad hoc exploration on small, clean datasets, but on a large warehouse it produces plausible wrong answers. A semantic layer makes failures show up as errors instead.
How do we measure whether a data agent is accurate?
Build a golden set of real business questions with correct SQL and expected results, include several phrasings and some unanswerable questions, and run it automatically after every change.
How long does it take to get a data agent ready for production?
It depends on the state of your data. One well-modeled domain can be ready quickly; a warehouse with duplicate tables and undefined metrics needs data engineering first.
