Beyond Dashboards: Building an Autonomous BigQuery Data Analyst Agent with Google ADK for Customer…

Beyond Dashboards: Building an Autonomous BigQuery Data Analyst Agent with Google ADK for Customer 360

How we democratized an enterprise Customer 360 lakehouse by building a schema-aware, cost-guarded conversational AI agent using Google’s Agent Development Kit (ADK) in Python.

1. The Last-Mile Bottleneck: Why Traditional BI and Naive Text-to-SQL Fail

In Part 1, we designed a Hub-and-Spoke Medallion Lakehouse in BigQuery, preserving domain context across SAP, Salesforce, and Shopify. In Part 2, we engineered an Enterprise Entity Resolution Engine, unifying millions of fragmented customer identities using PySpark GraphFrames and hybrid GenAI address deduplication.

At this point, the data engineering team achieved a massive technical milestone: the enterprise had a pristine, single source of truth.

And yet, within three weeks of launch, our data engineering team was drowning in ad-hoc Slack requests:

• “Can you run a query to find which Shopify VIPs who spent over $1,000 have open high-priority support tickets in Salesforce?”
• “How many wholesale contractors registered a retail warranty after experiencing a shipping delay in SAP last month?”

Why Traditional BI Dashboards Break Down

Traditional dashboards (Looker, Tableau, Power BI) are designed for pre-aggregated, predictable metrics (e.g., Monthly Recurring Revenue by Region). But executive decision-making and cross-channel investigations are fundamentally exploratory and ad-hoc. When business questions span three distinct operational systems, creating a new dashboard tile for every permutation creates an unmanageable sprawl of hundreds of brittle reports.

Why Naive “Text-to-SQL” LLM Wrappers Fail in the Enterprise

Many teams try to solve this by slapping a basic LLM prompt onto their database: user_prompt -> LLM -> BigQuery.

In an enterprise lakehouse with hundreds of tables, this naive approach collapses:

1. Hallucinated Schemas: The model invents non-existent columns (e.g., guessing customer_email instead of golden_primary_email).
2. Layer Confusion: The LLM queries raw Silver staging tables instead of conformed Gold marts, returning un-deduplicated, dirty records.
3. Runaway Compute Bills: The LLM generates un-partitioned full table scans or unbounded Cartesian cross-joins, burning through BigQuery slot quotas.
4. Security Vulnerabilities: A naive agent might attempt to execute destructive DDL/DML commands (DROP TABLE, DELETE FROM).

To bridge this last-mile gap, we architected an Autonomous BigQuery C360 Data Analyst Agent using the Google Agent Development Kit (ADK) in Python.

This article details how we built a production-ready conversational analyst equipped with domain schema context, Vertex AI context caching, pre-execution dry-run cost safeguards, and a self-correcting SQL execution loop.

2. Agentic Architecture: The Google ADK Framework

The diagram below illustrates how our ADK Agent processes natural language questions into validated, cost-optimized BigQuery results:

3. Engineering Pillar 1: Knowledge Grounding & Vertex AI Context Caching

An AI agent cannot write accurate enterprise SQL without understanding domain-specific data definitions. For instance, the agent must know that:

• UnifiedCustomerDimId represents an individual B2C consumer.
• UnifiedAccountDimId represents a corporate B2B commercial entity in SAP.
• curated_unified contains conformed Golden dimensions, whereas curated_shopify contains raw storefront transactions.

Compiling Markdown Dictionaries into the System Prompt

Rather than dumping thousands of lines of raw DDL into the prompt (which confuses LLMs), our configuration module dynamically compiles human-written markdown layer guides (c360_overview.md, customer_er_flow.md, unified_layer_guide.md) into a structured system instruction:

# c360_agent/config.py
import os
import glob

