Data Virtualization vs ETL: Denodo, Starburst Galaxy & When Not to Copy Data

Data virtualization is the discipline of leaving data where it already lives — in a Postgres OLTP database, an Iceberg table on S3, a Snowflake account, a SaaS API — and querying it through a single logical layer that federates the sources at runtime, instead of the older reflex of copying everything into one central store with a batch pipeline before anyone can ask a question of it. The hard architectural question was never "can we move the data"; it was "should we." Every copy is a new thing to schedule, to backfill, to keep in sync, to secure twice, and to explain when it drifts from the source — and for a surprising fraction of workloads, the copy buys you nothing the source could not have served directly.

This guide is the senior-data-engineering walkthrough for making that call deliberately — for weighing copy-based ETL/ELT against a virtual logical layer, and for building the layer when virtualization is the right answer — framed the way interviewers actually probe it. It covers the trade space that decides copy-versus-query (freshness, source load, latency, governance, storage cost); how Denodo assembles a data fabric from base, derived, and published views with a cost-based optimizer and caching; how Starburst Galaxy — managed Trino — runs federated MPP SQL across lakes and databases through catalog connectors; the mechanics of query pushdown and federation that make virtualization fast (or expose why it is slow); and, above all, when not to copy versus when to materialize, plus the hybrid pattern most mature platforms actually run. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, sharpen the pushdown and cost instincts on the query optimization practice library →, and rehearse the copy-versus-query trade on the system design practice library →.

---

On this page

• Data virtualization vs ETL — query in place or copy
• Denodo — the logical data fabric and its optimizer
• Starburst Galaxy — federated MPP SQL with Trino
• Query pushdown and federation mechanics
• When not to copy versus when to materialize
• Cheat sheet — data virtualization vs ETL
• Frequently asked questions
• Practice on PipeCode

---

1. Data virtualization vs ETL — query in place or copy

The core distinction — ETL copies data into one store; virtualization queries it where it lives

The one-sentence invariant: data virtualization exposes a single logical layer over many physical sources and resolves each query by federating to those sources at runtime — pushing as much work as possible down into them — whereas copy-based ETL/ELT physically moves data into one central store on a schedule and serves every query from that copy, so the entire decision reduces to a trade between freshness, source load, latency, governance, and storage cost, evaluated per access pattern rather than declared once for the whole platform. Copy when the same heavy query runs constantly and staleness is acceptable; virtualize when freshness matters, volume is modest, or you need one governed view over data you must not or cannot duplicate.

The two models, precisely.

• ETL / ELT (copy-based). A pipeline extracts from sources, transforms, and loads into a warehouse or lake; queries then hit the copy. The data is materialized — physically stored a second time — and is as fresh as the last load.
• Data virtualization (query-based). A logical layer defines views over the sources; a query against a view is decomposed, the parts are pushed to the sources, and the results are combined by the virtualization engine. No second copy exists; the data is as fresh as the source at query time.
• The logical layer. The abstraction that lets consumers query sales.orders without knowing (or caring) whether it is a Postgres table, an Iceberg file set, or a join across both — the same decoupling a warehouse view gives you, extended across systems.
• The data fabric. The organisational form of a mature virtualization layer: a catalogued, governed, semantically consistent set of virtual datasets over the whole estate, with security and lineage centralised in the layer.

The trade space — five axes that decide copy vs query.

• Freshness. Virtualization reads the source live, so it is always current; a copy is stale between loads. If a consumer needs "as of now," a copy has to either accept lag or be refreshed so often the pipeline dominates the cost.
• Source load. Virtualization pushes query load onto the sources — every consumer query becomes source work; a copy isolates the source (it is read once per load) and absorbs all query load on the warehouse. Virtualizing a hot query against a busy OLTP database can hurt the very system of record it reads.
• Latency. A copy is tuned for reads (columnar, indexed, co-located), so repeated analytical queries are fast; virtualization pays network round-trips and is bounded by the slowest source in the query — great for modest volumes, punishing for huge cross-source joins.
• Governance. Virtualization gives you one place to enforce row/column security, masking, and lineage over everything; a copy spreads governance across the pipeline and the warehouse and risks the copy and the source disagreeing on policy.
• Storage cost. Virtualization stores nothing extra; a copy duplicates data (and its history), plus the compute to build and refresh it. For rarely-queried or enormous data, the copy's storage and pipeline cost can dwarf any query it serves.

The 2026 reality — the tooling.

