Gemba AI Chatbot: A Text-to-SQL Agent for Store and Product Insights
Natural-language questions to grounded, real-time answers over BigQuery
A chatbot that lets merchandising and planning teams ask about store and product performance in plain Turkish and get an answer from the company's data in seconds. The agent works out what the question means in the retailer's own vocabulary, queries the data warehouse, checks what comes back before answering, and shows its progress as it goes — so teams get answers they can act on without waiting on an analyst or knowing any SQL.
Problem
Merchandising and planning teams at one of Turkey's leading fashion retailers needed answers to plain-language questions spanning store feedback, sales, and product performance. That data lives in BigQuery tables with messy, real-world characteristics: Turkish-locale casing quirks, heavily space-padded text fields, and product-hierarchy columns that are frequently blank.
An LLM querying this data will happily return a confident answer built on a silently failed string match. The interface had to be right, not just fluent.
Goal: Let business users ask in Turkish and get instant answers they can act on without independently verifying them.
Approach
The agent runs a loop rather than a single call. It resolves the incoming question against the conversation so far, grounds it in a glossary that maps this retailer's business vocabulary onto real columns and values, injects explicit correctness rules for the known data-quality traps, calls its NL→SQL tool, and inspects what comes back — retrying once when the result is empty or the call fails, and streaming progress throughout.
Google's Data Analytics Agent serves as the NL→SQL tool inside that loop. The engineering work is the loop around it: the grounding layer that decides what the tool is asked, the state that makes follow-up questions resolvable, the resilience that handles a bad response, and the transport that keeps a slow query from feeling broken.
System Architecture
A request enters through a thin router layer, is dispatched to a query handler holding the agent logic, and reaches external services only through infrastructure clients — so grounding changes never touch transport code, and vice versa. Everything downstream of that split exists to make a probabilistic system behave predictably enough for daily use.
- Grounding layer: A domain glossary and explicit correctness rules are injected into every tool call, mapping business vocabulary onto real columns and neutralizing Turkish casing, padded text, and null hierarchies.
- Conversation state: Chat history persists in MongoDB and loads per session, so follow-up questions resolve against what was already asked instead of starting cold.
- Observe-and-retry loop: The agent inspects each tool response and retries once on an empty answer or server error before surfacing a failure.
- Layered CQRS pipeline: Routers, query handlers, and infrastructure clients stay cleanly separated, keeping API surface, agent logic, and external calls independently changeable.
- Real-time progress streaming: Server-Sent Events push progress as the agent works, so longer queries never leave the user facing a blank screen.