def compile_knowledge_base(knowledge_dir: str) -> str:
    """Recursively compiles markdown architecture guides into structured agent context."""
    context_chunks = []
    guide_files = glob.glob(os.path.join(knowledge_dir, "**/*.md"), recursive=True)
    
    for file_path in sorted(guide_files):
        with open(file_path, "r", encoding="utf-8") as f:
            rel_name = os.path.relpath(file_path, knowledge_dir)
            content = f.read()
            context_chunks.append(f"### KNOWLEDGE GUIDE: {rel_name}\n\n{content}\n")
            
    return "\n---\n".join(context_chunks)

KNOWLEDGE_CONTEXT = compile_knowledge_base("c360_agent/knowledge")

SYSTEM_PROMPT = f"""
You are an expert BigQuery Data Analyst Agent for an Enterprise Customer 360 (C360) Lakehouse.
Your mission is to answer business and analytical questions by generating and executing accurate, cost-effective SQL queries.

CRITICAL OPERATIONAL RULES:
1. TARGET CONFORMED MARTS FIRST: Always query `c360_curated_unified` for golden demographics or cross-system questions. Use Spoke marts (`c360_curated_*`) only for system-specific line-item grain.
2. MANDATORY DRY RUN: You MUST execute `dry_run_bq_query` on every generated SQL statement before calling `execute_bq_query`. Inspect syntax and bytes scanned.
3. CLUSTER AWARENESS: Always filter or join on clustered keys (`UnifiedCustomerDimId`, `UnifiedOrderDimId`) to minimize BigQuery slot consumption.
4. STRICT READ-ONLY: You are strictly forbidden from executing INSERT, UPDATE, DELETE, DROP, or ALTER statements.

DOMAIN ARCHITECTURE CONTEXT:
{KNOWLEDGE_CONTEXT}
"""

The Cost Game-Changer: Vertex AI Context Caching

Including comprehensive data dictionaries results in a system prompt of ~45,000 tokens. If you pass 45k tokens on every user interaction, latency spikes to 8+ seconds and API token costs explode.

By configuring Google ADK’s native ContextCacheConfig, the entire knowledge base is cached directly in Vertex AI for 30 minutes:

# c360_agent/agent.py
from google.adk.agents.llm_agent import Agent
from google.adk.apps import App
from google.adk.agents.context_cache_config import ContextCacheConfig
from .config import SYSTEM_PROMPT
from .tools import (
    execute_bq_query,
    dry_run_bq_query,
    get_table_schema,
    list_dataset_tables,
    get_table_preview
)

# 1. Initialize the Google ADK Agent
root_agent = Agent(
    name="c360_data_analyst",
    model="projects/enterprise-c360-prod/locations/us-central1/publishers/google/models/gemini-2.5-flash",
    description="Autonomous BigQuery Data Analyst for the Enterprise Customer 360 Lakehouse.",
    instruction=SYSTEM_PROMPT,
    tools=[
        dry_run_bq_query,
        execute_bq_query,
        get_table_schema,
        list_dataset_tables,
        get_table_preview
    ]
)

# 2. Wrap in App with Vertex AI Context Caching
app = App(
    name="c360_agent_app",
    root_agent=root_agent,
    context_cache_config=ContextCacheConfig(
        ttl_seconds=1800,   # Cache prompt for 30 minutes
        cache_intervals=10  # Reuse across up to 10 conversational turns
    )
)

Result: Input latency dropped from 6.2 seconds down to 1.1 seconds, and token input costs were reduced by 88%.

4. Engineering Pillar 2: Safe, Cost-Guarded Tool Execution

Enterprise data platforms cannot give an LLM unrestricted database access. The agent must operate inside a hardened sandbox.

In c360_agent/tools.py, we engineered three layers of defense:

1. Regex Security Enforcement: Hard rejection of non-SELECT queries.
2. Cost-Guarded Dry Runs: Pre-flight validation measuring bytes scanned before spending a single slot.
3. Bounded Execution: Strict row limits and timeout parameters.

# c360_agent/tools.py
import subprocess
import re

