Why agents give wrong answers
Analytics agents give wrong answers for one main reason. The model can write SQL, but it does not know your business. When it has to guess at a join, a grain, or what a metric means, it guesses confidently and the answer looks fine even when it is wrong.
A naive text to SQL feature turns one sentence into one query and stops. There is no check, so the first plausible query is the answer you get. That works in a demo and breaks in production.
The common failure modes
Most wrong answers come from a short list of mistakes.
- Wrong joins. The agent joins two tables on a key that is not unique and the row count explodes, so every total is inflated.
- Wrong grain. It sums a daily table as if it were monthly, or counts orders when it meant to count customers.
- Ambiguous metrics. Ask for revenue and the agent has to decide whether refunds, tax, and discounts are in or out. Without a rule it picks one.
- Hallucinated columns. It writes a query against a column name that sounds right but does not exist, or means something else.
- No validation. The query runs, returns a number, and nobody checks if the number makes sense.
Why this is hard to catch
A wrong answer from an agent does not look wrong. It comes back as a clean number with a confident sentence around it. A business user has no way to know the join doubled the count. That is worse than no answer, because someone acts on it.
The model is not the bottleneck here. Modern models write decent SQL. The gap is that they do not know revenue excludes refunds, or that an active user has a specific definition, or that two tables look alike but mean different things.
Fixing this is not about a better prompt. It is about giving the agent the business knowledge it lacks, in a form it can read and you can govern. That work is called context engineering, and it is what the rest of this unit covers.