Each test is 5 questions with varying difficulty.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.
Presto and Trino represent the pinnacle of distributed SQL query engines designed for low-latency, ad-hoc analytics across heterogeneous data sources. Originally developed at Facebook (Meta) as Presto to overcome the sluggish latency of MapReduce-based Hive queries, the project later forked into PrestoDB and Trino (formerly PrestoSQL). In modern data engineering and data platform architectures for 2026, Trino and Presto have cemented themselves as the defacto standard federated query engines, enabling organizations to query petabyte-scale data directly where it lives—whether in object storage lakes like AWS S3 or Google Cloud Storage, relational databases like PostgreSQL, or NoSQL stores like Cassandra—without painful ETL synchronization phases. Software engineers, data platform engineers, and analytics infrastructure specialists are frequently tested on these query engines during technical interviews. Interviewers probe candidates not merely on basic SQL syntax, but on deep architectural mechanics: understanding the coordinator-worker topology, memory management thresholds, spilling mechanisms to local NVMe storage, custom connector implementations, cost-based optimizer (CBO) statistics collection, and distributed shuffle stages. Junior candidates are generally expected to understand basic execution plans, ANSI SQL compliance, and how to write efficient filter pushdown predicates. In contrast, senior engineers and tech leads are grilled on designing resilient multi-tenant clusters, tuning exchange buffer memory limits, debugging split stalls, optimizing broadcast versus partitioned joins, and managing catalog security policies using Apache Ranger or native Trino access control. Mastery of Presto and Trino unlocks the ability to design high-throughput, low-latency analytical platforms that serve both business intelligence dashboards and ad-hoc data science notebooks across globally distributed data lakes.
The enterprise shift toward decoupled storage and compute architectures, commonly realized through modern data lakes and open table formats like Apache Iceberg, Hudi, and Delta Lake, has made federated query engines indispensable. In 2026, companies rarely store all their data in a single monolithic data warehouse. Instead, raw logs reside in cloud object storage, transactional tables live in PostgreSQL or MySQL, and customer profiles sit in Cassandra. Trino and Presto act as the single virtualization layer over these fragmented silos, allowing data analysts and applications to execute ANSI SQL queries joining data across Amazon S3, Google Cloud Storage, and on-premises databases in milliseconds. From a business value perspective, this eliminates hours of redundant ETL pipelines, slashes cloud storage costs by avoiding data duplication, and dramatically accelerates time-to-insight for decision-makers. In production, massive enterprises like Netflix, Uber, and Shopify deploy clusters running thousands of worker nodes, processing petabytes of data daily while serving tens of thousands of concurrent interactive queries. In technical interviews, Presto and Trino serve as high-signal topics because they force candidates to confront the fundamental realities of distributed systems: network I/O bottlenecks, memory exhaustion during large hash joins, skewed data distributions across worker nodes, and thread synchronization across asynchronous network buffers. A weak candidate views Trino as just another SQL database, whereas a strong candidate understands it as a distributed computation engine whose performance depends entirely on careful memory budgeting, optimal join strategies, statistics-driven query planning, and efficient predicate pushdown into underlying storage layers.
The Trino and Presto architecture follows a decoupled, master-worker, massively parallel processing (MPP) model. The lifecycle of a query begins when a client submits an ANSI SQL statement via the REST API to the Coordinator. The Coordinator's parser transforms the raw SQL text into an Abstract Syntax Tree (AST), which the analyzer validates against the Metastore or catalogs. The planner then creates a logical query plan, subsequently optimized by the Cost-Based Optimizer (CBO) using statistical metadata. The resulting plan is transformed into a physical execution plan consisting of distinct stages containing fragments and tasks. These tasks are dispatched to worker nodes over HTTP/REST. Workers pull data splits from underlying storage systems via Connectors, process intermediate computations in memory, and stream results across network exchange channels using pipelined operators. Once execution concludes, the coordinator aggregates streams and returns rows back to the client.
Client Application (CLI / BI Tool)
↓ (Submits SQL via REST/JDBC)
[Coordinator Node]
├── [Parser & Analyzer]
├── [Cost-Based Optimizer (CBO)]
└── [Distributed Query Scheduler]
↓ (Dispatches Tasks & Splits)
[Worker Node 1] ←→ [Worker Node 2] ←→ [Worker Node N]
├── [Exchange Operators]
├── [Hash Join & Aggregators]
└── [Spill Engine (NVMe)]
↓ (Pulls Data via Connectors)
[Connectors (Iceberg / Hive / PostgreSQL / S3)]
↓
[Underlying Storage & Data Lakes]Used when joining a large fact table with a small dimension table. Instead of shuffling both tables across the network, the smaller table is broadcasted to all worker nodes containing partitions of the large fact table. In Trino, this is triggered automatically by the CBO when the dimension table size falls below `join-broadcast-enabled=true` and threshold limits, or forced explicitly using query hints such `SELECT /*+ BROADCAST(dim) */ * FROM fact JOIN dim ON fact.id = dim.id`.
Trade-offs: Eliminates expensive network shuffle phases for the large table, dramatically speeding up query execution. However, if the dimension table is too large, broadcasting causes worker node Out-Of-Memory errors.
Leverages table partitioning metadata to ensure workers only scan directories and files matching specific query predicates (e.g., `WHERE year = 2026 AND month = 03`). Trino's coordinator evaluates partition keys during the analysis phase and completely drops file splits that do not satisfy the condition before generating execution tasks.
Trade-offs: Reduces disk I/O and S3 request costs by orders of magnitude. Requires strict data layout adherence; over-partitioning can lead to excessive metadata overhead and slow planning times.
Configures resource groups in `resource-groups.properties` to govern query concurrency, memory limits, and spill allocations across multi-tenant user groups. Queries exceeding soft memory limits are queued, while queries exceeding hard memory thresholds trigger local NVMe disk spilling instead of immediate termination.
Trade-offs: Prevents rogue analytical queries from crashing the entire cluster and starving concurrent BI workloads. Spilling introduces local I/O latency penalties for heavy queries.
A runtime optimization where the build side of a join generates dynamic filter criteria based on matching keys, which are then pushed down to the probe side scan tasks on worker nodes before the data is read from storage. Configured via `dynamic-filtering-enabled=true`.
Trade-offs: Massively reduces data scanned from object storage and transferred across the network during star schema joins. Introduces slight coordination overhead as runtime filters are broadcasted between workers.
| Reliability | Achieved through stateless worker nodes, coordinator high availability with active-passive setups backed by shared metadata stores, and automatic query retries for transient node failures. If a worker crashes, the coordinator reschedules pending tasks on healthy nodes, though active queries running on the failed worker must be restarted. |
| Scalability | Horizontally scalable by adding worker nodes to the cluster dynamically. Discovery services coordinate worker registration via REST heartbeats. Storage scalability is completely decoupled, relying on cloud object stores or distributed file systems. |
| Performance | Optimized for interactive sub-second to minute-range analytical queries. Leverages vectorized execution, pipelined operators, HTTP-based exchange buffers, and local NVMe disk spilling for memory-intensive operations. |
| Cost | Driven by compute resource sizing (EC2 instance types or Kubernetes pod limits) and cloud storage API request rates (GET/PUT costs when scanning millions of unindexed small files). Cost is controlled by aggressive partition pruning, file compaction, and auto-scaling worker pools based on queue depth. |
| Security | Secured via TLS encryption for client-to-coordinator and coordinator-to-worker communication. Authentication is supported via LDAP, Kerberos, OAuth2, and JWT tokens. Fine-grained authorization is enforced using Apache Ranger or native Trino access control policies. |
| Monitoring | Monitored via Prometheus metrics scraped from JMX endpoints, visualizing query execution times, worker memory pool usage, garbage collection pauses, exchange queue depths, and connector latency in Grafana dashboards. |
PrestoDB and Trino diverged from the original Facebook Presto project due to governance and architectural roadmap differences. Trino (formerly PrestoSQL) focuses heavily on advanced cost-based optimizations, native SPI connector expansions, robust security integrations like Apache Ranger, and enhanced support for open table formats like Apache Iceberg. While both retain the core coordinator-worker MPP architecture, Trino has emerged as the dominant community-driven open-source standard for federated data analytics.
When a query's memory consumption approaches configured thresholds like `query.max-memory-per-node`, Trino can utilize local spilling if enabled via `query.spill-enabled=true`. Instead of crashing immediately with an Out-Of-Memory error, intermediate hash tables, aggregation states, and sorting buffers are flushed to local NVMe storage directories. Once memory pressure subsides, the spilled data is read back in chunks. If spilling is disabled or if hard limits are breached, the coordinator aborts the query.
Apache Hive translates SQL queries into physical MapReduce jobs that write intermediate states to disk between every phase, resulting in heavy disk I/O latency. In contrast, Trino uses a Massively Parallel Processing (MPP) pipeline architecture where worker nodes execute operators in memory and stream intermediate blocks directly to downstream workers across HTTP/TCP exchange buffers. This eliminates intermediate disk writes and drastically reduces end-to-end query latency.
The CBO evaluates multiple potential execution plan permutations for a given SQL query using table and column statistics such as cardinality, null fractions, and data distribution. By analyzing these statistics, the CBO determines the most efficient join order, selects between broadcast and distributed hash joins, and applies predicate pushdown rules. Without up-to-date statistics generated via the ANALYZE command, the CBO may select sub-optimal plans that degrade performance.
Connectors implement Trino's Service Provider Interface (SPI), acting as an abstraction layer between the core query engine and underlying storage systems. A connector translates logical table scans, projections, and predicate filters into native storage queries or API calls. This allows a single ANSI SQL statement to seamlessly join data residing in an Amazon S3 Iceberg data lake, a PostgreSQL operational database, and a Kafka streaming topic without requiring data duplication.
Data skew occurs when join or grouping keys are unevenly distributed, causing a single worker node to receive the vast majority of shuffle rows while other workers sit idle. This leads to query timeouts and memory exhaustion. It can be mitigated by filtering out null keys before joining, salting high-cardinality join keys to distribute rows evenly, or using skewed join optimization hints to repartition unbalanced datasets across worker nodes.
In production environments, combining coordinator and worker roles on the same node can lead to resource starvation. The coordinator is responsible for CPU-intensive tasks such as parsing SQL, analyzing ASTs, running the CBO, and scheduling tasks. If worker tasks running heavy data processing consume all available CPU and memory, the coordinator may drop worker heartbeats and fail cluster operations. Isolating the coordinator ensures reliable cluster stability.
The small file problem occurs when a data lake contains millions of tiny files (such as small Parquet or CSV files). When Trino queries these tables, the coordinator spends excessive time listing object storage directories, parsing metadata manifests, and generating thousands of tiny splits. This overwhelms memory and network resources. It is resolved by running periodic table compaction routines to merge small files into optimal 128MB–512MB blocks.
Trino enforces security through encrypted communication channels (TLS) for client connections and internal node exchanges, alongside authentication mechanisms like LDAP, Kerberos, OAuth2, and JWT tokens. For authorization, Trino supports native access control files as well as deep integration with Apache Ranger, allowing administrators to enforce role-based access control, column masking, and row-level filtering.
Dynamic filtering is a runtime optimization where the build side of a join generates filter criteria based on matching keys during execution. These runtime filters are then pushed down to the probe side scan tasks on worker nodes before data is fully read from storage. This significantly reduces the volume of data scanned from object storage and transferred across network exchange buffers, accelerating star schema joins.
No. Trino is engineered specifically as an OLAP (Online Analytical Processing) federated query engine optimized for read-heavy, large-scale analytical aggregations and scans. It does not support low-latency row-level inserts, updates, or ACID transaction isolation suited for high-throughput operational applications. Operational databases handle transactional writes, while Trino queries and analyzes the resulting data.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.