• Denodo is the incumbent enterprise logical layer: a metadata-driven fabric of base views (over sources), derived views (joins/transforms), and published data services, with a cost-based optimizer and a caching subsystem.
• Starburst Galaxy is managed Trino — an MPP SQL engine whose catalogs are connectors to sources, executing one ANSI-SQL query across a lakehouse, Postgres, and a document store, with aggressive pushdown.
• The semantic layer (dbt's semantic layer, Cube, and warehouse-native metric layers) overlaps: it virtualizes metric definitions even when the data is copied — a reminder that "don't copy" applies to logic as much as to rows.
• The hybrid default. Serious platforms rarely go all-copy or all-virtual: they virtualize by default for reach and freshness, then materialize (or cache) the proven hot paths — the pattern section 5 builds.

What interviewers listen for.

• Do you frame copy-vs-virtualize as a per-access-pattern trade, not a platform-wide religion? — senior signal.
• Do you name source load as a first-class cost of virtualization, not just latency? — required answer.
• Do you say freshness and governance consolidation are where virtualization wins and high-QPS repeated heavy queries are where copying wins? — senior signal.
• Do you know pushdown is what makes virtualization fast, and that a cross-source join is where it stops? — required answer.
• Do you reach for the hybrid — virtualize, then materialize the hot path — rather than defending one extreme? — senior signal.

Worked example — the copy-vs-virtualize decision matrix

Detailed explanation. The single most useful artifact for a virtualization interview is a memorised mapping of access pattern to strategy. Every senior discussion converges on it: given a consumer, a freshness need, and a volume, do you copy the data with ETL or query it live through a virtual layer? Walk through building the matrix for a business that has customer master data in Postgres, order events in Iceberg on S3, and a shipment feed in a partner API.

• The consumers. A finance dashboard (high volume, freshness-tolerant), a support console showing a customer's live order status (low volume, freshness-critical), a data-science exploration over all three sources (ad-hoc, exploratory).
• The tension. Copies are fast and isolate the sources but are stale and expensive to keep; virtual views are fresh and free of storage but load the sources and pay federation latency.
• The rule. Match the strategy to the access pattern's tolerance for staleness, its query volume, and how much source load it would impose.

Question. For each consumer, name the strategy (copy with ETL, virtualize, or hybrid) and justify it against freshness, volume, and source load.

Input.

Consumer	Volume	Freshness need	Strategy
Finance dashboard	high, repeated	daily OK	copy (ETL into warehouse)
Support live-order console	low	seconds (live)	virtualize (query source)
Data-science exploration	ad-hoc, low	live-ish	virtualize (federated)
Regulatory bulk extract	scheduled, huge	daily	copy (materialize + paginate)

Code.

-- COPY path (ETL): the finance dashboard reads a materialized warehouse table,
-- refreshed once a day. Fast repeated reads; the sources are touched once per load.
CREATE TABLE warehouse.fct_finance_daily AS
SELECT o.order_date, c.region,
       count(*)                     AS orders,
       sum(o.total_cents)::bigint   AS revenue_cents
FROM   iceberg.sales.orders        o
JOIN   postgres.crm.customers      c ON c.id = o.customer_id
GROUP  BY o.order_date, c.region;   -- built nightly by the pipeline

-- VIRTUALIZE path: the support console reads a LOGICAL view that federates
-- the live customer row and the live order rows at query time — no copy exists.
CREATE VIEW virt.customer_live_orders AS
SELECT c.id AS customer_id, c.name, c.region,
       o.order_id, o.status, o.total_cents, o.updated_at
FROM   postgres.crm.customers c
JOIN   iceberg.sales.orders   o ON o.customer_id = c.id;
-- Support queries: SELECT ... FROM virt.customer_live_orders WHERE customer_id = ?

Step-by-step explanation.

1. The finance dashboard runs the same heavy aggregation thousands of times a day and tolerates day-old data, so a nightly ETL copy into fct_finance_daily is correct: the join and grouping run once per load, and every dashboard read is a cheap scan of a small pre-aggregated table.
2. The support console needs a single customer's live order status — low volume, but it must be current to the second. Virtualizing is correct: the view federates the live Postgres customer row and the live order rows at query time, so there is no staleness and no pipeline to maintain.
3. The data-science exploration is ad-hoc and unpredictable — you cannot know in advance which slices they will want, so pre-copying every possibility is impossible; a federated virtual layer lets them query across all three sources on demand.
4. The regulatory bulk extract is huge and scheduled, so a materialized copy (paginated on export) is right: virtualizing a massive full scan across sources every run would hammer them and pay federation latency for no freshness benefit.
5. The mistake is a single strategy for all four: copying the live support view means stale order status; virtualizing the finance dashboard means every page load re-federates a heavy cross-source join and loads the sources. The matrix is the antidote — strategy follows the access pattern.

Output.

Access pattern	Right strategy	Wrong strategy (common mistake)
High-volume, freshness-tolerant	copy (ETL) + read the copy	virtualize (re-federate every query)
Low-volume, freshness-critical	virtualize (query source live)	copy (stale status)
Ad-hoc exploration	virtualize (federated)	copy every possible slice
Huge scheduled extract	copy + paginate	virtualize a full cross-source scan

Rule of thumb. State the access pattern first — volume, freshness need, and source-load tolerance — then let it choose: copy the hot, freshness-tolerant, repeated queries; virtualize the fresh, low-volume, or exploratory ones. It is a per-pattern decision, and a mature platform runs both side by side.

Worked example — a virtual view versus its materialized ETL twin

Detailed explanation. The clearest way to feel the trade is to write the same logical dataset two ways — once as a virtual view resolved at query time, once as a materialized table built by a pipeline — and reason about what each costs on the freshness/source-load/latency/storage axes. Build a "customer 360" that joins CRM (Postgres) and order history (Iceberg).

• The virtual twin. A view; every query re-executes the join against the live sources.
• The materialized twin. A table; a pipeline runs the join on a schedule and stores the result.
• The difference. Identical logic, opposite physics — one trades storage for freshness, the other trades freshness for read speed.

Question. Express a customer-360 dataset as both a virtual view and a materialized table, and state precisely what each costs on the four axes.

Input.

Axis	Virtual view	Materialized table
Freshness	live (source at query time)	as of last load
Source load	every query hits sources	one read per load
Read latency	federation + slowest source	local, tuned scan
Storage	none	full copy + history

Code.

-- VIRTUAL: no storage, always fresh; every read federates to both sources.
CREATE VIEW virt.customer_360 AS
SELECT c.id AS customer_id, c.name, c.region, c.tier,
       count(o.order_id)            AS lifetime_orders,
       coalesce(sum(o.total_cents),0)::bigint AS lifetime_cents,
       max(o.updated_at)            AS last_order_at
FROM   postgres.crm.customers c
LEFT JOIN iceberg.sales.orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.region, c.tier;

-- MATERIALIZED: a pipeline runs the SAME logic on a schedule into a stored table.
CREATE TABLE warehouse.customer_360 AS
SELECT * FROM virt.customer_360;         -- built nightly / hourly by ETL
CREATE INDEX ON warehouse.customer_360 (customer_id);   -- tuned for point reads

Step-by-step explanation.

1. The logic is byte-for-byte the same aggregation; only where and when it runs differs. This is the whole point — virtualization and ETL are two executions of one definition, not two different transformations.
2. virt.customer_360 stores nothing and is always current, but every consumer query re-runs the LEFT JOIN and GROUP BY against live Postgres and Iceberg — so a hundred dashboard refreshes are a hundred cross-source aggregations, and the sources feel all of it.
3. warehouse.customer_360 runs that aggregation once per load and stores the result, so consumer reads are a single indexed lookup on a local table — fast and cheap — at the cost of staleness between loads and the storage of a full second copy plus its history.
4. The read-latency gap is structural: the virtual view is bounded by network round-trips and the slower of Postgres and the S3 scan, while the materialized table is a co-located, indexed read the query planner can serve in milliseconds.
5. The senior read is that neither is "better" — they occupy opposite corners of the trade space, and the right choice is set by how many times the dataset is read between changes and how fresh consumers need it. High read-to-write ratio and staleness tolerance favour the copy; low volume and a freshness requirement favour the view.

Output.

Scenario	Virtual view	Materialized table
Queried 5x/day, freshness critical	ideal (fresh, low source load)	stale, wasteful pipeline
Queried 10k x/day, day-old OK	melts sources, slow	ideal (fast local reads)
Source is a fragile OLTP DB	risky (adds load)	safe (read once per load)
Data must not be duplicated	required (no copy)	non-compliant

Rule of thumb. Write the dataset once as a logical definition; choose materialize versus virtualize by the read-to-change ratio and the freshness requirement. Low ratio and high freshness need favour the view; high ratio and staleness tolerance favour the copy — and you can promote a proven virtual view into a materialized one without changing its logic.

Worked example — quantifying the source-load cost of virtualization

Detailed explanation. The axis juniors forget is source load. Virtualization does not make query work disappear — it relocates it onto the systems of record, and a naive virtual layer over a busy OLTP database can turn analytical curiosity into a production incident. Model the source-load cost of virtualizing a dashboard and the mitigations.

• The setup. A dashboard tile virtualized directly over the primary Postgres OLTP database.
• The blast. Each tile refresh runs an aggregate scan on the OLTP primary, competing with transactions.
• The mitigations. Push the aggregate down, read a replica, or cache/materialize — in increasing order of isolation.

Question. Estimate the source-load impact of virtualizing a hot aggregate over an OLTP primary and rank the mitigations from cheapest to strongest.

Input.

Setup	Source hit per refresh	Risk
Virtualize over OLTP primary	full aggregate scan	contends with transactions
+ push the aggregate down	aggregate at source	still on the primary
+ read a replica instead	aggregate on replica	primary protected
+ cache / materialize	source read once per TTL	primary barely touched

Code.

Source-load math — virtualizing a hot aggregate over OLTP.

Tile refreshes: 2,000 / minute (many users, 30s auto-refresh)
Naive virtual view over the PRIMARY:
  -> 2,000 aggregate scans/min hit the OLTP primary, competing with writes.
     Result: lock/IO contention, p99 transaction latency spikes. BAD.

Mitigation 1 — aggregate pushdown (compute SUM/GROUP BY at the source):
  -> still 2,000 queries/min, but each returns a tiny aggregated result,
     not raw rows. Less network + engine work, SAME primary load. Partial.

Mitigation 2 — point the base view at a READ REPLICA:
  -> 2,000 scans/min hit the replica; the primary keeps serving transactions.
     Isolation without a copy. Good default for virtualization over OLTP.

Mitigation 3 — cache / materialize the aggregate (TTL 60s):
  -> the source is read ~1 / 60s; the 2,000 refreshes are served from cache.
     Strongest isolation; trades freshness (<=60s). Best for a hot tile.

-- Base view over a REPLICA, not the primary — the cheapest strong mitigation.
CREATE VIEW virt.sales_by_region AS
SELECT region, sum(total_cents)::bigint AS revenue_cents, count(*) AS orders
FROM   postgres_replica.sales.orders     -- connector points at the replica
GROUP  BY region;                         -- pushed down to Postgres (see section 4)

Step-by-step explanation.

1. Virtualization relocates query work onto the source, so 2,000 tile refreshes a minute become 2,000 aggregate scans on whatever the base view points at — and if that is the OLTP primary, they contend with the transactions the business runs on.
2. Aggregate pushdown (mitigation 1) helps the engine and network — the source returns a small grouped result instead of raw rows — but the scan still executes on the primary, so it reduces federation cost without protecting the source.
3. Pointing the base view at a read replica (mitigation 2) is the cheapest strong fix: the analytical load lands on a replica built to absorb reads, and the primary is untouched — you get virtualization's freshness (replica lag is seconds) without endangering transactions.
4. Caching or materializing the aggregate (mitigation 3) is the strongest isolation: the source is read once per TTL and the flood of refreshes is served from the cache, so the primary is barely touched — at the cost of bounded staleness, which a dashboard tile usually tolerates.
5. The senior discipline is to never point a hot virtual view at an OLTP primary — default to a replica, and cache or materialize the moment volume rises. Virtualization's freshness is only free if the source can afford the load it relocates there.

Output.

Mitigation	Primary load	Freshness	When to use
Naive (over primary)	severe	live	never for hot tiles
Aggregate pushdown	severe	live	always (but not enough alone)
Read replica	none (on replica)	seconds	default for OLTP virtualization
Cache / materialize	negligible	≤ TTL	hot, freshness-tolerant tiles

Rule of thumb. Treat source load as a real cost of virtualization: push aggregates down, point base views at replicas rather than primaries, and cache or materialize hot queries. The freshness you get from querying in place is only worth having if the source can absorb the load you just moved onto it.

Senior interview question on choosing virtualization versus ETL

A senior interviewer might open with: "You have customer master data in Postgres, order events in Iceberg on S3, and a shipment feed behind a partner API. Product wants a customer-360 for a support console (live), a finance dashboard (high volume, daily fresh is fine), and an ad-hoc data-science surface. Decide what you copy with ETL and what you virtualize, justify each against freshness, source load, and cost, and explain how you would keep the virtual paths from overloading the sources — and why this is a per-access-pattern decision, not a platform-wide one."

Solution Using a per-pattern split, replica-backed virtual views, and a materialized hot path

-- 1. VIRTUALIZE the live, low-volume support view — federate at query time.
--    Base views point at REPLICAS so analytical reads never touch OLTP primaries.
CREATE VIEW virt.customer_live_orders AS
SELECT c.id AS customer_id, c.name, c.region,
       o.order_id, o.status, o.total_cents, o.updated_at
FROM   postgres_replica.crm.customers c
JOIN   iceberg.sales.orders           o ON o.customer_id = c.id;
-- Support: SELECT ... WHERE customer_id = ?  (a point lookup, tiny source load)

-- 2. COPY the high-volume finance dashboard — one heavy join per load, cheap reads.
CREATE TABLE warehouse.fct_finance_daily AS
SELECT o.order_date, c.region,
       count(*) AS orders, sum(o.total_cents)::bigint AS revenue_cents
FROM   iceberg.sales.orders   o
JOIN   postgres_replica.crm.customers c ON c.id = o.customer_id
GROUP  BY o.order_date, c.region;         -- built nightly by ETL
CREATE INDEX ON warehouse.fct_finance_daily (order_date, region);

-- 3. VIRTUALIZE the ad-hoc data-science surface — federated, incl. the partner API.
CREATE VIEW virt.orders_with_shipments AS
SELECT o.order_id, o.customer_id, o.total_cents, s.carrier, s.eta
FROM   iceberg.sales.orders o
LEFT JOIN partner_api.logistics.shipments s ON s.order_id = o.order_id;

# 4. Guardrails so virtual paths cannot overload the sources.
virtualization_policy:
  base_views:        replica_only        # never a virtual view over an OLTP primary
  hot_view_cache:    { view: virt.customer_live_orders, mode: none }   # point lookup, no cache needed
  aggregate_views:   { pushdown: required, cache_ttl: 60s }            # push down + cache hot aggregates
  partner_api:       { rate_limit: 300/min, cache_ttl: 120s }          # protect the fragile source
  promote_rule:      "if a virtual view exceeds 500 qpm, materialize it"

Step-by-step trace.

Consumer	Decision	Where the data lives at query time
Support console (live)	virtualize	live replica + Iceberg, point lookup
Finance dashboard (high vol)	copy (ETL)	local materialized table + index
Data-science (ad-hoc)	virtualize	federated across sources on demand
Partner shipment feed	virtualize + cache	API, cached 120s, rate-limited
Any hot aggregate	pushdown + cache	source read once per TTL

After the rollout, the support console reads virt.customer_live_orders as a per-customer point lookup against replicas — always fresh, negligible source load; the finance dashboard reads a nightly-built fct_finance_daily — one heavy join per load, millisecond reads; the data-science surface federates live across Iceberg and the partner API on demand; the fragile partner API is shielded by a rate limit and a 120-second cache; and any virtual aggregate that grows past 500 queries a minute is promoted to a materialized table. No virtual view touches an OLTP primary, and no copy exists that a source could have served cheaply.

Output:

Metric	All-copy	All-virtual	Per-pattern split
Support order-status freshness	stale (last load)	live	live
Finance dashboard read latency	fast	slow (re-federate)	fast
Pipelines to maintain	one per dataset	none	only the hot paths
OLTP primary load from analytics	none	severe	none (replicas + cache)
Storage duplicated	everything	nothing	only the hot path

Why this works — concept by concept:

• Per-access-pattern split — copy and virtualize are chosen per consumer against freshness, volume, and source load, so each dataset sits at the right corner of the trade space instead of being forced to a platform-wide default.
• Replica-backed base views — pointing virtual views at read replicas gives virtualization's freshness (seconds of lag) without letting analytical scans contend with the transactions on the OLTP primary — the single most important safety rule for virtualizing operational sources.
• Materialized hot path — the one high-volume, freshness-tolerant consumer (the finance dashboard) is copied so its repeated heavy join runs once per load, not once per view, turning O(reads) source work into O(loads).
• Cached / rate-limited fragile sources — the partner API is protected by a cache and a rate limit, so virtualization's live reach does not translate into hammering a source that cannot take it.
• Cost — one pipeline for the proven hot path plus stateless views for everything else, versus a pipeline per dataset (all-copy) or a source-melting re-federation per query (all-virtual). The eliminated cost is the pile of pipelines and copies that exist only because "load it into the warehouse" was the reflex — O(hot paths) materialization instead of O(datasets).

ETL
Topic — etl
ETL problems on copy pipelines and load strategies

Practice →

Design Topic — design Design problems on copy-vs-query and the logical layer

Practice →

---

2. Denodo — the logical data fabric and its optimizer

Wrap sources as base views, compose derived views, publish services — the optimizer decides pushdown and caching

The mental model in one line: Denodo is a metadata-driven logical layer built in three tiers — base views that wrap each physical source (a JDBC table, a REST endpoint, a flat file) one-to-one, derived views that join, transform, and aggregate base and other derived views across sources, and published data services (JDBC/ODBC, REST, GraphQL) that expose the derived views to consumers — all resolved at query time by a cost-based optimizer that rewrites the query, pushes work down to the sources, chooses join strategies and which side to ship, and can serve from a caching subsystem instead of re-federating — so you model the estate once as a governed data fabric and let the engine turn a consumer's query into the cheapest federated plan. You declare what the logical datasets are and who may see them; Denodo decides how to fetch them.

The three-layer view architecture.

• Base views. One per source object — a base view over crm.customers in Postgres, another over an S3 Parquet dataset, another over a REST API. The base view captures the source's schema and how to read it; it is the only place a physical source is named.
• Derived views. Composed from base (and other derived) views with joins, projections, filters, unions, and aggregations — this is where a customer sitting in Postgres is joined to orders sitting in S3 to form a logical customer_360, entirely in metadata.
• Published data services. A derived view is exposed as JDBC/ODBC for BI tools, as a REST or GraphQL data service for applications, or as a data-catalog entry — the same governed dataset, many contracts.
• Associations and the catalog. Denodo captures relationships and metadata so the catalog is browsable and the optimizer knows how views relate — the fabric is self-describing.

The cost-based optimizer.

• Query rewriting. The optimizer flattens the view stack into a single logical plan, prunes unused branches and columns, and pushes predicates and aggregations toward the sources.
• Pushdown. Whatever a source can execute — filters, projections, joins within the same source, aggregations — is delegated to it, so only the minimal result crosses the wire (the mechanics are section 4).
• Join strategy and data movement. For cross-source joins the optimizer chooses among strategies (nested-loop, hash, merge) and decides which side to ship — moving the smaller side to the larger, or using data movement to relocate one side into a source that can join locally.
• Statistics. Like any cost-based optimizer, it relies on row-count and cardinality statistics; stale or missing stats are the usual reason a federated plan goes wrong.

Caching — trading freshness for isolation.

• Full cache. The entire derived view is materialized into a cache database (a dedicated JDBC store) and refreshed on a schedule — effectively an ETL copy managed by the virtualization layer, for views that are hot and freshness-tolerant.
• Partial cache. Only queried subsets are cached on demand, so the working set is cached without materializing the whole view.
• Incremental cache. The cache is topped up with new/changed rows rather than fully rebuilt, cutting refresh cost for large slowly-changing views.
• When to cache. Cache a derived view when it is queried far more often than its sources change and the sources are expensive or fragile to hit repeatedly — the same logic as materializing, but reversible and centralised.

Governance — one place for policy.

• Row and column security. Policies on a derived view restrict rows (by role/tenant) and mask or drop columns, enforced by the layer for every consumer and contract.
• Lineage and catalog. Because every view names its inputs, Denodo derives end-to-end lineage from source column to published field — governance and impact analysis for free.
• Single enforcement point. The fabric is where security lives, so the source and the copy cannot drift on policy — there is no copy.

The failure modes senior engineers pre-empt.

• Base views over primaries. Wrapping an OLTP primary as a base view and letting hot derived views hit it relocates analytical load onto the system of record. Mitigation: base views over replicas; cache hot derived views.
• Cache staleness. A full-cached view served long after its refresh returns stale data. Mitigation: TTLs aligned to the refresh cadence; incremental cache for freshness-sensitive views.
• Missing statistics. Without source stats the optimizer picks bad join orders and ships the wrong side. Mitigation: gather and refresh stats; verify the plan on cross-source joins.

Common interview probes on Denodo.

• "What are the three view layers?" — base (wrap a source), derived (join/transform across sources), published (JDBC/REST/GraphQL services).
• "How does Denodo make federation fast?" — a cost-based optimizer that rewrites, pushes down, and chooses which side to ship, plus caching.
• "When do you cache?" — hot, freshness-tolerant views over expensive/fragile sources; full, partial, or incremental by size and freshness need.
• "Where does governance live?" — on the derived views, one policy per dataset, enforced for every consumer.

Worked example — a base view and a derived view across two sources

Detailed explanation. The core Denodo loop: create a base view per source object, then compose a derived view that joins them into a logical dataset — no data is copied, and the join is resolved at query time. Build a customer_360 from a Postgres customers base view and an S3/Parquet orders base view.

• Base views. bv_customers over Postgres, bv_orders over S3 Parquet.
• Derived view. dv_customer_360 joins and aggregates them.
• The result. One logical dataset over two systems, defined entirely in metadata.

Question. Define the two base views and the derived view that federates them, and explain what physically happens when a consumer queries the derived view.

Input.

Object	Layer	Source
bv_customers	base view	Postgres crm.customers
bv_orders	base view	S3 Parquet sales/orders
dv_customer_360	derived view	join + aggregate of the two
consumer query	published	SELECT ... WHERE region = 'EU'

Code.

-- Denodo VQL — base views wrap each source ONE-to-one (the only place a source is named).
CREATE WRAPPER JDBC pg_crm SOURCE = pg_replica SCHEMA = crm;   -- Postgres replica
CREATE BASE VIEW bv_customers
  FROM pg_crm.customers;                     -- id, name, region, tier

CREATE WRAPPER DF s3_sales SOURCE = s3 PATH = 's3://lake/sales/orders/' FORMAT = parquet;
CREATE BASE VIEW bv_orders
  FROM s3_sales;                             -- order_id, customer_id, total_cents, updated_at

-- Derived view: join across the two sources + aggregate. NO copy — resolved at query time.
CREATE VIEW dv_customer_360 AS
SELECT c.id AS customer_id, c.name, c.region, c.tier,
       count(o.order_id)                       AS lifetime_orders,
       coalesce(sum(o.total_cents), 0)         AS lifetime_cents
FROM   bv_customers c
LEFT JOIN bv_orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.region, c.tier;

# Consumer query against the published derived view:
SELECT customer_id, name, lifetime_cents FROM dv_customer_360 WHERE region = 'EU';

# What Denodo physically does (optimizer):
#   1. Push `region = 'EU'` into bv_customers -> Postgres returns ONLY EU customers.
#   2. Fetch matching orders from S3 for those customer_ids (semi-join movement).
#   3. Aggregate + join in the Denodo engine; return the small result.
# No customer or order data is stored in Denodo — only the view definitions are.

Step-by-step explanation.

1. Each base view wraps exactly one source object and is the only place that source is named — bv_customers knows it is Postgres, bv_orders knows it is S3 Parquet. Everything above the base layer is source-agnostic.
2. dv_customer_360 is a pure metadata definition: a join and aggregation over the two base views. Creating it copies nothing; it is a query template the optimizer will expand when someone reads it.
3. When a consumer filters WHERE region = 'EU', the optimizer pushes that predicate into bv_customers, so Postgres returns only EU customers rather than all of them — the filter reaches the source, not just the engine.
4. Denodo then fetches only the orders for those EU customers from S3 (a semi-join / data-movement decision), joins and aggregates in its engine, and returns the small result — the sources do the heavy filtering, the engine does the cross-source combination.
5. The senior point is that the logical dataset (customer_360) exists independently of the physical layout: you could later move orders from S3 to Snowflake by editing bv_orders alone, and every consumer of dv_customer_360 is unaffected — the decoupling that makes a fabric maintainable.

Output.

Step	Where it runs	Data moved
filter region = 'EU'	Postgres (pushed down)	only EU customers
fetch matching orders	S3 (by customer_id)	only those orders
join + aggregate	Denodo engine	small result
storage in Denodo	none	view definitions only

Rule of thumb. Model one base view per source object and compose derived views above them; the base layer is the only place physical sources are named, so the logical dataset survives any source migration. Let the optimizer push filters down to the sources — the derived view is a definition, not a copy.

Worked example — caching a hot derived view

Detailed explanation. When a derived view is queried far more often than its sources change, re-federating every query wastes source load and pays federation latency for no freshness benefit. Denodo's cache materializes the view into a cache store on a schedule — an ETL copy the virtualization layer manages for you. Add a full cache to dv_customer_360.

• The trigger. The view is hit thousands of times an hour; sources change hourly.
• The cache. Full cache into a cache database, refreshed hourly.
• The trade. Bounded staleness (up to an hour) for near-zero source load and fast reads.

Question. Configure a full cache on dv_customer_360 with an hourly refresh, and explain how consumers, freshness, and source load change.

Input.

Aspect	Uncached	Full cache (1h)
Source hit per query	yes (re-federate)	no (served from cache)
Freshness	live	≤ 1 hour
Read latency	federation + slowest source	local cache scan
Source load	every query	once per refresh

Code.

-- Enable a FULL cache on the derived view, stored in a dedicated cache database.
ALTER VIEW dv_customer_360
  CACHE = FULL
  CACHE DATABASE = cache_store          -- a JDBC store Denodo owns for caches
  TTL = 3600;                           -- serve cached rows for up to 1 hour

-- Scheduled refresh (Denodo scheduler / job): rebuild the cache each hour,
-- ideally right after the upstream sources settle.
REFRESH CACHE FOR dv_customer_360;      -- or INCREMENTAL for large slowly-changing views

# Effect on a consumer query:
SELECT * FROM dv_customer_360 WHERE region = 'EU';

#   cache MISS (first read after refresh)  -> federate sources, populate cache
#   cache HIT  (rest of the hour)          -> served from cache_store, sources untouched
#
# 5,000 queries/hour, sources change hourly:
#   uncached -> 5,000 cross-source federations/hour (heavy source load)
#   cached   -> 1 refresh/hour + 5,000 cheap cache reads (source read ONCE)

Step-by-step explanation.

1. CACHE = FULL tells Denodo to materialize the entire dv_customer_360 into cache_store and serve reads from there instead of federating — the virtualization layer is now running a managed ETL copy of the view.
2. The TTL = 3600 and the scheduled REFRESH bound staleness to an hour: reads are served from the cache until the next refresh rebuilds it, so consumers see data at most an hour old — aligned to how often the sources actually change.
3. Source load collapses from per query to per refresh: 5,000 reads an hour become one cross-source federation (the refresh) plus 5,000 cheap cache scans, so the fragile sources are read once instead of five thousand times.
4. For a large, slowly-changing view, an INCREMENTAL cache tops up only new/changed rows instead of rebuilding the whole thing — the same freshness at a fraction of the refresh cost, the caching analogue of incremental ETL.
5. The senior framing: caching a derived view is reversible materialization — you get the read speed and source isolation of a copy, but the definition stays virtual, so you can drop the cache and go live again without rewriting anything. It is the hybrid pattern (section 5) expressed inside Denodo.

Output.

Metric	Uncached	Full cache (1h)
Source federations / hour	5,000	1 (refresh)
Read latency	seconds (federate)	milliseconds (cache)
Freshness	live	≤ 1 hour
Reversible to live	n/a	yes (drop cache)

Rule of thumb. Cache a derived view when it is read far more often than its sources change and the sources are expensive or fragile — full cache for whole hot views, partial for a working set, incremental for large slowly-changing ones. Align the TTL to the source change cadence; the cache is reversible materialization, so you keep the virtual definition.

Worked example — the cost-based optimizer's ship-the-smaller-side decision

Detailed explanation. The optimizer's most consequential cross-source choice is which side of a join to move. Moving the wrong side across the network can turn a fast query into a multi-gigabyte transfer. Reason through a join between a small dimension in Postgres and a large fact in S3, and how statistics drive the plan.

• The join. Small bv_customers (Postgres, ~100k rows) to large bv_orders (S3, ~500M rows).
• The choice. Ship the small side to the large, or drag the large side to the small.
• The lever. Row-count and cardinality statistics tell the optimizer which is smaller.

Question. Explain how the optimizer should join a small Postgres dimension to a large S3 fact, and what goes wrong when statistics are missing.

Input.

Side	Rows	If shipped
bv_customers (Postgres)	~100k	small transfer — ship this
bv_orders (S3)	~500M	huge transfer — never ship this
stats present	—	optimizer ships the small side
stats missing	—	may ship the large side (disaster)

Code.

Query: SELECT c.region, sum(o.total_cents)
       FROM bv_customers c JOIN bv_orders o ON o.customer_id = c.id
       WHERE c.tier = 'enterprise'
       GROUP BY c.region;

WITH statistics (optimizer knows customers << orders):
  1. Push `tier = 'enterprise'` into Postgres -> ~5k customer rows returned.
  2. SHIP the 5k customer ids to the S3 side as an IN-list / semi-join.
  3. S3 scans only orders for those 5k customers (partition/file pruning helps).
  4. Aggregate in the engine. Data moved: ~5k ids + matching orders. FAST.

WITHOUT statistics (optimizer guesses wrong):
  1. Pull ALL 500M orders from S3 into the engine.
  2. Then join to customers in memory.
  -> 500M-row transfer + spill. Minutes-to-hours, or OOM. DISASTER.

-- The fix: give the optimizer statistics so it ships the SMALL side.
GATHER STATISTICS ON bv_customers;      -- row counts, distinct values
GATHER STATISTICS ON bv_orders;         -- so the planner knows 100k << 500M
-- Verify the plan before trusting it on a cross-source join:
DESC QUERY PLAN
SELECT c.region, sum(o.total_cents)
FROM bv_customers c JOIN bv_orders o ON o.customer_id = c.id
WHERE c.tier = 'enterprise' GROUP BY c.region;

Step-by-step explanation.

1. The predicate tier = 'enterprise' is pushed into Postgres first, shrinking the small side from 100k to ~5k rows before any cross-source work — pushdown and join strategy compound.
2. With statistics, the optimizer knows customers is orders of magnitude smaller, so it ships the ~5k customer ids to the S3 side as a semi-join, and S3 scans only the matching orders (helped by file/partition pruning) — the data moved is tiny.
3. Without statistics, the optimizer has no basis to prefer the small side and may pull all 500M orders into the engine to join them locally — a multi-gigabyte transfer that spills to disk or runs out of memory, turning a sub-second query into an outage.
4. GATHER STATISTICS is the fix: row counts and distinct-value estimates let the planner cost the two shipping directions and choose the cheap one — the same reason a warehouse optimizer needs ANALYZE.
5. The senior habit is to verify the plan (DESC QUERY PLAN) on any cross-source join, because federated plans are far more sensitive to bad stats than single-source ones — the penalty for shipping the wrong side spans the network, not just the buffer pool.

Output.

Condition	Data moved	Latency
Stats present, ship small side	~5k ids + matching orders	fast (sub-second)
Stats missing, ship large side	~500M rows	minutes / OOM
After GATHER STATISTICS	small side shipped	fast again
Plan verified with DESC	—	no surprises

Rule of thumb. On cross-source joins, make sure the optimizer has statistics so it ships the smaller side, and verify the plan before trusting it. A federated join that moves the wrong side pays the cost across the network — the difference between a sub-second query and a data-transfer incident.

Senior interview question on building a Denodo logical layer

A senior interviewer might ask: "Stand up a Denodo logical layer over a Postgres CRM and an S3 order lake with zero data copied by default. Cover how you structure base, derived, and published views; how the cost-based optimizer keeps a cross-source join fast; how you protect a fragile source and a hot dashboard with caching; where governance lives; and how you would promote a hot virtual view toward materialization without breaking consumers."

Solution Using layered views, pushdown, replica-backed sources, caching, and view-level governance

-- 1. Base views over REPLICAS/lake — the only place a physical source is named.
CREATE BASE VIEW bv_customers FROM pg_crm_replica.customers;   -- Postgres replica
CREATE BASE VIEW bv_orders    FROM s3_sales;                    -- S3 Parquet lake

-- 2. Derived view: the logical dataset, resolved at query time (no copy).
CREATE VIEW dv_customer_360 AS
SELECT c.id AS customer_id, c.name, c.region, c.tier,
       count(o.order_id) AS lifetime_orders,
       coalesce(sum(o.total_cents),0) AS lifetime_cents
FROM bv_customers c
LEFT JOIN bv_orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.region, c.tier;

-- 3. Governance on the derived view: row security + column masking, one policy, all consumers.
CREATE ROW RESTRICTION tenant_rows ON dv_customer_360
  USING (region = CURRENT_ROLE_REGION());     -- scope rows by the caller's role
CREATE COLUMN MASK mask_pii ON dv_customer_360
  COLUMN name USING (CASE WHEN HAS_ROLE('pii_reader') THEN name ELSE 'REDACTED' END);

-- 4. Caching for the hot dashboard + stats for the optimizer.
GATHER STATISTICS ON bv_customers; GATHER STATISTICS ON bv_orders;   -- ship the small side
ALTER VIEW dv_customer_360 CACHE = FULL CACHE DATABASE = cache_store TTL = 3600;
REFRESH CACHE FOR dv_customer_360;             -- hourly, aligned to source cadence

# 5. Promotion path (virtual -> materialized) without breaking consumers:
#    a) start virtual (no cache)           -> fresh, low volume
#    b) add FULL cache when volume rises    -> reversible materialization, same definition
#    c) if it becomes a core fact, build a warehouse table FROM dv_customer_360
#       and repoint the published service -> consumers keep the SAME view name/contract

Step-by-step trace.

Layer	Component	Purpose
Base	bv_* over replicas/lake	wrap sources; no primary hit
Derived	dv_customer_360	logical join across sources, no copy
Optimizer	pushdown + stats	filter at source, ship small side
Cache	full cache, TTL 3600	source read once/hour, fast reads
Governance	row restriction + mask	one policy, every consumer
Published	JDBC/REST service	stable contract across promotions

After deployment, base views read replicas and the lake (never the OLTP primary); dv_customer_360 is a metadata-only join whose predicates push into the sources and whose cross-source join ships the small customer side thanks to gathered statistics; a full cache with an hourly TTL turns thousands of dashboard reads into one source federation per hour; row restrictions and column masks enforce governance on the view for every consumer and contract; and the published service name stays fixed, so promoting the view from virtual to cached to fully materialized never breaks a consumer.

Output:

Metric	Naive federation	Layered Denodo fabric
OLTP primary load	analytics on primary	zero (replicas only)
Cross-source join cost	may ship large side	ships small side (stats)
Hot-dashboard source hits	per query	once per hour (cache)
Governance points	source + copy (drift)	one view policy
Cost to materialize later	rewrite consumers	repoint one service

Why this works — concept by concept:

• Three-layer views — base views localise every physical source, derived views hold the logical dataset, and published services hold the contract, so a source migration or a virtual-to-materialized promotion changes one layer without touching consumers.
• Cost-based pushdown — the optimizer pushes filters into the sources and, with gathered statistics, ships the smaller side of a cross-source join, so federation moves kilobytes instead of gigabytes.
• Reversible caching — a full cache with a TTL aligned to the source cadence gives copy-like read speed and source isolation while keeping the definition virtual, so materialization is a decision you can undo.
• View-level governance — row restrictions and column masks live on the derived view and apply to every consumer and every published contract, so there is exactly one place policy is enforced and nothing to drift against.
• Cost — one cache refresh per hour plus stateless federated reads, versus a per-query cross-source scan (naive virtualization) or a hand-built pipeline and copy per dataset (all-ETL). The eliminated cost is both the source-melting re-federation and the premature pipelines — O(refresh) managed caching in place of O(queries) federation or O(datasets) ETL.

Design
Topic — design
Design problems on layered logical models and data fabrics

Practice →

Data processing Topic — data-processing Data processing problems on joins, aggregation, and caching

Practice →

---

3. Starburst Galaxy — federated MPP SQL with Trino

One SQL query, many sources — catalogs are connectors and the engine pushes work to each

The mental model in one line: Starburst Galaxy is managed Trino — a massively parallel, in-memory SQL engine that separates storage from compute and reaches every source through a catalog (a configured connector), so a single ANSI-SQL statement can join a Postgres table, an Iceberg dataset on S3, and a MongoDB collection by their fully qualified catalog.schema.table names, with a coordinator planning the query and worker nodes executing it — pushing predicates, projections, and aggregations down into each source and performing cross-source joins itself — which makes it the query-engine face of federation: no data is copied, the lake is queried in place, and Starburst Galaxy adds the managed cluster, autoscaling, access control, and cached/materialized views on top. You point catalogs at sources and write SQL; Trino federates and pushes down.

The Trino execution model.

• Coordinator and workers. The coordinator parses, plans, and schedules; worker nodes execute stages in parallel and exchange data between stages — an MPP engine, not a source itself.
• Separation of storage and compute. Trino owns no storage; it reads from the sources and lakes at query time, so compute scales independently and the same data is queried by many engines.
• In-memory pipelined execution. Stages stream through memory with exchanges between them; there is no intermediate materialization to disk unless a stage spills — fast, but bounded by cluster memory for huge joins.
• ANSI SQL. One dialect over every connector, so a consumer writes standard SQL and never learns each source's native query language.

Catalogs and connectors — the federation surface.

• A catalog is a connector instance. postgres, iceberg, mysql, mongodb, tpch — each catalog is a configured connector to one source; a source can have several catalogs (e.g. prod vs replica).
• catalog.schema.table addressing. postgres.crm.customers and iceberg.sales.orders are addressed uniformly, and a single JOIN can span them — the federation is in the naming.
• Lakehouse connectors. Iceberg/Delta/Hive connectors read open table formats on object storage directly, with partition and file pruning — the "query the lake in place" core of virtualization.
• Operational-source connectors. Postgres/MySQL/Mongo connectors read live operational data, so Trino can join a lake fact to a live dimension — with the source-load caution from section 1.

Pushdown and performance.

• What pushes. Predicate (WHERE), projection (column pruning), and — for capable connectors — aggregate, LIMIT, and TopN pushdown send work into the source so it returns a minimal result (section 4 details the mechanics).
• Cross-source joins run in the engine. A join spanning two catalogs cannot be pushed to either source; Trino moves data between workers (broadcast or partitioned exchange) and joins in memory.
• Dynamic filtering. Trino can build a filter from the small side of a join at runtime and push it to the large side's scan, dramatically cutting the rows read from the lake.
• Cost-based optimizer. With table statistics, Trino reorders joins and picks distribution (broadcast vs partitioned) — the same stats discipline as Denodo.

Starburst Galaxy on top of Trino.

• Managed clusters and autoscaling. Galaxy runs and scales the coordinator/workers, so you consume federated SQL without operating Trino.
• Cached and materialized views. Galaxy can cache query results and maintain materialized views over federated queries — the built-in hybrid: virtualize by default, materialize the hot path.
• Access control and catalogs. Role-based access control, biac (built-in access control) policies, and a catalog/data-products layer add the governance a raw Trino lacks.
• Great Lakes / table management. Managed table maintenance (compaction, retention) for Iceberg so the lake stays fast without a separate job.

The failure modes senior engineers pre-empt.

• Cross-source join blowups. Joining two large sources forces a big in-engine exchange that can spill or OOM. Mitigation: push filters down first, use dynamic filtering, or materialize one side.
• Virtualizing a hot query over OLTP. A Postgres catalog pointed at a primary turns dashboards into OLTP load. Mitigation: point the catalog at a replica; cache/materialize hot results.
• Blind trust in pushdown. Assuming an aggregate pushed when it did not means the engine pulled every row. Mitigation: read EXPLAIN and confirm the pushed-down predicate/aggregate.

Common interview probes on Starburst/Trino.

• "What is a catalog?" — a configured connector to a source; catalog.schema.table addresses it, and a query can span catalogs.
• "Where do cross-source joins run?" — in the Trino engine (workers exchange data); only same-source joins push down.
• "How does Trino cut lake reads at runtime?" — dynamic filtering builds a filter from the small side and pushes it to the large scan.
• "What does Starburst Galaxy add over Trino?" — managed/autoscaled clusters, access control, and cached/materialized views for the hot path.

Worked example — a federated cross-source join (Postgres + Iceberg)

Detailed explanation. The signature Trino query joins a live operational dimension to a lake fact — a Postgres customers table to an Iceberg orders table on S3 — in one SQL statement across two catalogs. Write it and reason about what runs where.

• The catalogs. postgres (a replica) and iceberg (S3).
• The query. Join customers to orders, filter, and aggregate revenue by region.
• The execution. Filters push to each source; the join runs in the engine.

Question. Write a single federated query joining a Postgres dimension to an Iceberg fact and describe which parts run at the sources versus in the engine.

Input.

Piece	Catalog	Role
postgres.crm.customers	postgres (replica)	dimension (small)
iceberg.sales.orders	iceberg (S3)	fact (large)
filter	pushed to each source	shrink inputs
join + group by	Trino engine	combine, aggregate

Code.

-- One ANSI-SQL statement spanning TWO catalogs — no data copied.
SELECT c.region,
       count(*)                    AS orders,
       sum(o.total_cents) / 100.0  AS revenue_usd
FROM   iceberg.sales.orders    o                 -- lake fact on S3
JOIN   postgres.crm.customers  c                 -- live dimension (replica)
       ON c.id = o.customer_id
WHERE  o.order_date >= DATE '2026-01-01'          -- pushed into Iceberg (partition prune)
  AND  c.tier = 'enterprise'                      -- pushed into Postgres
GROUP  BY c.region
ORDER  BY revenue_usd DESC;

# What Trino does (coordinator plan):
#   Stage @ iceberg : scan orders WHERE order_date >= 2026-01-01  (partition + file pruning)
#                     project only (customer_id, total_cents)     (projection pushdown)
#   Stage @ postgres: SELECT id, region FROM customers WHERE tier = 'enterprise'  (pushed)
#   Dynamic filter  : build customer_id IN (<enterprise ids>) from the small side,
#                     push it into the Iceberg scan -> read far fewer order files.
#   Stage @ workers : JOIN the two streams in memory (broadcast the small customer side),
#                     GROUP BY region, ORDER BY. Return a tiny result.

Step-by-step explanation.

1. The query names both catalogs (iceberg.sales.orders, postgres.crm.customers) and joins them as if they were one database — the federation is entirely in the fully qualified names; the consumer writes ordinary SQL.
2. The order_date predicate pushes into the Iceberg connector, which prunes partitions and files so only 2026 data is scanned, and the tier predicate pushes into Postgres so only enterprise customers are returned — both sources shrink their output before anything crosses the wire.
3. Projection pushdown means Iceberg returns only customer_id and total_cents, not every column of the fact — the scan reads the minimum needed for the join and aggregate.
4. Dynamic filtering builds a customer_id IN (...) set from the small enterprise-customer side at runtime and pushes it into the Iceberg scan, so the lake reads only order files that can possibly match — a large reduction the static predicate alone could not achieve.
5. The cross-source join itself runs in the Trino engine: because the small customer side fits in memory, Trino broadcasts it to the workers scanning orders and joins in a single pass, then aggregates — the sources did the filtering, the engine did the combining.

Output.

Operation	Runs at	Effect
WHERE order_date >=	Iceberg (S3)	partition/file pruning
WHERE tier = 'enterprise'	Postgres	few customer rows
projection	both sources	minimal columns
dynamic filter	Iceberg scan	fewer files read
join + group by	Trino engine	small final result

Rule of thumb. Federate with catalog.schema.table and write plain SQL; let predicates and projections push to each source and let dynamic filtering shrink the lake scan from the small side. The engine only has to join and aggregate a small, pre-filtered stream — which is when cross-source federation is fast.

Worked example — aggregate pushdown versus pulling rows

Detailed explanation. The difference between a fast and a slow single-source Trino query is often whether the aggregate pushed down. If it did, the source computes the SUM/GROUP BY and returns a handful of rows; if it did not, Trino pulls every row and aggregates itself. Compare the two for a Postgres-backed metric and force the good plan.

• The query. SELECT region, sum(total_cents) ... GROUP BY region over a Postgres catalog.
• Pushed. Postgres runs the aggregate; returns one row per region.
• Not pushed. Trino pulls all rows and aggregates — huge transfer.

Question. Show an aggregate that pushes into Postgres versus one that does not, and explain how to keep it pushable.

Input.

Case	Source returns	Transfer
aggregate pushes down	one row per region	tiny
aggregate does not push	every raw row	huge
cause of no-push	unsupported expr / function	forces engine-side
fix	keep expr source-native	restores pushdown

Code.

-- PUSHES DOWN: plain SUM/GROUP BY on native columns -> Postgres computes it.
SELECT region, sum(total_cents) AS revenue_cents, count(*) AS orders
FROM   postgres.sales.orders
GROUP  BY region;
-- Postgres executes: SELECT region, sum(total_cents), count(*) ... GROUP BY region
-- and returns ~N-region rows. Trino just relays them.

-- DOES NOT PUSH: a Trino-specific function in the aggregate keeps it engine-side.
SELECT region, sum(from_big_endian_64(to_utf8(total_cents))) AS weird
FROM   postgres.sales.orders            -- Postgres can't run Trino's function
GROUP  BY region;                        -- => Trino pulls ALL rows, aggregates itself

# Verify with EXPLAIN — look for the aggregate INSIDE the TableScan (pushed) vs above it.
EXPLAIN SELECT region, sum(total_cents) FROM postgres.sales.orders GROUP BY region;

#   ... TableScan[postgres:... , GROUP BY region, sum(total_cents)]   <- PUSHED (good)
# vs
#   Aggregate[GROUP BY region, sum(...)]
#     └ TableScan[postgres:...  (all rows)]                            <- NOT pushed (bad)

Step-by-step explanation.

1. The first query aggregates native columns with standard SQL functions the Postgres connector supports, so Trino pushes the GROUP BY and sum into Postgres; Postgres computes the metric and returns one small row per region, and Trino merely relays it.
2. The second query wraps total_cents in Trino-specific functions the Postgres connector cannot translate to Postgres SQL, so the aggregate cannot be pushed — Trino must fetch every raw row from Postgres and compute the aggregate in the engine, a potentially massive transfer.
3. The cause is always the same: pushdown requires the source to be able to execute the pushed expression, so any function, cast, or collation the connector cannot translate forces the work back into the engine.
4. EXPLAIN is the diagnostic: a pushed aggregate appears inside the TableScan node (the source runs it), while a non-pushed aggregate appears as a separate Aggregate node above a full TableScan — reading the plan tells you which you got.
5. The senior habit is to keep aggregate expressions source-native (or pre-compute the odd expression in a subquery the source can run), and to confirm pushdown with EXPLAIN on any metric query — because the performance cliff between "source aggregates" and "engine pulls everything" is enormous and invisible without the plan.

Output.

Query	Aggregate runs	Rows transferred
native sum/group by	Postgres (pushed)	one per region
Trino-function aggregate	Trino engine	every raw row
after rewriting native	Postgres (pushed)	one per region
confirmed via EXPLAIN	—	no surprises

Rule of thumb. Keep aggregate and filter expressions source-native so the connector can push them down, and confirm with EXPLAIN that the aggregate sits inside the TableScan. A single unsupported function turns a one-row-per-group result into a full-table transfer — pushdown is the whole performance story for single-source federated queries.

Worked example — a Starburst materialized view for a hot cross-source query

Detailed explanation. When a federated cross-source query is both hot and expensive — a big lake-to-database join hit constantly — Starburst Galaxy can materialize it, so the expensive federation runs on a schedule and consumers read a fast local result. This is the hybrid pattern built into the engine. Materialize the customer-360 join.

• The query. The Postgres-to-Iceberg join from earlier, hit thousands of times an hour.
• The materialized view. Galaxy stores the result and refreshes it on a schedule.
• The trade. Bounded staleness for fast reads and near-zero repeated source load.

Question. Materialize a hot federated query in Starburst Galaxy and explain how reads, freshness, and source load change.

Input.

Aspect	Federated view	Materialized view
Cross-source join per read	yes	no (read stored result)
Freshness	live	≤ refresh interval
Read latency	federation (seconds)	local scan (ms)
Source load	every read	once per refresh

Code.

-- Starburst Galaxy: materialize the hot federated join; refresh on a schedule.
CREATE MATERIALIZED VIEW analytics.mv_customer_360
WITH ( refresh_interval = '1h',                     -- rebuild hourly
       grace_period     = '2h' )                    -- serve slightly stale if refresh lags
AS
SELECT c.region, c.tier, c.id AS customer_id,
       count(o.order_id)      AS lifetime_orders,
       sum(o.total_cents)     AS lifetime_cents
FROM   postgres.crm.customers c
LEFT JOIN iceberg.sales.orders o ON o.customer_id = c.id
GROUP  BY c.region, c.tier, c.id;

-- Consumers read the MV like a table — a local scan, no cross-source join.
SELECT region, sum(lifetime_cents) FROM analytics.mv_customer_360
WHERE tier = 'enterprise' GROUP BY region;

-- Manual refresh if needed (else the interval drives it):
REFRESH MATERIALIZED VIEW analytics.mv_customer_360;

Step-by-step explanation.

1. CREATE MATERIALIZED VIEW stores the result of the expensive Postgres-to-Iceberg join in Galaxy's managed storage, so the cross-source federation runs on the refresh schedule instead of on every consumer read.
2. refresh_interval = '1h' bounds staleness to an hour — consumers see data at most an hour old — while grace_period lets Galaxy keep serving the last good result if a refresh is delayed, so a slow source never makes the view unavailable.
3. Consumers query the materialized view exactly like a table: SELECT ... FROM analytics.mv_customer_360 is a fast local scan with no cross-source join, so a thousand dashboard reads an hour are a thousand cheap scans plus one federation for the refresh.
4. Source load collapses from per-read to per-refresh, which is the entire point of the hybrid: the lake and the database are read once an hour to rebuild the view, and the flood of reads never touches them.
5. The senior framing is that a materialized view is the promoted form of a federated query — you start virtual for reach and freshness, and when a specific cross-source query proves hot and expensive, you materialize just that one, keeping the SQL identical and letting Galaxy manage the refresh. It is section 5's hybrid, one view at a time.

Output.

Metric	Federated (live)	Materialized (1h)
Cross-source joins / hour	thousands	1 (refresh)
Read latency	seconds	milliseconds
Freshness	live	≤ 1 hour
Source load	every read	once per refresh

Rule of thumb. Keep queries federated for reach and freshness, and materialize the specific cross-source query that proves hot and expensive — Starburst Galaxy's materialized views run the federation on a schedule so consumers read a fast local result. Bound staleness with the refresh interval and let the grace period keep the view available when a source lags.

Senior interview question on federated querying with Starburst Galaxy

A senior interviewer might ask: "Give analysts one SQL surface over a Postgres CRM, an Iceberg order lake on S3, and a MongoDB event store using Starburst Galaxy, with nothing copied by default. Cover how catalogs federate the sources, how you keep a cross-source join fast, how you protect the operational databases from analytical load, how you confirm pushdown actually happened, and how you serve a hot dashboard without re-federating on every read."

Solution Using catalogs, pushdown with dynamic filtering, replica catalogs, EXPLAIN, and a materialized view

-- 1. Catalogs = connectors. Operational catalogs point at REPLICAS, not primaries.
--    postgres  -> Postgres READ REPLICA (crm)
--    iceberg   -> Iceberg tables on S3 (sales lake)
--    mongodb   -> MongoDB event store (replica set secondary)

-- 2. Federated query: filters + projections push to each source; join runs in engine.
SELECT c.region, count(*) AS orders, sum(o.total_cents)/100.0 AS revenue_usd
FROM   iceberg.sales.orders   o
JOIN   postgres.crm.customers c ON c.id = o.customer_id
WHERE  o.order_date >= DATE '2026-01-01'      -- partition prune in Iceberg
  AND  c.tier = 'enterprise'                  -- pushed into Postgres (replica)
GROUP  BY c.region;                            -- dynamic filter shrinks the lake scan

-- 3. Confirm pushdown BEFORE trusting the query in production.
EXPLAIN SELECT c.region, sum(o.total_cents)
FROM iceberg.sales.orders o JOIN postgres.crm.customers c ON c.id = o.customer_id
WHERE c.tier = 'enterprise' GROUP BY c.region;
-- Look for: ScanFilterProject[postgres, filter tier='enterprise'] (pushed)
--           dynamicFilter on iceberg scan (fewer files)          (pushed)

-- 4. Serve the hot dashboard from a materialized view (federation runs on a schedule).
CREATE MATERIALIZED VIEW analytics.mv_region_revenue
WITH ( refresh_interval = '15m', grace_period = '1h' ) AS
SELECT c.region, sum(o.total_cents) AS revenue_cents
FROM   iceberg.sales.orders o JOIN postgres.crm.customers c ON c.id = o.customer_id
GROUP  BY c.region;
-- Dashboard reads mv_region_revenue -> local scan, no cross-source join.

-- 5. Access control (Starburst biac): analysts read, cannot reach OLTP primaries.
GRANT SELECT ON iceberg.sales.* TO ROLE analyst;
GRANT SELECT ON postgres.crm.customers TO ROLE analyst;   -- replica catalog only
DENY  SELECT ON postgres_primary.* TO ROLE analyst;        -- no primary access

Step-by-step trace.

Layer	Component	Purpose
Catalogs	connectors to replicas + lake	federate sources, protect primaries
Pushdown	predicate/projection to sources	shrink inputs before the wire
Dynamic filter	small side → lake scan	read fewer order files
EXPLAIN	plan inspection	confirm pushdown, no surprises
Materialized view	15m refresh	hot dashboard reads local
Access control	biac roles	analysts on replicas only

After deployment, analysts write one ANSI-SQL statement across iceberg, postgres, and mongodb; predicates and projections push into each source and dynamic filtering shrinks the Iceberg scan from the enterprise-customer side; EXPLAIN confirms the pushed filters and dynamic filter before anything ships to production; the hot region-revenue dashboard reads a 15-minute materialized view so the cross-source join runs four times an hour instead of on every load; and access control binds analysts to replica catalogs, so no analytical query can reach an OLTP primary.

Output:

Metric	Naive federation	Starburst Galaxy setup
Sources addressed	separate tools/dialects	one SQL surface (catalogs)
Cross-source join cost	pulls full tables	pushed + dynamic-filtered
OLTP primary load	analytics on primary	zero (replica catalogs)
Pushdown certainty	assumed	verified (EXPLAIN)
Hot dashboard	re-federate every read	local MV, refresh 15m

Why this works — concept by concept:

• Catalogs as the federation surface — each catalog is a connector, so catalog.schema.table lets one SQL statement span a lake and several databases with no copy, and pointing operational catalogs at replicas keeps analytical load off the primaries.
• Pushdown plus dynamic filtering — predicates and projections execute at the sources and a runtime filter built from the small side prunes the lake scan, so the engine only ever joins a small, pre-filtered stream.
• EXPLAIN-verified plans — reading the plan confirms filters and aggregates actually pushed, turning the invisible performance cliff between "source filters" and "engine pulls everything" into something you check, not assume.
• Materialized views for the hot path — Galaxy runs the expensive federation on a schedule and serves consumers a local scan, the built-in hybrid that keeps freshness where it is needed and speed where it is hot.
• Cost — filters and aggregates run at the sources, dynamic filtering cuts lake reads, and one materialized view absorbs the hot dashboard, versus pulling full tables into the engine and joining them cold. The eliminated cost is the full-table cross-source exchange and the OLTP contention — O(pushed result) transfer plus O(refresh) materialization instead of O(rows) per query.

Joins
Topic — joins
Joins problems on cross-source and multi-table queries

Practice →

Data processing Topic — data-processing Data processing problems on MPP SQL and federated scans

Practice →

---

4. Query pushdown and federation mechanics

Push filters and aggregates to the source; when a join spans sources, the engine moves the smaller side

The mental model in one line: query pushdown is the optimization that decides where each piece of a federated query runs — a virtualization engine tries to delegate predicates (WHERE), projections (column pruning), aggregations (GROUP BY/SUM), and same-source joins down into the source so the source returns the smallest possible result, and only performs the operations a source cannot do — chiefly federation's defining case, the cross-source join — inside its own engine by moving data between workers (broadcasting the small side or partitioning both) — so the entire performance of virtualization comes down to how much work pushes down and how little data has to move, and the failure cases are exactly the expressions a source cannot execute, which silently drag every row into the engine. Maximise pushdown, minimise data movement, and verify both with EXPLAIN.

The kinds of pushdown.

• Predicate pushdown. A WHERE clause is sent to the source so it filters before returning rows — the single biggest win, because it can turn a billion-row scan into a thousand-row result at the source.
• Projection pushdown. Only the columns the query needs are requested, so a wide table returns a few columns — huge for columnar lakes where fewer columns means fewer bytes read.
• Aggregate pushdown. GROUP BY and aggregate functions run at the source, which returns one row per group instead of raw rows — the difference between transferring a summary and transferring the whole fact.
• LIMIT / TopN pushdown. LIMIT and ORDER BY ... LIMIT push down so the source stops early and returns only the top rows — cheap paging and "latest N" without a full scan.

Cross-source joins — where pushdown stops.

• Why they can't push. No single source holds both tables, so neither can execute the join; the engine must fetch (filtered) rows from each and join them itself — this is the irreducible core of federation.
• Data movement. The engine relocates data between workers: broadcast copies the small side to every worker holding the large side; partitioned exchange hashes both sides on the join key so matching rows meet on the same worker.
• Ship the smaller side. Broadcasting is cheap only if one side is small, so the optimizer (with statistics) broadcasts the small side and partitions when both are large — the ship-the-smaller-side rule from section 2.
• Dynamic filtering / semi-join reduction. Build a filter from the small side's join keys and push it to the large side's scan, so the large source reads only rows that can match — recovering some pushdown for the cross-source case.

When the optimizer cannot push down.

• Unsupported functions. An engine-specific function, regex, or UDF the source cannot execute keeps the whole expression in the engine and pulls raw rows.
• Type / collation mismatch. A cast or a different string collation between engine and source can make a predicate unsafe to push, so the engine filters instead.
• Non-deterministic or engine-semantics functions. now()-style or engine-defined semantics must run in the engine to stay correct, blocking pushdown.
• Connector limitations. Some connectors implement only predicate/projection pushdown, not aggregate — capability varies, so the same SQL pushes differently per source.

Verifying and forcing pushdown.

• Read EXPLAIN. The plan shows what pushed: a filter/aggregate inside the source scan node pushed; a separate operator above a full scan did not.
• Rewrite to source-native. Replace an unsupported function with one the source supports, or pre-compute the odd expression in a subquery the source can run.
• Pre-filter before the join. Apply the most selective predicates so the smallest possible streams reach the cross-source join.
• Materialize the un-pushable. If a query structurally cannot push (a mandatory cross-source join over huge data), materialize one side or the whole result — the hybrid escape hatch.

The failure modes senior engineers pre-empt.

• Silent no-push. An unsupported function quietly disables aggregate pushdown and the engine pulls every row — slow, and invisible without the plan. Mitigation: EXPLAIN on every heavy query; keep expressions source-native.
• Broadcasting a large side. Missing statistics make the optimizer broadcast a big table to every worker — memory blowup. Mitigation: gather statistics; check the exchange type in the plan.
• Un-pre-filtered cross-source joins. Joining two full tables across sources moves enormous data. Mitigation: push selective filters first; use dynamic filtering; materialize if structural.

Common interview probes on pushdown and federation.

• "What pushes down and what doesn't?" — predicates, projections, aggregates, LIMIT, same-source joins push; cross-source joins and unsupported expressions do not.
• "Where does a cross-source join run?" — in the engine, moving data (broadcast small side / partition both).
• "How do you know pushdown happened?" — read EXPLAIN; the operator is inside the source scan if pushed.
• "A query got slow after a small change — why?" — likely a function or cast broke pushdown, so the engine now pulls raw rows.

Worked example — verifying predicate, projection, and aggregate pushdown with EXPLAIN

Detailed explanation. The only reliable way to know what pushed is to read the plan. Take a single-source metric query and read its EXPLAIN to confirm the filter, the projection, and the aggregate all reached the source. Then break one and watch the plan change.

• The query. sum by region with a date filter on a Postgres catalog.
• The good plan. Filter, projection, and aggregate inside the source scan.
• The broken plan. A wrapped column pops the aggregate above the scan.

Question. Read an EXPLAIN to confirm all three pushdowns, and identify the change that would move the aggregate back into the engine.

Input.

Pushdown	In the plan (pushed)	In the plan (not pushed)
predicate	inside source scan	Filter node above scan
projection	few columns in scan	all columns fetched
aggregate	agg inside source scan	Aggregate node above scan
verdict	tiny transfer	full-table transfer

Code.

EXPLAIN
SELECT region, sum(total_cents) AS revenue_cents
FROM   postgres.sales.orders
WHERE  order_date >= DATE '2026-01-01'
GROUP  BY region;

# GOOD PLAN — everything pushed into the Postgres source scan:
Fragment 0 [SINGLE]
  Output[region, revenue_cents]
    RemoteExchange
      TableScan[postgres:sales.orders,
                pushdown = {
                  filter:      order_date >= 2026-01-01,   # predicate pushdown
                  projection:  [region, total_cents],      # projection pushdown
                  aggregate:   GROUP BY region, sum(total_cents)  # aggregate pushdown
                }]
# Postgres returns ~N-region rows. Transfer: tiny.

# BROKEN PLAN — wrap total_cents in an engine-only function and the aggregate un-pushes:
#   SELECT region, sum(my_engine_udf(total_cents)) ... GROUP BY region
Fragment 0 [SINGLE]
  Aggregate[GROUP BY region, sum(...)]        # <- now in the ENGINE
    RemoteExchange
      TableScan[postgres:sales.orders, filter: order_date>=..., projection:[region,total_cents]]
# Postgres returns EVERY matching row; Trino aggregates. Transfer: huge.

Step-by-step explanation.

1. In the good plan, the TableScan node carries a pushdown block listing the filter, the projection, and the aggregate — all three executed by Postgres, so the source returns only one row per region and the transfer is trivial.
2. Predicate pushdown (order_date >= 2026-01-01) means Postgres never sends rows outside the date range; projection pushdown means it sends only region and total_cents; aggregate pushdown means it sends grouped sums, not raw rows — the three compounding to a tiny result.
3. Wrapping total_cents in an engine-only function breaks aggregate pushdown specifically: Postgres cannot run the function, so it cannot compute the grouped sum, and the Aggregate operator moves above the scan into the engine.
4. The consequence is visible in the plan: the broken version's TableScan still pushes the filter and projection, but now returns every matching row for the engine to aggregate — the same query, an enormous transfer, and a large slowdown, all from one wrapped column.
5. The senior discipline is to treat EXPLAIN as mandatory on heavy federated queries: the operator's position (inside the scan versus above it) is the ground truth of what pushed, and it is the only way to catch a silent no-push before it becomes a production incident.

Output.

Plan	Aggregate location	Rows from source
good (native)	inside source scan	one per region
broken (wrapped column)	engine (above scan)	every matching row
predicate	pushed in both	date-filtered
projection	pushed in both	two columns

Rule of thumb. Read EXPLAIN on every heavy federated query and confirm the filter, projection, and aggregate sit inside the source scan node. If an aggregate is above the scan, an unsupported expression broke pushdown and the source is now shipping raw rows — rewrite it source-native before you ship.

Worked example — a cross-source join and its data movement

Detailed explanation. A join spanning two catalogs cannot push to either source, so the engine moves data. The cost is entirely in how much moves and which distribution is chosen. Walk a small-dimension-to-large-fact cross-source join and the broadcast-versus-partitioned decision.

• The join. Small Postgres dimension to large Iceberg fact.
• Broadcast. Copy the small side to every worker holding the large side.
• Partitioned. Hash both sides on the key when both are large.

Question. Explain the data movement for a cross-source join and when the engine should broadcast versus partition.

Input.

Situation	Distribution	Data moved
one side small	broadcast small side	small side × workers
both sides large	partitioned (hash)	both sides shuffled
small side, no stats	may broadcast large (bad)	large side × workers
dynamic filter available	prune large scan first	far fewer rows

Code.

SELECT c.region, sum(o.total_cents) AS revenue_cents
FROM   iceberg.sales.orders    o        -- LARGE fact (S3)
JOIN   postgres.crm.customers  c        -- SMALL dimension (replica)
       ON c.id = o.customer_id
WHERE  c.tier = 'enterprise'            -- shrinks the small side further
GROUP  BY c.region;

# Cross-source join: neither source can run it (data lives in two systems).
# Trino plan with statistics (customers << orders):

  1. Scan postgres.customers WHERE tier='enterprise'  -> ~5k rows  (pushed)
  2. Build a DYNAMIC FILTER: customer_id IN (<5k ids>)
  3. Push the dynamic filter into the iceberg.orders scan
       -> S3 reads only files/partitions with those customer_ids  (far fewer rows)
  4. BROADCAST the 5k-row customer side to every worker scanning orders
  5. Hash-join in memory on customer_id; GROUP BY region
  -> Data moved across the network: ~5k customer rows (broadcast) + pruned orders.

# WITHOUT stats: Trino might broadcast the 500M-row ORDER side instead -> OOM.
#   Fix: gather statistics so the SMALL side is the one broadcast.

# If BOTH sides were large (say two 500M facts):
#   -> PARTITIONED join: hash both sides on customer_id, shuffle so matching
#      rows meet on the same worker. More movement, but no side is copied N times.

Step-by-step explanation.

1. Because the two tables live in different catalogs, neither source can execute the join — this is the defining case of federation, and the engine must bring filtered streams from each side together itself.
2. With statistics, Trino knows the enterprise-customer side is tiny (~5k rows after the pushed filter) and broadcasts it: a copy goes to every worker scanning orders, so each worker can join its slice of orders locally without shuffling the huge fact.
3. Dynamic filtering compounds the win: the 5k customer ids become a filter pushed into the Iceberg scan, so S3 reads only order files containing those customers — the large side is pruned before it enters the join, not after.
4. Without statistics, the optimizer might broadcast the 500M-row order side to every worker — copying a huge table many times — which blows memory; the fix is GATHER STATISTICS so the small side is the one broadcast.
5. When both sides are genuinely large, broadcasting either is too expensive, so the engine uses a partitioned join: it hashes both sides on the join key and shuffles rows so matching keys land on the same worker — more total movement than a good broadcast, but no side is duplicated across all workers.

Output.

Case	Distribution	Network cost
small + large, with stats	broadcast small	small side × workers
small + large, no stats	broadcast large (bug)	huge / OOM
large + large	partitioned (hash)	both shuffled once
any + dynamic filter	prune large scan	far fewer rows moved

Rule of thumb. A cross-source join runs in the engine, so its cost is the data it moves: broadcast the small side (and only if statistics confirm it is small), partition when both sides are large, and always let dynamic filtering prune the large scan first. Missing statistics that broadcast the large side are the classic federated-join outage.

Worked example — a pushdown that breaks, and the fix

Detailed explanation. The most common "it got slow overnight" federated bug is a change that silently disables pushdown — a new function, a cast, a collation mismatch — so the source starts shipping raw rows. Diagnose one and apply the three fixes: rewrite native, pre-filter, or materialize.

• The break. A regex filter the source connector cannot translate.
• The symptom. The engine pulls the whole table and filters itself.
• The fixes. Rewrite to a source-native predicate; pre-filter; or materialize.

Question. A filter stopped pushing to the source and the query got slow. Diagnose it from the plan and give the fix that restores pushdown.

Input.

Step	Observation
symptom	query 100x slower after a filter change
EXPLAIN	Filter node above a full source scan
cause	regex/function the connector can't push
fix	source-native predicate, or pre-filter, or materialize

Code.

-- BROKE PUSHDOWN: a Trino regexp the Postgres connector won't translate ->
-- Postgres returns ALL rows; Trino filters in the engine. Slow.
SELECT order_id, total_cents
FROM   postgres.sales.orders
WHERE  regexp_like(order_ref, '^EU-\d{6}$');       -- not pushed

-- FIX 1 — source-native predicate the connector CAN push (LIKE + length):
SELECT order_id, total_cents
FROM   postgres.sales.orders
WHERE  order_ref LIKE 'EU-______'                  -- pushed: Postgres filters
  AND  length(order_ref) = 9;                       -- pushed (native function)

-- FIX 2 — pre-filter with a pushable predicate, THEN apply the regex on far fewer rows:
SELECT order_id, total_cents FROM (
  SELECT order_id, total_cents, order_ref
  FROM   postgres.sales.orders
  WHERE  order_ref LIKE 'EU-%'                       -- pushed: shrinks to EU rows
) t
WHERE regexp_like(order_ref, '^EU-\d{6}$');          -- engine filters a SMALL set

# FIX 3 — if the predicate is structurally un-pushable and the table is hot,
#          materialize the filtered result on a schedule (hybrid escape hatch):
CREATE MATERIALIZED VIEW analytics.mv_eu_orders
WITH ( refresh_interval = '30m' ) AS
SELECT order_id, total_cents, order_ref
FROM   postgres.sales.orders
WHERE  order_ref LIKE 'EU-%';       -- pushed part materialized; regex applied on reads

# Diagnosis first — always confirm with EXPLAIN:
#   BROKEN: Filter[regexp_like(...)] ABOVE TableScan[postgres, (all rows)]
#   FIXED : TableScan[postgres, filter: order_ref LIKE 'EU-______', ...]  (pushed)

Step-by-step explanation.

1. The regexp_like predicate uses Trino's regex engine, which the Postgres connector cannot translate to a Postgres expression, so pushdown fails and Postgres returns every row for Trino to filter — the plan shows a Filter node above a full TableScan.
2. Fix 1 rewrites the predicate to source-native constructs (LIKE plus a length check) that the connector can push, so Postgres does the filtering and returns only matching rows — restoring pushdown by speaking the source's language.
3. Fix 2 keeps the regex but pre-filters with a pushable LIKE 'EU-%' in a subquery, so Postgres narrows to EU rows (pushed) and the engine applies the exact regex to a small set — most of the reduction happens at the source even though the final predicate cannot push.
4. Fix 3 is the escape hatch when the predicate is structurally un-pushable and the query is hot: materialize the pushable part on a schedule so the expensive scan runs periodically and reads hit a small local table — trading freshness for isolation, the hybrid pattern applied to a pushdown problem.
5. The through-line is diagnosis-first: EXPLAIN reveals whether the filter sits above or inside the scan, and the fix is always to move as much filtering as possible back down to the source — by rewriting native, pre-filtering, or materializing.

Output.

Version	Filter runs	Rows from source
regex (broken)	engine (above scan)	entire table
native LIKE (fix 1)	source (pushed)	matching rows
pre-filter + regex (fix 2)	source then engine	EU rows only
materialized (fix 3)	scheduled scan	small local table

Rule of thumb. When a federated query slows down, EXPLAIN it first: a filter above a full source scan means pushdown broke. Restore it by rewriting to a source-native predicate, pre-filtering with a pushable clause before the un-pushable one, or — if it is structurally un-pushable and hot — materializing the pushable part on a schedule.

Senior interview question on pushdown and federation performance

A senior interviewer might ask: "Your federated query engine is hammering the sources — a dashboard query pulls millions of raw rows because an aggregate stopped pushing down, a cross-source join OOMs the cluster, and a regex filter drags whole tables into the engine. Diagnose each from the plan and design the fixes: how you confirm and restore pushdown, how you keep a cross-source join's data movement bounded, and when you give up and materialize."

Solution Using EXPLAIN-driven pushdown, dynamic-filtered joins, statistics, and selective materialization

-- 1. Diagnose with EXPLAIN — the operator's POSITION is the ground truth.
EXPLAIN SELECT region, sum(total_cents) FROM postgres.sales.orders
WHERE order_date >= DATE '2026-01-01' GROUP BY region;
--   pushed  : TableScan[postgres, filter+projection+aggregate]        (good)
--   broken  : Aggregate[...] ABOVE TableScan[postgres, all rows]      (fix expr)

-- 2. Restore aggregate pushdown by keeping expressions source-native.
--    BAD : sum(my_udf(total_cents))   -- engine-only, un-pushes the aggregate
--    GOOD: sum(total_cents)           -- pushed; Postgres returns one row per region
SELECT region, sum(total_cents) AS revenue_cents
FROM postgres.sales.orders GROUP BY region;

-- 3. Bound cross-source join movement: stats -> broadcast the SMALL side; dynamic filter.
ANALYZE postgres.crm.customers;         -- so the optimizer knows customers << orders
ANALYZE iceberg.sales.orders;
SELECT c.region, sum(o.total_cents)
FROM   iceberg.sales.orders o
JOIN   postgres.crm.customers c ON c.id = o.customer_id
WHERE  c.tier = 'enterprise'            -- small side; dynamic filter prunes the lake scan
GROUP  BY c.region;                      -- broadcast small side, no large-table shuffle

-- 4. Un-pushable + hot -> materialize the pushable part on a schedule.
CREATE MATERIALIZED VIEW analytics.mv_eu_orders
WITH ( refresh_interval = '30m' ) AS
SELECT order_id, customer_id, total_cents, order_ref
FROM   postgres.sales.orders
WHERE  order_ref LIKE 'EU-%';           -- pushed; the exact regex runs on the small MV

Step-by-step trace.

Problem	Diagnosis (EXPLAIN)	Fix	Effect
aggregate not pushing	Aggregate above scan	source-native expr	one row/group, tiny transfer
cross-source join OOM	large side broadcast	ANALYZE + dynamic filter	broadcast small side, prune large
regex pulls whole table	Filter above scan	pre-filter / materialize	source filters most rows
structurally un-pushable	full scan every run	scheduled MV	reads hit a small local table
general	plan inspected	verify before ship	no silent no-push

After the fixes, EXPLAIN confirms filters, projections, and aggregates sit inside each source scan; expressions stay source-native so aggregates keep pushing; statistics make the optimizer broadcast the small dimension and dynamic-filter the lake scan instead of shuffling a huge fact; and the one structurally un-pushable hot predicate is served from a 30-minute materialized view. The sources now return small, pre-filtered, pre-aggregated results, and the cluster moves kilobytes across the network instead of gigabytes.

Output:

Metric	Broken federation	Fixed federation
Rows pulled from source	millions (raw)	one per group / matched only
Cross-source join	OOM (large broadcast)	broadcast small + pruned
Regex query	full table scan	pushed pre-filter + small MV
Pushdown certainty	assumed	EXPLAIN-verified
Network moved	gigabytes	kilobytes

Why this works — concept by concept:

• EXPLAIN-driven diagnosis — the position of an operator (inside the source scan versus above it) is the ground truth of what pushed, so reading the plan turns a silent, invisible performance cliff into a checkable fact before anything ships.
• Source-native expressions — keeping filters and aggregates in constructs the connector can translate is what lets predicate, projection, and aggregate pushdown fire, so the source returns a summary instead of raw rows.
• Statistics-driven distribution — gathered statistics let the optimizer broadcast the genuinely small side and partition only when both sides are large, which is the difference between a bounded exchange and an out-of-memory shuffle.
• Dynamic filtering — building a filter from the small side and pushing it into the large scan recovers pushdown for the cross-source case, so the lake reads only rows that can match the join.
• Cost — pushed filters/aggregates, broadcast-small-side joins, dynamic filtering, and one scheduled materialization for the un-pushable case, versus dragging raw rows and shuffling huge tables. The eliminated cost is the full-table transfer and the OOM — O(pushed result) movement instead of O(rows), with O(refresh) materialization only where pushdown structurally cannot help.

Optimization
Topic — optimization
Optimization problems on pushdown, plans, and data movement

Practice →

Joins Topic — joins Joins problems on join strategy and broadcast vs partitioned

Practice →

---

5. When not to copy versus when to materialize

Virtualize by default; materialize the proven hot path — the hybrid every mature platform runs

The mental model in one line: the copy-versus-query decision is not all-or-nothing — the mature answer is virtualize by default and materialize deliberately: leave data in place and query it through the logical layer for freshness, reach, low-volume access, governance consolidation, and data you must not duplicate, and copy (materialize, cache, or run an ETL pipeline) only when a specific access pattern is hot, heavy, freshness-tolerant, or would overload its source — so materialization becomes a targeted optimization of a proven query rather than a reflex applied to every dataset, and every copy you keep is one you can justify by the reads it serves between changes. Start virtual, measure, and promote the few queries that earn a copy.

When virtualization wins — don't copy.

• Fast-changing sources. If the data changes faster than a copy could refresh (or freshness is the requirement), querying in place is the only way to be current — a copy is stale the moment it lands.
• Low query volume. A dataset read a handful of times a day does not justify a pipeline and a second copy; federate it and pay the small per-query cost.
• Exploratory / ad-hoc access. When you cannot predict which slices consumers want, a virtual layer lets them query anything; pre-copying every possibility is impossible.
• Governance consolidation and data residency. One logical layer enforces policy over everything, and virtualization avoids copying data across regulatory or residency boundaries — sometimes you legally must not copy.
• Prototyping. Virtualize to prove a dataset is useful before investing in a pipeline; many prototypes never need to be copied at all.

When to copy — materialize or ETL.

• High, repeated query volume. The same heavy query run constantly should run once per load, not once per read — a copy amortises the work.
• Heavy transformation. Multi-stage transforms, deduplication, SCD history, and enrichment are cheaper and more reliable computed once in a pipeline than re-derived per query.
• Source can't take the load. A fragile OLTP primary or a rate-limited API cannot absorb analytical query volume; copy it so the source is read once per load.
• Historical snapshots / SCD. Sources overwrite state; if you need history (as-of, slowly-changing dimensions), you must capture and store it — virtualization only sees "now."
• Strict low-latency SLA. Sub-second, high-concurrency serving needs a tuned local copy (or cache); federation latency and source load make live querying unreliable at that bar.

The hybrid pattern — virtualize first, materialize the hot path.

• Promote on evidence. Ship datasets virtual; watch which queries become hot or expensive; materialize those — the promotion path Denodo caching and Starburst materialized views make one-line.
• Cache the middle ground. Between fully virtual and a full ETL pipeline sits caching (full/partial/incremental) — reversible materialization with a TTL, ideal for hot-but-freshness-tolerant views.
• Incremental materialization. Materialize large slowly-changing datasets incrementally so the copy stays fresh without a full rebuild — the copy's cost tracks the change rate, not the size.
• Keep the definition stable. Because the logical view name is the contract, promoting virtual → cached → materialized never changes what consumers query — only where the bytes come from.

Anti-patterns senior engineers refuse.

• Virtualizing a hot high-QPS join over OLTP. Pointing a heavily-read virtual view at an OLTP primary relocates analytical load onto the system of record and can take it down. Mitigation: replica + cache, or materialize.
• Copy-everything eagerly. Building a pipeline for every dataset produces stale copies nobody reads, a maintenance backlog, and duplicated governance. Mitigation: virtualize by default; copy on evidence.
• Materializing without invalidation. A copy or cache with no refresh discipline serves stale data silently. Mitigation: TTLs aligned to change cadence; incremental refresh; monitor freshness.

Common interview probes on the copy decision.

• "When would you NOT copy data?" — fresh/fast-changing, low-volume, ad-hoc, governance/residency-bound, or prototype data.
• "When must you copy?" — high repeated volume, heavy transforms, fragile sources, history/SCD, strict low-latency SLAs.
• "How do you decide in practice?" — virtualize by default, measure, materialize the proven hot path; cache the freshness-tolerant middle.
• "How do you promote without breaking consumers?" — keep the logical view name stable; change only where the data is served from.

Worked example — the copy-vs-virtualize decision under an SLO

Detailed explanation. The decision is per-data-product, resolved by its SLO — a freshness target, a volume, and a latency target. Walk three products through the trade and place each on copy, virtualize, or hybrid.

• Product A. Exec revenue dashboard: high volume, freshness ≤ 1 h, p95 < 200 ms.
• Product B. Support live-order lookup: low volume, freshness live, p95 < 1 s.
• Product C. Fraud analyst exploration: ad-hoc, freshness live-ish, p95 < 5 s.

Question. For each product, choose copy, virtualize, or hybrid, justified by its SLO and source-load impact.

Input.

Product	Volume	Freshness	Latency	Choice
A: exec dashboard	high	≤ 1 h	< 200 ms	materialize (copy)
B: support lookup	low	live	< 1 s	virtualize (replica)
C: fraud exploration	ad-hoc	live-ish	< 5 s	virtualize + cache (hybrid)

Code.

Freshness / volume / source-load — the SLO decides copy vs virtualize.

Product A (exec dashboard)  high volume, freshness<=1h, p95<200ms
  -> COPY: materialize a mart (or Starburst MV) refreshed hourly; read locally.
     high read-to-change ratio + staleness OK => amortise the join once per load.

Product B (support lookup)  low volume, live, p95<1s
  -> VIRTUALIZE: federate a point lookup over REPLICAS; a copy would be stale.
     low volume => per-query federation is cheap; freshness is mandatory.

Product C (fraud exploration)  ad-hoc, live-ish, p95<5s
  -> HYBRID: virtualize for reach; cache hot repeated slices with a short TTL.
     unpredictable queries => can't pre-copy; cache what turns out hot.

Anti-pattern for all: point a hot virtual view at an OLTP PRIMARY.

-- Product A: copy (materialized), hourly refresh, indexed for fast reads.
CREATE MATERIALIZED VIEW analytics.mv_exec_revenue
WITH ( refresh_interval = '1h' ) AS
SELECT region, sum(total_cents) AS revenue_cents
FROM iceberg.sales.orders o JOIN postgres.crm.customers c ON c.id=o.customer_id
GROUP BY region;

-- Product B: virtualize, point lookup over a replica catalog (no copy).
-- SELECT * FROM postgres_replica.crm.customers c
--   JOIN iceberg.sales.orders o ON o.customer_id=c.id WHERE c.id = ?;

Step-by-step explanation.

1. Product A has a high read-to-change ratio and tolerates an hour of staleness, so materializing the join into a mart (or a Starburst MV) refreshed hourly is correct — the expensive cross-source aggregation runs once per hour and every dashboard read is a fast local scan under the 200 ms bar.
2. Product B needs a single customer's live order status — freshness is mandatory and volume is low, so virtualizing a point lookup over replicas is correct; a copy would be stale, and the low volume makes per-query federation cheap and well under the 1 s bar.
3. Product C's queries are unpredictable, so you cannot pre-copy the right slices; virtualize for reach and cache the slices that turn out hot with a short TTL — the hybrid that gives ad-hoc freedom without re-federating a repeated heavy query every time.
4. The SLO is the arbiter across all three: the read-to-change ratio and the freshness target decide copy versus virtualize, and the latency target decides whether a live federation can meet the bar or a local copy is required.
5. The invariant is that none of them points a hot virtual view at an OLTP primary: A reads a materialized copy, B reads replicas at low volume, C reads replicas/lake with a cache — the source of record is never the thing absorbing analytical query load.

Output.

Product	Strategy	Optimises	Trades
A: exec dashboard	materialize (copy)	latency, source load	freshness (≤ 1 h)
B: support lookup	virtualize (replica)	freshness	small per-query cost
C: fraud exploration	virtualize + cache	reach, freshness	staleness on cached slices
all	never OLTP primary on hot path	—	—

Rule of thumb. Resolve copy-vs-virtualize per data product with its SLO: materialize when the read-to-change ratio is high and staleness is tolerable, virtualize when freshness is mandatory and volume is low, and go hybrid (virtualize + cache) for ad-hoc surfaces. Whatever you choose, never let a hot virtual view hit an OLTP primary.

Worked example — promoting a hot virtual view to a materialized one

Detailed explanation. The hybrid's whole value is that promotion is cheap and reversible and does not break consumers. Take a virtual view that has become hot and promote it through cache to full materialization, keeping the contract stable. Show the three stages.

• Stage 1. Virtual view — fresh, low volume, no copy.
• Stage 2. Cached view — hot, TTL'd, reversible materialization.
• Stage 3. Materialized table — core fact, pipeline-built, contract unchanged.

Question. Promote a hot virtual customer_360 through caching to materialization without changing what consumers query.

Input.

Stage	Form	Trigger to advance
1	virtual view	volume rises past cheap federation
2	cached view (TTL)	becomes core, needs SLA/history
3	materialized table	stable, heavily read
all	same view name	consumers never change

Code.

-- STAGE 1 — virtual: fresh, no copy. Consumers query `analytics.customer_360`.
CREATE VIEW analytics.customer_360 AS
SELECT c.id AS customer_id, c.region, c.tier,
       count(o.order_id) AS orders, sum(o.total_cents) AS lifetime_cents
FROM postgres_replica.crm.customers c
LEFT JOIN iceberg.sales.orders o ON o.customer_id=c.id
GROUP BY c.id, c.region, c.tier;

-- STAGE 2 — cache it (reversible materialization) when it gets hot. Same name.
--   Denodo:  ALTER VIEW analytics.customer_360 CACHE = FULL TTL = 3600;
--   Starburst: CREATE MATERIALIZED VIEW analytics.customer_360_mv (... refresh 1h ...)
--              and repoint the published `customer_360` at the MV.

-- STAGE 3 — full materialization into a governed table, built by a pipeline.
CREATE TABLE warehouse.customer_360 AS SELECT * FROM analytics.customer_360;  -- initial
-- Then an incremental ETL job maintains it; the PUBLISHED view name stays `customer_360`,
-- now backed by the table. Consumers' SQL never changed across all three stages.

Step-by-step explanation.

1. At stage 1 the dataset is a pure virtual view over replicas and the lake — fresh, no copy, cheap while volume is low; consumers query analytics.customer_360 and neither know nor care that it federates.
2. When volume rises, stage 2 caches the view (Denodo full cache, or a Starburst MV the published name points at) — reversible materialization that gives copy-like read speed and source isolation while the definition stays virtual, and the consumer contract is unchanged.
3. When the dataset becomes a core fact that needs an SLA, history, or heavy transformation, stage 3 materializes it into a governed warehouse table maintained by an incremental pipeline — the full ETL copy, justified now by evidence rather than reflex.
4. The critical property across all three stages is that the published name (customer_360) never changes, so every consumer's SQL is identical whether the bytes come from a live federation, a cache, or a materialized table — promotion is invisible to consumers.
5. The senior discipline is to advance stages only on evidence (measured volume, a new SLA, a history requirement) and to keep each step reversible where possible — you can drop the cache and return to live, or rebuild the table from the same definition, because the logic lived in the view all along.

Output.

Stage	Freshness	Read latency	Source load	Consumer change
1 virtual	live	federation	per query	—
2 cached	≤ TTL	local	per refresh	none
3 materialized	≤ load	local, indexed	per load	none

Rule of thumb. Promote hot datasets virtual → cached → materialized on evidence, and keep the published view name fixed so consumers never change their SQL. Each step trades freshness for read speed and source isolation; because the logic lives in the definition, promotion (and reversal) is a config change, not a rewrite.

Worked example — the source-load safety check before virtualizing

Detailed explanation. Before you virtualize any view over an operational source, run a safety check: estimate the query volume it will attract and confirm the source can absorb it, or gate it behind a replica and a cache. Apply the check to a proposed virtual view over an OLTP orders table.

• The proposal. Virtualize live_orders directly over the OLTP primary.
• The check. Volume × per-query cost versus the source's spare capacity.
• The gate. Replica + cache, or reject and materialize.

Question. Decide whether to allow a proposed virtual view over an OLTP source, using a source-load safety check.

Input.

Check	Value	Verdict
expected volume	3,000 queries/min	high
per-query cost on primary	aggregate scan	expensive
primary spare capacity	~5% (OLTP-critical)	none to spare
decision	replica + cache, or materialize	do NOT hit primary

Code.

Source-load safety check — before publishing a virtual view over an operational DB.

1. Estimate demand:      3,000 queries/min expected (dashboard auto-refresh).
2. Estimate per-query:   each runs an aggregate scan (not a point lookup).
3. Source headroom:      OLTP primary runs at ~95% on transactions -> ~none spare.
4. Verdict:              3,000 aggregate scans/min on a maxed primary = OUTAGE.

Gate the view instead of pointing it at the primary:
  a) base view -> READ REPLICA          (isolate analytical load)
  b) + aggregate pushdown               (source returns summaries, not rows)
  c) + cache/MV, TTL aligned to freshness (source read once per TTL)
  d) if still too heavy -> MATERIALIZE   (pipeline; source read once per load)

