← Back to Portfolio
🤖 GenAI · LLM Application Engineering

Conversational LLM-Powered Business
Intelligence System

Conversational data access over 3 major BigQuery databases using Gemini 2.5 Pro enabling business users to query complex enterprise data with plain English, no SQL required.

Gemini 2.5 Pro / Flash GCP Vertex AI BigQuery FastAPI Text-to-SQL MCP Server Layer LLM Orchestration Python Front end UI
Pipeline Built and Accuracy-Validated
Full pipeline operational on Vertex AI Workbench · MCP server layer integrated · 85–90% accuracy achieved across Gemini Flash and Pro
85–90%
Query Accuracy Achieved (Gemini Flash & Pro)
3
Critical Business BigQuery Databases Integrated
2
LLM Models Benchmarked: Gemini 2.5 Flash vs Pro
0
SQL Knowledge Required by Business Users
MCP
Server Layer for Accuracy Improvement

🎯 Problem Statement

The organisation manages critical business data across three separate BigQuery databases product, customer, and sales/operations each with its own schema and no shared query layer. Getting answers to cross-functional business questions required raising requests to the data or engineering team, translating requirements into SQL, and waiting for results. This created a dependency that slowed down day-to-day decisions.

The goal was to build a single conversational interface where any business user could ask a question in plain English and get accurate, structured results directly from the data with no SQL knowledge required and no analyst in the loop.

🏗️ System Architecture

⚡
FastAPI
Backend API
Port 8081
→
🔌
MCP Server
Tool Layer
Accuracy Control
→
🤖
Gemini 2.5
Pro / Flash
Vertex AI
→
🗄️
BigQuery
Product Data Customer Data Sales / Ops Data
→
🪣
GCS Bucket
JSON Data Models
Schema + Business Context
→
📊
Response
Table / Text
Rendered UI
Context Layer: JSON data models loaded from GCS · Cross-database schema relationships · Product ↔ Customer entity mappings · Business-level field definitions
MCP Layer: Intercepts LLM tool calls · Validates SQL before execution · Routes queries to correct BigQuery datasets · Improves end-to-end accuracy

⚙️ Key Technical Challenges & Solutions

Challenge
Multi-database schema context 3 major BigQuery databases with distinct schemas must be passed to the LLM accurately without exceeding context limits.
Solution
Structured JSON data models per database with selective schema injection based on query intent only relevant tables/columns included in the LLM context window.
Challenge
Cross-database entity relationships product and customer data spread across databases with implicit join logic the LLM must understand.
Solution
JSON data models stored in GCS and loaded at inference time encoding schema, foreign key relationships, business-level field definitions, and product-customer entity mappings across all three datasets.
Challenge
SQL correctness and hallucination LLM-generated SQL must be syntactically valid for BigQuery dialect, semantically correct, and free from hallucinated table or column names.
Solution
MCP server layer intercepts and validates all LLM tool calls before execution. Validates field names against the loaded schema, blocks non-SELECT statements, and catches hallucinated identifiers before they reach BigQuery. Combined with prompt engineering and few-shot SQL examples.
Challenge
Model selection under cost constraints Gemini 2.5 Pro delivers higher reasoning quality but at greater cost. Flash is faster and cheaper but accuracy on complex queries was unknown.
Solution
Structured benchmarking of both Gemini 2.5 Flash and Pro across the full query test set. Both models achieved 85–90% accuracy on the representative query set, validating that Flash is deployable at scale without sacrificing meaningful accuracy.
Challenge
Full-stack orchestration connecting chat UI → FastAPI → Vertex AI → BigQuery → formatted response in a coherent, low-latency flow.
Solution
FastAPI async endpoints handling LLM calls and BigQuery execution in sequence, with streaming response rendering for perceived performance.

💡 What Makes This Different

