One SQL Query Across Multiple Systems, Without Copying Data First
How Exasol Virtual Schemas Let You Query Data Where It Lives
A business question often depends on data stored across several systems.
Which customers opened a support ticket last quarter, later downgraded their plan, and how much had they spent beforehand?
The data may already exist. Tickets might sit in one system, subscriptions in another, and revenue in a warehouse. Answering the question can still require a new pipeline to bring those datasets together.
That adds work before the analysis can begin.
Exasol Virtual Schemas provide another option. They make external data queryable through standard SQL, so teams can combine remote data with data stored in Exasol without creating a new ingestion pipeline for every question.
Virtual Schemas do not remove every reason to move data. Frequently queried datasets, complex transformations, historical snapshots, and workloads that must protect source systems may still justify materialization. The difference is that copying data does not have to be the first step.
Four Costs of Copying Data
Copying data can introduce four recurring costs: time, freshness, governance, and maintenance.
Time: A new copy often requires more than a scheduled load. Teams may need to prioritize the work, define a model, build the pipeline, test it, and release it before analysis can begin.
Freshness: Maintaining another copy can create a gap between the source and the data available for analysis. For time-sensitive decisions, that gap can matter.
Governance: Source systems may already have access controls, audit trails, retention policies, and requirements governing where data can be stored. Creating another copy may require those controls to be implemented and maintained in another location. Data residency requirements can also restrict where information can be moved.
Maintenance: Source schemas change. Columns can be renamed, fields added, or tables restructured. Downstream pipelines then need to accommodate those changes.
Centralizing data remains useful for many workloads. But it does not eliminate the work required to synchronize and govern additional copies.
What Is a Virtual Schema?
A Virtual Schema is an abstraction layer that makes an external data source queryable through SQL.
After a source is registered, its contents appear as virtual tables. Analysts can query those tables alongside data stored in Exasol.
A query can join an Exasol table with an external table, or combine virtual tables from multiple systems. For that query, teams do not need to build an additional ingestion pipeline or refresh schedule first.
One SQL Query Across Multiple Systems
Analysts can continue using standard SQL even when the underlying data comes from different systems.
For example, customer records might remain in Oracle while order data is stored in Exasol:
SELECT
c.customer_name,
SUM(o.order_total) AS total_revenue
FROM ORACLE.CUSTOMERS c
JOIN SALES.ORDERS o
ON c.customer_id = o.customer_id
GROUP BY c.cusomer_name
ORDER BY total_revenue DESC;CUSTOMERS is exposed through a Virtual Schema, while SALES.ORDERS is stored locally in Exasol.
From the analyst’s perspective, the query uses familiar SQL. The next question is how Exasol limits the amount of remote data transferred during execution.
How Pushdown Reduces Data Movement
Querying a remote source inefficiently can require large volumes of unnecessary data to cross the network.
Consider a query requesting support tickets from last quarter from a table containing nine years of history. Fetching the full table and filtering it locally would transfer far more data than the query needs.
Pushdown reduces that transfer.
When Exasol processes a query involving a virtual table, it identifies supported operations that can be executed by the remote source. Depending on the source and adapter, this can include filters, projections, and other operations.
The source processes those operations before returning data to Exasol. As a result, less data may need to cross the network before Exasol performs the remaining analytical work.
Engineers can inspect this behavior with EXPLAIN VIRTUAL, which shows the statement sent to the source. This makes it possible to review pushdown behavior before using a federated query in production.
How Virtual Schemas Expand Data Access for MCP-Connected Agents
An AI agent connected to Exasol through MCP can work with data Exasol is able to access.
If analytical data becomes available only after teams copy it into a central platform, the agent is limited by the integrations already in place.
Virtual Schemas can expose external sources as virtual tables alongside local Exasol tables. The agent can then query accessible data through SQL without requiring a new ingestion pipeline for every source.
This can be useful for agentic analytics because the combinations of data an agent needs may differ from those anticipated when existing pipelines were designed.
Where Virtual Schemas Fit with ETL, ELT, and Lakehouses
Virtual Schemas, ETL or ELT pipelines, and lakehouses solve different workload requirements.
ETL and ELT remain appropriate when data is queried frequently, requires extensive transformation, must be stored as a persistent historical snapshot, or should be separated from the operational source before analytical use.
Lakehouses can store large volumes of raw and semi-structured data. Virtual Schemas can complement that architecture where supported external sources need to be queried without another permanent copy.
Virtual Schemas are particularly useful when:
- a pipeline does not yet exist
- a source is queried infrequently
- teams need to explore data before deciding whether to materialize it
- governance or residency requirements make copying data difficult
- a query needs to combine external data with data already stored in Exasol
Because virtual tables can be queried alongside local tables, teams can use virtualization and materialization within the same architecture.
What Can You Connect?
Virtual Schemas connect to relational databases, cloud data warehouses, object storage, and search platforms through open-source adapters.
Where an existing adapter is not available and the source exposes a JDBC driver, a custom dialect can be developed.
See the Exasol documentation for the current list of supported adapters and dialects.
When to Virtualize and When to Materialize
Virtual Schemas do not eliminate the need to materialize data.
Frequently queried datasets may benefit from being stored in Exasol for analytical workloads. Performance in a federated query also depends on factors including source-system performance, network conditions, and the operations the adapter can push down.
Virtualization gives teams another option before committing to a permanent data copy.
Teams can query a source first, evaluate how the workload behaves, and materialize the data when performance, transformation, or workload requirements justify it.
This makes data movement an architectural decision rather than an automatic prerequisite for analysis.
Reduce Unnecessary Data Copies
Governance, residency requirements, storage costs, and data transfer costs can all influence where organizations choose to store data.
Virtual Schemas provide another way to make data available for analysis. Data can remain in its existing system when appropriate while still being accessible through SQL.
A modernization strategy can therefore distinguish between data that needs to be materialized and data that only needs to be queryable.
Go deeper
- Virtual schemas documentation: concepts, EXPLAIN VIRTUAL, and the complete adapter list: docs.exasol.com
- Integrations: what Exasol connects to across your estate: exasol.com/integrations
- Supported Dialects: https://github.com/exasol/virtualschemas/blob/main/doc/user_guide/dialects.md
- GitHub: https://github.com/exasol/virtual-schemas/tree/main
- Exasol Database — the engine behind federated analytical queries: exasol.com/exasol-database
The Takeaway
You will still move data when the workload requires it.
Virtual Schemas give you another option when it does not.
They make external data accessible through SQL and can push supported operations to the source before results return to Exasol. A query can then combine virtual tables with data stored locally in Exasol.
Materialize data when performance, transformation, governance, or workload requirements justify it. Otherwise, querying it where it already lives may remove the need for another pipeline.