DuckDB as a Query Layer Over Parquet for AI Agents

DuckDB's agent mode cuts AI query costs by reformatting output for machines instead of humans.

· 10 min read
Cover illustration for “DuckDB as a Query Layer Over Parquet for AI Agents”
Agent-Safe Data Access · October 12, 2026 · 10 min read · 2,263 words

An AI agent working with data behaves nothing like a human analyst sitting at a keyboard. It issues dozens of small queries in rapid succession while it builds up context, and it cannot pause and ask a colleague what a truncated result actually contained. Every byte that comes back costs tokens and time, so an agent's data access pattern is really a cost structure in disguise. Research and data analysis already rank as the second most common agent use case, at 24.4 percent, according to LangChain's State of AI Agents survey, making it the volume driver behind a large share of agent activity today, a share that will keep growing as more teams put agents in front of their data.

Point an agent at a production database directly, and the costs compound fast. Ask two agents the same question on the same day, and they can come back with two different numbers because each one reasoned its way to a slightly different definition. An AWS principal solutions architect noted that predicting and pre-replicating every dataset an agent might need is impractical once agents are embedded broadly into an application. The on-demand, query-production-directly model that works fine for a human running an occasional report breaks down once agents are the ones asking. What agents need is not a faster path to the same production tables, but a data layer built for how they actually work: isolated from transactional systems, fast enough to support dozens of small queries, and consistent enough that the same question always gets the same answer.

Parquet and DuckDB's respective contributions to the solution

Parquet and DuckDB solve two different halves of that problem, and it helps to separate them before looking at what they do together. Parquet is a columnar storage format that stores data column by column. A query that sums one field across millions of records only has to read the bytes for that column, not drag every other field in each row along with it. For the kind of aggregation, filtering, and trend analysis an analytical agent spends most of its time doing, that difference in how data gets read keeps a scan cheap.

DuckDB is the engine that runs those scans. It can query Parquet files directly, with no loading step in between, and it supports the full range of SQL an analytical agent actually needs: joins, aggregations, filters, window functions, time-series transformations, all without sending any of that work back to the source database.

Put the two together and a clear pattern emerges: materialize refined analytical datasets as Parquet files on a schedule, then let DuckDB query those files. The production database keeps doing what it's for, holding transactional truth, while agents get a separate, fast, isolated surface built for analysis. A simplified dataset definition makes this concrete. Take Supabase account records and Stripe charge data as the sources, set a refresh schedule, store the result as Parquet, query it with DuckDB, and attach a stated purpose to the whole thing. The agent that touches this dataset is consuming a defined product, built for the specific analysis it's going to perform, rather than rummaging through raw tables and guessing at what they mean.

Why pre-materialized datasets change what agents can reliably do

The deeper shift here is not about speed. When business logic gets encoded once, into a governed dataset carrying its own metric definitions, dimensions, refresh state, lineage, and permissions, every agent that queries it inherits that logic. That's a different kind of reliability than a faster query gives you.

Take a metric like MRR, NRR, churn, or LTV. A dataset built this way carries more than rows of numbers. It has a stable name and a stated purpose, context about where it came from and what it relates to, defined dimensions and measures, explicit metric definitions, a known refresh state, anomaly state, and permissions attached directly to it, metadata that makes it something other people and other agents can pick up and reuse rather than a one-off query result that dies with the session that produced it.

That reusability follows a lifecycle: a dataset moves from temporary to durable as it proves useful. A dataset built for a single investigation can expire once the investigation ends. One built to feed a dashboard or report sticks around to serve it. And one that proves useful enough gets promoted into something durable: the query definition, the materialized result, the metadata, the lineage, and the refresh behavior all carry forward, so future agents, future metrics, and future workflows can build on it.

The obvious objection is staleness: a dataset refreshed on a schedule hands an agent yesterday's numbers when it might want today's. For the kind of context agents are actually reasoning about, trends, cohort health, revenue trajectory, a dataset refreshed on a defined schedule is more trustworthy than a live query against a production OLTP database, which can return an inconsistent, mid-transaction snapshot that looks complete but isn't. A number that is twenty minutes old and internally consistent beats a number that is live and wrong.

DuckDB's agent mode and the faster, cheaper query loop

This isn't a theoretical architecture. DuckDB v2.0 ships with a native agent mode built into its CLI, and it's a direct response to the fact that terminal output designed for human eyes actively works against a model reading it. When the CLI detects that an AI coding agent is calling it rather than a person, it switches its output format to suit the reader that's actually there.

The default, human-facing output was never built with a model in mind. Padded column borders add visual alignment a human eye appreciates and a model gets no value from. Before a query expected to scan a million rows or more actually runs, the CLI reports that estimate up front, so the agent can decide whether to wait for it. And if a query starts generating a runaway number of output rows, the CLI cuts it off early rather than let it keep pouring tokens into an unbounded result.

Detection runs automatically. The CLI checks for environment variables set by Claude Code, Codex, Cursor, Gemini CLI, and GitHub Copilot, and if one of those is present, stdout is not a terminal, and no explicit output format was requested, agent mode switches on by itself. Two flags, -agent and -no-agent, override that detection in either direction, and the DUCKDB_AGENT_MODE environment variable does the same.

In a controlled test, agent mode cut the tokens that DuckDB's output consumed by roughly 59 percent across 22 TPC-H analytical questions, answered by Claude Code against a scale-factor 100 dataset, with every run landing on the correct answer. The saving is real and repeats on every query an agent runs, so it compounds over a long session even though no single query feels dramatically cheaper. There's also a built-in discovery path for agents that never got configured for this explicitly: if a run fails while writing to a pipe, and no agent environment variable was detected, and neither override flag was set, DuckDB prints a hint to stderr pointing the agent toward the -agent flag.

