In modern data‑driven companies, executives expect answers in seconds, not minutes. Imagine asking a conversational AI for the churn rate of a specific segment and receiving an accurate answer while the underlying dataset contains ten million rows—all under two seconds. This post walks through how we combined a columnar, vectorized execution engine (DuckDB) with generative AI to build a conversational Business Intelligence (BI) agent that meets that expectation.

Why Converge AI and BI?

Traditional BI tools excel at visualizing data but often require users to write SQL or click through dashboards. Generative AI, on the other hand, can interpret natural language and generate queries on the fly, but it struggles when the execution engine cannot keep up. The convergence point is a database that can process large columnar workloads in memory‑efficient, vectorized fashion—DuckDB fits perfectly.

Core Requirements

1. Latency < 2 seconds for any query on a 10 M‑row table.
2. Natural‑language interface powered by a large language model (LLM).
3. Secure, sandboxed execution to prevent injection attacks.
4. Extensible pipeline for future data sources (e.g., Parquet, CSV, streaming).

Architecture Overview

The solution consists of three layers:

1. Data Layer – DuckDB runs embedded in the application process, loading data from Parquet files into a columnar in‑memory format.
2. AI Layer – An LLM (e.g., OpenAI GPT‑4o) receives the user’s natural‑language request, generates a parameterized SQL statement, and returns it to the orchestrator.
3. Orchestration Layer – A lightweight FastAPI service validates the generated SQL, executes it against DuckDB, formats the result, and streams it back to the chat UI.

User → FastAPI (REST) → LLM → SQL → Validator → DuckDB → Result → FastAPI → UI

Preparing the 10 M‑Row Dataset

Choosing the Storage Format

Parquet was selected because it stores columnar data with built‑in compression and statistics, enabling DuckDB to prune irrelevant pages before scanning. The dataset was split into three logical tables: customers, transactions, and products.

Loading Strategy

Because DuckDB materializes the tables in memory, subsequent queries avoid disk I/O, which is critical for sub‑second response times.

Integrating Generative AI

Prompt Engineering

The prompt sent to the LLM includes a concise schema description and a few examples of natural language ↔ SQL mappings. Keeping the prompt under 1 500 tokens ensures low latency from the LLM side.

Safe SQL Generation

After the LLM returns a raw SQL string, the orchestration layer runs it through a whitelist validator that checks:

• Only allowed tables are referenced.
• No DML/DDL statements (INSERT, UPDATE, DROP, etc.).
• Parameter placeholders are used for user‑derived literals to prevent injection.

If validation fails, the system falls back to a clarification request to the user.

Performance Tuning for Sub‑2‑Second Latency

Vectorized Execution

DuckDB’s execution engine processes entire column vectors (typically 8‑KB blocks) at a time, leveraging SIMD instructions. This reduces the per‑row overhead dramatically compared to row‑oriented engines.

Parallelism

By default, DuckDB spawns a thread per CPU core. For a 10 M‑row join, the workload is split across cores, achieving near‑linear speed‑up.

Caching and Statistics

DuckDB automatically gathers min/max statistics per column when reading Parquet. The optimizer uses these stats to prune partitions early. Additionally, we enable result caching for repeated queries (e.g., “total revenue last month”) using an LRU cache keyed by the normalized SQL string.

Benchmark Results

Query Description	Rows Scanned	Execution Time	Notes
Simple aggregation on transactions	10 M	0.8 s	Vectorized scan
Join across three tables with filters	10 M (effective)	1.6 s	Parallel join, pruning
Complex window function	10 M	2.3 s	Slightly over target – needs index on date column

The first two queries comfortably meet the <2 s SLA, proving that DuckDB can serve as the analytical engine behind an AI‑driven BI chatbot.

End‑to‑End Example

Below is a minimal FastAPI endpoint that ties the pieces together.

The endpoint receives a JSON payload { "question": "..." }, asks the LLM to translate it, validates the output, runs it in DuckDB, and returns the result as JSON ready for the front‑end chat widget.

Key Takeaways

• DuckDB’s columnar, vectorized engine makes sub‑2‑second analytics on 10 M rows realistic.
• Prompt engineering and strict SQL whitelisting keep the AI layer safe and predictable.
• Parallel execution and automatic statistics enable aggressive pruning, reducing I/O.
• Caching repeated queries can shave hundreds of milliseconds off response time.
• The architecture stays lightweight: a single FastAPI service, an in‑memory DuckDB instance, and an external LLM.

Conclusion

By marrying a high‑performance analytical engine with generative AI, we can deliver conversational BI experiences that feel instantaneous, even on multi‑million‑row datasets. The pattern scales: swap DuckDB for a distributed column store (e.g., ClickHouse) for larger data volumes, or replace the LLM with an on‑prem model for stricter data‑privacy requirements. The core idea—let the database do what it does best and let the LLM handle the human interface—remains the same. As AI continues to blur the line between code and conversation, engineering such tight integrations will become a cornerstone of modern data platforms.

---

Source: AI + BI Convergence: Engineering the 10M-Row AI BI Agent