Only publish the virtual view once it provably cannot harm the source.

-- The gated, safe version: replica base view + cached aggregate (not the primary).
CREATE VIEW analytics.live_orders_by_region AS
SELECT region, sum(total_cents) AS revenue_cents, count(*) AS orders
FROM   postgres_replica.sales.orders     -- REPLICA, never the primary
GROUP  BY region;                          -- pushed down to Postgres
-- Cache/MV in front: source read ~once per TTL, thousands of reads served locally.

Step-by-step explanation.

1. The safety check starts with demand: 3,000 dashboard refreshes a minute, each an aggregate scan rather than a cheap point lookup — so the view will attract heavy, repeated source work, not incidental traffic.
2. It then weighs that against the source's headroom: an OLTP primary already at ~95% on transactions has essentially no spare capacity, so adding 3,000 aggregate scans a minute would starve the transactions the business runs on — a self-inflicted outage.
3. The verdict is to gate the view rather than point it at the primary: move the base view to a read replica (isolating analytical load), push the aggregate down (summaries, not rows), and cache or materialize (source read once per TTL) — layered isolation.
4. If even the gated version is too heavy for the replica, the check escalates to full materialization: a pipeline reads the source once per load and consumers read the copy, removing per-query source load entirely.
5. The senior discipline is that this check is mandatory before publishing any virtual view over an operational source — virtualization's freshness is only safe if the source can absorb the load it relocates, and the check is what turns "virtualize everything" from a hazard into a governed decision.

