The single biggest predictor of SQL correctness in LLM-generated queries is whether the full relevant schema is present in the context window at generation time. A study published by Google Research in 2023 ("Can LLMs Generate Correct SQL?") found that Gemini Pro's SQL accuracy on BigQuery benchmarks improved from 61% to 89% when schema context was injected — a 28-point gain without any fine-tuning.

In practice, agents inject schema by first calling GetBigQueryTableSchemaTool for each candidate table, then including the JSON schema response verbatim in the system prompt for the SQL-generation step. The token cost is modest — a typical BigQuery table schema is 200–800 tokens — and the accuracy gain is substantial.

Complex analytical questions rarely map to a single SQL statement. Agents that attempt one-shot SQL for multi-step logic (e.g., "find customers who bought product A but not B in the last 90 days, then rank them by lifetime value") frequently produce syntactically valid but logically wrong queries. The more robust pattern is query decomposition: the agent breaks the question into sub-queries, executes each as a separate tool call, and passes intermediate results back into the context before constructing the final query.

Standard SQL generation prompts trained on generic databases produce subtly wrong BigQuery SQL. The most common failures are use of backtick-free table references (valid in some engines, rejected by BigQuery's parser), missing project qualifiers, and using CURRENT_DATE() syntax from MySQL rather than BigQuery's CURRENT_DATE (no parentheses).

Every BigQuery query job can be submitted in dry-run mode — the query is parsed and optimized but not executed, and the response includes an estimate of bytes that would be processed. This costs nothing. For agents, dry-run is the mandatory first step before submitting any LLM-generated SQL to production tables.

Production deployments at Google Cloud enterprise customers typically implement five distinct guardrail layers, each catching a different failure class:

BigQuery allows tables to be marked with requirePartitionFilter — any query that does not include a filter on the partition column is rejected at parse time, before any bytes are scanned. For agent-generated SQL over date-partitioned event tables (a common pattern in analytics), this is the single most effective cost control: an agent that forgets a WHERE clause on the date column gets a hard error rather than a $10,000 bill.

Google's own documentation recommends this setting for any partitioned table larger than 100GB that an agent may query. It is set at table creation or via ALTER TABLE DDL and is enforced by BigQuery's query planner, not by application code — meaning it cannot be bypassed even if the agent's guardrail logic has a bug.

Even a syntactically correct, cost-efficient query can overwhelm an agent's context window if it returns millions of rows. The standard pattern is to enforce a maximum result row count in the QueryBigQueryTool wrapper — returning at most 1,000 rows by default — and to have the agent summarize or aggregate before returning large result sets to the user. LangChain's built-in BigQueryTool enforces a 1,000-row default and surfaces the total row count so the agent can request aggregated queries if needed.

For small result sets (under ~200 rows), the standard pattern is to convert BigQuery rows directly to JSON or a Markdown table and inject them into the LLM's context as a tool response. The LLM then synthesizes its answer from this grounded data rather than from its parametric knowledge — a technique called Retrieval-Augmented Generation (RAG) over structured data.

For larger result sets, the agent uses BigQuery's Storage Read API to stream results in Arrow format, applying aggregations or sampling before injecting into context. The BigQuery Storage Read API (launched 2019, now generally available) delivers data at 10x the throughput of the REST export API — enabling real-time agent workflows over large tables without staging to Cloud Storage first.

Without explicit grounding instructions, LLMs frequently extrapolate beyond the returned data — inferring trends from small samples, adding context from training data, or smoothing over gaps. The documented production pattern is to add a strict grounding instruction to the synthesis step:

Every BigQuery query job returns a unique job ID (format: project:region.job_XXXXXXXX). Including this ID in the agent's response gives analysts a direct audit trail — they can paste the job ID into the BigQuery console and see the exact SQL, execution time, bytes processed, and result set. This is the structured-data equivalent of citation links in a RAG document answer.

The Wayfair implementation referenced above exposed job IDs in a collapsible "Data Sources" section in their internal analytics chat UI, allowing data engineers to immediately audit any agent-generated insight. According to their blog post, this single feature reduced analyst trust questions from approximately 40% of sessions to under 5% — users with audit access simply checked the job rather than questioning the output.

Agents must be explicitly instructed on two failure modes. Empty result sets — where the query executes successfully but returns zero rows — should prompt the agent to acknowledge the absence of data and suggest why (wrong date range, mis-spelled filter value, data not yet loaded). Partial result sets — where the row limit was hit — should prompt the agent to disclose that the answer is based on a sample, not the full data, and offer to run an aggregated query instead.