def dry_run_bq_query(query: str) -> str:
    """Dry-runs a BigQuery SQL query to validate syntax and estimate bytes scanned."""
    clean_query = query.strip().upper()
    
    # Layer 1: Strict Read-Only Guard
    if not (clean_query.startswith("SELECT") or clean_query.startswith("WITH")):
        return "SECURITY ERROR: Only SELECT queries are permitted. DDL/DML is strictly forbidden."

    try:
        # Executes 'bq query --dry_run' to inspect execution planner without billing slots
        result = subprocess.run(
            ["bq", "query", "--use_legacy_sql=false", "--dry_run", query],
            capture_output=True,
            text=True,
            check=True
        )
        # bq CLI outputs estimated bytes scanned to stderr on dry run
        dry_run_output = result.stderr if result.stderr else result.stdout
        return f"DRY_RUN SUCCESS: {dry_run_output.strip()}"
    except subprocess.CalledProcessError as e:
        return f"DRY_RUN SYNTAX ERROR: {e.stderr.strip()}"


def execute_bq_query(query: str) -> str:
    """Executes a validated BigQuery SQL query and returns the formatted tabular results."""
    clean_query = query.strip().upper()
    
    if not (clean_query.startswith("SELECT") or clean_query.startswith("WITH")):
        return "SECURITY ERROR: Only SELECT queries are permitted."

    try:
        result = subprocess.run(
            ["bq", "query", "--use_legacy_sql=false", "--format=pretty", "--max_rows=50", query],
            capture_output=True,
            text=True,
            check=True
        )
        return result.stdout
    except subprocess.CalledProcessError as e:
        return f"EXECUTION ERROR: {e.stderr.strip()}"

5. Engineering Pillar 3: The Autonomous Self-Correction Loop

One of the greatest capabilities of an agentic architecture (versus a simple prompt-response pipeline) is the Self-Healing Execution Loop.

When writing SQL against complex schemas, even the best LLMs occasionally misspell a column name or format a timestamp incorrectly. Instead of failing and dumping a raw SQL error to the business user, the agent catches the error, diagnoses the problem, and repairs itself:

Real Production Trace: The Self-Healing Loop in Action

To see the power of autonomous agentic loops versus naive text-to-SQL, let’s walk through an actual production trace from our ADK debug logs.

Notice how the agent catches an invalid column join, intercepts the BigQuery compiler diagnostics, self-corrects using the conformed Hub key, validates the 14.2 MB scan, and delivers an executive answer — all in 2.4 seconds without human intervention.

💬 The Business User’s Question

“What is our total e-commerce revenue from VIP customers who registered a product warranty?”

⚙️ Step 1: Initial Attempt & Compiler Rejection

The agent first identifies the relevant tables (dim_customer and CustomerWarranties) and attempts a standard join on the raw source key customer_id:

-- Attempt 1: Generated by Agent (Pre-Flight Dry Run)
SELECT 
    SUM(c.total_spent) AS total_vip_revenue
FROM `enterprise-c360-prod.c360_curated_shopify.dim_customer` c
JOIN `enterprise-c360-prod.c360_curated_shopify.CustomerWarranties` w
    ON c.customer_id = w.customer_id;

Before executing against live data, the agent passes this query to dry_run_bq_query. BigQuery's execution planner rejects it:

❌ BigQuery Compile Error:
Column 'customer_id' not found in table CustomerWarranties; did you mean UnifiedCustomerDimId?

🧠 Step 2: Autonomous Diagnosis & Self-Correction

In a standard LLM wrapper, this error would be dumped onto the business user’s screen.

In our ADK architecture, the agent intercepts the compiler feedback. It consults its compiled C360 knowledge base and realizes:

• “The warranty table does not link at the raw Shopify customer_id grain. It links across systems via the conformed clustered key UnifiedCustomerDimId."

The agent generates an instant self-healing fix:

  SELECT SUM(c.total_spent) AS total_vip_revenue
  FROM `enterprise-c360-prod.c360_curated_shopify.dim_customer` c
  JOIN `enterprise-c360-prod.c360_curated_shopify.CustomerWarranties` w
-     ON c.customer_id = w.customer_id;
+     ON c.UnifiedCustomerDimId = w.UnifiedCustomerDimId;