Output.

Option	Primary load	Freshness	Allowed?
virtual over primary	severe	live	no (outage risk)
virtual over replica	none (replica)	seconds	yes, if replica can take it
replica + cache/MV	negligible	≤ TTL	yes (recommended)
materialize	none (per load)	≤ load	yes (if still too heavy)

Rule of thumb. Run a source-load safety check before publishing any virtual view over an operational database: estimate volume × per-query cost against the source's headroom, and gate with a replica, pushdown, and caching — or materialize — until the view provably cannot harm the source. Virtualization is only free when the source can afford the load you move onto it.

Senior interview question on the copy decision and hybrid architecture

A senior interviewer might ask: "You are asked to 'just load everything into the warehouse.' Push back with a principled framework: which datasets you would leave virtual and why, which you would copy and why, how you protect operational sources when you virtualize, how caching and materialization fit between fully-virtual and full-ETL, and how you promote a hot virtual view to a copy without breaking consumers — all tied to freshness, volume, source load, and SLOs."

Solution Using virtualize-by-default, gated operational sources, tiered materialization, and stable contracts

# 1. The framework — virtualize by default, copy on evidence.
LEAVE VIRTUAL:  fresh/fast-changing, low-volume, ad-hoc, governance/residency-bound, prototypes
COPY (ETL/MV):  high repeated volume, heavy transforms, fragile sources, history/SCD, strict SLA
MIDDLE (CACHE): hot but freshness-tolerant -> reversible materialization with a TTL

