Build a Data Analytics AI Agent with Vertex AI, Gemini & BigQuery
Most businesses drown in unused data because analyzing it requires SQL skills or expensive BI tools. This AI agent lets anyone ask natural language questions like "show me sales by region" and get instant visual answers - no technical skills needed. See how to deploy this game-changing analytics assistant in your organization.
The Business Problem: Data Rich But Insights Poor
Most companies today collect mountains of data but struggle to extract timely insights. The retail company in our demo had all their transaction data in BigQuery - dates, customer IDs, product categories, amounts, and regions - but answering basic questions like "what's our top selling product?" required writing SQL queries or waiting for a data analyst.
This creates three painful bottlenecks: 1) Business teams can't access data when they need it, 2) Simple questions take hours or days to answer, and 3) Valuable data sits unused because the barrier to analysis is too high. The demo shows how asking "show me total sales by region" instantly surfaces that the South region leads with $35,414 while East trails at $28,364 - insights that would normally require technical skills.
80% of business data goes unused because employees lack the technical skills to analyze it. An AI analytics agent removes this barrier by letting anyone ask questions in plain English.
Solution Overview: Conversational Analytics Agent
The solution is an AI agent that acts as a translator between natural language and your data warehouse. When the user asks "what are our top product categories?", the agent performs four magic steps: 1) Interprets the request using Gemini's language understanding, 2) Generates the appropriate SQL query, 3) Executes it against BigQuery, and 4) Formats the results as a visualization or table.
In the demo, this process happens in seconds - from asking "please show product categories in tabular format" to seeing that outdoor products lead with $27,250 in sales. The agent handles complex follow-ups too, like switching from a table to a pie chart visualization with a simple "show as pie chart" request.
Technical Architecture Breakdown
The system has three core components: 1) A web interface for user queries, 2) The Vertex AI agent orchestrating the workflow, and 3) BigQuery housing the data. When the user asks "show me daily sales as a line chart", here's what happens:
- The web app sends the text to the Vertex AI agent
- The agent uses Gemini to analyze the request and formulate SQL
- The SQL executes against BigQuery (demo shows a full year of daily sales)
- Results return to Gemini for formatting instructions
- The web app renders the visualization (line chart showing fluctuations from $542 to $1,621)
Key Insight: The same architecture works with AWS (Redshift + Bedrock) or other cloud providers - just swap the data warehouse and LLM components while keeping the agent orchestration layer.
Preparing Your BigQuery Data
The demo uses a simple denormalized table with seven columns: transaction_date, customer_id, product_category, amount, quantity, region, and product_id. This flat structure makes it easier for the AI to query without complex joins. To implement this for your business:
- Ensure your data is clean and consistently formatted
- Document your schema thoroughly (column names, data types)
- Create sample questions and expected queries for testing
- Set up appropriate IAM permissions for the agent
The retail demo shows how even basic data can yield powerful insights - like identifying POD553 as the top product ($1,318 in sales) and POD586 as the worst performer (only one unit sold).
Vertex AI Agent Configuration
Vertex AI serves as the brains of the operation. Configuration involves:
- Creating a new agent in Vertex AI Conversation
- Loading the data schema as system instructions
- Defining the query execution workflow
- Setting up the BigQuery connection
- Configuring response formatting rules
The demo agent handles complex requests like comparing regional performance or analyzing temporal trends because its system instructions clearly explain the available data and relationships. At 2:15 in the video, you can see how these instructions guide Gemini to generate accurate SQL.
Gemini Model Integration
Gemini's role is threefold: 1) Interpret natural language queries, 2) Generate appropriate SQL, and 3) Format results understandably. The integration requires:
- Selecting the right Gemini model version (1.5 Pro in the demo)
- Crafting effective system prompts that include schema details
- Implementing query validation safeguards
- Setting temperature low (0.1-0.3) for deterministic SQL
The demo shows Gemini correctly handling ambiguous requests - when asked "worst performing in terms of number of sales", it understood to count units sold rather than revenue, correctly identifying POD586.
Building the User Interface
The web interface needs just three components:
- A chat input for natural language questions
- A visualization area (using Chart.js or similar)
- A results table for tabular data
The demo interface is simple but powerful - it interprets commands like "show as bar chart" or "display in table" to toggle visualization types. The entire UI can be built with about 200 lines of JavaScript connecting to the Vertex AI agent API.
Pro Tip: Add voice input using the Web Speech API to make the system even more accessible - perfect for warehouse managers or retail staff who need hands-free analytics.
Deployment Options & Alternatives
While the demo uses Google Cloud (BigQuery + Vertex AI + Gemini), the architecture is cloud-agnostic:
- AWS: Redshift + Bedrock (Claude) + QuickSight
- Azure: Synapse + OpenAI + Power BI
- Multi-cloud: Snowflake + Anthropic + custom UI
For enterprises with existing BI tools, the agent can feed results into Tableau or Looker instead of building a custom UI. The key is maintaining the natural language interface while leveraging your current investments.
Watch the Full Tutorial
See the complete implementation from start to finish in the video tutorial below. At 3:45, watch how the agent handles a complex temporal analysis request - "show daily sales as a line chart" - revealing seasonal patterns across the full year.
Key Takeaways
This AI analytics agent demonstrates how natural language interfaces can democratize data access across organizations. The retail demo shows concrete benefits:
- Instant answers to questions like "top product by revenue" (POD553 at $1,318)
- Visual trend analysis without dashboard pre-building (year-long line chart)
- Comparative insights (South region outperforming East by 24%)
In summary: Any business with data in BigQuery, Redshift, or Snowflake can implement this AI agent in weeks, not months - transforming unused data into daily decision-making fuel.
Frequently Asked Questions
Common questions about this topic
A data analytics AI agent solves three core business problems: First, it enables non-technical team members to access complex data insights without SQL knowledge. Second, it reduces time-to-insight from hours to seconds by automating query formulation and execution. Third, it democratizes data access across organizations.
In the retail demo, the agent instantly identified the South region as top performer with $35,414 in sales versus East region's $28,364 - insights that would normally require specialized analytics skills. This empowers store managers, marketing teams, and executives to make data-driven decisions without waiting for analysts.
- Eliminates SQL knowledge requirements for data access
- Reduces analytics turnaround from days to seconds
- Surfaces insights non-technical users wouldn't know to ask for
Yes, the architecture is adaptable to other data warehouses. The demo shows it working with BigQuery on Google Cloud, but the same approach works with AWS Redshift or Snowflake. The key components remain: a data warehouse with SQL capabilities, an LLM for query formulation, and a visualization layer.
The agent simply needs permissions to execute queries against your chosen database and return structured results. We've implemented successful versions using Redshift with Anthropic Claude and Snowflake with OpenAI GPT-4, following the same architectural pattern shown in the demo.
- Works with any SQL-compatible data warehouse
- Uses same agent orchestration layer
- Only the connection details change
Modern LLMs like Gemini achieve 85-90% accuracy on first-attempt SQL generation for common analytical queries when properly prompted. The demo shows the agent correctly handling complex requests like "show me total sales by region" and "top selling product".
Accuracy improves when: the data schema is well-documented in system instructions, the agent can validate queries against a test environment first, and there's human review for mission-critical queries. For the retail demo, all generated queries returned correct results, including identifying POD586 as the worst-selling product with only one unit sold.
- High accuracy for common analytical queries
- Schema documentation boosts performance
- Optional validation step catches errors
The agent can generate all standard business intelligence visualizations based on natural language requests. The demo shows it creating bar charts (for regional sales comparison), line charts (for daily sales trends), pie charts (for market share analysis), and tabular data views.
The system interprets visualization requests from phrases like "show as bar chart" or "present in tabular format". More complex visualizations like heatmaps or scatter plots are possible with additional configuration. The demo's line chart showing a full year of daily sales fluctuations (from $542 to $1,621) demonstrates the system's visualization capabilities.
- Standard BI chart types supported
- Natural language controls visualization type
- Custom visualizations possible with configuration
Three key differences make AI agents superior to traditional BI: First, the natural language interface eliminates the need for dashboard pre-building. Second, dynamic querying allows for ad-hoc questions not anticipated in advance. Third, conversational refinement lets users ask follow-ups.
While Tableau requires predefined reports, the AI agent demonstrated could instantly answer "what's our worst performing product?" by identifying POD586 with only one unit sold - a question that would require new report development in traditional BI. The agent adapts to each question in real-time rather than relying on pre-built views.
- No pre-built dashboards required
- Handles unanticipated questions
- Conversational refinement of results
The solution works with as little as a single denormalized table containing transaction data. The retail demo uses a table with just 7 columns: transaction date, customer ID, category, amount, quantity, region, and product ID.
Minimum requirements are: clean, structured data in your warehouse, at least 1,000 records for meaningful analysis (demo shows a year's worth), and clear column naming. The system doesn't require complex joins or star schemas - the demo uses a simple flat table structure anyone can create.
- Single table implementation possible
- 1,000+ records recommended
- Clear column names essential
Vertex AI serves three critical functions: It provides managed access to Gemini models through Vertex AI's Model Garden, offers tools for prompt engineering and system instruction tuning, and enables deployment scalability.
In the demo, Vertex AI handles the conversation state management between the web interface and Gemini, then orchestrates the query execution workflow with BigQuery. This integration means businesses don't need to build complex middleware - just configure the agent's system instructions within Vertex AI to match your data schema and business needs.
- Managed access to Gemini models
- Prompt engineering tools
- Built-in orchestration layer
GrowwStacks specializes in implementing AI data analytics agents tailored to your business data. We handle full architecture design and deployment, custom agent training on your specific data schema, integration with your existing data warehouse, and user interface development.
Our team can deploy a production-ready version of the retail demo solution in 2-4 weeks, or build more complex implementations for enterprises. We've helped eCommerce, healthcare, and manufacturing clients transform their analytics with similar AI agents, delivering 10-20x faster insights across their organizations.
- End-to-end implementation
- Schema-specific agent training
- Rapid deployment timeline
Get Your Own AI Analytics Agent in 30 Days
Every day without this solution means missed opportunities hiding in your unused data. GrowwStacks can deploy a production-ready analytics agent that answers your team's business questions instantly - no SQL required.