Agents are increasingly supplementing or replacing human data analysis. Instead of a human user sitting behind a computer, issuing SQL queries and writing code to answer important business questions, the bulk of this work is starting to shift to agents.
For example, a grocery store may want to know: “was my sale on eggs last week profitable?” Obviously, this is not a trivial question to answer. It cannot be answered by simply looking at egg sales that week vs other weeks. To answer this question correctly, higher level questions need to be answered. Did this sale bring in customers who would not otherwise have come? Did it bring in new customers who have never entered the store before (and have they come back again since that sale)? What else was bought in the same transaction as eggs? Are they physically located near the eggs in the store? How profitable are those other items? It is precisely these types of non-trivial questions — questions that may have a series of followup questions and many rounds of analysis of one or more datasets — that agents have the potential to provide an enormous amount of value. Instead of a human having to attack these questions from all of the relevant angles, the agent can take control and perform a lot of the grunt work, with some amount of guidance and direction from a human.
Everybody knows that AI tools in general — and agents are no exception — are only as good as the humans who are guiding them. If you don’t know what to ask or to request, or how to optimally phrase the request, the result might fall short of what the tool is able to accomplish. Some people are better at using AI than others, and those people get the most out of these tools.
Aside from the way the request is made, there is another, and perhaps more important factor in the quality of the output: the quality (and amount) of the input data to the request. Most are familiar with the concept of attaching input data or files to AI prompts. For enterprise users, this may involve handing over keys to one or more enterprise database systems to an AI agent to use in answering specific questions. In our example above, the agent will need access to the relevant transaction and user data to answer the question of “was my sale on eggs last week profitable?”
In most non-trivial scenarios, more than one database may contain relevant data for a particular interaction. For example, the transaction data for our grocery store application may be stored in one database, and the customer loyalty (e.g. loyalty card information that contains information about the name, address, and other demographic information) data may be stored in an entirely different database. This demographic information is crucial to answer questions about the profitability of a particular sale — especially when breaking down the data across different demographics for the purposes of targeted campaigns).
In these scenarios, the key question is how to pass the data in these different database systems to the AI agent. Whether this is done well or poorly will have strong implications on the experience of the users across three critical dimensions: the correctness/quality of the output, the speed/latency of getting this output, and the cost (e.g. in tokens) of the tool in responding to user prompts.
A comparison of two (or four) alternative architectures #
Option 0: Give the agent a dump of the raw data
One potential option for getting the data to the agent is to simply extract the data from the source and then send this extracted data to the agent. For example, the database system can be asked to write the data from the relevant tables to a file (or set of files). This file or files can then be sent as an attachment to the agent. Alternatively, the data could be piped more directly from the database to the agent. Either way: the point is that the raw data gets sent to the agent, and any subsequent data processing that is required, it will do itself.
This option can potentially get costly. To the extent that agents charge per data item received/processed, sending terabytes of data to the agent can get prohibitively expensive. Furthermore, there are significant performance disadvantages relative to option 1 that I will discuss shortly.
These disadvantages of option 0 reduce its practicality to the point where it is not a good option in practice. This is why I’m calling it “option 0” — it is not something that I would recommend outside of specific unusual circumstances.
Option 1: Give the agent the keys to your database system and let it drive
The simplest practical way to pass data to the agent is simply by giving the agent direct access to the database system(s). The agent then writes its own SQL queries (or queries in any other language that is supported by the database system) to access data as it needs it.
By giving the agent direct access to the database, it can iteratively refine its requests over the data. Instead of giving the agent a giant dump of the entire database and being forced to locally perform advanced query operations over it (as in option 0 above), the agent acts as a user of the database — allowing the database execution engine to do what it does best: process its own local data using carefully optimized query plans. The agent merely oversees this process, sending successive (or parallel) data processing requests to the database system until it reaches a satisfactory conclusion that it can return to its client.
It has been noted in the literature that this type of usage can sometimes be a challenge for the database system. Agents can be far more demanding than humans, and can sometimes overwhelm the database system with speculative queries as it iteratively refines its focus on behalf of a user request. Nonetheless, the database industry is laser-focused on supporting agentic workloads moving forward, and modern database systems are increasingly capable of handling this type of scalable data processing efficiently.
Option 1 yields better performance than option 0
Agents are very powerful and only getting more advanced over time. However, it will be a while before they can process terabytes of tabular/structured data at the same level of optimization as the elite database systems on the market today that have hyper-optimized code based on decades of research (not all of which may be in the public domain). Until that point, a major efficiency gain can be achieved by subcontracting the scalable data processing to the database system instead of trying to do everything itself.
Moreover, even if the agent was just as good as the database system in processing data, there is still an overhead in writing and passing the raw data to the agent. Data is often stored in a proprietary format inside the database system. To get the raw data out of the system, this format needs to be deserialized (and often decompressed) before being written to a file or being passed to the agent. This overhead can be avoided if the database system processes its own data, but must be paid if it is extracted from the system (as in option 0 above).
Furthermore, if the data is larger than what fits in memory at the agent, it then has to be written to storage again once it reaches the agent, and serialized to the data format that the agent prefers (or the format used by the external system that the agent writes the data to). This write overhead is also avoided if the data is simply processed at its source, rather than being passed in raw form to the agent.
The bottom line is that letting the agent function as a user of the database system is generally preferable to extracting data into the agent to perform processing there. Only if the database system is incapable of handling the scale or type of requests from the agent should a full data extraction be considered.
We discussed above that in most non-trivial scenarios, more than one database may contain relevant data for a particular interaction. In these scenarios, the keys to each of these database systems would need to be passed to the agent. The agent is only as good as the data it has access to. If the agent is not given access to a database that may contain potentially relevant data, this data cannot be considered by the agent when being given a question to answer.
Option 2: Give the agent the keys to a virtualization layer that includes access to potentially many database systems
Instead of giving the agent access to each database system (that may contain relevant data) individually, an alternative architecture is to first unify them using a virtualization (or federation) layer, and then give the agent access to the virtualization layer instead of each database individually.
The difference between option 1 and option 2 is illustrated in the figure above. At first blush, options 1 and 2 appear very similar — the AI agent is issuing queries to three different databases and each one is returning query results. The only difference is that in option 1 the AI agent issues queries to the database systems directly, while in option 2, these queries first go through a virtualization layer. The virtualization layer is then in charge of rewriting these queries so that they can be sent to the underlying databases, and collecting the results before returning them to the agent.
There are, however, a surprising number of practical differences between these options, that impact the quality, speed, and cost of the results. I will discuss these differences below. But first I want to briefly mention a third option (that is not the focus of this post).
Option 3: Use the agent that comes with your database system or virtualization layer
This whole post is assuming that you have an AI agent that you want to use that is a distinct piece of software from the database systems or virtualization tools that store and manage your data. However, it is important to remember that many of these systems have their own AI agents and tools that are specifically designed to work with the data managed by them.
For example, Starburst’s AIDA tool is tightly integrated with its query engine and is capable of answering the questions you might ask an AI agent. This tight integration along with native knowledge of the data managed by Starburst and how Starburst optimizes its access of this data can often lead to a better quality set of results, and often at higher speeds. Option 3 is so simple, there is no real need to discuss it in this post. When the AI agent you want to use is tightly integrated with your data architecture, this option is often a no-brainer.
The goal of this post however, is to discuss the architectural options outside of option 3. For example, you might have an enterprise licence for a particular AI agent, or have other reasons why that agent is preferred (e.g. it might be better at accessing data outside of that which is managed by the database or virtualization system). In those scenarios, what is the optimal data architecture?
What are the downsides to having the virtualization/federation layer?
The main difference between options 1 and 2 discussed above is that option 1 gives the agent direct access to all the relevant database systems, while option 2 places a virtualization or federation layer between the agent and the database systems. Option 2 thus requires additional software that is not required by option 1. To the extent that this software is separate from the underlying database systems, this adds some additional cost and complexity to the data infrastructure.
The reason why this adds cost is obvious. If none of the existing database systems being used support this type of global data virtualization that manages access to its own data along with data stored by other database systems, then a separate software vendor needs to be contracted to provide this service. Unless a free and open source option is used (such as Trino) the software vendor must generally be paid for its services. Even if a free and open source option is used — the hardware and engineering costs of deploying the software still have to be paid.
The addition of complexity comes from the need to configure the virtualization layer to interface with the underlying database systems. It needs to be given access to every underlying database system whose data it will be accessing. It needs basic information such as the location and port of the underlying database systems, the username and password (or other key information) that it should use to access this data, etc. It also may need metadata that describes the data inside those databases (to whatever extent the data schemas are insufficient).
Furthermore, if the virtualization layer is a separate piece of software, there may be some effort involved in learning how to use it. Increasingly, vendors are reducing these efforts by providing cloud-based software-as-a-service options (such as Starburst Galaxy) that greatly reduce the training required to use the software. Nonetheless, everything requires at least some effort in getting familiar with its interface and capabilities.
There is also the complexity of choosing what virtualization software to use in the first place. Data virtualization is far from a commodity, and each software solution works differently.
For example some data virtualization products only work with a limited set of database systems. If you are using a database that is not supported by the virtualization product, that database will necessarily be left out of the data being virtualized. Other products (such as Starburst) work with a large number of underlying database systems. There can also be a significant performance difference between virtualization products. Some products are not designed for massively parallel processing and have issues with scenarios in which it receives large amounts of data from the underlying database systems. Others (again — Starburst would be an example) are designed for scalability from the outset. The bottom line is that research must be performed when selecting a virtualization product, and this research adds complexity to the process.
One other downside (although usually not significant) is the extra hop required to get requests to the database systems and results back. When the agent interfaces directly with the database systems, there is no extra hop. However, when there is a virtualization layer between them, queries and results must be routed through this layer. Usually this extra hop of communication is not noticeable relative to the latency of the actual query processing and the agent computation, but at least in theory this is a slight disadvantage.
What are the advantages to having the virtualization/federation layer?
Joins across distinct datasets
The biggest advantage of having a virtualization layer is when datasets stored by different database systems need to be joined together. For example, in the scenario discussed above where the transaction data is stored in one database system and the customer loyalty data stored in a different database system, it is easy to envision scenarios where joining these two datasets together would be useful. For example, breaking down profits by demographic requires the profit data from the transaction dataset and and the demographic data from the customer loyalty database.
Without a virtualization layer, the agent needs to pull both datasets from the respective underlying systems and perform the join there. Both datasets need to be in one place in order for the join to occur (I’m ignoring unusual scenarios in which the databases are able to communicate with each directly via semi-join style of algorithms). Since the datasets being joined are currently managed by separate database systems, the raw data being joined will need to be extracted and performed by the agent.
All of the issues mentioned above that come up when agents deal with raw data thus reemerge. It can be costly (since the agent needs to perform computation as it reads the raw data). It can be slow (because agents are not optimized for joining raw, structured data). It requires additional deserialization and serialization costs. In general, performing the join in the agent is far less efficient than performing it in a database system.
Moreover, many modern agents do not in practice perform the join locally. Instead, they write the data being joined to an external database system and perform the join there. This further reduces performance, since load costs need to be incurred when writing each table being joined to the external database.
In contrast, performing joins across external datasets are a major use case for data virtualization systems. Almost every data virtualization system supports such joins — in many cases they implement multiple different algorithms and choose the optimal one on the fly based on the datasets being joined and the source systems storing them (I discuss different algorithms in my O’Reilly book: “Data virtualization in the Cloud Era”).
Scalable data virtualization systems — such as Trino and Starburst — are designed to allow multiple independent machines to perform such joins in parallel. For example, they route tuples to different machines based on a hash of the join key and then perform joins of each hash partition locally.
Generally speaking, data virtualization systems are able to perform joins of external datasets far more efficiently (and at lower cost) than modern agents. In some cases, agents will avoid trying the join in the first place due to the costs involved unless it can be outsourced in this way. Thus, the mere presence of the data virtualization system allows the agent to explore a larger space of possibilities in answering a query, and thus may lead to higher quality results.
This is the type of thing that an end user may not notice. The same agent with the same query may return different quality results depending on the data infrastructure operating underneath the agent. Most agents will not let the user know — “I might have returned better results if I had tried out an alternative path involving a join of two disparate datasets”. The bottom line is that the user needs to set up the agent for the highest probability of success prior to using it.
Tidiness
The entire point of a data virtualization or federation system is to provide a single interface for users to access all the data in the enterprise (or at least all the data that the system has been set up and given permissions to access). Instead of needing to access each data source individually, users have a single place to go in order to access everything they need.
In most scenarios, the agent is not the only user that will need to access data at the enterprise. Humans (and other agents) will also be accessing data.
After the data virtualization software has been installed and set up, the same system can serve as the interface to the data — both for the agent(s) and human users. When new datasets are created or added to the enterprise, only a single update needs to be made to the data virtualization system (to set up access to it). Once this is done, all users — agents and humans — will be able to benefit from its existence.
In contrast, without the data virtualization system, someone has to realize that this new dataset may be useful to the agent and give the agent direct access to it, separately from any human that may already have access to it. There are separate actions and setup steps that need to be taken for each user that needs access to the new dataset. And the very existence of this dataset may not be easy to communicate if there is no centralized catalog across the enterprise.
In such scenarios, it often happens that useful and valuable datasets get ignored and neglected — merely from the inertia of the additional steps required to alert the agent to their existence. Once again, this leads to lower quality results from agent queries in a way that is essentially impossible to detect.
Just like things run more smoothly in a tidy house, so too things run smoothly when using a tidy data architecture.
Result caching
Agents are notorious for sending many similar queries with overlapping subqueries or access patterns. Many data virtualization systems use result caching to handle such workloads — where results of subqueries are cached and reused when reappearing in subsequent agent queries. This can reduce the burden on the underlying systems from handling the large scale of requests that agents can often generate.
Native API knowledge
Data virtualization systems are designed to work with many different types of underlying database systems. For example, Starburst comes with dozens of connectors to different underlying systems. Each connector is designed to optimize access of that system, often taking advantage of particular features in the design of that system.
While agents are also often broad in their knowledge of how to communicate with a large variety of systems, this knowledge can often be more superficial. This may result in a reduction of efficiency when communicating with these systems — which can reduce the quality, performance, or inexpensiveness of the results.
Conclusion
There are a lot of advantages to using a data virtualization system to interface between the agent and underlying database systems. Data virtualization systems are more capable of performing scalable joins across large external datasets. They help give the agent a global view of all data that may be useful, and are more knowledgeable in leveraging the particular technical details of the underlying systems when communicating with them. Overall, they improve the efficiency of the communication with the data sources, reduce cost, and improve quality of the results.
The main disadvantages are upfront complexity and cost. However, it is important to note that there are a lot of reasons to use data virtualization even outside the context of using AI agents. This upfront complexity and cost can thus be amortized across other use cases.
Furthermore, many organizations are already running data virtualization software without realizing it. For example, many of Starburst’s customers originally set up Starburst to build a “lakehouse architecture” over a data lake in which Starburst accesses data stored in Parquet files and other open data formats in the lake. In many cases, they are unaware that Starburst is actually a data virtualization product. However, it is quite trivial to set up Starburst to access other systems as well, and then give the agent access to Starburst.
The bottom line is that if you are not tied to a particular agent or AI tool, using one that is tightly integrated with your data management platform (such as Starburst AIDA) is often best. But even if you are tied to a given agent, it is still important to leverage data virtualization technology in order to maximize efficiency in the way that the agent accesses and leverages enterprise data.