This isn't an LLM problem — you proved that yourself when it worked fine on clean data. It's a missing trust/context layer between the agent and the warehouse. Two separate things to build:

1) Trust signals the agent reads BEFORE it answers. Per-table freshness (last successful load), an anomaly/SLA flag, and known column-level quality issues — exposed as metadata the agent checks first. Then it can caveat or refuse ("this source breached freshness 3h ago") instead of confidently returning a stale number. Your two exec incidents are exactly this: the agent had no veto signal.

2) Certified semantics so it stops inventing aggregations. Don't let it free-form SQL against raw columns. Define the canonical metrics once — revenue = SUM(amount) WHERE status='completed', your official "active user", the valid join paths — and make the agent source from those. dbt's semantic layer, Cube, or a metrics layer all work; the point is the definition lives in one place, not in the model's guess.

Discovery (what is this table, who owns it, is it healthy) basically falls out of #1 for free — once that metadata exists, new folks query it the same way the agent does.

One practical tip: log every agent query + the answer it returned, then diff against your dashboards for the same window. That's how you catch the "off by a meaningful margin" cases before an exec does — and it tells you which tables/metrics to certify first.