🛡️ Step 3: Pre-Flight Cost Validation

The agent sends the repaired SQL back through dry_run_bq_query. This time, the syntax compiles, and BigQuery returns the scan estimate:

✅ Dry Run Passed: Query will process 14.2 MB when run.
(Cost: < $0.0001 | Execution Time: 0.8s)

📊 Step 4: Bounded Execution & Executive Synthesis

With the dry run verified and costs bounded, the agent calls execute_bq_query and synthesizes the raw tabular output into an executive summary:

MetricValueTotal VIP Revenue$4,281,940.50Unique Matched Customers18,420 profilesCross-Channel LinkageShopify Storefronts ➔ Product Warranties

🤖 Agent Response to User:
“Total retail revenue from customers who have registered at least one product warranty is $4,281,940.50. This analysis successfully joined 18,420 unique customer profiles across Shopify orders and digital warranty registrations using the conformed UnifiedCustomerDimId."

The user never saw the initial column mismatch. The agent caught the compiler feedback, repaired its SQL dialect, validated the 14.2 MB scan footprint, and returned a verified answer in 2.4 seconds.

6. From Terminal CLI to Interactive Web UI

Data engineers and business stakeholders interact with data platforms differently. We packaged the agent with two distinct access modes:

Mode 1: Terminal CLI for Data Engineers & CI/CD

Data engineers can run rapid exploratory queries directly from their command line:

python3 c360_agent/agent.py "Show me top 5 customers by SAP delivery volume in 2026"

Mode 2: Interactive Web Debug Console (adk web)

For business analysts, product managers, and executive stakeholders, the Google ADK includes a built-in development and debug web console.

Launch the local web server with hot-reload enabled:

adk web --port 8001 c360_agent --reload

Navigating to http://localhost:8001 renders an interactive workspace where users can:

• Chat naturally with the lakehouse.
• Expand the Trace Inspector to review every tool call, dry-run response, and SQL statement executed.
• Inspect BigQuery execution time and slot utilization per turn.

Summary: The Enterprise Autonomous Agent Checklist

If you are planning to build an autonomous BigQuery data analyst agent atop your enterprise data platform, follow these engineering rules:

The Trilogy Concluded: The Modern Enterprise Data Foundation

Across this three-part series, we have built the complete modern enterprise customer data stack on Google Cloud:

1. Part 1 (The Architecture): We abandoned the 300-column monolithic table in favor of a Medallion Hub-and-Spoke model, preserving domain context across SAP, Salesforce, and Shopify while clustering on unified keys.
2. Part 2 (The Identity Engine): We solved the 50-trillion comparison trap using an Identity Spine, computed transitive closures with Serverless PySpark GraphFrames, preserved IDs with Lookup & Inherit, and conquered dirty addresses with a Hybrid Gemini + Vector Search pipeline (+2.3% duplicate recovery).
3. Part 3 (The Agentic Layer): We bridged the last-mile gap by deploying an Autonomous Data Analyst Agent with Google ADK, turning a static lakehouse into an active, conversational enterprise intelligence partner.

Enterprise Customer 360 is no longer about buying an expensive, rigid third-party black box. By building natively on modern cloud primitives — BigQuery, Dataform, Serverless Spark, and Google ADK — you can achieve total architectural control, unmatched query performance, and transformative business agility.

Thank you for following the Enterprise Customer 360 Trilogy! If you found this series valuable, share it with your engineering team, and connect with us to share how you’re solving identity and agentic data challenges in your organization.

Beyond Dashboards: Building an Autonomous BigQuery Data Analyst Agent with Google ADK for Customer… was originally published in Google Cloud – Community on Medium, where people are continuing the conversation by highlighting and responding to this story.

Source Credit: https://medium.com/google-cloud/beyond-dashboards-building-an-autonomous-bigquery-data-analyst-agent-with-google-adk-for-customer-b7fbc460bfd3?source=rss—-e52cf94d98af—4

About the Author

Deven Goratela

Administrator

Visit Website View All Posts