Skip to content

SQL query engines

A SQL query engine runs SQL over data it does not own: it reads tables from object storage and other systems through connectors, spreads the work across many machines, and returns the result.

Before reading

Apache Iceberg: table format basics

Why it matters

In a lakehouse the engine is the part people touch: analysts query through it, transformations run on it, dashboards call it. Because the data lives in open tables, the engine can be chosen per workload and replaced later, and several engines can work on the same tables. Knowing how engines differ is what makes that choice sensible.

How it works

A query goes through the same steps in most engines. A coordinator parses the SQL, asks the catalog where each table's metadata is, and plans the query: which files to read after pruning, how to split the work, in what order to join. Workers then read their share of the files directly from object storage, filter, join and aggregate, exchanging intermediate results over the network when data must be regrouped, for example for a join or a GROUP BY. The coordinator collects the final result.

flowchart TB
  U[Analyst, BI tool, dbt] -->|SQL| CO[Coordinator:<br/>parse, plan, schedule]
  CO -->|where is the table?| CAT[Catalog]
  CO --> W1[Worker]
  CO --> W2[Worker]
  CO --> W3[Worker]
  W1 <-->|exchange| W2
  W2 <-->|exchange| W3
  W1 --> S[(Object storage:<br/>Iceberg tables)]
  W2 --> S
  W3 --> S
  W1 --> PG[(Other systems<br/>via connectors)]

The engine holds no data. Storage and compute are separate, so a cluster can be resized, paused or replaced without moving data, and two clusters can serve different teams from the same tables.

Connectors let one engine read many kinds of source: Iceberg tables, relational databases, Kafka topics, search indexes. Each appears as a catalog with schemas and tables. Joining across connectors is called federation. It is useful for exploration and for small reference data, but every federated query loads the source system, and filters cannot always be pushed down to it, so regular reporting should read data that has been ingested into the lake.

Engines differ mainly in how they execute:

  • Interactive engines keep a cluster running and stream data between workers in memory, so queries return in seconds. Trino is the common open-source example, also offered as managed services such as Amazon Athena and Starburst. Dremio is in this group too; SAP announced its acquisition in May 2026 (checked October 2026).
  • Batch engines write intermediate results so that a failed task can be retried without restarting the query. Apache Spark is the standard example and also runs Python and machine learning code. Interactive engines can offer a fault-tolerant mode for long jobs at some speed cost.
  • In-process engines, such as DuckDB, run inside one program on one machine. They need no cluster and are very fast on data that fits one machine, which covers more workloads than people expect.
  • Real-time analytical databases, such as ClickHouse and StarRocks, store data in their own optimised format for very low latency, and can also read lake tables.
  • Cloud warehouses such as Snowflake and BigQuery can read, and in some cases write, Iceberg tables, so they can act as one more engine on the same data.

SQL dialects differ between engines in functions, types and date handling. A transformation written for one engine usually needs changes for another.

Key terms

  • Coordinator: the process that plans a query and schedules work.
  • Worker: a process that reads data and executes part of the plan.
  • Exchange: moving intermediate rows between workers, for example to group or join by key.
  • Connector: a plugin that lets the engine read and write one kind of system.
  • Federation: one query reading from several systems through connectors.
  • Fault-tolerant execution: retrying failed parts of a query instead of the whole query.

Example

Trino with two catalogs: lake for Iceberg tables and shopdb for the orders database. Revenue per customer segment for one day:

SELECT c.segment, sum(o.amount) AS revenue
FROM lake.shop.clean_orders AS o
JOIN shopdb.shop.customers AS c
  ON o.customer_id = c.customer_id
WHERE o.ordered_at >= TIMESTAMP '2026-10-28 00:00:00 UTC'
  AND o.ordered_at <  TIMESTAMP '2026-10-29 00:00:00 UTC'
GROUP BY c.segment;

The engine prunes orders to one day in the lake and reads customers from the database through its connector. Run hourly by a dashboard, this query would put load on the orders database; customers should then be ingested into the lake as clean_customers.

Pitfalls

  • Federation for production reports. It loads source systems and breaks when they are slow or down.
  • One cluster for everything. Large transformations and interactive dashboards compete for the same workers; separate them or schedule them apart.
  • Choosing an engine by benchmark. Test with your own data, concurrency and query mix.
  • Assuming SQL is portable. Dialect differences surface when a second engine is introduced.
  • Ignoring the engine's support for the table format version and features you use, such as deletes or branches.

Learn more

  • Trino documentation, overview and concepts: https://trino.io/docs/current/overview/concepts.html
  • Apache Spark SQL guide: https://spark.apache.org/docs/latest/sql-programming-guide.html
  • DuckDB documentation: https://duckdb.org/docs/

Query performance on lake tables · Catalogs · dbt · Data lake, warehouse and lakehouse