Federated Query (Query-in-Place)

1. An analyst submits a standard ANSI SQL query to the federated engine's coordinator, unaware of where the underlying data physically resides.
2. The query coordinator (Trino/Presto) parses the SQL, consults its catalog metadata, and builds a distributed execution plan that splits work across connectors.
3. Each connector (Hive, PostgreSQL, MongoDB, S3/Parquet, Kafka) translates its slice of the plan into the native dialect or API of the target system and pushes down predicates, projections, and aggregations where the source supports them.
4. The source systems execute filtered scans in-place and stream only matching rows back to the engine's worker nodes—no bulk copy, no staging.
5. The worker nodes perform cross-source joins, aggregations, and sorts entirely in memory, shuffling intermediate results between each other as needed.
6. The coordinator assembles the final result set and streams it back to the analyst or BI tool, typically in seconds against live data.

• Zero data duplication and always-fresh results vs. query latency bound by the slowest, least-optimized source.
• Rapid onboarding of new sources via connectors vs. no control over source indexing or physical layout, limiting pushdown efficiency.
• Single unified SQL dialect vs. leaky abstractions where source-specific quirks and type mismatches surface at runtime.
• Lower storage cost vs. higher, less predictable network and compute cost per query.

A multi-brand retailer runs Trino to power its merchandising analytics. Live inventory levels sit in a PostgreSQL operational store, three years of historical sales live as Parquet files in S3, and clickstream events land in Kafka topics. An analyst investigating slow-moving stock writes one SQL query that joins current stock-on-hand from Postgres against aggregated sales velocity from the S3 lake, filtered by store region. Trino pushes the region predicate down to both sources, scans only the relevant partitions, joins them in-memory across its worker cluster, and returns results in eight seconds—no nightly warehouse load, no duplicated inventory table, and figures that match the operational system exactly.

• Memory-bound execution: Cross-source joins and aggregations run in worker memory; a join whose build side doesn't fit spills to disk or fails outright. Tune per-query and per-node memory limits, and prefer broadcast joins only when the smaller side is genuinely small.
• Pushdown is best-effort, not guaranteed: Predicates, projections, and aggregations only push down where the connector supports them; an unsupported function silently pulls full tables across the network. Inspect EXPLAIN plans to confirm filters reach the source rather than the engine.
• No cross-source transactions or consistency: Each source is read at its own point in time, so a join sees Postgres and object-store snapshots that are seconds apart. There is no isolation across connectors—accept eventual, not point-in-time, correctness.
• Type and dialect mismatches surface at runtime: Timestamps, decimals, and nested types map imperfectly across connectors; schema drift in a source table breaks queries mid-flight. Pin catalog schemas and test after any source DDL change.
• Failure and retry semantics: A single failed split can abort the whole query—most engines do not checkpoint long-running analytical queries, so retries restart from scratch. Set source-side query timeouts to protect fragile OLTP systems from runaway scans.
• Observability and cost drivers: Instrument bytes scanned per source, spill volume, and wall-clock per stage; network egress from object stores and repeated cold scans dominate the bill. Watch coordinator queue depth and worker memory pressure as concurrency climbs.