Natural language interface for dbt-modeled PostgreSQL data warehouses.
Ask a question in plain English. Get back validated SQL, query results, and a natural-language explanation — no aggregation in the AI layer, no hallucinated column names.
Most NL2SQL systems hand the LLM a raw schema and hope it writes correct GROUP BY logic. This one doesn't.
dbt aggregates. The AI only queries.
All GROUP BY, JOIN, SUM, COUNT, and AVG logic lives in dbt mart models — pre-computed, tested, and documented. The AI generates only SELECT ... FROM mart.* WHERE ... ORDER BY ... LIMIT ... queries. When a question arrives, the answer is already in the mart table; the LLM just needs to filter it correctly.
-- What the LLM is NOT allowed to generate
SELECT customer_id, SUM(payment_value) AS clv
FROM raw.orders
GROUP BY 1
-- What it generates instead
SELECT customer_id, customer_lifetime_value
FROM mart_customers
ORDER BY customer_lifetime_value DESC
LIMIT 10This constraint keeps the SQL validator simple, makes hallucinated aggregations structurally impossible, and makes the value of data modeling visible — without the mart tables, the AI cannot answer.
LLM context comes from dbt docs generate rather than raw INFORMATION_SCHEMA. The manifest includes human-written column descriptions, test definitions, and meta.filterable hints — the AI only sees columns that are documented and tested.
Streamlit console ── pure HTTP client, no logic
│ live pipeline trace · generated SQL · Superset tab
▼
┌─────────────────────────────────────────────────┐
│ FastAPI Gateway │
│ POST /query · POST /query/stream (SSE) │
│ GET /health · POST /dashboard/guest-token │
│ structured JSON logging · request-ID │
└────────────────┬────────────────────────────────┘
│
▼
┌────────────────────────────────────────────────┐
│ LangGraph Workflow │
│ │
│ [SQL Generator] ←── dbt manifest.json │
│ ↓ (curated schema context │
│ ↓ + prompt from YAML) │
│ [SQL Validator] ←── 3-layer AST validation │
│ ↙ ↘ │
│ valid invalid ──→ back to generator │
│ ↓ (max 2 retries, then │
│ ↓ [Error Handler]) │
│ [SQL Executor] ←── analyst_ro role │
│ ↓ (read-only, 500 row cap) │
│ ↙ ↘ │
│ ok failed ──→ [Error Handler] │
│ ↓ │
│ [Result Formatter] ──→ SQL + rows + NL text │
└────────────────────────────────────────────────┘
│
▼
┌────────────────────────────────────────────────┐
│ PostgreSQL │
│ raw schema (Olist) · dbt marts │
│ analyst_ro: SELECT only · no DDL/DML │
└────────────────────────────────────────────────┘
Every node emits an SSE event on /query/stream, so the client watches the SQL
get generated, rejected, self-corrected and executed in real time rather than
waiting on a single blocking response.
| dbt | Apache Superset | AI Copilot | |
|---|---|---|---|
| Role | Data foundation | Business intelligence | Natural language interface |
| Consumers | BI + AI layers | Business users | Anyone |
| Output | Tested mart tables | Dashboards & charts | SQL + query results |
| Key constraint | Single source of truth for all metrics | Read-only superset_ro role |
Cannot aggregate — queries dbt marts only |
dbt is the heart. Both the BI layer and the AI layer consume the same mart models — metric definitions live in one place, tested once.
| Layer | Technology |
|---|---|
| Backend | Python 3.13+, FastAPI, Uvicorn, psycopg (async) |
| AI Workflow | LangGraph, LangChain, OpenAI API |
| Data Modeling | dbt Core 1.9+, dbt-postgres |
| SQL Validation | sqlglot (AST-level parsing) |
| Database | PostgreSQL 17 |
| Observability | Langfuse, structured JSON logging |
| BI | Apache Superset (dashboard mart layer, embedded via guest token) |
| Infra | Docker Compose, uv, multi-stage Dockerfile |
| UI | Streamlit (SSE console) |
| Quality | mypy (strict), ruff, pytest |
The warehouse runs on the Olist Brazilian E-Commerce dataset — 100k+ orders, 9 source tables, open license. A trimmed, FK-consistent ~15k-order sample is committed under data/seed/ so make up populates the warehouse with no Kaggle download; point OLIST_DATA_DIR at the full dataset to load everything.
data/seed/ (committed sample) · data/raw/ (full Kaggle dataset, optional)
│
▼
models/staging/ ← type casting, column renaming, 1:1 with sources
│
▼
models/intermediate/ ← join orchestration, business logic
│
▼
models/marts/
├── ai/ ← consumed by the AI copilot (tagged [ai, mart])
│ ├── mart_customers.sql (1 row/customer: LTV, segment, avg review)
│ ├── mart_orders.sql (1 row/order: revenue, delivery days, payment type)
│ ├── mart_sellers.sql (1 row/seller: orders, revenue, on-time %)
│ ├── mart_product_categories.sql
│ ├── mart_monthly_revenue_by_category.sql
│ └── mart_payment_behavior.sql
├── core/ ← fact/dimension tables for analysts
└── dashboard/ ← pre-aggregated reports for Superset
Every model has a schema.yml with column descriptions, not_null/unique tests, and meta.filterable hints that flow directly into the LLM prompt context.
Superset connects to PostgreSQL via a dedicated superset_ro read-only role and queries the dashboard mart subdirectory — pre-aggregated tables purpose-built for chart performance.
The separation from the AI marts is intentional: dashboard marts are denormalized for fast GROUP BY at query time in Superset, while AI marts are denormalized so the AI never needs to GROUP BY at all. Same dbt foundation, different mart shapes for different consumers.
superset_ro has SELECT access on main_marts only — no raw schema, no intermediate models, no DDL.
A native dashboard export is committed under infra/superset/assets/dashboards/ and imported automatically by the superset-import one-shot (make up-dashboard), gated on the dbt build so it renders against real data. The importer rebuilds the connection sqlalchemy_uri from .env at import time, so the exported (masked) password and host are irrelevant — the dashboard just works on a fresh clone.
Natural language questions flow through a LangGraph workflow: SQL generation → validation → execution → result formatting, with a dedicated error handler as the terminal path for anything that fails. Schema context is not a separate node — the generator node pulls a curated view of the dbt manifest (only ai-tagged marts, with column descriptions and meta.filterable hints) straight into its prompt, so the model never sees a raw INFORMATION_SCHEMA dump.
The retry loop is a conditional edge, not a wrapper: a rejected query routes back to the SQL generator with the validator's structured error attached, up to 2 retries, then falls through to the error handler for a graceful response.
The validator runs before any query reaches PostgreSQL, using sqlglot to parse SQL into an AST — string matching is not used.
| Layer | What it checks |
|---|---|
| Write guard | Rejects anything that isn't a SELECT. INSERT, UPDATE, DELETE, DDL → rejected. |
| Aggregation guard | Rejects GROUP BY, JOIN, SUM, COUNT, AVG at the AST node level. |
| Schema check | Validates table and column references against the dbt manifest. |
On failure, a structured error message feeds back to the SQL generator node (max 2 retries before graceful error).
A thin HTTP client over the API — all logic stays server-side. Two tabs:
Copilot — ask a question, watch the pipeline work. Because the UI consumes
/query/stream, each node reports as it happens: "Generating SQL — attempt 1",
"↩ Rejected: unknown column — retrying", "Executed ✓ — 42 rows". When the run
finishes, a persistent panel keeps the generated SQL, the model's rationale for
writing it that way, the self-correction count, and the row count on screen.
Results render as a table plus an optional bar/line chart when the shape is a
clear label + measure.
Dashboard — the same dbt marts as a governed Superset dashboard, embedded
with a short-lived guest token minted server-side (POST /dashboard/guest-token)
and scoped to the read-only Embedded_Guest role. No admin surface, no edit.
The self-correction trace is the point: a failed validation is not hidden, it is the part of the run worth showing.
- Read-only PostgreSQL role (
analyst_ro): even if the validator is bypassed, PostgreSQL rejects writes at the role level - Schema-based access control:
analyst_rohas SELECT onmain_martsandmain_stagingonly; raw schema is not exposed - Row cap + timeout: 500-row LIMIT applied automatically; 10-second statement timeout enforced via connection option
- Secret handling: credentials stored as
pydantic.SecretStr, never logged
Every workflow run is traced in Langfuse — LLM calls, token usage, latency, SQL generated, validation outcome, cache hit/miss. Structured JSON logs include request-ID correlation.
{
"timestamp": "2025-01-15T10:23:41.123Z",
"level": "INFO",
"message": "sql_validator: passed",
"request_id": "req-abc123",
"model": "mart_customers",
"elapsed_ms": 12
}| Phase | Status | Description |
|---|---|---|
| Data Foundation | ✅ Done | PostgreSQL 17, auto-seeded Olist sample, analyst_ro + superset_ro roles |
| Docker Pipeline | ✅ Done | make up chains seed → dbt build → API; Superset dashboard auto-import |
| dbt Modeling | ✅ Done | 26 models: 7 staging → 2 intermediate → 17 marts (ai / core / dashboard) |
| Query Engine | ✅ Done | SQL executor, manifest parser, 3-layer AST validator |
| LangGraph Workflow | ✅ Done | Generator → validator → executor → formatter, conditional retry + error handler |
| FastAPI Endpoints | ✅ Done | POST /query, POST /query/stream (SSE), GET /health, POST /dashboard/guest-token |
| Streamlit Console | ✅ Done | Streaming pipeline trace, generated SQL + rationale, results table + chart |
| Superset Embedding | ✅ Done | Guest-token embedding scoped to the read-only Embedded_Guest role |
| Observability | ✅ Done | Langfuse tracing, structured JSON logging with request-ID correlation |
| Query Cache | ⏳ Planned | Hash the normalized question, skip the LLM on a hit — not built yet |
| Dataset Bootstrap | ⏳ Planned | Script the full Kaggle download; today the committed sample covers make up |
Prerequisites: Docker, uv
git clone https://github.com/melisklc0/analytics-copilot
cd analytics-copilot
cp .env.example .env # optional: add your OpenAI API key for the AI copilot
make up # core: Postgres → seed sample → dbt build → API + consoleThat single command bootstraps a populated warehouse: a committed, FK-consistent
Olist sample (~15k orders) seeds automatically, then dbt builds every mart — no Kaggle
download needed. The seed is idempotent, so re-running make up never clobbers data.
| Service | URL |
|---|---|
| Streamlit console | http://localhost:8502 |
| API + OpenAPI docs | http://localhost:8090/docs |
Superset (with make up-dashboard) |
http://localhost:8088 |
Langfuse (with make up-observability) |
http://localhost:3000 |
The console's Dashboard tab needs Superset, so start with make up-dashboard
if you want the embedded BI view; the Copilot tab works on core alone.
Optional stacks (opt-in via Compose profiles, kept out of the default to stay light):
make up-dashboard # + Superset BI (:8088)
make up-observability # + Langfuse tracing (:3000)
make up-all # core + Superset + Langfuse
make down # stop everything (keeps seeded data)
make down-volumes # stop + wipe volumes (fresh re-seed next time)Full Kaggle dataset instead of the sample — download CSVs into
data/raw/olist-dataset/, then:
OLIST_DATA_DIR=data/raw/olist-dataset SEED_FORCE=1 make upLocal (non-Docker) workflow:
make seed # load the sample into a running PostgreSQL
make dbt-pipeline # deps → run → test → docs generate
make run # FastAPI on :8090 with hot reload
make test # pytestcurl http://localhost:8090/health
# {"status": "ok", "service": "analytics-copilot-api"}Local development:
uv run ruff check .
uv run ruff format --check .
uv run mypy src/analytics_copilot/
uv run pytestCI runs all four gates on every push to main and every PR.
src/analytics_copilot/
├── app.py # FastAPI factory + lifespan manager
├── api/ # Routers, exception handlers
├── core/ # Pydantic Settings, custom exceptions
├── observability/ # Structured JSON logging, Langfuse integration
├── schemas/ # Pydantic API contracts
├── services/
│ ├── manifest_parser.py # dbt manifest.json → LLM schema context
│ ├── sql_validator.py # 3-layer AST-based SQL validation
│ └── sql_executor.py # Async PostgreSQL executor (read-only)
└── workflow/ # LangGraph graph + nodes
streamlit/
└── app.py # SSE console — pure HTTP client, no business logic
dbt/
├── models/staging/ # 7 source-aligned views
├── models/intermediate/ # 2 business-logic views
└── models/marts/ # 17 analytics-ready tables (ai / core / dashboard)
infra/
├── postgres/init.sql # Schema DDL + role-based access control
├── api/Dockerfile # Multi-stage build, non-root user
├── pipeline/ # One-shot seed + dbt build jobs (bootstrap chain)
├── superset/ # BI stack + auto-imported dashboard export
└── langfuse/ # Self-hosted LLM observability (observability profile)
scripts/
├── make_sample.py # Generate the committed FK-consistent Olist sample
└── load_olist.py # Seed raw.* from the sample (or full dataset)
tests/
├── unit/services/ # SQL validator, executor, manifest parser, Superset
├── unit/workflow/ # Each node, the compiled graph, SSE streaming
└── *.py # API (health, query), logging, tracing
Apache 2.0