Diagram: Agent Mode Cuts DuckDB Output Tokens by 59%. Visualizes: Show the token reduction achieved by DuckDB's agent mode in a simple before/after magnitude comparison.

Platform-level adoption of this pattern by major infrastructure providers

What DuckDB has built into its own CLI is a small piece of a much larger shift happening across cloud infrastructure. AWS has embedded the DuckDB analytical engine directly inside Aurora PostgreSQL and acquired DuckLabs B.V., the company behind DuckDB, positioning the combination as a way for applications, agents included, to query live Aurora transactions and historical Parquet records stored on Amazon S3 in a single query, with no ETL pipeline and no duplicated data sitting in between.

The mechanics of that integration show the pattern working at production scale. It's available across every commercial AWS region and AWS GovCloud (US) region at no additional feature charge, with customers paying only for the incremental Aurora compute and S3 requests the queries actually use. The integration runs on Aurora PostgreSQL versions 17.11 and 18.6 and later, turned on through the aurora_analytics extension.

AWS frames the use case for agents directly: combining recent transactional data with years of Parquet history in a single query is what an agent actually needs, and trying to pre-replicate every dataset an agent might ask for in advance simply doesn't scale. That a cloud provider of AWS's size has built this capability into its core database offering, rather than leaving it to third-party tooling, says something about where the architecture is heading. DuckDB layered over Parquet, sitting next to a Postgres-compatible operational database, is becoming a standard shape for analytical infrastructure, and teams that want the same separation of concerns don't need to leave their existing cloud provider to get it.

Connecting agents to a DuckDB-over-Parquet layer via MCP, without production database credentials

Getting an agent to this layer without handing it the keys to production is its own design problem, and the Model Context Protocol, MCP, is the open standard that has emerged to solve it. MCP gives AI models and agents one consistent interface for reaching external tools and data sources. Expose a DuckDB-over-Parquet layer through an MCP server, and an agent gets fast analytical access without ever holding a production database credential.

Every one of those clients reaches the same governed dataset, with the same metric definitions, rather than each building its own interpretation of what the numbers mean.

Governance here goes further than deciding who gets a connection. Attribute-based access control, ABAC, can filter the actual rows a query returns and redact specific fields at runtime. A fraud-detection agent can query transaction amounts and timestamps without ever seeing a payment card number or a customer's name, because the governance layer filters what comes back before the agent ever sees it. That distinction, between restricting access and filtering the return, is what makes ABAC a meaningfully different control than a simple yes-or-no permission.

MCP is the transport, not the source of truth, so a secured connection says nothing about whether the data it carries is correct. Connect an agent through MCP to raw, uncertified tables, and it will still produce confidently wrong answers with total fluency. The pre-modeled Parquet dataset sitting behind the connection, the same governed, single-definition layer described earlier, is what makes it trustworthy. MCP moves the question of access out of the way. It does nothing to answer the question of whether the data being accessed is actually correct.

Agents querying production databases directly as a governance failure, not just a performance problem

Everything above points toward a conclusion that goes past performance. An agent holding a direct connection to a production database can read tables that have nothing to do with its task, and if permissions are even slightly misconfigured, it can write to them. A human analyst who stumbles onto an unfamiliar table pauses and asks what it is. An agent does not pause. It follows its prompt and the permission boundary of its connection, with no judgment in between to catch a mistake before it happens.

The right response is to evaluate every action an agent attempts against policy before it executes, taking into account the content of the request, what happened earlier in the session, and the current business context surrounding it. A static, role-based permission set on a database connection cannot reason about any of that. A governed query layer can, because the check happens at the point of the request.

The structural fix is the architecture already laid out here: read-only Parquet datasets, queried through DuckDB, exposed through MCP. That gives an agent fast, accurate analytical context, and the architecture itself guarantees there is no production write path available to misuse. The protection is built into what the agent can physically reach, rather than depending on a policy being followed correctly every time, which is a stronger guarantee than any procedural rule. Enterprises applying this pattern hold to a simple principle: agents never carry production database credentials in their configuration, every database query an agent makes routes through a gateway that enforces permissions and logs what happened, and that routing is the baseline a working system needs before agents touch production data.

A working implementation of this pattern for a Supabase team without a data team

None of this requires a dedicated data team to build. No separate data warehouse, no ETL platform, no data engineer required to keep it running.

Getting a text-to-SQL agent to work reliably on top of that pipeline takes more than a single well-crafted prompt. The agent needs a loop: inspect the schema, draft a query, check it, run it, read whatever comes back (an error or a result), and retry if it has to. That loop only works well because DuckDB is embedded, so each pass through it costs nothing in network overhead, unlike a loop that has to round-trip to a remote database server on every retry.

The fragmentation most startups deal with is genuinely messy: user data sits in a production Postgres database, payment data lives in Stripe, marketing data lives in HubSpot, and the metrics the business actually runs on live in a spreadsheet somebody updates by hand. A Parquet-and-DuckDB layer with a defined refresh schedule is the mechanism that pulls those sources into one place an agent can actually query. The metrics that matter, MRR, NRR, churn, LTV, get computed once, stored in a form that's queryable with full history and rolling comparisons attached, and served from that single governed layer to dashboards, Slack digests, and AI agents alike, instead of getting reimplemented slightly differently in each place that needs them.

Sources

  1. Agent Mode in the DuckDB CLI

More in Agent-Safe Data Access