Contents
In brief
A user asks in plain English, an LLM generates SQL, DuckDB runs it in-process — the user sees only the answer. A three-stage architecture (context, generation, execution) plus read-only guardrails removes the bottleneck of “the one person who knows SQL.”
What happened
The usual pain: marketing and ops wait for a dashboard or ticket while data already sits in CSV/Parquet from HubSpot, Stripe, or an ERP. The bottleneck is not the database but translating a business question into a query.
DuckDB fits: in-process, no server, reads CSV, Parquet, and JSON as tables immediately. Exports the team already drops on disk become queryable with zero migration.
A reasoning model sits between the question and DuckDB. It needs schema context: column names, types, a few sample rows — all injected into the system prompt before SQL generation.
The UI is Streamlit (~30 lines of Python): input box and result table. The same pipeline can attach to a Telegram webhook for answers inline in chat.
The author defines three explicit stages:
- Context loading — on startup, read the file, infer types, build a metadata block prepended to every prompt.
- Query generation — question + metadata → LLM → SQL string. No execution yet: validate first — only existing columns, no writes/deletes, valid syntax.
- Execution and formatting — DuckDB runs the validated query; results become a table or plain text (message-safe for Telegram).
Stage separation lets you test generation and execution independently — a lesson from production pipelines where flat architectures broke under load.
Why it matters
Natural language analytics lowers the BI barrier: non-technical users get insights without SQL. Reliability, though, comes from prompt design, validation, and metadata — not Streamlit or model choice alone.
Key limits:
- Metadata injection works up to ~50 columns; beyond that, context hits token limits — filter to relevant fields.
- The system prompt must demand SQL only, no markdown or commentary — otherwise validation breaks. A one-shot example in the prompt cuts malformed outputs sharply.
- Telegram round-trip is 3–8 seconds; for simple aggregations a dashboard is faster; for unpredictable questions, the tradeoff is acceptable.
- Security: dynamic SQL needs read-only connections, allowlisting, and output size limits — real risk if exposed beyond a trusted team.
Two-tier routing (fast model for simple aggregations, reasoning model for complex joins) keeps median latency low.
In practice
If you build this agent over flat files:
- Validation layer first — never wire LLM output straight to DuckDB; check column refs against metadata before execution.
- Generate the metadata block at runtime from the actual file, not hardcoded in the prompt.
- Add a “show SQL” toggle so non-technical users can audit how their question was interpreted.
- Connect to n8n or other ETL: the agent becomes the read layer on whatever pipelines already write to disk.
Code volume is modest (~25 lines UI, ~15 DuckDB, ~30 LLM+validation), but time belongs in prompt engineering and guards.
Takeaway
An AI data analyst without SQL is a practical NL→SQL implementation on DuckDB with strict stage separation and read-only safety. Read the original if you want to give the team ad-hoc analytics without a queue to the sole SQL expert.