Trino

Overview

Trino is a distributed SQL query engine built for fast, interactive analysis of large data. It does not store data. Instead it connects to many systems, such as object storage, relational databases and lakehouse tables, and lets you query them with standard SQL. You can even join a table in a lake with a table in a database in one statement.

People choose Trino when data lives in several places and copying it all into one warehouse is slow or costly. It splits a query into small tasks, runs them in parallel on many workers and streams results between stages in memory. This makes it well suited for exploration, dashboards and ad hoc questions over a lakehouse built on formats such as Apache Iceberg and Delta Lake. It is also a handy way to read the large tables that tabular machine learning models need.

What it is

Trino is an open source, massively parallel SQL engine. It follows the ANSI SQL standard closely and adds many functions for arrays, maps, JSON, dates, geospatial data and statistics. It is written for the Java virtual machine and runs as a cluster of server processes.

The key idea is separation of compute and storage. The engine has no data of its own. Storage stays where it is, and Trino reads it through connectors.

The parts of a cluster

• Coordinator: accepts queries, parses and plans them, schedules work and tracks progress.
• Workers: run tasks, read data from sources, process it and exchange results.
• Catalogs: named configurations of a connector, such as lake or crm.
• Schemas and tables: the usual structure inside a catalog.
• Clients: command line tools, JDBC drivers, Python libraries and BI tools that submit SQL.

A fully qualified table name has three parts: catalog.schema.table. That naming is what makes federation natural. Each part tells Trino where to go.

What Trino is not

• It is not a database. It does not manage storage, indexes or transactions of its own in the classic sense.
• It is not a stream processor. Queries are bounded and finish. For continuous logic see Apache Flink.
• It is not a transformation framework, although tools like dbt can run on top of it.

Everyday analogy

Imagine a research librarian with access to many libraries in a city. You ask one question. The librarian sends helpers to each library, each helper reads the relevant shelves, and the results are combined into a single answer on your desk. Trino is the librarian and the connectors are the library cards. The books never move permanently to one place.

Where it came from

Trino began as a project at a large social media company in the early 2010s. Analysts there needed to query very large data in Hadoop faster than the MapReduce based tools of the time allowed. The engine, then called Presto, executed queries in memory and in a pipelined way, avoiding writes of intermediate results to disk. It was open sourced and quickly adopted by many companies.

Later the original creators left the company and continued development under a new name. That fork is now called Trino. The older name, Presto, remains in use by another project. The two share history and many ideas, but they evolved separately. When you read an older article about Presto, much of the concept still applies to Trino.

Over time Trino grew from a Hadoop era tool to a general lakehouse and federation engine. It added support for modern table formats, fault-tolerant execution, row and column security and a very large set of connectors.

Versions and feature lists change often. Refer to the project documentation for the current state.

How it works

Follow a query from the client to the result.

Step 1: Submit SQL

A client sends a SQL statement to the coordinator over HTTP. The statement may touch one catalog or several.

Step 2: Parse and analyze

The coordinator parses the text into a syntax tree. It then checks table names, column names and types against the catalogs. At this point permission checks run. If anything is missing, the query fails early with a clear message.

Step 3: Build and optimize a plan

The coordinator turns the tree into a logical plan, which is a tree of operations like scan, filter, join and aggregate. The optimizer rewrites the plan to make it cheaper.

Common optimizations include:

• Predicate pushdown: send filters down to the source so less data is read.
• Projection pushdown: read only the needed columns.
• Partition pruning: skip partitions that cannot match.
• Join reordering: join small inputs first, based on statistics.
• Dynamic filtering: use values from one side of a join to skip rows on the other side at run time.
• Aggregation pushdown: let a database compute sums or counts when it can.

Think of shopping with a list. You do not carry every item in the shop to the till and then discard what you do not need. You only pick what is on the list. Pushdown is the same idea.

Step 4: Split into stages and tasks

The plan is cut into stages at points where data must be exchanged, such as before a join or a group by. Each stage is run as many parallel tasks on the workers. Tasks are divided further into splits, which are chunks of data, such as a range of a file.

Step 5: Read through connectors

A connector tells Trino how to list splits for a table, how to read them and which operations it can push down. For a lake table the connector reads metadata from a catalog service and then reads files, often in columnar formats like Parquet or ORC. For a database the connector issues a query over a client driver.

