What is Data Virtualization?

Data virtualization is the "query where the data lives" approach. Instead of copying every source into a central warehouse, you put a query engine in front (Trino, Starburst, Denodo, Dremio, Snowflake cross-database queries) and let users issue one SQL statement that federates across Postgres, S3 + Iceberg, MongoDB, Kafka, and the Snowflake warehouse. The appeal is obvious: no ETL pipeline for every new source, no data duplication, real-time freshness. The reality is subtler.

What virtualization is actually good at

Data virtualization earns its keep in three places: (1) exploration and discovery — analysts can sniff a new source before you commit to ingesting it; (2) "last-mile" joins between warehouse data and a low-volume source (e.g., joining Snowflake fact tables with a Postgres config table that has 5K rows); (3) cross-region or cross-cloud federation where physically replicating everything is impractical or violates data-residency rules. It loses its value when you start federating heavy BI workloads across half a dozen sources — pushdown works imperfectly, network latency dominates, and queries that would take 2 seconds on a materialized warehouse table take 45 seconds on the virtualization layer.

Field story

A retailer I worked with used Trino/Starburst to federate across their Snowflake warehouse, a Postgres product catalog, and S3 + Iceberg event logs. This worked beautifully for data exploration — analysts prototyped dashboards in days instead of waiting on an ETL pipeline. When those dashboards moved to production and got hit by 200 concurrent users, Trino struggled; the queries were joining 50M-row Iceberg tables with Snowflake fact tables over the network, and p95 latency spiked to 30+ seconds. The fix was to materialize the production joins into Snowflake (regular ETL) and leave Trino for exploration. Virtualization is a great front door for analysts; it's rarely the right answer for production BI. Know what workload you're actually building before you commit either way.

Data Virtualization is a data integration technique that allows users to access, query, and combine data from multiple heterogeneous sources through a single virtual layer — without physically moving, copying, or transforming the data into a centralized repository.

How It Works

Traditional ETL:
  Source A → [Extract] → [Transform] → [Load] → Data Warehouse → Query
  Source B → [Extract] → [Transform] → [Load] → Data Warehouse → Query
  (Hours/days of latency, data duplication)

Data Virtualization:
  Source A ──┐
             ├── Virtual Layer → Query (real-time, no duplication)
  Source B ──┘

Key Concepts

Virtual Views

• Define logical tables that map to physical data sources
• Users query virtual views as if they were local tables
• The virtualization layer translates queries to source-native formats

Query Federation

• Execute a single SQL query across multiple databases
• The engine pushes filters and aggregations to source systems (pushdown)
• Results are combined and returned to the user

Semantic Layer

• Define business-friendly names and relationships
• Abstract away technical details of source systems
• Enforce consistent calculations and metrics

Benefits

1. Real-Time Access: Query current data without waiting for ETL loads
2. No Data Duplication: Reduce storage costs and governance complexity
3. Faster Time to Value: Skip the ETL pipeline — query data immediately
4. Single Access Point: One SQL interface for all data sources
5. Reduced Complexity: Fewer ETL pipelines to build and maintain

Limitations

• Performance: Complex joins across sources can be slow
• Source Dependency: If a source is down, virtual queries fail
• Not for Heavy Analytics: High-volume BI workloads still need a warehouse
• Caching Needed: Frequently accessed data may need materialization

Popular Tools

Common Use Cases

1. Data Fabric: Unified access across on-premise and cloud data
2. Real-Time Dashboards: BI on live operational data
3. Data Exploration: Query new sources without building ETL first
4. Regulatory Reporting: Access audit-ready data from source systems
5. API Layer: Serve data to applications via virtual SQL views

Data virtualization sells a beautiful story. The pitfalls are where production burns you:

• Pushdown is imperfect and unpredictable. The query engine claims to push filters and aggregations to sources, but support varies wildly per connector. A function that pushes down to Postgres may not push down to MongoDB, and the engine silently falls back to pulling the entire table across the network. Profile every federated query; check EXPLAIN output for what actually pushed down.
• Network latency kills cross-source joins at scale. Joining 100M Iceberg rows with 500M Snowflake rows requires pulling one side across the network. Even with fast pipes, this is seconds to minutes. Virtualization works best when one side of the join is small (a dimension table) or when both sides are in the same cloud region.
• Source availability becomes your availability. If your dashboard federates Postgres + Snowflake + MongoDB and one source has a 30-second blip, every query across the virtualization layer fails. Traditional warehouses buffer against source outages; virtualization doesn't. Plan for cached/materialized views of critical queries.
• Concurrency crushes query engines. Trino, Starburst, Denodo can handle 20 analysts; they struggle with 2000 concurrent dashboard users hitting the same virtualized view. Production BI workloads usually need materialization. Use virtualization for the front door, materialize for the hot path.
• Cost accounting is fragmented. With a warehouse, you see the whole bill. With federation, costs show up in every source (source database CPU, egress charges, virtualization engine compute). Federated queries can look free until the egress invoice arrives.
• Governance is harder than it looks. Masking policies, row-access policies, column-level security — these are often enforced per-source, not at the virtualization layer. A "unified" view can expose PII that individual sources had locked down. Verify governance enforcement end-to-end before opening federated access to sensitive sources.
• Semantic layer drift. Teams define virtual views with business-friendly names and calculations; over time these drift from source reality. Treat the semantic layer like code — version-controlled, tested, owned.
• Don't use virtualization as a substitute for a data platform. A common anti-pattern: "we don't need a warehouse, we'll just federate with Trino." This works for dozens of queries; it breaks for thousands. At scale you need both.