Evolution of Hermes V3: Building a Conversational AI Data Analyst

swiggy · E-commerce · 2024 · source Agentic: yes · promotional bias: med · summary-only (no fetched source) · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: enterprise conversational data analysis

Swiggy needed to evolve Hermes from a basic Slack text-to-SQL assistant into a reliable conversational AI analyst that could handle business metrics, context, explanations, and production quality controls.

Constraints:

The source article was not fetched for this extraction, so evidence is summary-only.
Initial Hermes struggled with niche metrics, derived logic, conversational context, explainability, and inconsistent prompts.
Snowflake SQL history lacked metadata for traditional supervised training approaches.
Generated SQL needed transparency because incorrect queries could lead to poor business decisions.
Production deployment needed RBAC, ephemeral Slack replies, audit logs, and SSO controls.

Architecture

Hermes V3 combines SQL2Text-generated prompt examples, vector retrieval over historical query prompts, conversational memory, a ReAct-style agent/orchestrator, metadata retrieval, SQL generation, explanation/confidence layers, automated charter tests, Slack feedback, RBAC, ephemeral responses, audit logs, and SSO identity controls.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  slack-interface["Slack interface"] --> conversational-agent-layer["Conversational agent layer"]
  conversational-agent-layer --> vector-prompt-retriever["Vector prompt retriever"]
  vector-prompt-retriever --> sql-generator["SQL generator"]
  sql-generator --> explanation-layer["Explanation layer"]
  explanation-layer --> slack-interface
  quality-control-loop["Quality control loop"] --> conversational-agent-layer
  governance-layer["Governance layer"] --> slack-interface

Drag to pan · scroll or use +/− to zoom · reconstructed from the source's described component topology

Components

Component	Role	Interacts with
`Slack interface`	User-facing entry point for natural-language data questions and feedback.	`Conversational agent layer`
`Conversational agent layer`	Examines history, clarifies requests, rewrites queries, fetches metadata, and executes commands.	`Vector prompt retriever`
`Vector prompt retriever`	Searches similar generated prompts and injects few-shot examples.	`SQL generator`
`SQL generator`	Produces SQL from user intent, retrieved examples, and metadata.	`Explanation layer`
`Explanation layer`	Shows assumptions, logical steps, and confidence scores.	`Slack interface`
`Quality control loop`	Runs charter tests, collects weekly feedback, analyzes negative responses, and rolls out fixes.	`Conversational agent layer`
`Governance layer`	Applies RBAC, ephemeral replies, audit logs, and SSO-authenticated Slack controls.	`Slack interface`

Data flow

Existing SQL and business context are converted into natural-language prompts, embedded for vector retrieval, injected as few-shot examples when users ask questions, then an agent layer uses conversation history, metadata retrieval, prompt memory, SQL generation, clarification, and explanation layers to return governed Slack responses.

“This system works by taking existing SQL queries and their business context to generate corresponding natural language prompts.”summary, stated

“The agent can parse user intent and check for completeness, maintain conversational context and prompt memory, retrieve metadata including tables, columns, and definitions”summary, stated

Why this architecture

Swiggy chose retrieval, memory, agentic workflow, and explanations because Hermes needed to move beyond one-shot SQL translation. Historical SQL provided examples, conversation memory supported iterative analysis, and transparency helped users judge generated queries.

“The insight that LLMs are better at understanding SQL than writing it led to an innovative SQL2Text pipeline using large context Claude models.”summary, stated

“The explanation layer represents a sophisticated approach to AI transparency, providing users with the context needed to evaluate and trust the generated SQL.”summary, stated

Models used: Claude large context models · Vector similarity search · SQL2Text pipeline · ReAct-style reasoning loop · Snowflake · Slack

Scale

Hermes improved query accuracy from 54% to 93%.
“These enhancements improved query accuracy from 54% to 93%”summary, stated
The reported benchmark used approximately 100 manually tagged queries.
“improving accuracy from 54% to 93% on a benchmark of approximately 100 manually tagged queries.”summary, stated
Fully incorrect queries reportedly fell from 20% to 7%.
“the system reduced fully incorrect queries from 20% to just 7%”summary, stated
Ambiguous prompt accuracy increased by 20-25%.
“This structured approach led to a 20-25% increase in query accuracy on ambiguous prompts”summary, stated
Wide tables were chunked in batches of 75 columns to stay under token limits.
“For wide tables, columns are chunked in batches of 75 to stay under token limits”summary, stated

Alternatives considered (roads not taken)

Basic English-to-SQL translation.

Rejected because: It lacked context, explainability, and reliable handling of niche metrics or derived logic.

“struggles with niche metrics and derived logic, lack of conversational context, absence of explainability, and inconsistent outputs across similar prompts.”summary, stated

Traditional supervised training on SQL history.