Step 6: Process in memory with pipelines

Workers pass batches of rows called pages between operators. Pages are columnar in memory, which makes processing efficient. Results flow through stages without being written to disk, unless memory limits force a spill or fault-tolerant mode is on.

This pipelining is why Trino feels interactive. The first stage can start sending data to the second before it has finished.

Step 7: Exchange data between stages

For joins and aggregations, rows are hashed and sent across the network to the right worker. Small tables can be broadcast to all workers. Large tables are partitioned by join key. The optimizer picks the strategy from statistics.

Step 8: Return results

The final stage sends rows to the coordinator, which streams them to the client. Large result sets are paged. For big outputs it is better to write results into a table instead, using a create-table-as statement.

Step 9: Fault tolerance

By default a failed task fails the query. This suits short interactive queries. For long running batch work, an optional fault-tolerant execution mode writes intermediate data to external storage and retries failed tasks. This trades some speed for resilience.

Step 10: Resource control

Resource groups limit concurrency and memory per user or team. The coordinator queues queries when limits are reached. Without them one heavy query can slow down everyone.

Key concepts and terms

A worked example

This example queries a lake table and a database table together. The catalogs and hosts are fake.

Configure two catalogs

# etc/catalog/lake.properties
connector.name=iceberg
iceberg.catalog.type=hive_metastore
hive.metastore.uri=thrift://metastore.example.internal:9083
fs.native-s3.enabled=true

# etc/catalog/crm.properties
connector.name=postgresql
connection-url=jdbc:postgresql://crm-db.example.internal:5432/crm
connection-user=readonly_user
connection-password=${ENV:CRM_DB_PASSWORD}

The password comes from an environment variable instead of the file. Property names vary by version, so check the documentation for your release.

Explore

SHOW CATALOGS;
SHOW SCHEMAS FROM lake;
SHOW TABLES FROM lake.sales;
DESCRIBE lake.sales.orders;

A federated join

SELECT
    c.region,
    date_trunc('month', o.ordered_at) AS month,
    count(*)                          AS orders,
    sum(o.order_total)                AS revenue,
    approx_percentile(o.order_total, 0.5) AS median_order
FROM lake.sales.orders AS o
JOIN crm.public.customers AS c
  ON o.customer_id = c.id
WHERE o.ordered_at >= DATE '2026-01-01'
  AND c.is_active
GROUP BY 1, 2
ORDER BY 1, 2;

What Trino does

1. It finds that lake.sales.orders is an Iceberg table and crm.public.customers is a PostgreSQL table.
2. It pushes the date filter to the lake scan. Partition pruning skips old files.
3. It pushes the is_active filter to the database, so fewer customer rows cross the network.
4. It estimates that customers is the smaller side and broadcasts it to the workers, or applies dynamic filtering so only matching order rows are processed.
5. Workers join and aggregate in parallel and the coordinator returns the final rows.

Look at the plan

EXPLAIN
SELECT count(*) FROM lake.sales.orders WHERE ordered_at >= DATE '2026-01-01';

EXPLAIN ANALYZE
SELECT region, count(*) FROM crm.public.customers GROUP BY region;

EXPLAIN shows the plan. EXPLAIN ANALYZE runs the query and reports actual rows and time per operator. Use it to find the slow step.

Create a table from a query

CREATE TABLE lake.features.customer_month
WITH (format = 'PARQUET', partitioning = ARRAY['month'])
AS
SELECT
    o.customer_id,
    date_trunc('month', o.ordered_at) AS month,
    count(*)           AS n_orders,
    avg(o.order_total) AS avg_total,
    max(o.ordered_at)  AS last_order_at
FROM lake.sales.orders AS o
GROUP BY 1, 2;

This produces a partitioned feature table in columnar format. It can then feed a model pipeline.

Use it from Python

import trino

conn = trino.dbapi.connect(
    host="trino.example.internal",
    port=443,
    user="analyst",
    http_scheme="https",
    catalog="lake",
    schema="features",
)
cur = conn.cursor()
cur.execute("SELECT * FROM customer_month WHERE month >= DATE '2026-01-01' LIMIT 1000")
rows = cur.fetchall()
columns = [c[0] for c in cur.description]

The rows can be loaded into a data frame and handed to a modeling library.

A small numeric picture

