Spaces:
Running on Zero
Running on Zero
| title: analyst-buddy | |
| emoji: 🛍️ | |
| colorFrom: red | |
| colorTo: yellow | |
| sdk: gradio | |
| sdk_version: 6.17.3 | |
| app_file: app.py | |
| python_version: "3.12" | |
| pinned: false | |
| short_description: Ask your shop data in plain English - agentic SQL | |
| tags: | |
| - track:backyard | |
| - sponsor:modal | |
| - achievement:welltuned | |
| - achievement:fieldnotes | |
| - achievement:sharing | |
| # analyst-buddy — ask your business data in plain English | |
| **A tiny AI analyst reads your tables, writes the SQL, and shows its work.** Ask a question about | |
| your small-business data and get back the answer, the result table, a chart, and the query that | |
| produced it. An owner who isn't a data expert can explore their operations and generate insights | |
| through natural language. Every answer can be reviewed and rated, and those ratings improve the | |
| model over time. | |
| **Fine-tuning makes the difference.** On the questions a small-business owner asks (across four | |
| sample business databases, including a pet-shop database the model never saw in training), an | |
| off-the-shelf Qwen3-1.7B gets about 4% correct. After fine-tuning, the same small model gets about | |
| 49% correct, an improvement of about 11×, and still runs on a laptop. The work is ongoing: | |
| one- and two-table questions are reliable, three-table joins are not yet, and a longer training run | |
| showed signs of overfitting that we're still analyzing. | |
| **How it works.** Rather than prompting a general chatbot to write SQL, we turned the open | |
| Qwen3-1.7B model into a purpose-built SQL agent that works the way an analyst does on an unfamiliar | |
| database: it studies the columns, scans a few sample rows, then builds the query piece by piece | |
| (fixing joins, correcting column names, retrying after errors) until it understands the data and can | |
| find the answer. The agent learns that loop (**DESCRIBE → SAMPLE → QUERY → ANSWER**) through a | |
| supervised warm-up and then GRPO reinforcement learning, and verifies its own answer before | |
| returning it. Served on Hugging Face **ZeroGPU**, trained on **[Modal](https://modal.com)**. | |
| - **▶ Demo video:** https://youtu.be/T82rzsRZTfU | |
| - **Live Space:** https://huggingface.co/spaces/build-small-hackathon/analyst-buddy | |
| - **Direct app:** https://build-small-hackathon-analyst-buddy.hf.space | |
| - **Fine-tuned model:** https://huggingface.co/hjerpe/sqlenv-qwen3-1.7b-grpo-v2 | |
| - **Agent traces (dataset):** https://huggingface.co/datasets/hjerpe/analyst-buddy-traces | |
| - **Field notes (blog):** https://hjerpe-analyst-buddy-blog.static.hf.space/ | |
| - **Social post:** https://x.com/hjerpeadam/status/2066590389146960314 | |
| --- | |
| # SQLEnv: Teaching Small Models to Explore Databases | |
|  | |
|  | |
|  | |
| SQLEnv is the RL training engine behind **analyst-buddy** — an agentic small-model | |
| SQL data analyst (see [offload.md](offload.md)). It trains small language models to | |
| answer questions about SQL databases through *iterative exploration*: instead of | |
| producing one-shot SQL from a fully visible schema, the agent discovers the schema | |
| step by step using four tools — DESCRIBE, SAMPLE, QUERY, and ANSWER. | |
| It runs **in-process** and is trained with [TRL](https://huggingface.co/docs/trl)'s | |
| GRPO implementation — no environment server, no external services, your data never | |
| leaves the machine. A 0.6B-parameter model trained here goes from 0% to ~30% | |
| accuracy on a curated Spider subset, learning to explore schemas, recover from SQL | |
| errors, and format answers correctly. | |
| > **Note:** this is a continuation of the original | |
| > [sqlenv](https://github.com/hjerpe/sql-env) repo with the OpenEnv dependency | |
| > removed (training never used the HTTP serving layer) and **data-quality | |
| > guardrails** added — see *Data quality* below. | |
| ## Quick Start | |
| ```bash | |
| uv sync --extra dev # core + test deps (no torch) | |
| uv run pytest tests/ -v # run the environment + guardrail tests | |
| make help # all dev/training shortcuts (test, lint, smoke, pilot, train, eval) | |
| ``` | |
| Training needs the heavy extras (`torch`/`transformers`/`trl`): | |
| ```bash | |
| uv sync --extra dev --extra training | |
| ``` | |
| > **⚠️ Platform note — no Docker required.** Modern PyTorch ships **no macOS-Intel | |
| > (x86_64) wheels** — only Apple Silicon (arm64), Linux, and Windows. On an Intel Mac | |
| > `--extra training` fails to resolve `torch`, and that's expected: training is meant to | |
| > run on a **GPU**. Open `notebooks/train_grpo.ipynb` on a **Colab L4** (the notebook is | |
| > built for it) or any Linux/arm64 GPU box. For everyday local work — the environment, | |
| > tests, validator, data prep — you only need `--extra dev`, which requires no torch. | |
| ## Serve & deploy the app | |
| The Gradio app (`app.py` → `server/app_ui.py`) is published to a Hugging Face ZeroGPU | |
| Space. Re-running is safe — it creates or reuses the Space and uploads only the runtime | |
| files. Log in first (`hf auth whoami` should show you on the target org). | |
| ```bash | |
| make deploy # publish to the default Space (zero-a10g) | |
| make deploy SPACE=you/analyst-buddy # publish to your own Space | |
| make deploy HARDWARE=cpu-basic # request different hardware | |
| ``` | |
| `make deploy` wraps `scripts/deploy_space.py`, which takes the same | |
| optional config directly: `--repo`, `--hardware`, `--message`, `--private`. The | |
| fine-tuned model does **not** need to be live to deploy — the app serves the vanilla | |
| model + the scripted demo until it's published (then flip `available=True` in | |
| `server/serving.py` and redeploy). | |
| ## How It Works | |
| Each episode starts with a natural-language question and a list of table names. The | |
| schema (columns, types, relationships) is hidden. The agent uses four actions to explore: | |
| | Action | Purpose | | |
| |--------|---------| | |
| | `DESCRIBE table` | Reveal column names, types, and row count | | |
| | `SAMPLE table` | Preview representative rows | | |
| | `QUERY sql` | Execute read-only SQL | | |
| | `ANSWER value` | Submit a final answer (ends episode) | | |
| The environment provides dense reward at each step (operational feedback + progress | |
| toward the answer) and a terminal reward for correctness (+1.0 correct, 0.0 wrong). | |
| ```python | |
| from sql_env.server.sql_environment import SQLEnvironment | |
| from sql_env.models import SQLAction | |
| env = SQLEnvironment(questions_path="data/questions/questions_train.json", | |
| db_dir="data/databases", tokenizer=tok) | |
| obs = env.reset(seed=42) | |
| obs = env.step(SQLAction(action_type="DESCRIBE", argument="employee")) | |
| obs = env.step(SQLAction(action_type="QUERY", argument="SELECT COUNT(*) FROM employee")) | |
| obs = env.step(SQLAction(action_type="ANSWER", argument="10")) | |
| # obs.done=True, obs.reward=1.0 | |
| ``` | |
| ## Data quality (harness guardrails) | |
| In RL the environment *is* the data generator, so a broken `(question, db, gold)` | |
| triple silently poisons the gradient. SQLEnv separates **harness failures** (gold SQL | |
| errors/times out, DB missing, gold result empty/degenerate) from **model failures** | |
| (bad SQL, wrong answer, budget exhaustion — legitimate negative signal), and keeps the | |
| harness rate observable: | |
| - `reset()` raises a typed `HarnessError` on a broken episode setup (fail fast — never | |
| train on an empty/degenerate gold the model could fluke-match). | |
| - The TRL adapter counts harness failures in `training/env_metrics.py` and neutralizes | |
| their reward so a broken episode can't push the gradient; the live training plot shows | |
| the running rate and warns above **5%**. | |
| - **Run the offline validator before training:** | |
| ```bash | |
| uv run python scripts/validate_questions.py # report per split | |
| uv run python scripts/validate_questions.py --write-clean # also write *.clean.json | |
| ``` | |
| On the bundled Spider subset this currently reports a **7.2% gold-empty rate in the | |
| train split** (34/473) and 3.0% in eval (6/203) — questions whose gold SQL returns no | |
| rows (e.g. "airports in Aberdeen", which the DB doesn't contain). The training | |
| notebook auto-excludes these via `load_question_prompts(..., db_dir=...)`. | |
| ## Training | |
| We train Qwen3 (0.6B → 1.7B/4B) with [GRPO](https://arxiv.org/abs/2402.03300) | |
| (SFT warmup + two-phase GRPO) through TRL's `environment_factory`. Production | |
| training runs on **[Modal](https://modal.com)** (single GPU, vLLM colocate); a | |
| legacy Colab path lives in [train_grpo.ipynb](notebooks/train_grpo.ipynb). | |
| Everything is wrapped in the **`Makefile`** — run **`make help`** to list targets. | |
| Deep dives: [docs/guides/modal-rl-training.md](docs/guides/modal-rl-training.md) | |
| (the run), [docs/guides/training_playbook.md](docs/guides/training_playbook.md) | |
| (metrics + failure modes), [docs/guides/dev-environment-parity.md](docs/guides/dev-environment-parity.md) | |
| (why local ≠ Modal). | |
| ### Run the cost ladder (cheapest gate first) | |
| Validate each step before paying for the next. Override `CONFIG=`/`GPU=`/`STEPS=` | |
| inline; add `FORCE=1` to bust a stale Modal image. | |
| | Step | Command | Cost | Proves | | |
| |------|---------|------|--------| | |
| | 0. Setup | `make setup` | — | `uv sync` + the ad-hoc `modal` CLI | | |
| | 1. Inspect SFT data | `make sft-inspect` (local) / `make inspect-sft` (real tokenizer on Modal) | ~$0 | The cold-start data + loss mask | | |
| | 2. Smoke (dry run) | `make smoke` | cents (T4) | Whole pipeline on a tiny model; validates the (transformers, trl, vllm) trio | | |
| | 3. Pilot | `make pilot CONFIG=… GPU=A100-80GB` | ~$2, self-stops at `STEPS` | vLLM/full-FT memory + reward machinery | | |
| | 4. Full run | `make train CONFIG=… GPU=A100-80GB` | $$ | Convergence (resumes from the pilot checkpoint) | | |
| | 5. Eval gate | `make eval CONFIG=… GPU=A100-80GB` | ~$1 | `success_rate` vs the 0.28–0.32 baseline | | |
| ### Inspect a live or finished run | |
| - **Weights & Biases** (`WANDB_PROJECT=analyst-buddy-grpo`) — the live dashboard. | |
| Watch: `rewards/sql_env_reward_func/mean` (should trend **up** over the full | |
| run — ignore noise over <100 steps), `tools/failure_frequency` (should **fall**), | |
| `completions/clipped_ratio`, `loss`, `grad_norm`, and step time (the cost gauge). | |
| - **Modal logs** — `modal app list` → `modal app logs <app-id>` streams stdout, | |
| including a printed sample completion every `logging_steps` (`num_completions_to_print`). | |
| - **Trajectories** — every rollout is logged to the volume. Pull + browse offline: | |
| `make replay-pull RUN=<run_id>` then `make replay-summary RUN=<run_id>` | |
| (or `scripts/replay.py show …` for a single transcript). `run_id` is the config's | |
| `run_id` / `output_dir` basename. | |
| ### Resume, repair, continue | |
| - **Resume is automatic.** Configs set `resume: "auto"` — relaunch the *same* | |
| `make train …` after a crash, a 6-hour timeout, or a pilot, and it continues | |
| from the last `checkpoint-*` on the volume (verified by a config-hash drift | |
| guard). The pilot → full transition is just dropping `--max-steps` (i.e. | |
| `make pilot` → `make train`). | |
| - **Stop anytime** — `modal app stop <app-id>` (near-free; the next `make train` | |
| resumes). The bounded pilot also self-stops at its step cap. | |
| - **OOM / step-time too slow** — lower `vllm_gpu_memory_utilization` or | |
| `per_device_train_batch_size` in the config, or bump the GPU | |
| (`GPU=A100-80GB`), then relaunch. These knobs are hash-exempt, so resume still | |
| works. Full-FT of a 1.7B+ model + vLLM needs **A100-80GB**. | |
| - **`--smoke` fails at GRPOTrainer construction** — a (transformers, trl, vllm) | |
| version mismatch; pin an exact pair in `pyproject.toml` and re-smoke (see the | |
| reconciliation note in `training/modal_app.py`). | |
| - ⚠️ **Never edit repo files while a `modal run` image build is in flight** — | |
| Modal bakes the tree and aborts with "… was modified during build process." | |
| ## Evaluation | |
| ```python | |
| from sql_env.evaluation import evaluate, RandomPolicy, OraclePolicy | |
| result = evaluate(env, policy, n_episodes=50, seed=0) | |
| print(f"Accuracy: {result.success_rate:.1%}, Reward: {result.avg_reward:.3f}") | |
| ``` | |
| Results on the curated 10-database Spider subset (each cell is the min–max band | |
| across 2 runs, N=50 episodes per run). These are scores from an internal | |
| apples-to-apples harness (curated subset, hidden-schema agentic exploration, | |
| N=50 episodes) — not a leaderboard score: | |
| | Method | Accuracy | Parse Rate | Avg Steps | | |
| |--------|----------|------------|-----------| | |
| | Zero-shot | 0% | 24-28% | 10.8-12.4 | | |
| | 1-shot | 0-2% | 16-17% | 14.0-14.8 | | |
| | 3-shot | 0% | 19-20% | 13.8-14.8 | | |
| | GRPO v1 (2 epochs) | 28-30% | 95-100% | 3.5-4.0 | | |
| | GRPO v2 (4 epochs) | 24-32% | 87-95% | 3.5-4.0 | | |
| This evaluation is not comparable to the official Spider leaderboard, which uses | |
| different scoring, full-schema input, and a broader database set. | |
| ## Data | |
| 676 questions (473 train, 203 eval) across 10 Spider databases with difficulty labels, | |
| plus 120 multi-turn SFT warmup trajectories generated from gold SQL. See | |
| [docs/data-sources.md](docs/data-sources.md) for provenance, curation, and regeneration. | |
| Data in `data/` is adapted from [Spider](https://yale-lily.github.io/spider) | |
| (Yu et al., 2018) and shared under CC BY-SA 4.0. See [DATA_LICENSE](DATA_LICENSE). | |
| ## Project Structure | |
| ``` | |
| analyst-buddy/ | |
| ├── __init__.py, models.py # Package init + typed wire models (Pydantic) | |
| ├── server/ | |
| │ ├── sql_environment.py # Environment + HarnessError guardrail | |
| │ ├── reward.py # Three-layer reward function | |
| │ ├── verifier.py # Type-aware answer verification | |
| │ └── synthetic/ # Metamorphic data checks | |
| ├── evaluation/ # evaluate(), Random/Oracle policies | |
| ├── training/ # TRL adapter, data loading, env_metrics, viz | |
| ├── scripts/ # Data curation, SFT gen, validate_questions | |
| ├── notebooks/train_grpo.ipynb # SFT warmup + two-phase GRPO | |
| ├── data/{databases,questions,sft}/ # 10 Spider DBs, questions, SFT trajectories | |
| ├── configs/ # Training configurations (CPU, Colab L4) | |
| ├── tests/ # Environment, reward, verifier, guardrail tests | |
| └── docs/ # ARCHITECTURE, RUNBOOK, data-sources, ADRs — see docs/README.md | |
| ``` | |
| **Docs:** [`docs/README.md`](docs/README.md) is the index — architecture, runbook, design | |
| decisions (ADRs), and data provenance. `AGENTS.md` is the navigation map for agents. | |
| ## References | |
| - Yu et al. (2018). [Spider: A Large-Scale Human-Labeled Dataset for Text-to-SQL](https://yale-lily.github.io/spider). EMNLP. | |
| - Shao et al. (2024). [DeepSeekMath](https://arxiv.org/abs/2402.03300). (GRPO algorithm) | |
| - Ng, Harada, Russell (1999). [Policy Invariance Under Reward Transformations](https://people.eecs.berkeley.edu/~pabbeel/cs287-fa09/readings/NgHaradaRussell-shaping-ICML1999.pdf). ICML. | |
| ## License | |
| Code: [MIT](LICENSE). Data: [CC BY-SA 4.0](DATA_LICENSE). | |