Retail · Agentic Workflow
Retail Executive Analytics Agent
Grounding Executive Questions in BigQuery, with Guardrails That Keep Answers Safe to Share
A prototype agent that answers sales, customer, and product questions from BigQuery, with PII controls and a confirm step before deleting saved reports.
Impact snapshot
| Metric | Result |
|---|---|
| Analyst pattern reuse | Every analysis turn retrieves prior Question, SQL, and Report examples and uses them as few-shot patterns |
| SQL self-repair | Failed or empty queries are rewritten up to 2 times, then the turn stops instead of looping |
| PII exposure in answers | Email, name, and similar columns are denied in SQL and masked in rows and final text |
| Destructive report deletes | The user previews their own reports, then deletion runs only after the exact phrase CONFIRM DELETE |
| Cost visibility | Each turn records latency, tokens, BigQuery bytes billed, and an estimated USD cost |
| Persona changes | Report tone is reloaded every turn from configuration, with no code redeploy |
These are capabilities the prototype implements. They are not measured client ROI. The warehouse is the public thelook_ecommerce dataset, and this build has no production traffic.
Tech stack
Python, LangGraph & BigQuery
Python
LangGraph
LangChain
Gemini
BigQuery
Chroma
SQLite
Docker
Agent: Python 3.12, LangGraph, LangChain, and Pydantic. The model is Gemini 3.5 Flash, with Gemini 3.5 Flash Lite as fallback.
Warehouse: Google BigQuery, dataset bigquery-public-data.thelook_ecommerce (orders, order_items, products, users), with a dry-run and a byte cap before a query is allowed to run.
Golden Knowledge: Curated trios of question, SQL, and report, retrieved as few-shot examples. Similarity search uses Chroma, with a lexical fallback.
App state and interface: SQLite for reports, preferences, traces, and metric events. A command-line interface for chat, one-off questions, traces, and metrics. Packaged with uv, Docker, and Docker Compose. Persona is hot-loaded each turn.
Production design
Documented, not deployed in this prototype: Cloud Run and FastAPI for the API edge, auth, and correlation IDs. Gemini on Vertex AI. Cloud Storage plus Vertex AI Vector Search for curated trios. Cloud SQL (Postgres) for reports, conversations, and preferences. Cloud Scheduler and a Cloud Run job to re-embed approved trios. Cloud Trace, Cloud Logging, and Secret Manager. Optional LangSmith or Langfuse for model spans.
The Challenge
Store and regional managers needed answers about sales, customers, products, and time trends without writing SQL. The source of truth is raw BigQuery transaction data that includes personal data. A model that only writes SQL from the schema misses how analysts have already framed the same questions, and a chat box with no controls can leak PII, run unbounded scans, or delete saved reports on a casual instruction. The system also had to keep learning, survive bad SQL and model failures without crashing, be evaluable before release, and let a non-developer change the report voice without a redeploy.
The Solution
Hybrid analysis
A LangGraph agent classifies intent, retrieves similar analyst examples, then plans and writes read-only BigQuery SQL from those examples plus schema cards. New capabilities are additional graph tools, not a rewrite of the runtime.
Safe answers by default
Off-topic and injection attempts are refused before any query. PII columns are blocked in SQL. Rows and the final report are masked again before they reach the user.
High-stakes report library
Deletes are scoped to the current user, shown as a preview, and applied only after the exact phrase CONFIRM DELETE. Cancel or a mismatched phrase leaves the library unchanged. Deletes are soft, so the audit trail remains.
Bounded self-healing
Syntax errors and empty results go through a repair loop capped at two extra attempts. Rate limits and timeouts retry, then fall back to the lite model. If BigQuery is unavailable, the session returns a degraded answer instead of crashing. Dry-run and a byte cap stop oversized scans.
Learning without silent promotion
Managers can prefer tables or bullets, and that preference is stored per user. A positive rating writes a candidate example. A negative rating is quarantined. A curator promotes an example explicitly. Failures are not auto-promoted into the golden set.
Operable and testable
Every turn gets a correlation ID, a trace of retrieved examples and SQL attempts, and a metrics row for latency, repairs, PII hits, guardrail blocks, and token and BigQuery cost. An offline eval checks intent, PII masking, SQL allowlisting, and the delete confirmation flow before a live model call.
The ROI & Results
There is no before-and-after business metric for this prototype. A pilot would be needed for time to insight, analyst hours avoided, or cost per question. What the build changes for an executive analytics workflow:
Plain-language reports
Managers ask in plain language and get a report with insights and action items, grounded in BigQuery and prior analyst patterns, instead of waiting on a custom SQL request.
PII stays inside the agent
Answers that would expose customer PII are blocked or redacted before they leave the agent.
Deletes require confirmation
Saved-report cleanup cannot run from a single sentence. The user sees what would be deleted and must confirm.
A bounded bill
A bad query costs at most two repairs, and each turn has an estimated model plus BigQuery cost, so self-healing does not become an open-ended bill.
Tone without a redeploy
Weekly tone changes are a configuration edit. The agent picks them up on the next message.