Rejected because: The SQL query corpus lacked metadata needed for traditional training.

“Swiggy had a substantial corpus of SQL queries executed on Snowflake, these queries lacked the metadata necessary for traditional training approaches.”summary, stated

Generic embeddings for column inference.

Rejected because: The redesigned metadata strategy first narrows tables via metric definitions and descriptions, then fetches columns from those specific tables.

“Rather than relying solely on generic embeddings to infer column names, the system first fetches tables based on metric definitions and table descriptions”summary, stated

Patterns

`rag-retrieval` (few-shot prompt retrieval)

Hermes retrieves similar historical query prompts and injects them as few-shot examples for SQL generation.

“the system searches for similar prompts using vector similarity and injects the top results as few-shot examples to guide the LLM.”summary, stated

`agent-memory` (conversation context)

Hermes remembers recent prompts and carries context forward so users can refine questions without restating context.

“implementing contextual memory that remembers recent prompts and carries context forward across sessions.”summary, stated

`tool-use-orchestration` (agentic SQL workflow)

An agent layer decides whether to clarify, rewrite, retrieve metadata, or execute commands.

“an Agent layer that examines conversation history and decides appropriate actions, whether to clarify ambiguous requests, rewrite queries, fetch metadata, or execute commands.”summary, stated

`observability-tracing` (quality feedback)

Weekly Slack feedback and root cause analysis feed continuous quality improvement.

“collecting weekly feedback via Slack with simple "Was this correct? Yes/No" responses, followed by root cause analyses for every negative response.”summary, stated

Tradeoffs

Generate few-shot prompts from historical SQL.

Chose SQL2Text plus vector retrieval over Traditional supervised training on unlabeled SQL history.

Rationale: The approach reuses existing SQL and business context despite missing labeled metadata.

“The use of SQL2Text for generating training examples represents an innovative approach to the common problem of lacking labeled data in specialized domains.”summary, stated

Deploy through Slack with privacy controls.

Chose Private DMs plus central help channel over A fully public channel or separate standalone app.

Rationale: The design balances privacy, adoption, support, and community engagement.

“defaulting to private DMs for querying while maintaining one central help channel for escalations and feedback.”summary, stated

Explain generated SQL.

Chose Assumptions, logical steps, and confidence scores over Opaque SQL answers.

Rationale: Users need context to evaluate and trust generated SQL in business decision workflows.

“breaks down assumptions made by Hermes, details logical steps used to build queries, and assigns confidence scores from 1 (low) to 3 (high).”summary, stated

Gotchas (what bit them)

Accuracy claims come from a small benchmark.

Root cause: The 54% to 93% claim used about 100 manually tagged queries and may not cover all real-world complexity.

Resolution: Treat the metric as directional and keep production feedback, tests, and root-cause analysis in the loop.

“based on a relatively small benchmark of 100 manually tagged queries, which may not represent the full complexity of real-world usage.”summary, stated

Long conversations may drift.

Root cause: Conversational memory likely has context-window and context-drift limitations over longer sessions.

Resolution: Use an agent layer to examine history and decide when to clarify, rewrite, or fetch metadata.

“likely has limitations in terms of context window management and potential context drift over longer conversations.”summary, stated

Agentic workflows add troubleshooting complexity.

Root cause: Orchestrator logic introduces more moving pieces than a basic text-to-SQL translator.

Resolution: Use explanation layers, quality monitoring, automated tests, and audit logs.

“introduces additional complexity that could impact system reliability and troubleshooting.”summary, stated

Evaluation

Hermes uses manually tagged query benchmarks, ambiguous-prompt accuracy checks, automated charter test suites, weekly Slack correctness feedback, root-cause analysis for negative responses, and proactive fixes across related metrics.

Metrics: Query accuracy · Fully incorrect query rate · Ambiguous prompt accuracy · Table not found errors · Weekly correctness feedback · Charter test pass rate · Confidence scores

“automated test suites triggered for each newly onboarded charter to validate all defined metrics”summary, stated

“Fixes are then rolled out proactively across all similar metrics in that charter”summary, stated

Outcomes

Reported query accuracy improved from 54% to 93%.
Fully incorrect queries reportedly dropped from 20% to 7%.
Ambiguous-prompt accuracy improved by 20-25% and table-not-found errors were virtually eliminated.
Hermes became the backbone for other internal AI copilots and evolved into a Text to Insights tool.

The summary reports a 100-query manually tagged benchmark, 75-column metadata chunks for wide tables, and expansion beyond Hermes into other internal copilots.

“This structured approach led to a 20-25% increase in query accuracy on ambiguous prompts and virtually eliminated "table not found" errors.”summary, stated

“Hermes has become the backbone for other internal AI co-pilots and evolved into a "Text to Insights" tool”summary, stated