Suppose the orders table holds 2 billion rows over three years in monthly partitions. A query for the last month touches about one thirty-sixth of the partitions, so roughly 55 million rows. If the file format also stores min and max statistics per file, even fewer files are opened. This is why partitioning and columnar formats matter more than raw cluster size.

Typical deployment patterns

• Shared interactive cluster. One coordinator and an autoscaling pool of workers serve analysts and dashboards.
• Dedicated batch cluster. A separate cluster with fault-tolerant execution runs long transformations at night.
• Per-team clusters. Each team gets isolated capacity and its own resource limits.
• Gateway in front. A routing layer sends queries to different clusters and allows upgrades without downtime.
• Embedded in a platform. A managed lakehouse product hides the cluster and exposes SQL endpoints.

Pick the pattern from your workload mix. Mixed heavy and light work on one cluster is the most common cause of complaints.

Strengths and limitations

Strengths

• Standard SQL. Analysts can use familiar syntax and tools.
• Federation. One query can join many systems without copying data.
• Speed for interactive work. In-memory pipelined execution gives quick answers on large data.
• Open table formats. Strong support for Iceberg, Delta Lake and Hive style tables.
• Many connectors. Lakes, relational databases, search engines, streaming logs and more.
• Scale out. Add workers to handle larger data and more users.
• Separation of compute and storage. Scale each independently and avoid vendor lock-in on storage.
• Security features. Authentication, access control and row or column rules are available.

Limitations

• Not a storage engine. It depends on the quality of the underlying systems.
• Source load. A federated query can put heavy read load on an operational database.
• Memory hungry. Large joins and aggregations need enough memory, or the query fails or spills.
• Default fail-on-error. Without fault-tolerant mode, a worker loss ends the query.
• Network cost. Joins across remote systems move data over the network.
• Operational effort. Running and tuning a cluster takes skill, though managed offerings exist.
• Limited write semantics for some connectors. Updates, deletes and transactions depend on the connector and table format.
• Statistics matter. Poor or missing statistics cause poor plans.

How it compares with alternatives

Trino and Athena are close relatives in spirit. Athena is a managed service, and its engine lineage is connected to the Presto family. If you need a managed, no-ops query service on object storage, Athena is attractive. If you need cross-system federation and control, Trino is a strong pick.

Role in tabular foundation models

Tabular foundation models learn from raw tables. They read rows and columns, handle mixed types, tolerate missing values, can work without column names and are often pre-trained on large numbers of synthetic or real tables. Many support in-context learning, so a handful of labelled rows are passed at prediction time without retraining. Trino does not train or serve these models, but it is a convenient bridge between scattered enterprise data and the tables a model needs.

Gathering tables from many places

Real data is spread over lakes, operational databases and warehouses. Trino lets you assemble a training or context table with one SQL query across those systems. You do not need to build a pipeline for every source before you can even look at the data.

Sampling for in-context learning

A model that uses in-context examples needs a good sample of labelled rows. Trino has sampling clauses and functions, so a query can draw a random or stratified sample directly from a huge table.

SELECT *
FROM lake.features.customer_month TABLESAMPLE BERNOULLI (0.1)
WHERE label IS NOT NULL;

You can also write stratified samples with window functions, for instance taking up to 200 rows per class using row_number(). Balanced context sets are valuable for models like TabPFN and TabICL, which rely on context rows.

Profiling mixed types and missing values

Before feeding a model, you want to know the shape of the table. Trino can compute null rates, distinct counts, min and max, and quantiles for every column.

SELECT
    count(*)                                   AS n_rows,
    count_if(income IS NULL) * 1.0 / count(*)  AS income_null_rate,
    approx_distinct(city)                      AS city_cardinality,
    approx_percentile(income, ARRAY[0.01, 0.5, 0.99]) AS income_q
FROM lake.features.customer_month;

These summaries guide decisions about which columns to keep, which to flag as missing and which to treat as categories. The same output helps check imputation results; see imputation networks and masked cell modeling.

Handling tables with no useful column names

Some tabular models do not rely on column names. Trino lets you produce a version of the table with generic names (c1, c2, and so on) as a view, and a version with full names, so you can test whether names help. Views make this cheap, as no data is copied.

Feature windows and as-of correctness

