AI Data Analyst Agent
Autonomous data analysis agent with DuckDB in-process OLAP, AST SQL safety validation, and post-synthesis numerical faithfulness verification.
Executive Summary // 30-Second Recruiter Brief
Commercial LLM data analysis demos often prompt models to write and execute arbitrary Python code via exec(), exposing severe security vulnerabilities (RCE), non-deterministic crashes, and unverified numerical claims in narrative summaries.
Engineered an interactive analytics agent utilizing 4 constrained deterministic tools (query_data, plot_chart, summary_stats, clarify), an AST SQL filter with 33 disallowed keywords, an automated dataset profiler, and a post-synthesis Numerical Faithfulness Guard verifying cited numbers within 5% tolerance.
1. Problem Formulation & Motivation
Commercial LLM data analysis demos often prompt models to write and execute arbitrary Python code via exec(), exposing severe security vulnerabilities (RCE), non-deterministic crashes, and unverified numerical claims in narrative summaries.
To design a safe, deterministic analytics agent where the LLM functions strictly as a semantic router and parameter extractor, while analytical computations execute through pre-compiled, sandboxed tools.
2. Approach & System Solution
Engineered an interactive analytics agent utilizing 4 constrained deterministic tools (query_data, plot_chart, summary_stats, clarify), an AST SQL filter with 33 disallowed keywords, an automated dataset profiler, and a post-synthesis Numerical Faithfulness Guard verifying cited numbers within 5% tolerance.
3. Architecture & Execution Pipeline
Deterministic analytics pipeline combining automated dataset profiling, constrained tool dispatch, in-memory DuckDB OLAP, and numerical token verification.
AI Data Analyst Agent — Topology
Interactive Visual Data Pipeline · Click any stage to inspect execution state
Interactive web application and Rich terminal interface supporting custom CSV uploads.
Execution Data Flow Sequence
6 Steps- 1User loads the default Superstore Sales dataset (7,500 rows) or uploads a custom CSV.
- 2Dataset Profiler computes schema metadata, missing value distributions, and temporal anchors.
- 3User enters a natural language analytical question.
- 4LLM Router selects exactly 1 of 4 constrained execution tools with structured Pydantic parameters.
- 5Selected tool executes deterministically (DuckDB SQL, Plotly visualization, or statistical profiling).
- 6Response Synthesizer generates an executive narrative; Numerical Faithfulness Guard verifies all quoted numbers against raw tool output.
4. Engineering Decisions & Trade-Offs
Constrained Tool Execution vs Arbitrary Code Generation
Dynamic Temporal Reference Anchoring
5. Implementation Snippet
def validate_sql(self, sql_query: str) -> None:
"""Validates that the SQL query is strictly read-only and safe."""
clean_sql = sql_query.strip()
clean_sql = re.sub(r"--.*?(\n|$)", " ", clean_sql)
clean_sql = re.sub(r"/\*.*?\*/", " ", clean_sql, flags=re.DOTALL).strip()
upper_sql = clean_sql.upper()
if not (upper_sql.startswith("SELECT") or upper_sql.startswith("WITH")):
raise ValueError("Security violation: Only read-only SELECT and WITH (CTE) queries are permitted.")
if clean_sql.count(";") > 1 or (clean_sql.count(";") == 1 and not clean_sql.endswith(";")):
raise ValueError("Security violation: Multiple chained SQL statements are not allowed.")
for forbidden in config.disallowed_sql_keywords:
if re.search(rf"\b{forbidden}\b", upper_sql):
raise ValueError(f"Security violation: Prohibited SQL keyword '{forbidden}' detected.")6. Evaluation & Measured Results
Systematically evaluated on a 20-question ground-truth benchmark spanning aggregations, regional comparisons, descriptive statistics, chart generation, and ambiguous queries with 80% numerical faithfulness.
20 / 20 correct tool routing decisions on ground-truth benchmark dataset
20 / 20 queries executed without runtime crashes or syntax errors
17 / 20 value-level correctness score across complex aggregations and edge cases
Single-turn tool execution latency (compared to ~12s for iterative code-fix loops)
7. Limitations & Production Considerations
- •Multi-hop compound questions requiring chained operations must execute as single flattened SQL queries or trigger clarification.
- •In-memory DuckDB dataset processing is bounded by available system RAM for large CSV files.
Related Technical Essays
Deterministic Guardrails for LLM Agents: Enforcing Safe SQL and Pydantic Schemas without Open-Ended Code Execution
Giving language models unbounded code execution in analytical pipelines opens catastrophic security and hallucination vectors. This guide demonstrates how to architect deterministic, sandboxed agent tools using AST-level SQL inspection, DuckDB read-only boundaries, and Pydantic runtime schema contracts.
In-Process Columnar OLAP with DuckDB: Architecture, Vectorized Execution, and Analytics Engineering
Traditional client-server databases introduce significant serialization overhead for analytical workloads. This deep-dive examines how DuckDB leverages columnar storage, Morsel-driven parallelism, and vectorized SIMD execution to process millions of rows directly in-process with sub-second response times.