CASE STUDY DETAILS
BQ Data Intelligence
Agent
Project Type
Web Platform
Challenge
Business users cannot self-serve analytics without SQL knowledge.
Domain
Data Intelligence
Impact
Data Democratisation
OVERVIEW
BigQuery Intelligence is a production-ready, enterprise-grade AI-powered analytics platform that enables organisations to query, analyse, and visualise their Google BigQuery data using natural language.
Built on a FastAPI backend with Gemini AI via Vertex AI, the platform democratises data access by allowing business users, analysts, and administrators to interact with complex datasets without writing SQL.
The platform provides three role-based workspaces — Admin Console, Analyst Workspace, and User Console — each tailored to the specific needs of its audience, from IT governance and semantic layer management to self-serve reporting and AI-driven insights.
PROBLEM STATEMENT
Traditional BI tools require SQL expertise, lengthy dashboard development cycles, and centralised data team bottlenecks.
Enterprise data teams face a growing gap between the volume of data stored in cloud warehouses like BigQuery and the ability of business users to extract actionable insights.
- Business users cannot self-serve analytics without SQL knowledge
- Analysts spend excessive time on repetitive ad-hoc query requests
- No governed, auditable layer between users and raw BigQuery tables
- Anomaly detection and root cause analysis require manual investigation
- No unified platform for role-based access, cost governance, and semantic standardization
- Fragmented tooling across SQL editors, BI dashboards, and data catalogues
SOLUTIONS
To build an AI-powered conversational analytics agent that enables business users to query BigQuery data in natural language, investigate anomalies and trends, identify root causes, and generate actionable business insights without relying on analysts for every question.
1. Natural Language to SQL (NL→SQL)
- Users type business questions in plain English.
- Gemini AI generates validated BigQuery SQL.
- No SQL knowledge required.
3. AST SQL Governance Engine
- Implemented AST-based SQL validation.
- Restricted query execution to SELECT statements only.
- Prevented unsafe Cartesian joins.
5. BigQuery Dataset Explorer
- Retrieves table schemas and metadata directly from BigQuery.
- Displays row counts and storage size for each table.
- Identifies partitioning and clustering keys to help analyze and optimize.
2. Role-Based Access Control
- Three distinct roles (Admin, Analyst, User) with JWT authentication.
- department-level access governance.
4. Semantic Layer
- Implemented a centralised Semantic Layer for business metrics.
- Created a standardised metric catalogue for Revenue, Churn Rate, and Fulfillment Rate.
6. Verified Query Catalog
- Pre-certified SQL templates for common business queries.
- Shared access to SQL templates for all analysts.
- One-click query execution with CSV export functionality.
APPROACH
Our Approach
Requirement Analysis & Stakeholder Mapping
- Defined functional requirements for three user roles: Admin, Analyst, and Business User.
- Identified role-specific workflows, responsibilities, and access requirements.
Semantic Layer Design
- Designed domain-driven semantic layers covering Sales, Customer, and Operations.
- Implemented fully qualified BigQuery table references and standardised metric definitions.
Agent Orchestration Architecture
- Designed a multi-agent pipeline: SQL Generator → AST Validator → BigQuery Executor → Analyzer → Explainer.
- Implemented SQL validation to ensure safe and controlled query execution.
Real BigQuery Data Integration
- Integrated live BigQuery data for dashboard statistics and business KPIs.
- Connected trend analysis and anomaly detection to real BigQuery queries.
- Implemented root cause analysis using actual business data instead of mock production data.
Agile Iterative Development
- Followed an iterative development approach with continuous feature improvements.
- Completed frontend data binding and functionality validation using live BigQuery data.
Final Delivery
- Integrated AI-powered claims platform
- Production-ready application architecture
- Role-based dashboards
- Automated claim processing
- AI analysis and routing
- Developer-ready system structure
SUCCESS AND IMPACT
Platform Validation Results
- Users can ask natural language questions (e.g. “Show me revenue by region”) and the system automatically generates and executes BigQuery SQL using Gemini on Vertex AI, returning human-readable explanations.
- Built a pipeline of specialized AI agents: SQLGeneratorAgent → QueryValidator → BigQueryClient → AnalyzerAgent → ExplainerAgent, all orchestrated by AgentOrchestrator for clean separation of concerns.
- Built a full analyst dashboard with live BigQuery-backed endpoints: dashboard stats, trends & anomalies (Z-score detection), root cause decomposition, verified query library, schema explorer, and CSV export.
Expected Business Impact
- Fully operational AI-powered analytics platform connected to live BigQuery data.
- Natural language querying eliminating SQL barriers for business users.
- Governed, auditable query execution with AST validation on every request.
- Real-time anomaly detection and root cause analysis from production data.
- Role-based access control with department-level quota management.
- Centralised semantic layer preventing metric calculation inconsistencies.
- Live BigQuery schema explorer with table metadata, row counts, and storage stats.