Trino supports window functions and date arithmetic. You can build features that use only past data, such as trailing thirty day counts, with a frame clause. This avoids leakage when you build training tables. For streaming or very fresh features, use a stream engine such as Apache Flink and land results in a lake table that Trino can query.

Anomaly detection support

Large scale anomaly detection often starts with simple statistics. Trino can compute z-scores, rolling baselines and rare-category counts at scale. A model-based approach, such as zero-shot scoring from a tabular foundation model, then focuses on the flagged subset. See anomaly detection for the model concepts.

Sector and semantic classification

For tasks like assigning an industry sector to a business record, Trino can join the record table with reference tables, such as code lists, from other systems. It can also store model predictions as a table, so analysts can query labels next to source attributes. See semantic sector heads for the modeling side.

Continual learning and monitoring

With snapshot-capable table formats, you can query a table as of an earlier time. That makes it easy to compare a column distribution now with a month ago and detect drift. When drift is large, you can refresh the context set or schedule fine-tuning. This supports continual learning without guesswork.

Governance

Access rules, column masking and row filters help ensure that only allowed data reaches a model pipeline. Combine this with a data catalog and data lineage to know where each training table came from.

Limits of the role

Trino reads data. It does not provide a GPU, it does not run gradient descent and it does not keep a model. For the model side, see the architecture, research and benchmarks pages.

Practical guidance and best practices

Pick good file formats

Use columnar formats such as Parquet or ORC. They allow reading only needed columns and use statistics for skipping. Avoid huge numbers of tiny files.

Partition with care

Partition by a column that is often filtered, such as date. Avoid partition columns with very many values, since they create too many small partitions. Table formats with hidden partitioning, such as Apache Iceberg, reduce the risk of user mistakes.

Keep tables compact

Streaming and frequent small writes create many small files. Schedule compaction. See Iceberg compaction. Also expire old snapshots to control metadata growth.

Collect statistics

Run the statistics command for important tables so the optimizer can order joins sensibly.

ANALYZE lake.sales.orders;
SHOW STATS FOR lake.sales.orders;

Write filters that can be pushed down

• Compare columns to constants, not wrapped in functions, where possible.
• Filter on partition columns explicitly.
• Select only the columns you need.
• Use LIMIT while exploring.

Be careful with federated joins

• Join a large remote table to a small local one, not the reverse.
• Filter early so less data crosses the network.
• For frequent heavy joins, materialize the remote data into the lake on a schedule.
• Give Trino a read-only database account and, if possible, point it at a replica.

Control cost and concurrency

• Use resource groups to cap memory and concurrency per team.
• Set query time limits.
• Monitor queue length and query failure reasons.
• Use separate clusters for batch and for interactive workloads if both are heavy.

Use fault-tolerant mode for long jobs

For multi-hour transformations, turn on fault-tolerant execution with external exchange storage such as Amazon S3. Keep interactive clusters in the default mode for speed.

Secure the cluster

• Require TLS and authentication.
• Use role-based access control and column masking for sensitive fields.
• Store secrets in a secrets manager, not in catalog files.
• Audit queries.

Deploy and observe

Run on Kubernetes or virtual machines with autoscaling workers. Export metrics from the JMX endpoint to Prometheus and build dashboards in Grafana. Watch memory per node, queued queries, split wait time and failed queries by error type.

A short checklist before going live

• Confirm every catalog connects with a read-only account.
• Run statistics on the ten largest tables.
• Set memory limits per query and per node.
• Turn on TLS and authentication.
• Test one failed worker to see what users experience.

Common mistakes

1. Treating Trino as a database. It needs durable storage and metadata elsewhere.
2. Querying production databases directly at peak hours. A big scan can slow down the application.
3. Small files everywhere. Query planning and reading slow down dramatically.
4. Over-partitioning. Partitions by a high-cardinality column create millions of tiny pieces.
5. Skipping statistics. The optimizer then guesses, and a bad join order can be disastrous.
6. Using select star on wide tables. Columnar benefits disappear.
7. Wrapping filter columns in functions. Pushdown and pruning may stop working.
8. Ignoring memory limits. One large cross join can fail the query or hurt others.
9. Sharing one cluster with no limits. Heavy ad hoc queries starve dashboards.
10. Confusing Trino and Presto documentation. Settings and features differ between the projects.
11. Using it for low-latency serving. Trino is for analytics queries, not millisecond lookups.
12. Hard-coded credentials in catalog files. Use secret substitution.
13. Forgetting time travel limits. Old snapshots may be expired and no longer queryable.
14. Building ML training sets with future information. Always check the time window of every feature.