-- 2. Virtual by default, over REPLICAS, with the source-load safety gate.
CREATE VIEW analytics.customer_360 AS
SELECT c.id AS customer_id, c.region, c.tier,
       count(o.order_id) AS orders, sum(o.total_cents) AS lifetime_cents
FROM postgres_replica.crm.customers c            -- replica, never primary
LEFT JOIN iceberg.sales.orders o ON o.customer_id=c.id
GROUP BY c.id, c.region, c.tier;

-- 3. Copy the proven hot, freshness-tolerant path (materialize once per load).
CREATE MATERIALIZED VIEW analytics.mv_exec_revenue
WITH ( refresh_interval = '1h' ) AS
SELECT region, sum(total_cents) AS revenue_cents
FROM iceberg.sales.orders o JOIN postgres_replica.crm.customers c ON c.id=o.customer_id
GROUP BY region;

# 4. Promotion path — contract stays stable across all forms.
#    published name `analytics.customer_360` is ALWAYS what consumers query:
#      virtual view  -> full cache (TTL)  -> materialized table (incremental ETL)
#    Each step trades freshness for read speed + source isolation; consumers never change SQL.
#    Reverse any step (drop cache -> live) because the logic lives in the definition.

Step-by-step trace.

Dataset	Decision	Why
Support live lookup	virtualize (replica)	freshness mandatory, low volume
Ad-hoc exploration	virtualize + cache	unpredictable slices, cache hot ones
Exec dashboard	materialize (copy)	high volume, staleness OK
History / SCD needs	copy (ETL)	source overwrites; need snapshots
Residency-bound data	virtualize (no copy)	must not duplicate across boundary
Hot virtual view	promote virtual→cache→table	evidence-driven, contract stable

