AskSherlock
An open-source Genie-style chat over a ClickHouse cost store, where the model never writes SQL.
1 · Purpose
AskSherlock lets a person ask a cost question in plain English and get the number, a chart and the SQL that produced it. It works over a ClickHouse cost store, as an open-source alternative to Databricks AI/BI Genie. The model only turns the question into a small validated intent from a fixed vocabulary; a semantic layer writes the SQL. It runs on a two-week time box, and the public write-up of what worked is the main deliverable.
2 · Business benefits
3 · Status and how to join
Good first tasks this week:
- Write ten gold questions with their expected intent objects.
- Draft the JSON Schema for the intent object.
- Write the value dictionary generator for one dimension.
4 · Requirements
What it must do
- Accept a question in plain English.
- Turn it into an intent object naming measures, dimensions, filters and time grain from a closed vocabulary.
- Validate the intent against a schema and refuse anything invalid before touching the database.
- Resolve user words to real values using generated value dictionaries and synonyms.
- Compile the intent into SQL through a semantic layer and run it.
- Return the number, a chart chosen from the data's shape, and the SQL.
How well it must do it
- The model never writes SQL.
- An evaluation suite of about 120 cases runs in CI and reports intent and execution accuracy.
- Stop rule: below 85% accuracy with clean refusals at the checkpoint, stop building and write up why.
- Light on a laptop: the database is capped in memory and only runs while working.
5 · Architecture, very high level
6 · Tech stack, per platform
| Component | Open stack | Databricks | Azure | AWS |
|---|---|---|---|---|
| Question input | Web panel in an existing Next.js app | — | — | — |
| Intent generator | Open-weight model on Groq | — | Azure AI Foundry models | Amazon Bedrock models |
| Intent validator | JSON Schema | — | — | — |
| Value resolver | Generated YAML value dictionaries | — | — | — |
| Semantic layer | Cube Core or a small hand-written compiler, authored in OSI shape | Unity Catalog Metric Views | — | — |
| Cost store | ClickHouse | — | ClickHouse on an Azure VM | ClickHouse on EC2 |
| Answer view | Next.js charts | AI/BI Genie (the reference being replaced) | — | — |
| Evaluation suite | Table-driven tests + Langfuse | — | — | — |
7 · What needs to be done
Research and decisions 2 of 2 done
| Task | Status | Size | Skill |
|---|---|---|---|
| Open-source landscape and architecture | done | M | research |
| Six decision records: intent not SQL, no graph in v1, value dictionaries, model format | done | M | architecture |
Phase 0 to 1: questions and semantic layer 0 of 4 done
| Task | Status | Size | Skill |
|---|---|---|---|
| Seed gold question set | doing | S | analytics |
| Model measures and dimensions over the main cost table | todo | M | semantic modelling |
| One-day spike: semantic engine versus hand-written compiler | todo | M | SQL |
| Value dictionary generator | todo | S | Python / SQL |
Phase 2 to 3: intents and evaluation 0 of 2 done
| Task | Status | Size | Skill |
|---|---|---|---|
| Intent schema, validator, model call and refusal path | todo | M | LLM engineering |
| Evaluation harness of about 120 cases in CI | todo | M | evaluation |
Phase 4 to 5: UI and write-up 0 of 2 done
| Task | Status | Size | Skill |
|---|---|---|---|
| Answer panel with chart and SQL | todo | M | frontend |
| Public write-up of results | todo | M | technical writing |
8 · Roles
| Role | How many | Hours / week | What they do |
|---|---|---|---|
| Semantic modeller | 1 | 3-4 | Defines metrics, dimensions and joins. |
| LLM engineer | 1 | 2-3 | Builds intent generation, validation and refusal. |
| Evaluator | 1 | 1-2 | Writes gold questions and checks answers. |
9 · What you'll learn
- Why letting a model write SQL fails on real enterprise schemas.
- How a semantic layer turns a fuzzy question into correct SQL by construction.
- How value dictionaries stop silent empty results.
- How to set a stop rule before you start building.
Level-up questions
- Can a semantic engine handle this warehouse's table engine cleanly, or is a small compiler simpler?
- What accuracy is good enough to ship a chat answer?
- Which questions should the system refuse?
10 · Links
- Links come once the work is public.