How do I query a Delta table from Trino or DuckDB?

Summary

• Trino reads Delta tables natively with its Delta Lake connector: create a catalog file with connector.name=delta_lake, point it at a Hive metastore, enable your object store's file system, and query the tables with standard SQL.
• A Delta table that exists in storage but not in the metastore can be registered in Trino with CALL <catalog>.system.register_table(...), once delta.register-table-procedure.enabled=true is set.
• DuckDB reads Delta tables with its delta extension, built on Delta Kernel: SELECT * FROM delta_scan('s3://...') after creating an S3 secret. Attaching a table with (TYPE delta) adds time travel and blind appends.
• For tables governed by Unity Catalog, DuckDB's unity_catalog extension attaches a catalog directly, and Trino's Iceberg connector reads through Unity Catalog's Iceberg REST endpoint once Iceberg reads are enabled on the Delta table.
• Before you start, check the table's Delta features: Trino lists the features its connector supports, and Databricks' docs say deletion vectors aren't supported for Iceberg v2 reads of Delta tables.

How do I query a Delta table from Trino or DuckDB?

Both engines read Delta Lake without Spark. In Trino, define a catalog that uses the Delta Lake connector and a metastore, then query tables as catalog.schema.table. In DuckDB, load the delta extension and call delta_scan on the table's path. If the table is governed by Unity Catalog, connect through the catalog instead of raw paths, so permissions and credentials stay with the catalog.

Option 1: Trino's Delta Lake connector

Trino's connector "can natively read the Delta Lake transaction log and thus detect when external systems change data," and it provides read and write access, including INSERT, UPDATE, DELETE and MERGE. It needs a metastore (the Hive Thrift metastore is the default) and one enabled file system, such as native S3 support. The second file below is for Unity Catalog tables, covered in Option 3.

# etc/catalog/delta.properties
connector.name=delta_lake
hive.metastore.uri=thrift://example.net:9083
fs.s3.enabled=true
s3.region=<aws-region>
delta.register-table-procedure.enabled=true

# etc/catalog/uc.properties
connector.name=iceberg
iceberg.catalog.type=rest
iceberg.rest-catalog.uri=https://<workspace-url>/api/2.1/unity-catalog/iceberg-rest
iceberg.rest-catalog.warehouse=<uc-catalog-name>
iceberg.rest-catalog.security=OAUTH2
iceberg.rest-catalog.oauth2.token=<token>
iceberg.rest-catalog.vended-credentials-enabled=true
iceberg.security=read_only
fs.s3.enabled=true

Tables written to storage but never added to the metastore can be registered by location. The procedure is off by default to prevent unauthorized access, which is why the property above enables it. Time travel uses FOR VERSION AS OF or FOR TIMESTAMP AS OF.

CREATE SCHEMA IF NOT EXISTS delta.sales;
CALL delta.system.register_table(schema_name => 'sales', table_name => 'orders', table_location => 's3://my-bucket/delta/orders');
SELECT * FROM delta.sales.orders;
SELECT * FROM delta.sales.orders FOR VERSION AS OF 3;
SELECT * FROM uc.sales.orders;

Option 2: DuckDB's delta extension

DuckDB runs inside your process, so there's no cluster to stand up. Its delta extension "adds support for the Delta Lake open-source storage format" and "is built using the Delta Kernel." It autoloads on first use, pushes down filters and projections, scans tables with deletion vectors, and supports blind appends with INSERT INTO on an attached table. Credentials come from DuckDB secrets; the credential_chain provider fetches them through the AWS SDK (profiles, SSO, assumed roles and so on).

INSTALL delta;
LOAD delta;
CREATE SECRET (TYPE s3, PROVIDER credential_chain);
SELECT * FROM delta_scan('s3://my-bucket/delta/orders');

ATTACH 's3://my-bucket/delta/orders' AS orders (TYPE delta);
SELECT * FROM orders AT (VERSION => 3);

INSTALL unity_catalog;
LOAD unity_catalog;
CREATE SECRET (TYPE unity_catalog, TOKEN '<token>', ENDPOINT 'https://<workspace-url>', AWS_REGION '<region>');
ATTACH '<uc-catalog-name>' AS uc (TYPE unity_catalog, DEFAULT_SCHEMA 'sales');
SELECT * FROM uc.sales.orders;

Option 3: Tables governed by Unity Catalog

Raw paths bypass the catalog's permissions. For Unity Catalog tables, DuckDB's unity_catalog extension (the last block above) attaches a catalog with a token and your workspace URL, and works with Databricks or Unity Catalog OSS. Trino reaches the same tables through its Iceberg connector and Unity Catalog's Iceberg REST endpoint (the uc.properties file). Trino's docs require iceberg.security=read_only for this connection, and the Delta table must have Iceberg reads enabled.

Trino vs DuckDB for Delta tables

Capability	Trino	DuckDB
Where it runs	Coordinator and workers in a cluster	Embedded in a host process
Finding tables	Metastore, or register_table by location	Table path via delta_scan or ATTACH ... (TYPE delta)
Unity Catalog	Iceberg connector against the Iceberg REST endpoint, read-only	unity_catalog extension
Time travel	FOR VERSION AS OF, FOR TIMESTAMP AS OF	AT (VERSION => n) or VERSION at attach
Writes	INSERT, UPDATE, DELETE, MERGE	Blind appends with INSERT INTO
Deletion vectors	Supported for readers and writers	Supported for scans

How Databricks approaches external engines

Databricks treats Unity Catalog as the access layer for outside engines. It serves Delta clients through the Unity REST API and Iceberg clients through the Iceberg REST catalog, and credential vending lets those clients inherit the privileges Unity Catalog grants. External data access must be enabled on the metastore, and readers need the EXTERNAL USE SCHEMA privilege. Databricks lists DuckDB among Beta clients for the Unity REST API path; contact your account team to use it. For the Trino route, Iceberg reads (UniForm), documented as Read Delta Lake tables with Iceberg clients, generates Iceberg metadata alongside the Delta log without rewriting data files.

FAQs

Can Trino write to Delta tables?

Yes, through the Delta Lake connector against a metastore. The Unity Catalog route through the Iceberg REST endpoint is read-only.

Why does Trino reject some Delta tables?

Trino's docs list the Delta table features its connector supports, including deletion vectors and column mapping, and say "No other features are supported." The list omits variant, which Trino has read since release 471. Check the table's features before you point Trino at it.

Do I need Spark installed to read Delta from DuckDB?

No. The delta extension uses Delta Kernel, so DuckDB reads the transaction log and Parquet files on its own.