Building an AI Agent Analytics System on Apache Iceberg Data

Business intelligence has a bottleneck — and it's not the data. It's the humans in the loop.

A product manager wants to know which customer segments had declining retention last quarter. She files a ticket. Three days later, an analyst builds a dashboard. The answer arrives after the decision window has closed. Meanwhile, the data team drowns in ad-hoc requests: one-off Slack questions, "quick" pivot tables, executive requests for numbers that should be self-service but never are.

This is the BI bottleneck that has persisted for a decade despite billions invested in dashboarding tools, semantic layers, and self-service platforms. The problem was never data availability — it was the translation layer between a business question and a SQL query. Every question required a human who understood both the business domain and the data model.

AI agents are eliminating that translation layer. Conversational analytics — where business users ask questions in natural language and receive answers in seconds, backed by real SQL against real data — has moved from prototype to production in 2026. Google Cloud's Conversational Analytics is now generally available across BigQuery, Looker, and Apache Iceberg tables via REST catalogs. Databricks Genie lets executives ask what they want to know in plain language. ThoughtSpot's Spotter Semantics ships a deterministic query engine and an MCP server for any AI agent. The message from every major vendor is the same: the question is becoming the interface.

And the data backs it up. Gartner predicts 60% of agentic analytics implementations will fail without a governed semantic layer. Deloitte reports 25% of companies using generative AI piloted agentic systems in 2025, expected to reach 50% by 2027. Forrester's 2026 evaluation argues the data lakehouse is becoming an execution layer for agentic AI, where agents need continuous access to trusted, governed, real-time context.

Analytics is where most organizations first encounter the AI agent + data use case. Not ML training, not ETL orchestration, not data quality monitoring — analytics. Because every department has questions, every question translates to SQL, and every SQL query needs infrastructure underneath it.

But there's a foundational question that most conversational BI implementations gloss over: what does the analytics infrastructure underneath the agent actually look like?

An agent that generates SQL needs more than an LLM and a database connection. It needs to discover what data exists. It needs to choose the right engine for each query. It needs guardrails that prevent expensive mistakes. And it needs a data layer that stays fast, open, and well-maintained as analytics workloads scale.

This guide covers how to build that system on Apache Iceberg — the open table format that provides the ideal foundation for AI-powered analytics — and how LakeOps provides the infrastructure control plane that makes it production-ready. LakeOps sits between agents and your Iceberg tables: its multi-engine query routing (via QueryFlux) ensures each query hits the optimal engine, its MCP server gives agents tools to discover data and generate queries, and its guardrails prevent expensive full-table scans.

Why Apache Iceberg Is the Ideal Foundation for AI Analytics

Before diving into architecture, it's worth understanding why Iceberg — rather than a proprietary warehouse or a raw data lake — is the right substrate for an AI analytics system.

Open Format, Multi-Engine Access

AI analytics is evolving rapidly. The agent framework you use today may not be what you use in 18 months. The LLM behind your SQL generation will certainly change. Iceberg's open specification means your data layer is decoupled from every other component in the stack — you can swap agents, models, engines, and visualization tools without migrating a single byte of data.

This matters for analytics specifically because the query patterns are unpredictable. An agent might need Trino for an interactive dashboard query, Spark for a historical trend analysis, and DuckDB for a quick ad-hoc exploration — all on the same tables. Iceberg's multi-engine access model makes this possible without data duplication.

Time Travel, Schema Evolution, and Partition Pruning

Some of the most valuable business questions are temporal: "How did our conversion rate change after the new pricing page?" Iceberg's snapshot-based architecture provides built-in time travel, letting agents query data as it existed at any historical point — unlocking change analysis, audit trails, and decision reconstruction.

Iceberg also handles schema evolution natively — adding columns, renaming fields, widening types without rewriting data. An AI analytics agent discovers the current schema at query time rather than depending on a static data model that drifts from reality.

And for performance: Iceberg's hidden partitioning and partition pruning — combined with column-level statistics in Puffin files — let engines skip irrelevant data files entirely. A question like "What's our revenue by region for the last 12 months?" against a multi-terabyte orders table gets sub-second responses instead of minutes of full-table scanning.

The Current BI Pain — and Why Agents Change Everything

Before detailing the architecture, it's worth naming the specific dysfunction AI analytics solves.

Business users wait days for dashboard changes. A VP of Marketing wants marketing-qualified leads broken down by source channel on the weekly report. The request goes to the BI team's backlog, competes with 40 other requests, and ships in two weeks. The campaign she wanted to evaluate ended nine days ago.

