From Plain English to BigQuery: Architecting an AI-Powered Analytics Assistant (SQL — Slack & Retool)
Introduction: The Analytics Bottleneck
In any data-heavy organization, the business intelligence or data team often finds themselves answering the same operational questions daily: “What were our top five products yesterday?” or “How many active subscriptions do we currently have?”
While dashboards are great, decision-makers often need quick, ad-hoc numbers on the fly. Waiting in a Jira queue for a data engineer to write a simple SQL query slows down the entire business.
To solve this, I architected and developed a Natural Language Analytics Assistant — an AI tool that allows anyone in the company to ask plain-English questions, translates those questions into precise SQL, executes the queries securely, and delivers the results instantly.
Here’s a deep dive into how I built it, the architecture behind the scenes, and the technical challenges I had to overcome.
The Solution: A High-Level Overview
At its core, the application acts as a bridge between the company’s users and our data warehouse.
Instead of forcing users to learn SQL or navigate complex BI tools, users simply type their question into the interface. For example: “Show me a breakdown of gross sales by product type for this month.”
The assistant instantly:
Validates that the question can be answered using our available data.
Dynamically selects the correct database schema context based on the user’s selected brand or storefront.
Translates the English question into a syntactically correct SQL query.
Executes the query against the data warehouse.
Synthesizes the raw data into a human-readable answer.
Offers interactive options to instantly generate visual charts (Bar, Line, Pie) or export the raw data as a CSV.
Under the Hood: The Technical Stack
To build a secure, highly responsive, and intelligent bot, I leaned on a modern, cloud-native stack:
Language: Python 3
Web Framework: Flask + Gunicorn
AI / LLM: Google Gemini 2.5 Flash via Vertex AI
Data Warehouse: Google Cloud BigQuery (google-cloud-bigquery)
Data Visualization: Pandas & Matplotlib
Frontend UI / Internal Tooling: Retool (Migrated from an initial Slack Bot MVP)
Infrastructure: Google Cloud Run (Containerized via Docker)
Key Architecture Decisions
Building an AI that writes code (SQL) and executes it against a production database requires strict guardrails. Here is how the orchestration works:
Get Joseph Mario Medillo’s stories in your inbox
Join Medium for free to get updates from this writer.
1. The Multi-Agent Orchestrator
Instead of passing everything into a single, massive LLM prompt, I designed a multi-agent orchestration pattern (pm_agent.py):
The QA Agent (SA1): Acts as the bouncer. It intercepts the user’s question and checks it against our allowed schema catalog. If a user asks a question about data we don’t track, the bot politely rejects it without wasting compute or risking a hallucinated SQL query.
The SQL Writer Agent (SA2): This agent takes the validated question and writes the SQL. It is strictly constrained by a 10 GB “Cost Guard” (dry-run) to prevent runaway query costs on massive tables.
The Orchestrator: Manages the flow, executes the SQL via the BigQuery client, validates the returned payload for nulls or negative anomalies, and uses Gemini to format the final answer.
2. Context-Aware Schema Injection
Our organization runs multiple independent e-commerce storefronts, each with its own dataset. To make the assistant scalable, I built a dynamic schema routing system. The user selects a specific brand from the UI, and the assistant dynamically rewrites its baseline schema dictionary in memory, swapping out the underlying table paths before sending the context to the SQL Writer Agent.
Challenges & Takeaways
Building this bot wasn’t without its hurdles. Here is one of the biggest technical challenges I faced and how I solved it:
Handling Inevitable SQL Syntax Errors
LLMs are powerful, but they occasionally hallucinate column names or write invalid SQL (e.g., misplacing a GROUP BY clause). Initially, a bad query would just crash the bot and frustrate the user.
The Fix: I engineered a Self-Healing SQL Retry Loop. If BigQuery throws an execution error, the Orchestrator catches the exception. Instead of giving up, it takes the exact BigQuery error string, pairs it with the broken SQL, and sends it back to the SQL Writer Agent with instructions to fix it. The system will automatically retry up to 3 times. If it succeeds after a retry, the final answer includes a subtle “Medium Confidence” flag, advising the user that the query required self-correction.
Furthermore, I built a conversational thread memory system. If a user spots an issue with the data, they can submit follow-up feedback (e.g., “Wait, use the shopify_orders table instead”). The orchestrator injects the previous SQL and the user’s feedback into the prompt context, allowing the AI to intelligently rewrite the query.
The Next Evolution: Migrating to Retool
While the initial version of this tool was built as a Slack Bot (complete with Block Kit buttons and asynchronous webhooks), we eventually outgrew the conversational interface. To truly scale the tool for enterprise use, I migrated the frontend to Retool.
Moving to Retool unlocked several massive advantages:
Richer UI/UX: Instead of cramming data tables and Matplotlib PNGs into a Slack thread, Retool allowed me to render beautiful, native, interactive data grids. Users can sort, filter, and paginate through hundreds of rows of results effortlessly.
Access Control & Governance: Retool’s built-in authentication made it much easier to restrict certain datasets or query capabilities to specific teams (e.g., Marketing vs. Finance) without writing complex Slack permission logic.
Structured Inputs: I was able to replace fuzzy Slack commands with a clean dashboard featuring explicit dropdowns for Brand Selection, date pickers, and a dedicated chat window for the natural language input.
Actionable Workflows: With Retool, the generated data wasn’t just text in a chat — it became structured JSON that we could pipe into other internal tools, turning the analytics assistant into an operational powerhouse.
The Python/Flask backend and Vertex AI orchestration remained exactly the same; they simply shifted from responding to Slack webhooks to serving a REST API for the Retool frontend.
Conclusion
Building this Natural Language Analytics Assistant was a masterclass in combining cloud infrastructure with generative AI. By implementing a strict multi-agent architecture, a self-healing retry loop, and eventually migrating to a robust Retool dashboard, the project transformed from a neat proof-of-concept into a reliable, enterprise-grade internal tool.
It has empowered non-technical team members to pull their own analytics instantly, freeing up the data engineering team to focus on core infrastructure rather than ad-hoc SQL requests. The future of business intelligence isn’t just better BI dashboards — it’s conversational, contextual, and instantly accessible.
