Data virtualization

What is data virtualization?

Data virtualization lets you query and combine data from several systems through a logical layer, without first copying all of that data into a central store.

You ask the virtual layer a question. The virtualization engine works out which sources are needed, sends subqueries to those sources, and combines the answers. The user sees tables or views. Behind the scenes, the data can still live in CRM, ERP, a SQL database, object storage, a data lake, or a SaaS system.

The contrast with the classic approach is clear. With ETL or ELT, you copy data into a warehouse or lakehouse and report on the copy. With virtualization, the query reaches into the source at the moment you ask.

How does a virtualization engine work?

A virtualization engine usually does three things.

1. Split the query
   The engine reads the query and decides which parts belong to which source. A join between CRM customers and accounting invoices may become one subquery to CRM and one to accounting.

2. Push work down
   Where possible, it pushes filters, column selection, aggregations, and sometimes joins to the source system. Trino documentation calls this pushdown: parts of the query are passed through to the connected data source so less data has to move across the network.

3. Combine the results
   The partial answers come back, and the engine performs the remaining join, sort, calculation, or aggregation.

Pushdown is the difference between a useful federation layer and an expensive data vacuum. The more work the source can do safely, the less the virtualization engine has to drag back and process itself.

Virtualization versus ETL and ELT

The main question is whether users query a copy or the live source.

ETL and ELT create a copy. Data is extracted, loaded, and transformed into a warehouse, lakehouse, or mart. Reports hit that curated store.

Data virtualization queries the original systems through a logical layer. That gives fresher data and fewer copies, but it puts query load back on the source systems.

The same trade-off appears in Power BI. Import mode copies data into the model and is fast for users. DirectQuery leaves data at the source and queries it live. Data virtualization sits closer to the DirectQuery side of that trade-off.

Virtualization and ETL do not exclude each other. Many architectures virtualize smaller or fresher sources and materialize large history into a warehouse or lakehouse.

An SME example

A wholesaler has three systems: ERP for stock and invoices, a webshop for live orders, and a transport platform for delivery status. The manager wants to know which orders are paid, in stock, and already on the road.

A classic pipeline copies data from all three systems into a warehouse every night. That works, but delivery status is always a day behind. With data virtualization, a logical view can query the webshop and transport platform live and combine that with stock data.

The benefit is freshness. The cost is source load. If ten people open the same dashboard at the same time, the webshop and transport platform may receive ten live queries.

Tools and patterns

Denodo
Denodo describes data virtualization as a single logical data-access layer over physical sources such as warehouses, lakes, databases, applications, APIs, and files. It also supports caching and materialisation patterns when pure federation is too slow.

Trino and Starburst
Trino is an open-source SQL query engine with connectors to many data sources. Starburst builds commercial products around Trino. These tools are often used for query federation across warehouses, lakes, and databases.

OneLake shortcuts in Microsoft Fabric
OneLake shortcuts connect Fabric to existing data without directly copying it into OneLake. Microsoft describes shortcuts as a way to make OneLake a unified namespace across domains, clouds, and accounts. This is not full data virtualization in the Denodo sense, but it is a zero-copy access pattern with a similar goal.

What to watch out for

Load on production systems
Every query can hit the source. A dashboard that refreshes all day may slow an operational system. Caching can help, but then the data is no longer fully live.

Slowest source wins
The virtual view is often limited by the slowest underlying system or API.

Cross-system joins are hard
A join between two large tables in different systems may require pulling a lot of data into the engine. Starburst and other federation vendors point to cross-source optimisation as one of the hard parts.

Governance still matters
A logical layer can hide complexity, but it cannot magically align definitions, access rules, or data quality across sources.

Do not use it as an excuse to skip modelling
For repeated reporting on large, historical, governed data, a warehouse, lakehouse, data mart, or semantic model may still be the better user experience.