Frequently asked questions

What is Trino used for?

Trino is used to run fast SQL queries across large datasets that live in different systems. Common uses are interactive analytics on a data lake, joining lake tables with operational databases, powering dashboards and creating derived tables for further processing.

Is Trino the same as Presto?

They share the same origin but are separate projects today. Trino is the continuation developed by the original creators under a new name, while the Presto name is used by a different community project. Many concepts are the same, but configuration and features have diverged.

Does Trino store my data?

No. Trino reads data from other systems through connectors and keeps only temporary data in memory, and sometimes on disk during spills or fault-tolerant runs. Your tables live in object storage, databases or whichever systems you connect.

What is a connector in Trino?

A connector is a plugin that teaches Trino how to talk to a data source. It lists tables, splits data for parallel reads, converts types and may push down filters. You configure each connector as a catalog.

Can Trino write data?

Yes, depending on the connector and table format. You can create tables from queries and insert rows. Updates, deletes and merges are available for table formats that support them. Check the capabilities of your connector before relying on them.

How does Trino compare with Spark?

Trino focuses on fast interactive SQL with pipelined in-memory execution. Spark is a broader engine for batch jobs, machine learning and code-based transformations. Many teams use both: Trino for exploration and dashboards, Spark for heavy pipelines.

Is Trino good for real-time data?

It queries current data in tables, so it can show fresh results if a streaming job keeps writing to the tables. It is not a stream processor, and it is not designed for single-record lookups with very low latency.

How do I make Trino queries faster?

Use columnar formats, partition on commonly filtered columns, keep files a healthy size, collect statistics, push filters down, select only needed columns and avoid heavy joins across slow remote systems. Reading the output of EXPLAIN ANALYZE shows where time goes.

How can Trino help with machine learning data preparation?

It can pull data from several sources into one query, sample rows, compute profiles and quantiles, build time-correct features with window functions and write the result as a table in a lake. That table can then be read by a training or inference pipeline.

Is Trino secure enough for sensitive data?

It provides authentication, TLS, access control and masking features, and many organizations run it for sensitive workloads. Real security also depends on how you configure it and on the controls of the underlying sources, so plan roles, auditing and secret management with care.

Glossary

• Broadcast join: A join strategy that sends a small table to every worker.
• Catalog: A named instance of a connector in Trino.
• Connector: A plugin that gives Trino access to a data source.
• Coordinator: The node that plans queries and manages workers.
• Dynamic filtering: Using values found at run time on one side of a join to prune the other side.
• Federation: Querying several systems together as if they were one.
• Fault-tolerant execution: A mode that stores intermediate results so failed tasks can be retried.
• Lakehouse: A design that adds table management on top of lake storage.
• Metastore: A service that stores definitions of tables.
• ORC: A columnar file format.
• Page: A batch of columnar data passed between operators.
• Parquet: A widely used columnar file format.
• Partition pruning: Skipping partitions that cannot match a filter.
• Partitioned join: A join where both sides are split by the join key.
• Pushdown: Letting the source perform part of the work.
• Resource group: A rule that limits concurrent queries and memory.
• Spill: Writing intermediate data to disk when memory runs low.
• Split: A unit of data assigned to a task.
• Stage: A group of tasks that run the same part of a plan.
• Worker: A node that executes tasks.

Summary

Trino is a distributed SQL engine that separates compute from storage and reaches many systems through connectors. It plans queries, pushes work down to sources, runs tasks in parallel with in-memory pipelines and returns results quickly. It shines for interactive analysis of lakehouse tables and for federated queries across lakes and databases.

For tabular foundation models, Trino is a practical data access and preparation layer. It can assemble tables from scattered sources, sample and stratify context rows, profile types and missing values, build time-correct features, store model predictions beside source data and compare snapshots to spot drift. It does not train or serve models. Use good file formats, sensible partitions, collected statistics and clear resource limits, and Trino will serve both analysts and model pipelines well.

Related

It belongs to Batch and streaming processing in my stack, under Data engineering and lakehouse. Others in the same group: