Conversational BI Agent

A natural language business intelligence assistant that translates plain-English questions into SQL queries, executes them against a DuckDB warehouse, and renders interactive charts — all inside a Streamlit chat interface.

Works with any dataset. Bring your own CSVs — no code changes required.

---

Project Summary

Ask questions like "Which products have the highest reorder rate?" or "Show me sales trends by category" and get back a chart, a data table, an AI-generated insight, and the SQL that produced it — no SQL knowledge required.

The agent uses Claude Sonnet to understand your question, explore the schema, and write accurate SQL against whatever data you've loaded.

---

Problem It Solves

Traditional BI tools require analysts to write SQL or navigate a GUI. This agent removes that barrier:

• Non-technical users can query a complex data warehouse in plain English
• Analysts get instant exploratory queries without hand-writing SQL
• Any dataset — drop in CSVs, run setup, start asking questions
• Large datasets (millions of rows) are handled via AI-generated pre-aggregated derived tables, so queries stay fast

---

Key Features

• Natural language to SQL — Claude Sonnet interprets questions and generates DuckDB SQL
• Dataset-agnostic — works with any CSV-based dataset; no code changes when switching datasets
• AI-assisted setup — setup_dataset.py analyzes your schema and auto-generates optimized derived tables
• Tool-use agent — the LLM can call run_sql multiple times for multi-step reasoning before committing to a final answer
• LLM-driven chart selection — the model picks the right chart type (bar, line, scatter, pie, or table) based on the question
• AI-generated insights — 2–3 sentence narrative explaining the key finding in each result
• Conversation memory — prior turns are passed as context so follow-up questions work naturally
• Self-correcting SQL — on execution failure the agent retries once with the error message appended
• Parquet-backed warehouse — CSVs are auto-converted to Parquet on first run for fast columnar queries
• Export results — every query result has a one-click CSV download
• Schema sidebar — live table browser showing row counts and column types

---

Architecture

data/*.csv
    │
    ▼ (auto-converted once, cached as .parquet)
DuckDB raw tables
    │
    ▼ (derived tables built from data/derived_tables.sql, once on startup)
Pre-aggregated derived tables
    │
    ▼
Agent (src/agent.py)
  ├─ System prompt: live schema + auto-detected dataset hints
  ├─ Tool loop: LLM calls run_sql() for exploration (up to 10 iterations)
  └─ Final response: XML → reasoning, chart_type, SQL, insight
    │
    ▼
DuckDB query execution (src/database.py)
    │
    ▼
Plotly chart (src/visualization.py) + st.dataframe fallback
    │
    ▼
Streamlit chat UI (app.py)

How Derived Tables Work

On startup, database.py reads data/derived_tables.sql and builds any tables defined there. This file is generated by setup_dataset.py and is git-ignored — it belongs to the dataset, not the codebase.

• If derived_tables.sql exists → derived tables are built; agent prefers them over raw tables
• If it doesn't exist → agent works directly on raw tables (fine for small datasets)

The agent's schema view always reflects whatever tables exist, and labels derived tables as DERIVED — PREFER THIS so the LLM knows to use them first.

Agent Response Format

<response>
  <reasoning>Step 1/2/3 planning...</reasoning>
  <chart_type>bar|line|scatter|pie|table</chart_type>
  <sql>SELECT ...</sql>
  <insight>Key finding in 2-3 sentences.</insight>
</response>

---

Tech Stack

Component	Technology
LLM	Claude Sonnet (claude-sonnet-4-6) via Anthropic SDK
Database	DuckDB (in-process, file-backed)
Storage format	Parquet (auto-converted from CSV)
UI	Streamlit
Charts	Plotly
Data manipulation	pandas
Config	python-dotenv

---

Setup & Installation

Prerequisites

• Python 3.10+
• An Anthropic API key

1. Clone the repository

git clone <repo-url>
cd conversational-bi-agent

2. Create a virtual environment

python -m venv .venv
source .venv/bin/activate   # Windows: .venv\Scripts\activate

3. Install dependencies

pip install -r requirements.txt

4. Configure environment variables

cp .env.example .env

Open .env and set your API key:

ANTHROPIC_API_KEY=sk-ant-...

Optional overrides (defaults shown):

DATA_DIR=data/
DB_PATH=data/warehouse.duckdb
MAX_ROWS=10000

5. Add your dataset and run setup

Option A — Your own CSVs:

Copy your CSV files into data/, then run:

python setup_dataset.py

This converts CSVs to Parquet and calls Claude once to analyze your schema and generate data/derived_tables.sql with appropriate pre-aggregations for your data.

Option B — Instacart (Kaggle):

Requires Kaggle credentials at ~/.kaggle/kaggle.json.

python setup_dataset.py --kaggle psparks/instacart-market-basket-analysis

Option C — CSVs from another directory:

python setup_dataset.py --source /path/to/your/csvs

6. Run the app

streamlit run app.py

---

How to Use

1. Open the app in your browser (Streamlit prints the URL, typically http://localhost:8501)
2. Wait for "Database ready" — the sidebar shows all loaded tables with row counts
3. Type a question in plain English, for example:
   • "What are the top 10 products by sales volume?"
   • "Show revenue trends over time"
   • "Which category has the highest average order value?"
   • "How does customer retention vary by region?"
4. The agent will:
   • Run exploratory intermediate queries if needed
   • Return a chart (or table) with the results
   • Show a plain-English insight below the chart
   • Expand the SQL section to see the exact query that ran
5. Click Download CSV to export any result set
6. Ask follow-up questions — the agent remembers the last 10 turns of conversation

---

Project Structure

├── app.py                  # Streamlit entry point
├── setup_dataset.py        # Dataset onboarding: CSV → Parquet + AI-generated derived tables
├── requirements.txt
├── .env.example
├── src/
│   ├── agent.py            # Tool-use LLM agent (NL → SQL), dynamic system prompt
│   ├── database.py         # DuckDB connection, CSV/Parquet materialization, derived tables
│   ├── visualization.py    # chart_type string → Plotly figure
│   └── config.py           # Environment variable loading
├── data/                   # All data files — git-ignored
│   ├── *.csv               # Your source CSVs (git-ignored)
│   ├── *.parquet           # Auto-generated (git-ignored)
│   ├── warehouse.duckdb    # Persistent DuckDB file (git-ignored)
│   └── derived_tables.sql  # AI-generated derived table definitions (git-ignored)
└── tests/

Nothing in data/ is committed to git. The repo ships zero dataset-specific content.

---

Switching Datasets

To replace the current dataset with a new one:

python setup_dataset.py --reset --source /path/to/new/csvs

--reset deletes warehouse.duckdb, all Parquet files, and the existing derived_tables.sql before loading the new data. Claude will generate a fresh derived_tables.sql for the new schema.

setup_dataset.py Flags

Flag	Description
--source PATH	Directory containing CSV files to load
--kaggle SLUG	Kaggle dataset slug to download first
--reset	Delete warehouse + parquets + derived_tables.sql before loading
--skip-analysis	Keep existing derived_tables.sql, skip AI generation