Ai Agent Sql Validation Wrong Results

AI agents produce wrong SQL results confidently and frequently. The query compiles, runs, returns a number — and the number is subtly, dangerously wrong. Validation is the only defense, and it has to be layered because no single check catches everything. This guide walks through the five validation layers Data Workers uses to catch wrong results before they reach users.

'Compiles and runs' is not the same as 'correct.' Every SQL validation framework for agents needs to address that gap explicitly, with tests that go beyond execution.

Layer 1: Compilation Check

The cheapest layer: dry-run the query before executing it. For Snowflake and BigQuery this means EXPLAIN. For Postgres it means preparing the statement. Compilation catches dialect errors, missing tables, and wrong column names — maybe 30 percent of agent SQL bugs. It is necessary but far from sufficient.

Layer 2: Schema Validation

The second layer compares the query's column references to the catalog's known schema. Columns must exist, types must match, and units must be consistent. This catches the fabricated-column bug and the unit-mismatch bug (cents vs dollars) that compilation alone misses.

Layer 3: Range Validation

• •Plausible ranges — revenue must be non-negative, percentages must be 0-100
• •Historical comparison — the result must be within N standard deviations of recent values
• •Row count sanity — a 'total users' query should not return 3 rows
• •Null handling — all-null results usually indicate a bad join
• •Empty result detection — zero rows when historical shows consistent data = red flag
• •Unit sanity check — dollars in the millions for a small business = unit error

Layer 4: Consistency Validation

The fourth layer compares the new query result to the result of a semantically equivalent 'trusted' query from the catalog or a dbt model. If they disagree by more than a small tolerance, flag the result. This catches the wrong-join bug and the missing-filter bug, which are among the most common agent SQL mistakes.

Layer 5: Human Review for High-Risk Queries

Any query that drives a business decision, updates production, or exports data for external use must go through human review. The agent proposes, the human reviews, and only then does the query run. Data Workers integrates this with Slack and PR-style gates. See autonomous data engineering.

Measuring Validation Effectiveness

Track the percentage of agent SQL queries caught at each layer. A healthy pipeline catches most bugs at layers 1-3 (cheap, automated) and only escalates high-risk queries to layer 5 (expensive, human). If most bugs are caught at layer 5, your automated validation is too weak; if none are caught at layer 5, your human review is wasted effort. See AI for data infrastructure.

Regression Testing Agent SQL

Every bug caught becomes a regression test. Data Workers ships a 200-query golden set per agent, with known-correct answers. Every model release runs against the suite; regressions are caught at CI, not in production. The suite grows with every bug, which means the agent gets more robust over time.

Wrong SQL results are the most dangerous AI agent failure mode because they look identical to correct results. Layered validation is the only real defense. To see the full validation stack, book a demo.

The most dangerous wrong results are the ones that look plausible. If an agent reports revenue of 1.2 billion when it should be 1.2 million, a human might catch it. If the agent reports 1.19 billion when it should be 1.2 billion, no one notices. Subtle wrong answers are why validation layers 3 and 4 (range and consistency) are so important — they catch the mistakes that a casual eye would miss. Range validation is especially good at catching order-of-magnitude errors from unit mismatches.

Consistency validation against a trusted reference query is the highest-leverage single technique. If your dbt project already computes monthly revenue in a trusted model, the agent's ad-hoc revenue query should produce results within a small delta of the trusted model. When they disagree, the agent's query is almost always wrong. This check is cheap (one extra SELECT against the warehouse) and catches a large fraction of mistakes. Data Workers auto-discovers trusted reference queries from dbt manifest metadata, so the pattern works with zero configuration.

Historical comparison is often the single most effective validation layer. A new query that returns a number wildly different from its historical equivalent is almost always wrong. The trick is finding the right historical equivalent — if the query is new and has no history, you need a semantically equivalent query from the catalog. Data Workers' catalog agent maintains an index of canonical queries by intent, which lets the validation layer find the right reference query automatically.

Another effective pattern is sanity checks against external sources. If your agent reports total revenue of $X for last month, that number should roughly match what finance reports. When the agent's number disagrees with finance by more than a small tolerance, flag the query for human review. External cross-check is the most reliable validation because it tests against ground truth rather than another agent-derived number.

Five layers: compile, schema, range, consistency, human. No single check catches everything. Layered validation is the only real defense.