Analysts are overwhelmed with ad-hoc requests. A senior data analyst spends 60% of their week fielding Slack questions: "What was our renewal rate for enterprise accounts?" "Can you pull churn numbers by cohort?" Each answer requires writing a query, validating results, and formatting for a non-technical audience. The analyst was hired to find patterns and build models. Instead, they're a human SQL interface.

Data engineers are building one-off queries instead of infrastructure. When the analytics team can't keep up, requests overflow to data platform. Engineers who should be building pipelines are writing ad-hoc SELECT statements for the CFO.

AI agents connected to a well-maintained Iceberg lakehouse dissolve this bottleneck. Business users ask questions directly and get answers in seconds, with the right engine handling each query. The analyst's role shifts from report-builder to analytics architect. The data engineer goes back to building infrastructure.

But this only works if the infrastructure underneath the agent is designed for the workload. Dashboard BI sends a few hundred queries per day with stable shapes. Agent analytics sends thousands per day with unpredictable shapes, chained in reasoning loops where each step depends on the previous result. The infrastructure must be fast, safe, cost-efficient, and adaptive.

The Agent Analytics Architecture

Building a production AI analytics system on Iceberg requires five layers, each solving a distinct problem in the pipeline from natural language question to actionable insight.

Layer 1: Discovery — The Agent Understands What Data Exists

The first thing an analytics agent must do is understand your data landscape. Without context on what tables exist, what columns mean, and how data is organized, the agent hallucinates table names, guesses at column semantics, and generates plausible but wrong SQL.

This is where most naive implementations fail. Giving an agent a database connection and a system prompt that says "query these tables" produces an agent that invents table names and runs SELECT * on multi-terabyte tables. Research confirms this: a peer-reviewed study in Frontiers in Big Data showed that a schema-aware text-to-SQL baseline only achieves 67% accuracy on Iceberg queries, while a semantically enriched agent with structured discovery tools achieves 100% on the same query set.

A well-designed discovery layer provides structured tools rather than raw SQL access. LakeOps exposes an MCP (Model Context Protocol) server that any compatible agent can connect to — Claude, LangChain, Cursor, or custom builds. The MCP server provides 27 purpose-built tools, including:

• list_catalogs enumerates all Iceberg catalogs with table counts, total size, and health status. The agent starts here to understand the data landscape.
• search_tables filters tables by name, catalog, namespace, or health status. An agent asked "What's happening with our marketing data?" can search for tables in the marketing namespace without knowing exact names.
• get_schema returns column names, types, partition specs, sort orders, and identifier columns for a specific table — everything the agent needs to write accurate SQL.
• get_table_metadata provides full Iceberg metadata including snapshot statistics and AI context, enabling the agent to understand not just what data exists, but how it's organized and how it's changing.

This structured discovery eliminates hallucination. Instead of guessing, the agent calls list_catalogs → search_tables → get_schema in sequence, building an accurate model of the data before writing a single line of SQL.

Layer 2: Query Generation — Natural Language to SQL with Context

Once the agent understands the data, it translates the user's question into SQL. This is where the quality of the discovery layer pays off — an agent with accurate schema context generates dramatically better SQL than one working from a stale system prompt.

The key insight is that SQL generation is not a single-shot operation. A question like "Which product categories had declining margins last quarter?" triggers a chain of queries:

1. 1.Schema discovery to find the right tables (products, orders, costs)
2. 2.Sampling to understand column distributions and data quality
3. 3.An aggregation query to compute margins by category
4. 4.A comparison query to identify quarter-over-quarter decline
5. 5.Drill-down queries to understand what's driving the decline
6. 6.Validation queries to cross-check against alternative data sources

A single user question can produce 15–30 SQL statements. This is fundamentally different from traditional BI where a dashboard query is written once by an analyst and executed thousands of times. Agent analytics queries are generated dynamically, executed in chains, and adapted based on intermediate results.

This query chain pattern — what practitioners call the ReAct (Reasoning + Acting) loop — has profound implications for the infrastructure underneath. Every query must be fast (latency compounds through the chain), safe (no single query in the chain should scan petabytes), and cost-efficient (30 queries per interaction at $0.08 each adds up fast across thousands of daily interactions).

Layer 3: Engine Routing — The Right Engine for Every Analytical Query