After the framework is applied, most datasets stay virtual over replicas behind the source-load safety gate — fresh, no copies, one governance layer; the few high-volume freshness-tolerant queries are materialized once per load; the ad-hoc surface is virtual with hot slices cached; history and strict-SLA needs justify pipelines; residency-bound data is never copied; and any virtual view that proves hot is promoted through cache to a table without a single consumer changing their SQL. Copies exist only where the reads-between-changes justify them.

Output:

Metric	"Copy everything"	Virtualize-by-default hybrid
Pipelines maintained	one per dataset	only proven hot paths
Data duplicated	everything (+ history)	only the hot path
Freshness (live datasets)	stale between loads	live
OLTP primary safety	n/a (all copied)	gated (replica + cache)
Governance points	source + every copy	one logical layer
Cost	O(datasets) pipelines	O(hot paths) copies

Why this works — concept by concept:

• Virtualize by default — leaving data in place keeps it fresh, avoids per-dataset pipelines, and consolidates governance, so a copy has to earn its existence by the reads it serves between changes rather than being the reflex.
• Gated operational sources — the source-load safety check plus replica base views and caching mean virtualization never relocates analytical load onto an OLTP primary, which is the one failure that turns "query in place" into an outage.
• Tiered materialization — caching sits between fully-virtual and full-ETL as reversible materialization, so the freshness-tolerant hot middle gets copy-like speed without a bespoke pipeline, and only the truly core facts become full copies.
• Stable contracts — the published view name is the contract, so promoting virtual → cached → materialized (or reversing it) changes only where the bytes come from, never what consumers query.
• Cost — copies scale with the number of proven hot paths, not the number of datasets, and freshness is preserved wherever it matters, versus a pipeline and a stale duplicate per dataset. The eliminated cost is the pile of pipelines and copies built by reflex — O(hot paths) materialization instead of O(datasets) ETL, with governance enforced once.

