The standard advice for organisations that need to query across multiple databases is to build a data warehouse: copy everything into one place, transform it into a common schema, and query from there. This works, but it introduces a copy of your data that needs to be maintained, a transformation pipeline that breaks when source schemas change, and a latency between when data is written and when it is queryable. Virtual schemas are an alternative that avoids all three of those problems, with some trade-offs.
What a virtual schema is ¶
A virtual schema is a logical representation of data that does not physically exist in any one place. It maps fields from multiple source systems to a common set of table and column names. When a query is run against the virtual schema, the query router translates it into queries against the actual source systems, retrieves the results, joins them in memory, and returns a unified result set. No data is copied. The source systems are queried live.
The trade-offs compared to a warehouse ¶
The main trade-off is query performance. A warehouse query runs against data that is already in one place, indexed and optimised for analytical queries. A virtual schema query has to reach out to multiple source systems, each of which may have different performance characteristics. For queries that join large tables across slow source systems, this can be slower than a warehouse. The right answer depends on your data volumes, your source system performance, and how fresh your data needs to be.
Where virtual schemas work well ¶
Virtual schemas work well for operational reporting. Queries that need fresh data and join a moderate number of rows across a small number of sources. They also work well as a first step before building a warehouse: you can validate your schema design and your query patterns against live data before committing to a transformation pipeline. And they work well in environments where source schemas change frequently, because the virtual schema can be updated without rebuilding a warehouse.
Caching as a middle ground ¶
A configurable result cache sits between the two extremes. Frequently-run queries are cached at a TTL you set. Five minutes, one hour, one day. The first run hits the source systems; subsequent runs within the TTL window return the cached result. This reduces load on source systems and improves query performance for repeated queries, while keeping data fresh enough for most operational use cases.
The Core module in Meshblend implements a virtual schema with configurable caching. If you want to understand how it would map to your specific source systems, the notes from our engineering team cover several worked examples.