Here's where most analytics architectures miss a critical optimization: not every query in the chain should run on the same engine. In a typical Iceberg deployment, you run 2–5 engines on shared tables. Without intelligent routing, every query hits the same engine regardless of its shape — you either overpay or wait too long.

LakeOps multi-engine query routing solves this by exposing a single SQL endpoint that transparently routes each query to the optimal engine. Powered by QueryFlux — an open-source, Rust-based SQL proxy with ~0.35ms P50 overhead — the routing layer dispatches queries based on cost, latency, or workload type.

Consider what this means for a single analytics conversation. A business user asks the agent: "What was our revenue last quarter?"

• The agent calls search_tables to find the orders table → served from metadata cache, 0ms engine cost
• "What was revenue last quarter?" → Trino (fast interactive aggregation, sub-second on compacted data)
• "Now compare revenue trends over the last 3 years" → Spark (heavy aggregation across 36 months of historical data, distributed scan)
• "Quick — what are today's numbers so far?" → DuckDB (tiny selective query, instant, sub-100ms, zero infrastructure cost)

Three engines, one conversation, automatic routing. The user never knows. The agent never decides. QueryFlux examines each query's shape and dispatches it to the engine that fits.

The routing decision uses a three-router stack designed for the mixed workload that analytics agents produce:

1. 1.Adaptive router — for the ~80% of agent queries that are parameterized templates (the same aggregation shape with different date ranges or filters). Routes based on accumulated performance statistics across engines. Decision cost: 0ms from in-memory cache.

1. 1.LLM router — for novel query shapes the adaptive router hasn't seen. A language model reasons about optimal engine placement using live table statistics and engine capabilities. Cached by query template, so subsequent executions of the same shape skip the LLM.

1. 1.Semantic router — for rare query shapes where the LLM router's confidence is low. Finds the most similar previously-routed query using local embeddings and mirrors its routing decision.

Routing groups organize this cleanly. An analytics routing group might pair Trino + DuckDB for SELECT and AGGREGATE queries at high priority, while a separate reporting group routes to Snowflake + ClickHouse for scheduled dashboard refreshes. Each group gets its own stable endpoint URL, engine pool, and guardrail configuration. Agents connect to their endpoint and inherit everything automatically.

Layer 4: Guardrails — Preventing Expensive Mistakes

Giving AI agents SQL access to production data is powerful — and dangerous. Analytics queries are particularly risky because they're exploratory by nature — you can't whitelist every possible query. You need layered guardrails that enforce safety without blocking legitimate analytics.

LakeOps provides five composable guardrails that sit between routing and engine dispatch:

ReadOnlyGuard blocks DDL and DML statements (INSERT, UPDATE, DELETE, DROP) for agent sessions. Uses SQL parsing rather than string matching, catching mutations hidden in CTEs and function calls.

RowLimitGuard injects LIMIT N when a query lacks one — preventing agents from producing multi-gigabyte result sets during reasoning loops.

CostEstimateGuard runs EXPLAIN before execution and rejects queries exceeding a scanned-bytes threshold. This is the critical guardrail for analytics. An agent that writes SELECT * FROM orders JOIN products without a join condition would produce a cartesian product scanning terabytes — CostEstimateGuard catches it before a single byte is scanned. This guardrail is the difference between a $500/month analytics bill and a $50,000 surprise.

PIIMaskGuard rewrites queries to protect sensitive columns via three strategies: exclude (remove column), hash (SHA256 pseudonyms), or null out (returns NULL). This prevents PII from entering the LLM context window — a GDPR and CCPA compliance requirement.

HumanApprovalGuard pauses high-stakes queries for human review via Slack or email — triggered by pattern, cost threshold, or table sensitivity.

A typical analytics agent guardrail stack:

1groups:2  - name: analytics-agent3    guards:4      - type: read_only5      - type: row_limit6        max_rows: 50007        inject_if_missing: true8      - type: cost_estimate9        max_scanned_bytes: 10_000_000_00010      - type: pii_mask11        sensitive_columns:12          "users.email": hash13          "users.ssn": exclude14          "payments.card_number": null_out

Every guard action is logged — what fired, what was rewritten, what was rejected — providing full auditability. Guard evaluations feed back into routing decisions: if a query is frequently rejected by the cost guard on one engine, the router learns to dispatch that shape to a more cost-efficient backend.

Layer 5: Result Interpretation and Follow-Up Reasoning

The final layer is where agent analytics diverges most sharply from traditional BI. A dashboard displays query results. An analytics agent interprets them.