ETL
Topic — etl
ETL problems on materialization and incremental loads

Practice →

Optimization
Topic — optimization
Optimization problems on caching, freshness, and read/write ratios

Practice →

---

Cheat sheet — data virtualization vs ETL

• The core trade. ETL/ELT copies data into a central store and serves queries from the copy; data virtualization queries in place through a logical layer that federates the sources at runtime. Decide per access pattern, not platform-wide: copy the hot, heavy, freshness-tolerant, repeated queries; virtualize the fresh, low-volume, ad-hoc, or must-not-copy ones.
• The five axes. Freshness (virtual = live, copy = stale between loads); source load (virtual pushes load onto sources, copy isolates them); latency (copy = fast local reads, virtual = network + slowest source); governance (virtual = one enforcement point, copy = spread and can drift); storage cost (virtual = none, copy = duplicate + history + pipeline).
• The golden safety rule. Never point a hot virtual view at an OLTP primary. Base views/catalogs go to replicas; cache or materialize hot aggregates. Virtualization relocates query work onto the source — only free if the source can absorb it.
• Denodo model. Three layers — base views (wrap one source each; the only place a source is named) → derived views (join/transform/aggregate across sources; no copy) → published data services (JDBC/REST/GraphQL). A cost-based optimizer rewrites, pushes down, and ships the smaller side; caching (full/partial/incremental) is reversible materialization; governance (row/column security, masking, lineage) lives on the derived views.
• Starburst Galaxy (Trino) model. MPP SQL, storage/compute separated; a catalog is a connector, catalog.schema.table federates; coordinator plans, workers execute. Predicate/projection/aggregate/TopN push to capable sources; cross-source joins run in the engine with dynamic filtering; Galaxy adds managed clusters, access control, and cached/materialized views for the hot path.
• Pushdown. Predicate (WHERE), projection (columns), aggregate (GROUP BY/SUM), and LIMIT/TopN push into the source so it returns a minimal result. Same-source joins push; cross-source joins do not — the engine moves data. Pushdown is the whole performance story: maximise it, minimise data movement.
• When pushdown breaks. Unsupported functions/UDFs, type/collation mismatches, non-deterministic functions, and connector limits keep work in the engine and pull raw rows. Fixes: rewrite source-native, pre-filter with a pushable predicate, or materialize the un-pushable part.
• Cross-source joins. Broadcast the small side (only with statistics confirming it is small); partition (hash) both when both are large; use dynamic filtering to prune the large scan from the small side. Missing stats that broadcast the large side are the classic federated-join OOM.
• Verify with EXPLAIN. The operator's position is ground truth: a filter/aggregate inside the source scan pushed; a separate operator above a full scan did not. EXPLAIN every heavy federated query before shipping.
• The hybrid pattern. Virtualize by default; measure; materialize the proven hot path. Cache (full/partial/incremental) is the reversible middle. Keep the published view name stable so promoting virtual → cached → materialized never changes consumer SQL.
• When to copy. High repeated volume, heavy transforms, fragile sources, history/SCD, strict low-latency SLA. When not to copy. Fresh/fast-changing, low-volume, ad-hoc, governance/residency-bound, prototypes.
• Statistics discipline. Both Denodo and Trino are cost-based; gather and refresh source statistics so the optimizer pushes down correctly and ships the smaller side. Stale/missing stats are the usual reason a federated plan goes wrong.

