Trino DuckLake Connector — Feature Support

Connector for the DuckLake open table format (spec v1.0). Supports PostgreSQL and MySQL 8+ (shared, multi-process) and a local DuckDB .db file (single-process; ideal for dev, local interactive use, and single-node Trino deployments) as catalog metadata backends. SQLite/Turso, and a remote-DuckDB backend over Quack RPC for shared DuckDB-as-catalog without filesystem mounting, are planned next. (For a catalog shared with DuckDB itself, use PostgreSQL — DuckDB's own MySQL reader is not yet stable; see dev-docs/CATALOG-BACKENDS.md.)

Tested with DuckDB 1.5.4 for cross-engine compatibility.

The DuckLake spec is included as a submodule. All documentation and feature tables in this module are current against that version of the spec.

Note: This connector reads and writes parquet data files only. The experimental DuckDB/vortex/lance data-file formats and the DuckDB-engine predicate-pushdown path were removed and now live in the standalone duckbridge Trino → DuckDB connector. The ducklake-catalog metadata/catalog plane is unaffected.

Install

Download

Grab the latest plugin archive from the GitHub Releases page — each release ships trino-ducklake-<version>.zip and trino-ducklake-<version>.tar.gz (with .sha256 sidecars). Both expand to a single trino-ducklake-<version>/ directory of JARs.

# verify + unpack (example, zip)
sha256sum -c trino-ducklake-<version>.zip.sha256
unzip trino-ducklake-<version>.zip

(Or build it yourself — see Building from source at the bottom.)

Install into Trino

Copy the unpacked plugin directory into your Trino installation's plugin directory, renaming it to ducklake:

cp -r trino-ducklake-<version> /usr/lib/trino/plugin/ducklake

The plugin directory should contain all JARs flat (not nested in a subdirectory):

/usr/lib/trino/plugin/ducklake/
  trino-ducklake-<version>.jar
  ...other jars...

Configure a catalog

Create a catalog properties file at etc/catalog/ducklake.properties. Pick the backend that matches your deployment shape.

PostgreSQL — shared multi-process deployment (multiple Trino workers, mixed DuckDB + Trino access):

connector.name=ducklake

# DuckLake metadata catalog database (PostgreSQL)
ducklake.catalog.database-url=jdbc:postgresql://<host>:5432/ducklake
ducklake.catalog.database-user=ducklake
ducklake.catalog.database-password=<password>

# Base location for data files (local path or S3)
ducklake.data-path=s3://<bucket>/<prefix>/

# Optional tuning
ducklake.catalog.max-connections=10

MySQL 8+ — shared multi-process deployment, same shape as PostgreSQL (the connector talks to the catalog directly over JDBC; the backend dialect is inferred from the URL scheme):

connector.name=ducklake

# DuckLake metadata catalog database (MySQL 8+)
ducklake.catalog.database-url=jdbc:mysql://<host>:3306/ducklake
ducklake.catalog.database-user=ducklake
ducklake.catalog.database-password=<password>

# Base location for data files (local path or S3)
ducklake.data-path=s3://<bucket>/<prefix>/

The MySQL Connector/J driver ships with the connector, so no extra jar is needed. The MySQL database must already contain a DuckLake metadata schema before Trino connects — initialize it once from a DuckDB session (the DuckDB mysql extension bootstraps the ducklake_* tables on ATTACH):

duckdb -c "INSTALL ducklake; LOAD ducklake; INSTALL mysql; LOAD mysql; \
  ATTACH 'ducklake:mysql:db=ducklake host=<host> port=3306 user=ducklake password=<password>' AS lake \
    (DATA_PATH 's3://<bucket>/<prefix>/'); \
  DETACH lake;"

Cross-engine note. Trino read/write against a MySQL-backed catalog is supported and tested. However, DuckDB itself currently cannot reliably read a DuckLake-on-MySQL catalog — DuckDB 1.5.4's mysql extension is unstable on that path (upstream issue, not this connector). If you need DuckDB and Trino to share one catalog today, use PostgreSQL. See dev-docs/CATALOG-BACKENDS.md.

Local DuckDB .db file — single-process deployment (one Trino coordinator, zero or one workers on the same host), dev/test, or interactive single-user use:

connector.name=ducklake

# DuckLake metadata catalog database (DuckDB file)
ducklake.catalog.database-url=jdbc:duckdb:/var/lib/trino/ducklake/lake.db

# Base location for data files
ducklake.data-path=/var/lib/trino/ducklake/data/

The .db file must already contain a DuckLake metadata schema before Trino connects. The simplest way to initialize one is from a one-shot DuckDB session:

duckdb -c "INSTALL ducklake; LOAD ducklake; \
  ATTACH 'ducklake:/var/lib/trino/ducklake/lake.db' AS lake \
    (DATA_PATH '/var/lib/trino/ducklake/data/'); \
  DETACH lake;"

user and password are not required for the local DuckDB backend.

Concurrency limits: a single DuckDB .db file can be safely accessed by multiple connections within one process (HikariCP pool sharing one DuckDB DatabaseInstance), but not by multiple OS processes simultaneously. For multi-worker Trino clusters or for sharing the catalog with concurrent DuckDB CLI sessions, use the PostgreSQL backend.

For S3 storage, add to the same catalog properties file:

fs.native-s3.enabled=true
s3.region=<region>

Restart Trino for the new plugin and catalog to take effect.

Configuration

Catalog properties (set in etc/catalog/<name>.properties):

Session Properties

Snapshot resolution precedence: query clause > session property > catalog config > current snapshot.

Type System

"Degraded" means data is fully preserved and round-trips correctly, but type-specific operators and functions are not available through Trino.

Read Operations

Virtual Columns

Row-lineage metadata is exposed as $-prefixed hidden columns, matching the convention Trino's Iceberg and Delta connectors use. They are not included in SELECT * or DESCRIBE but are queryable by explicit name (and usable in WHERE / GROUP BY):

SELECT "$path", "$row_id", "$file_row_number", "$snapshot_id" FROM orders WHERE id = 42;

-- File distribution of a table
SELECT "$path", count(*) FROM orders GROUP BY "$path";

-- $path predicate prunes data files before the scan (optimization; results are correct either way)
SELECT * FROM orders WHERE "$path" = 's3://bucket/.../ducklake-abc.parquet';

Notes: virtual columns are read-only (writing them is rejected); $file_index and $filename are intentionally not exposed (see DESIGN-virtual-columns.md § 8); DuckDB-style unprefixed aliases (rowid, filename, …) are deferred.

Write Operations

Partitioning

Temporal partition values follow the DuckLake 1.0 calendar encoding (the default; spec PR duckdb/ducklake-web#349 settled this). A deprecated epoch path is preserved behind ducklake.temporal-partition-encoding=epoch for compatibility with pre-resolution catalogs; it should not be needed against any v1-conformant catalog.

Statistics

Non-Parquet Data Files (removed — moved to duckbridge)

This connector reads and writes parquet data files only (delete files: parquet/puffin; inlined data unchanged). The experimental duckdb (.db), vortex, and lance data-file formats — along with the DuckDB-engine read/write path, function/expression pushdown, and the lance vector/FTS/hybrid search table functions — were removed and now live in the standalone duckbridge Trino → DuckDB connector.

A catalog that still references a non-parquet file_format (from an older deployment) fails loud with a named NOT_SUPPORTED error at metadata load and split generation, rather than returning silently-wrong results. The ducklake-catalog module's DuckDB/Quack usage is the metadata/catalog plane and is unaffected.

Change Feed

Three table functions expose the rows that changed between two snapshots (DuckLake's data change feed):

-- Rows changed between snapshots 3 and 4 (bounds inclusive):
SELECT * FROM TABLE(ducklake.system.table_changes(
        schema_name => 'sales', table_name => 'orders',
        start_snapshot => 3, end_snapshot => 4))
ORDER BY snapshot_id;

• Bounds are inclusive on both ends. Each bound is given as a snapshot id (start_snapshot / end_snapshot, BIGINT) or a timestamp (start_timestamp / end_timestamp, TIMESTAMP WITH TIME ZONE, resolved to the snapshot active at-or-before it — like AS OF). A bound may not be given both ways; start is required, end defaults to the current snapshot.
• Columns are read as of the end-snapshot schema (schema evolution applies — a column added in the window shows as its default/NULL for earlier changes; a dropped column is omitted).
• rowid follows DuckLake row lineage: when a file carries the embedded lineage column (parquet field-id 2147483540 / _ducklake_internal_row_id, written by lineage-preserving UPDATE / compaction — e.g. DuckDB), that preserved rowid is used; otherwise it's row_id_start + file position. So a lineage-preserving UPDATE's delete and re-insert land on the same rowid in one snapshot and pair into update_preimage + update_postimage. This connector's OWN UPDATE/MERGE writes emit the lineage column too (session property write_row_lineage, on by default — DuckDB preserves lineage unconditionally, so this is the cross-engine-faithful behavior; parquet data files): rewritten rows keep their original rowid — stable across engines and across CHAINED updates — and Trino-written updates pair in the change feed exactly like DuckDB-written ones. Compaction (rewrite_data_files) preserves rowids the same way, and the queryable $row_id virtual resolves the embedded lineage on plain reads. Set write_row_lineage = false for the legacy shape: a fresh row_id_start per rewrite, updates surfacing as delete + insert.
• The feed reads file-based data/delete files and DuckLake's inlined data (small writes DuckDB keeps in ducklake_inlined_* tables): inlined inserts, inlined-row deletes, and inline file-position deletes all surface. Compaction that expires snapshots can still limit what the feed can report (per the DuckLake spec).

Table Properties

Set on CREATE TABLE ... WITH (...) and CREATE TABLE ... AS SELECT ... WITH (...).

Example:

CREATE TABLE sales.orders (
    id BIGINT,
    region VARCHAR,
    event_date DATE,
    amount DOUBLE
) WITH (
    partitioned_by = ARRAY['region', 'year(event_date)'],
    location = 's3://my-bucket/cold-tier/orders/'
);

Procedures

Procedures are exposed under the system schema of the catalog, following the Trino convention (CALL <catalog>.system.<procedure>(...)). The <catalog> token is the catalog name configured in etc/catalog/<name>.properties — a single procedure invocation operates on whichever DuckLake catalog you invoke through.

add_files

CALL ducklake.system.add_files(
    schema_name => 'sales',
    table_name => 'orders',
    files => ARRAY['s3://bucket/legacy/2024/orders.parquet'],
    allow_missing => false,         -- default: false. Reject if a table column is missing from the file
    ignore_extra_columns => false,  -- default: false. Reject if the file has columns not in the table
    hive_partitioning => false      -- default: false. Parse {key=value}/ path segments as partition values
)

Parquet column names are matched to table column names case-insensitively at registration time, and the registration writes a ducklake_name_mapping row that the read path consults — so files with case-differing or otherwise-renamed columns read correctly through both Trino and DuckDB. Column reordering between file and table is supported. Each unique parquet schema seen across the files array shares one ducklake_column_mapping row. The procedure produces one snapshot per invocation and participates in the connector's concurrent-conflict matrix (an add_files racing a DROP COLUMN against the same column will fail non-retryably, matching upstream).

Known limitation in v1: hive partitioning is supported only for the IDENTITY transform. Tables with year(col) / month(col) / bucket(N, col) partition specs are not yet accepted with hive_partitioning => true, since their stored partition value is derived (not the original column value) and can't be projected back. Identity-partition columns missing from the parquet body are projected from the catalog's ducklake_file_partition_value row at read time, so hive-style external file imports round-trip through SELECT.

remove_orphan_files

CALL ducklake.system.remove_orphan_files(
    schema_name => 'sales',
    table_name => 'orders',
    retention_threshold => '7d',    -- default '7d'; floored by ducklake.remove-orphan-files.min-retention
    dry_run => false                -- default false; true logs candidates and deletes nothing
)

Deletes files under the table's data path that no catalog row references — the residue of failed/aborted commits, which previously had no Trino-side remedy. Only files older than the retention threshold are removed; that grace period is what makes the op safe without a global lock (a file young enough to still be referenced by an in-flight, possibly cross-engine, writer is never touched), and the threshold is floored by the ducklake.remove-orphan-files.min-retention config so it can't be set dangerously low. The known set spans every data/delete-file path the catalog references at any snapshot (so end-snapshotted-but-not-yet-cleaned files are safe) plus files already scheduled for deletion. Touches storage only — no snapshot, no catalog mutation. See dev-docs/DESIGN-maintenance.md.

Empty directories are intentionally left in place. The procedure deletes only the selected orphan files, never a directory tree: a writer can add a file after an emptiness check, making recursive directory cleanup unsafe.

Path comparison recognizes file:/, file:///, and local:/// aliases when native local storage is enabled, and s3/s3a/s3n aliases when native S3 is enabled. Object-key slashes, dot segments, percent text, and case are not normalized. Stored paths and deletion targets are not rewritten. Canonical paths in a single other filesystem namespace remain supported. Mixed unqualified or Hadoop/other namespaces and noncanonical non-S3 paths cause an explicit failure before any deletion, including in dry runs. This considers all retained and scheduled references, even outside the selected scan root. Do not blindly replace bare paths with file: URIs: native local.location and Hadoop's default filesystem can make them refer to different storage. See the path-comparison limits.

expire_snapshots + cleanup_old_files

-- Expire snapshots older than 30 days (catalog-wide), then reclaim the freed files.
CALL ducklake.system.expire_snapshots(retention_threshold => '30d');
CALL ducklake.system.cleanup_old_files(retention_threshold => '7d');

-- Or expire specific snapshot ids (not floored by min-retention):
CALL ducklake.system.expire_snapshots(snapshot_ids => ARRAY[12, 13, 14]);

expire_snapshots removes old DuckLake snapshots — which are catalog-wide versions, so this is a catalog-scoped procedure, not table-scoped — and reclaims the data/delete files that only those snapshots referenced (liveness is the half-open [begin_snapshot, end_snapshot) window against the surviving snapshots; the latest snapshot is never expirable). It is a plain catalog transaction (no new snapshot) that only schedules the dead files into ducklake_files_scheduled_for_deletion; cleanup_old_files is the second phase that checks a bounded batch after the grace period. Before deleting, it scans retained data/delete references in SQL-limited pages. If another retained reference owns the same file, cleanup preserves the file and cancels only the obsolete selected queue request. It does not leave that request for a later DuckDB cleanup to consume. The final owner's normal retirement can schedule the file again for eventual reclamation.

Each call selects at most ducklake.cleanup.batch-size requests (256 by default, maximum 1024). Repeat the call for further batches. Reference pages share that size; exceeding ducklake.cleanup.max-reference-files fails before any deletion or cancellation. Paths and the catalog root are bounded to 16,384 characters, with bounded overflow/lookahead sentinels. These limits bound coordinator materialization, not database scan time or the number of members in a legacy directory deletion. Identical duplicate queue rows describe one request and are removed together. Failed deletions remain queued; persistent failures or deferred requests may require operator attention before later batches can progress.

Dry runs perform the same owner/path/budget checks without modifying storage or the queue. Prefix-only directory overlaps are deferred with a warning, not cancelled: retiring a child does not necessarily reschedule the whole parent. Those requests require manual reconciliation before another engine processes them. RV-03's path-identity restrictions also apply here.

This is not a lock against simultaneous import/cleanup, nor a guarantee that an independent stock DuckDB cleaner will check owners. Avoid overlapping those operations on the same objects. Retention provides a grace period, not a coordinated reader lease. See dev-docs/DESIGN-shared-file-ownership.md. Expire also GCs the metadata rows of fully-expired dropped tables; dead schema/view/macro rows are left as harmless dangling rows (a follow-up).

Partial (cross-snapshot compacted) files: DuckLake's merge_adjacent_files can merge rows from multiple snapshots into one file (ducklake_data_file.partial_max set), physically carrying each row's origin snapshot. The connector reads such files correctly: a time-travel read at snapshot S drops rows whose _ducklake_internal_snapshot_id > S (only when partial_max > S), so time travel over a DuckDB-compacted table returns the right rows. Consolidated parquet delete files spanning snapshots (ducklake_delete_file.partial_max) are filtered the same way — only the deletions recorded at or before the read snapshot apply. Consolidated puffin (deletion-vector) delete files are filtered too: a real PFA1 container tags each blob with a ducklake-snapshot-id, and a time-travel read applies only the blobs whose snapshot id is <= S. Every partial-file shape (data, parquet-delete, puffin-delete) is now read correctly — no gate remains.

Cross-Engine Compatibility

The connector is tested for bidirectional compatibility with DuckDB:

Upstream corpus replay

Beyond the hand-written cross-engine suites, the connector is verified against DuckLake's own upstream test corpus — the 466 sqllogictest files (7,600+ test cases) that the reference C++ implementation ships in duckdb/ducklake test/sql/, vendored as the ducklake git submodule and driven by the published dev.brikk.ducklake:ducklake-test-corpus-replay replay harness. Each corpus file is executed verbatim through an embedded DuckDB oracle (no dialect translation), building real catalog state on an isolated PostgreSQL metadata database; every lake read the corpus performs is then re-executed through a live Trino query runner against the same catalog and compared row-for-row against the oracle's result — including intermediate states mid-file. A divergence means Trino read the same DuckLake state differently than DuckDB: exactly the class of spec-conformance bug that is hardest to catch by hand (on first contact the harness found two real bugs that 138 hand-written test classes had missed). The corpus grows with every upstream release, so conformance coverage compounds for free:

./gradlew test --tests "*TestTrinoCorpusReplay"                            # starter set
./gradlew test --tests "*TestTrinoCorpusReplay" -Dducklake.corpus.dirs=all # full corpus

Not Yet Implemented

Maintenance Operations

All maintenance operations are shipped as CALL system.* procedures — see Procedures: rewrite_data_files (compaction / optimize, both non-partial and partial-emitting shapes), expire_snapshots, cleanup_old_files, remove_orphan_files, flush_inlined_data, plus stats recompute via ANALYZE. The connector exposes these as procedures rather than the ALTER TABLE ... EXECUTE optimize alias (which needs a separate SPI for no capability gain). Design notes: dev-docs/DESIGN-maintenance.md.

DDL

• ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE (type promotion) is supported for widening changes, on both top-level columns and nested struct fields (see the feature chart above). Still not supported: narrowing / incompatible changes (rejected at DDL time), and type changes to list elements or map values.

Commit Context

Session properties for annotating write snapshots (DuckDB set_commit_message equivalent — commit_author / commit_message / commit_extra_info) are a deliberate non-goal: the required columns are not in the DuckLake spec's ducklake_snapshot table, so writing them would risk cross-engine divergence. Revisit only if DuckLake upstream defines them.

View storage

A Trino view is stored using only slots the DuckLake spec defines and DuckDB understands, so a catalog holding Trino views stays fully loadable by DuckDB / pg_ducklake:

Nothing is stored as JSON. column_aliases in particular must be a spec quoted list — DuckDB parses it at catalog load and refuses the whole catalog otherwise.

Views from other writers. dialect = 'trino' only says the SQL is Trino SQL; a different DuckLake connector for Trino may write it with different (or no) tags. Such a view — and any trino/<variant> dialect — is listed (so you can find it) but any use fails with an INVALID_VIEW error stating the view, the reason (e.g. the 'trino.column_types' tag ... is missing), and the remedy: CREATE OR REPLACE VIEW from this connector, or DROP VIEW. Both remedies always work; the connector never executes a guessed definition. information_schema.views skips such views (with a server-side warning) rather than failing for the schema.

Views created by DuckDB (or other engines) in their own dialect are not visible in Trino. Cross-dialect transpilation (e.g., DuckDB SQL to Trino SQL) is a research item.

Catalog Backends

Known Limitations

• The variant type is readable as VARCHAR but without shredded field access (e.g., payload.user syntax is not supported). Variant statistics for shredded sub-fields are not used for pushdown.
• Geometry types are readable as VARBINARY but without spatial functions or bounding-box statistics for file pruning.
• int128 maps to DECIMAL(38, 0). Values in the narrow ±1.7e38..±10^38 edge bands exceed the decimal's range and will error on read.
• uint128 is degraded to VARCHAR; Trino cannot represent a ≥39-digit signed-or-unsigned integer. Values round-trip as text; no numeric operations are available.
• Unsigned integer types (uint8/16/32/64) are widened to larger signed Trino types on read (uint8 → SMALLINT, uint16 → INTEGER, uint32 → BIGINT, uint64 → DECIMAL(20, 0)). On the write path, values outside the unsigned range of the target DuckLake column are rejected at INSERT time with NUMERIC_VALUE_OUT_OF_RANGE — e.g. a SMALLINT 300 or a negative value written to a uint8 column fails cleanly instead of silently wrapping to 44.
• Files written before a failed commit become orphans. Reclaim them with CALL system.remove_orphan_files(schema_name, table_name) (see Procedures).
• Puffin deletion vectors (DuckLake's Roaring-bitmap delete files) are both read AND written. By default Trino-side DELETE/UPDATE/MERGE emits DuckLake-spec parquet positional delete files ((file_path, pos) with file-local positions — readable by DuckDB); set the session property write_deletion_vectors = true to emit .puffin deletion-vector files instead (matching DuckDB's write_deletion_vectors option). Both shapes are read by Trino and DuckDB. Position-delete filtering over parquet data files is cross-engine tested in both directions (Trino-writes/DuckDB-reads and DuckDB-writes/Trino-reads).
• Row lineage is both read AND written (session property write_row_lineage, on by default). Trino's UPDATE/MERGE rewrites AND rewrite_data_files compaction embed each row's original rowid (_ducklake_internal_row_id, parquet field-id 2147483540 — DuckDB's own encoding), so rowids stay stable across engines, across chained updates, and across compaction; change feeds (Trino's table_changes AND DuckDB's) pair rewrites into update_preimage/update_postimage, and the $row_id virtual resolves the preserved ids on plain reads. Parquet data files only; cross-engine tested (DuckDB observes the same rowid after a Trino lineage UPDATE).
• The experimental duckdb / vortex / lance data-file formats, the DuckDB-engine read/write path, function/expression pushdown, and the lance search table functions were removed and moved to the standalone duckbridge Trino → DuckDB connector (see Non-Parquet Data Files).

Building from source

Requires JDK 25. The repo pins it via mise (mise.toml) — run mise install — or point JAVA_HOME at any JDK 25. The shared catalog layer (dev.brikk.ducklake:ducklake-catalog) resolves from Maven Central; clone with submodules (git clone --recurse-submodules, or git submodule update --init) so the corpus fixture and ducklake-web spec are present.

Build the plugin distribution

./gradlew pluginDist

Produces build/distributions/trino-ducklake-<version>.{zip,tar.gz} (+ .sha256), each expanding to a trino-ducklake-<version>/ directory of jars — the same artifacts attached to a GitHub Release. For just the exploded plugin dir (no archive), use ./gradlew pluginAssemble (output under build/trino-plugin/trino-ducklake-<version>/).

Local dev stack (Docker Compose)

For interactive hacking against a real Trino + Postgres + MinIO + DuckLake stack with optional TPC-H seed data, see compose/README.md.

Running tests

Tests require Docker or Podman (PostgreSQL — and the Quack DuckDB sidecar when that backend is selected — run via Testcontainers). The default test backend is PostgreSQL; flip backends with the ducklake.test.catalog-backend system property:

./gradlew test                                                # PostgreSQL (default)
./gradlew test -Dducklake.test.catalog-backend=DUCKDB_LOCAL   # local DuckDB .db file
./gradlew test -Dducklake.test.catalog-backend=DUCKDB_QUACK   # remote DuckDB over Quack (experimental)

Releasing

Push a v<version> tag (e.g. v0.1.0); the release.yml workflow builds pluginDist and attaches the archives + checksums to the GitHub Release for that tag.

Additional Documentation

• SAMPLES.md — SQL examples for DDL, DML, time travel, metadata tables, and DuckDB interop
• TODO for READ side — Read mode open items + research notes
• TODO for WRITE side — Write mode open items + design rationale
• duckbridge — the standalone Trino → DuckDB connector that now owns the DuckDB-engine pushdown, executors, and lance/vortex surfaces
• dev-docs/archive/ — Historical context: closed work, archived plans, the DuckLake 1.0 spec impact reference, the reuse audit, and the date/time pushdown program design + empirical TZ findings (incl. the tombstoned duckdb-format trackers)