When the agent returns "Marketing spend efficiency declined 23% in Q3," it doesn't stop there. It explains what drove the decline, suggests hypotheses, generates follow-up queries to test them, and produces a narrative a non-technical stakeholder can act on.

This reasoning loop is where multi-engine routing pays off most. The initial aggregation runs on Trino for speed. The drill-down queries route to DuckDB for cost efficiency. A historical comparison pulls from Spark for the large backfill scan. Each query in the chain hits the optimal engine, and the agent threads the results together into a coherent analysis — all within seconds.

A Complete Analytics Session: From Question to Insight

To see the full architecture working as a system, walk through a realistic end-to-end analytics session. A VP of Sales opens the analytics agent and asks:

"How are our enterprise deals trending compared to last year, and which reps are outperforming?"

Step 1: Discovery (200ms). The agent calls list_catalogs, search_tables, and get_schema to map the CRM data landscape — finding crm_prod.sales.deals, crm_prod.sales.opportunities, and crm_prod.sales.reps.

Step 2: Current quarter aggregation (0.8s). Enterprise deals by rep for Q3 2026. QueryFlux routes to Trino — interactive aggregation on compacted, date-partitioned data.

Step 3: Year-over-year comparison (2.1s). Same aggregation for Q3 2025 and trailing twelve months — 14 months of data. QueryFlux routes to Spark for distributed scan. The CostEstimateGuard validates ~45 GB, well under threshold.

Step 4: Rep ranking (0.3s). Percentile rankings with win rates — complex window functions on a small result set. Routes to DuckDB at sub-100ms.

Step 5: Drill-down (0.6s). Deal-level detail for top 5 reps. Routes to Trino. The RowLimitGuard injects LIMIT 500.

Step 6: Trend data (0.2s). Monthly pipeline values for chart generation. Routes to DuckDB.

Step 7: Interpretation. The agent synthesizes: "Enterprise deals are up 18% YoY by volume but down 6% by average contract value. Reps Sarah Chen, Marcus Williams, and David Park are outperforming by close rate (62%, 58%, 55% vs. team average of 41%). The decline in ACV is concentrated in the mid-market upsell segment — worth investigating whether the new pricing tier is cannibalizing larger deals."

Total: 7 steps, 3 engines, 4.2 seconds, $0.12. On a single engine, the same chain would take 15–20 seconds and cost $0.45+. The VP gets an analyst-grade answer before they could have typed the Slack message to request it.

The Closed-Loop: How Analytics Agents Make the Lake Faster

The most powerful property of this architecture is that it's self-improving. Agent analytics queries feed back into every optimization layer:

Analytics patterns drive compaction sort orders. When agents consistently filter on customer_segment and order_date, LakeOps detects those patterns and physically re-sorts data to match. Queries that scanned terabytes now skip 90% of the data via min/max pruning.

Hot analytics tables get prioritized maintenance. Tables receiving heavy agent traffic are compacted more frequently. LakeOps monitors per-table query latency and elevates compaction priority where agents experience degradation.

Compaction expands routing options. A table with 50,000 small files forces even simple queries onto heavy distributed engines. After compaction into a few hundred sorted files with lean manifests and Puffin statistics, the same query runs on DuckDB in sub-second time. The routing layer detects this shift and dispatches more traffic to cheaper engines automatically.

Agent query telemetry improves routing over time. Every query contributes to the adaptive router's performance database. Within days, the router learns which engine is fastest and cheapest for each recurring template.

This closed loop — agents query → routing optimizes → compaction adapts → more engines become viable → routing optimizes further — means the system gets faster and cheaper the more it's used. Production deployments report agent query P95 latency dropping from 5–10 seconds to under 500ms, with per-query cost reductions of 65%.

LakeOps as the Analytics Infrastructure Control Plane

Stitching together an AI analytics system from individual components — a separate compaction tool, a standalone proxy, custom guardrail middleware — is possible but operationally expensive. The components don't share context: the routing layer doesn't know which tables are degraded, the compaction engine doesn't know which columns agents filter on, and the guardrails don't feed cost data back into routing decisions.

LakeOps provides all five layers as a unified control plane:

• MCP server with 27 purpose-built tools for agent discovery, analysis, and governance — any MCP-compatible agent connects with zero integration code
• Multi-engine routing via QueryFlux with cost, latency, and throughput strategies per routing group — supporting Trino, Spark, DuckDB, Snowflake, Athena, StarRocks, ClickHouse, and Flink
• Agentic AI guardrails — ReadOnly, CostEstimate, PIIMask, RowLimit, and HumanApproval composable per endpoint
• Autonomous table maintenance — compaction, snapshot expiry, orphan cleanup, and manifest rewriting driven by health signals, not schedules, with a Rust + DataFusion engine that's 95% faster and 90% cheaper than Spark-based compaction
• Observability — per-agent cost attribution, guardrail audit logs, routing metrics, and session replay for debugging agent reasoning chains

The critical differentiator is shared context. Agent query patterns inform compaction sort orders. Table health signals influence routing decisions. Guardrail firing rates feed back into cost thresholds. Everything optimizes toward the same objective: fast, safe, cost-efficient analytics.

Setup takes approximately 10 minutes. Connect your existing catalogs (AWS Glue, REST/Polaris, Nessie, Gravitino, S3 Tables) and engines (Trino, Spark, Snowflake, DuckDB, Athena, Flink). No agents to install, no data to move, no pipelines to modify. Agents connect via standard Postgres, MySQL, or Arrow Flight wire protocols — no custom SDK needed.

Beyond Dashboards: Autonomous Analytics Agents

The architecture described so far supports reactive analytics — a human asks a question, the agent answers it. But the same infrastructure enables something more powerful: proactive, autonomous analytics agents that surface insights before anyone asks.

Consider an agent that runs continuously, monitoring key business metrics:

• Anomaly detection — the agent queries revenue, conversion rates, and churn metrics on a schedule. When a metric deviates, it automatically investigates: drilling into segments, comparing time periods, checking for correlated changes.
• Automated reporting — instead of dashboards that go stale, the agent generates a weekly business review narrative: what changed, why it changed, what to watch — generated fresh from current data with SQL backing for every claim.
• Alert enrichment — when a monitoring system fires ("revenue dropped 15% hour-over-hour"), the analytics agent runs root-cause analysis before a human sees the alert. By the time the on-call person opens the notification, the agent has identified the drop is concentrated in APAC and correlated with a payment processor timeout.
• Self-service follow-ups — after presenting findings, the agent suggests related questions: "Would you like to see this broken down by customer tier?" Each follow-up triggers a new query chain that routes optimally across engines.

These patterns are possible today with the right infrastructure. The LakeOps MCP server provides the structured tool interface agents need. Multi-engine routing ensures even high-frequency autonomous agents run cost-efficiently. Guardrails prevent runaway costs. And autonomous table maintenance keeps data fast regardless of query volume.

As documented in the LakeOps blog on AI agents and Iceberg, the compound effect is transformative. As storage optimizes for agent patterns, more engines become viable. As routing improves, costs drop. As costs drop, more use cases become feasible. The system scales not by adding infrastructure, but by getting smarter about how it uses what it already has.

Getting Started

If you're building an AI analytics system on Iceberg, here's a practical starting path:

Step 1: Get your tables healthy. An analytics agent hitting a table with 200,000+ small files will timeout regardless of how good the SQL generation is. Enable continuous compaction, snapshot expiration, and manifest consolidation. This is a prerequisite — uncompacted tables pay a 5–10x latency penalty that no amount of routing or prompt engineering compensates for.

Step 2: Deploy guardrails before the first analytics query. Start with ReadOnlyGuard and RowLimitGuard on every agent session. Add CostEstimateGuard with a conservative threshold (10 GB scanned). The cost of deploying guards too early is zero. The cost of deploying them too late is one bad query that scans your entire lake.

Step 3: Enable multi-engine routing. Connect your engines and configure routing groups per workload type. Even two engines — Trino for interactive queries and DuckDB for quick lookups — can cut analytics costs by 50% compared to routing everything through a single warehouse.

Step 4: Connect your analytics agent via MCP. Point your agent at the LakeOps MCP endpoint — any MCP-compatible framework works with zero integration code. The agent inherits structured discovery, routed query execution, and the full guardrail stack automatically.

Step 5: Monitor and iterate. Use per-agent cost attribution and routing metrics to identify expensive reasoning chains. Adjust guardrail thresholds based on what fires. Let the closed-loop system tune compaction and routing as analytics patterns emerge.

The BI bottleneck isn't inevitable. The infrastructure to eliminate it — open data formats, intelligent query routing, composable guardrails, and autonomous optimization — exists today. The question isn't whether AI agents will replace the dashboard-request-queue workflow. It's how quickly your analytics infrastructure adapts to support them.