Frontier Model - Gemini 2.5 Pro/Flash

  • Built on Google's latest frontier model, running natively on Vertex AI
  • Superior code generation and multi-step reasoning for complex SQL
  • Not an OpenAI wrapper fully within the GCP ecosystem

Multi-Schema Context Management

  • Handles 3 production databases with distinct schemas simultaneously
  • Selective schema injection only relevant tables sent to the LLM per query
  • Cross-entity relationships understood across database boundaries

GCS-Backed JSON Data Models

  • JSON data models for all 3 datasets stored in GCS, loaded at inference
  • Encode business semantics field definitions, entity types, join logic
  • LLM generates SQL reflecting actual business logic, not just table structure
  • Product and customer data relationships explicitly mapped across databases

Full-Stack, Production-Bound Architecture

  • FastAPI backend + chat UI frontend — not a notebook or demo
  • Hosted on Vertex Workbench, architected for production migration
  • Every component independently deployable and scalable

MCP Server Layer for Accuracy Control

  • Model Context Protocol server intercepts all LLM tool calls before execution
  • Validates SQL field names against the loaded schema — blocks hallucinated identifiers
  • Enforces SELECT-only execution, preventing accidental data modification
  • Improves end-to-end system accuracy beyond what prompt engineering alone achieves

85–90% Accuracy Validated Across Two Models

  • Benchmarked Gemini 2.5 Flash and Pro across a representative business query set
  • Both models achieved 85–90% accuracy on SQL generation and response quality
  • Flash validated for production-scale deployment — cost-efficient without accuracy loss
  • Structured evaluation methodology: SQL correctness, cross-DB joins, edge cases

🛠️ Technical Stack

🤖
Gemini 2.5 Pro / Flash
LLM · SQL Generation · Reasoning
☁️
GCP Vertex AI
LLM Hosting · Model Orchestration
🗄️
BigQuery
Data Warehouse · SQL Execution
⚡
FastAPI
Backend API · LLM-BQ Orchestration
🔌
MCP Server Layer
Tool Interception · SQL Validation · Accuracy Control
📋
JSON Data Models
Schema Context · Relationship Maps
🪣
GCS Bucket
Data Model Storage · Context Loading
🐍
Python
Core Logic · BQ Client · Prompt Engineering
📊
Vertex Workbench
Development & Pre-Production Hosting

📏 Evaluation Results

The system was evaluated across a representative set of real business queries covering all three BigQuery databases. Both Gemini 2.5 Flash and Pro were benchmarked across SQL correctness, cross-database join accuracy, and response quality. Both models achieved 85–90% accuracy on the full query set. The MCP server layer was introduced mid-evaluation and materially improved accuracy by catching hallucinated field names and invalid joins before BigQuery execution.

Overall Accuracy: 85–90%
Achieved across both Gemini 2.5 Flash and Pro on the full representative business query set
Model Comparison: Flash vs Pro
Both models benchmarked at comparable accuracy — Flash validated for production-scale cost efficiency
MCP Layer Impact
Introduced mid-evaluation — materially improved accuracy by intercepting hallucinated field names and invalid joins before execution
Cross-DB Join Reliability
Entity resolution validated across product and customer database boundaries using structured JSON data model mappings

📈 Expected Business Impact

📉 One-Stop Data Access

  • Single interface for all customer, product, and sales data queries
  • No analyst or engineering involvement for routine data requests
  • Business users query across all 3 databases from one chat window

⚡ Faster Decision Velocity

  • Data questions answered in seconds, not hours or days
  • No ticket queue results returned directly in the interface
  • Frees analyst time for higher-value, non-routine work

🏢 Enterprise-Grade Scalability

  • Built on Vertex AI and BigQuery scales with GCP infrastructure
  • Designed for production deployment, not just internal tooling
  • Data models in GCS make it easy to extend to new datasets
⚠️ Disclaimer: This case study is sanitised for confidentiality. No client identifiers, schemas, or proprietary data are disclosed. All metrics are representative of system design targets.