Frequently asked questions

What is data virtualization and how is it different from ETL?

Data virtualization is an integration approach that leaves data in its source systems and exposes a single logical layer over them, resolving each query by federating to the sources at runtime and pushing as much work as possible down into them — so consumers query one virtual dataset (say customer_360) without knowing it is really a Postgres table joined to Iceberg files on S3. ETL/ELT does the opposite: a pipeline physically extracts, transforms, and loads the data into a central warehouse or lake, and queries are served from that copy. The practical difference is a trade across five axes — virtualization is always fresh, stores nothing extra, and gives one governance point, but pushes query load onto the sources and pays federation latency; a copy is fast for repeated reads and isolates the sources, but is stale between loads and costs storage plus a pipeline. Neither is universally better; the mature answer is to decide per access pattern and often run both.

Denodo vs Starburst Galaxy — which do I pick?

Pick Denodo when you want a broad enterprise logical-data-fabric platform: a modelled layer of base, derived, and published views over many source types (databases, files, APIs, mainframe), with a cost-based optimizer, a rich caching subsystem, a data catalog, and governance/lineage built for a large, heterogeneous estate consumed by BI, apps, and data services. Pick Starburst Galaxy (managed Trino) when your center of gravity is SQL analytics over a lakehouse and databases and you want a fast MPP engine to run one ANSI-SQL query across catalogs, with strong pushdown, dynamic filtering, managed autoscaling clusters, access control, and materialized views for the hot path. Denodo leans "logical data fabric and data services for the whole enterprise"; Starburst leans "federated MPP SQL engine for the lakehouse." Many organisations use Starburst as the query engine over the lake and Denodo (or a semantic layer) as the broader logical/governance layer — they are not mutually exclusive.

What is query pushdown and why does it matter?

Query pushdown is the optimization where a virtualization engine delegates parts of a query — filters (predicate pushdown), column selection (projection pushdown), aggregations (aggregate pushdown), and LIMIT/TopN — down into the source, so the source executes them and returns the smallest possible result instead of raw rows. It matters because it is essentially the entire performance story of virtualization: a query that pushes a WHERE and a GROUP BY into the source might transfer a few summary rows, while the same query that fails to push drags millions of raw rows into the engine to filter and aggregate there — often a 10–100x difference, and invisible unless you read the plan. Pushdown stops at the cross-source join (no single source holds both tables), which the engine must perform itself by moving data; and it breaks when a query uses functions, casts, or collations the source cannot execute. The senior habit is to keep expressions source-native and confirm pushdown with EXPLAIN on every heavy federated query.

When should I NOT copy data?

Leave data virtual — do not copy — when freshness is the requirement or the source changes faster than a copy could refresh, because a copy is stale the moment it lands; when query volume is low enough that a pipeline and a second copy are not worth their maintenance; when access is ad-hoc and exploratory so you cannot predict which slices to pre-copy; when you need one governance and lineage point over the whole estate; and — importantly — when regulation or data residency means you must not duplicate the data across a boundary. Prototyping is another: virtualize to prove a dataset is useful before investing in a pipeline, and many prototypes never need to be copied. The counterpoint is that you should copy when a query is hot and heavy and freshness-tolerant, when transformations are complex, when the source cannot take the analytical load, when you need history/SCD, or when a strict low-latency SLA demands a tuned local store. The discipline is to virtualize by default and copy only the paths that earn it.

How does caching work in a virtualization layer?

Caching is reversible materialization inside the virtualization layer: instead of re-federating a derived view on every query, the engine stores its result and serves reads from the cache until a TTL or refresh rebuilds it. Denodo offers full cache (materialize the whole view into a cache database), partial cache (cache only the queried subset), and incremental cache (top up new/changed rows rather than rebuild); Starburst Galaxy offers cached and materialized views over federated queries with a refresh interval and grace period. The trade is the usual one — you accept bounded staleness (up to the TTL/refresh cadence) in exchange for fast local reads and near-zero repeated source load, which is exactly right for a view that is queried far more often than its sources change. Because the logical definition stays in place, caching is reversible: you can drop the cache and go live again, or promote further to a full materialized table, all without changing what consumers query. Align the TTL to how often the sources actually change so the cache never serves data staler than the business tolerates.

Does data virtualization replace the warehouse?

No — it complements it. Data virtualization is excellent for fresh, low-volume, ad-hoc, cross-source, and governance-consolidated access, and for avoiding copies you cannot justify or are not allowed to make; but a warehouse or lakehouse is still the right home for high-volume repeated analytics, heavy transformations, historical snapshots and slowly-changing dimensions, and strict low-latency high-concurrency serving, because a tuned local copy amortises the work and isolates the sources. The mature architecture is a hybrid: virtualize by default for reach and freshness, keep the warehouse for the heavy curated core, and use the virtualization layer's own caching and materialization to promote proven hot paths — often with a federated engine like Starburst querying the lakehouse directly. Virtualization changes when and what you copy, shrinking the pile of pipelines and duplicates built by reflex, but it does not eliminate the warehouse; it makes the decision to materialize a deliberate, evidence-driven one.

Practice on PipeCode

• Drill the ETL practice library → for the copy-pipeline, incremental-load, and materialization problems that decide when a virtual view should become a physical table.
• Sharpen the pushdown and plan-reading instincts on the query optimization practice library → for the predicate/aggregate pushdown, data-movement, and caching trade-offs a federated engine lives or dies on.
• Rehearse the copy-versus-query architecture on the system design practice library → for the logical-layer, source-load, and freshness/latency/cost decisions a virtualization platform must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the cross-source joins and data processing patterns that federation and pushdown make concrete against real graded inputs.

Lock in data virtualization muscle memory

Docs explain Denodo and Starburst Galaxy. PipeCode drills explain the decision — when to leave data in place and query it through the `logical layer`, when `query pushdown` makes federation fast and when a cross-source join stops it, and when a hot virtual view must finally be materialized. Pipecode.ai is Leetcode for Data Engineering — virtualization-and-ETL practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice query optimization problems →