Natural Language BigQuery: Building an NL→SQL Multi-Agent System with Google ADK

The most common complaint from business stakeholders is: "I have to ask data engineering just to get a number." NL-to-SQL systems solve this — but generic approaches hallucinate column names that don't exist in your schema, producing SQL that fails silently or returns wrong answers. This system solves that by making the agent schema-aware before it writes a single character of SQL.

Two-Agent Architecture

User question (natural language)
  ↓
Root Orchestrator (Gemini 2.5 Flash)
  ├── Routes to BigQuery Agent for: "query this data"
  ├── Routes to Pandas Agent for: "analyze this result"
  └── Chains both for: "find X, then show me the trend"
         ↓
BigQuery Agent (NL2SQL Specialist)
  ├── Initialized with full schema for all configured datasets
  ├── Translates NL → SQL (only references real columns)
  ├── Validates SQL against schema before execution
  └── Executes via google-cloud-bigquery client
         ↓
Pandas Agent (Analysis Specialist)
  ├── Receives result set as DataFrame
  ├── Runs in Vertex AI Code Interpreter (sandboxed Python)
  ├── Applies: groupby, pivot, rolling avg, correlations
  └── Generates charts + statistical summaries

Schema-Aware NL2SQL — Preventing Hallucinations

The most important architectural decision is injecting the real schema into the BigQuery agent's system prompt before any user query. The agent can only reference columns that actually exist:

python

1from google.cloud import bigquery
2from google.adk.agents import LlmAgent
3from google.adk.tools import FunctionTool
4
5bq_client = bigquery.Client()
6
7def get_schema_context(datasets: list[str]) -> str:
8    """Build schema context string for all configured datasets."""
9    schema_parts = []
10    for dataset_ref in datasets:
11        tables = list(bq_client.list_tables(dataset_ref))
12        for table_ref in tables:
13            table = bq_client.get_table(table_ref)
14            cols = ", ".join(
15                f"{f.name} ({f.field_type})"
16                for f in table.schema
17            )
18            schema_parts.append(
19                f"Table `{table.full_table_id}`: {cols}"
20            )
21    return "\n".join(schema_parts)
22
23SCHEMA_CONTEXT = get_schema_context(CONFIGURED_DATASETS)
24
25bigquery_agent = LlmAgent(
26    name="bigquery_agent",
27    model="gemini-2.5-flash",
28    instruction=f"""You are a BigQuery SQL specialist.
29You MUST only reference the following columns — never invent column names.
30
31SCHEMA:
32{SCHEMA_CONTEXT}
33
34Rules:
351. If the user asks about a column that doesn't exist, say so clearly
362. Always use fully-qualified table names: `project.dataset.table`
373. Use LIMIT 1000 unless the user explicitly asks for all rows
384. For date filters, use DATE() or TIMESTAMP() functions appropriately""",
39    tools=[FunctionTool(execute_bigquery_sql), FunctionTool(explain_schema)],
40)

Sandboxed Pandas Analysis

The Pandas agent runs code in Vertex AI's sandboxed Code Interpreter — no arbitrary code execution on the server. This is critical for a tool that executes AI-generated Python code:

python

1from vertexai.preview.extensions import Extension
2
3code_interpreter = Extension.from_hub("code_interpreter")
4
5def run_pandas_analysis(dataframe_json: str, analysis_request: str) -> dict:
6    """Execute Pandas analysis in Vertex AI sandboxed environment."""
7    code_prompt = f"""
8import pandas as pd
9import json
10
11# Load the data
12data = json.loads('''{dataframe_json}''')
13df = pd.DataFrame(data)
14
15# User's analysis request: {analysis_request}
16# Write Python/Pandas code to fulfill this request.
17# Use df as the input DataFrame.
18# Print results and generate a plot if appropriate.
19"""
20    response = code_interpreter.execute(
21        operation_params={
22            "query": code_prompt,
23        }
24    )
25
26    return {
27        "output_text": response.get("generated_code", ""),
28        "execution_result": response.get("execution_result", ""),
29        "output_files": response.get("output_files", []),  # Charts as base64
30    }

Multi-Turn Session Persistence

The system supports follow-up questions that reference previous results: "Now filter that to just Q4" or "Add a comparison to last year." Session history is persisted in SQLite (single-instance) or Cloud SQL (multi-instance):

python

1# FastAPI + ADK integration
2from google.adk.cli.fast_api import get_fast_api_app
3
4# ADK handles session management automatically when using get_fast_api_app()
5# Sessions persist across requests via the configured session service
6app = get_fast_api_app(
7    agent=root_orchestrator,
8    session_service=DatabaseSessionService(db_url=DATABASE_URL),
9    allow_origins=["*"],
10)
11
12# Example multi-turn conversation:
13# Turn 1: "Show me revenue by category for Q3"
14#   → BigQuery Agent executes SQL, returns 8 rows
15# Turn 2: "Which grew fastest?"
16#   → Pandas Agent: calculates MoM growth on the Q3 results
17# Turn 3: "Show me the same for Q4"
18#   → BigQuery Agent: re-runs query with Q4 date filter
19#   (References prior conversation context automatically)

Example Interactions

"Show me revenue by product category for last quarter, excluding returns" → BigQuery Agent generates SQL with date filters and JOIN to returns table
"Which category grew the fastest?" → Pandas Agent calculates month-over-month growth rates, returns ranked list with percentage changes
"Why did DAU drop 15% on March 3rd? Show me everything that changed" → Chains both agents: BigQuery queries across 4 tables, Pandas correlates the anomalies
"Summarize the top 10 SKUs by margin for the last 6 months" → Single BigQuery query with window functions + Pandas formatting

Hallucinated column names (schema-aware): 0
Specialist agents (BigQuery + Pandas): 2
Code runs in sandboxed environment: 100%
Session persistence across questions: Multi-turn