Preview

This feature is in preview and is subject to change.

DuckLake

info

MotherDuck supports DuckDB client versions 1.4.1 through 1.5.5 in all regions. For the range each region supports, see client version support.

DuckLake is an open table format for large-scale analytics that provides data management capabilities similar to Apache Iceberg and Delta Lake. It organizes data into partitions based on column values like date or region for efficient querying, with actual data files stored on object storage systems. DuckLake innovates by storing metadata in database tables rather than files, enabling faster lookups through database indexes and more efficient partition pruning using SQL queries, while the columnar data itself resides on scalable object storage infrastructure.

MotherDuck provides support for managed DuckLake, enabling you to back MotherDuck databases with a DuckLake catalog and storage for petabyte-scale data workloads.

tip

Looking for code examples? Check out the integration guide to see how easy it is to start using DuckLake with MotherDuck.

Key characteristics

Database-backed metadata: DuckLake stores table metadata in a transactional database (PostgreSQL, MySQL) rather than files, providing:

• Faster metadata lookups through database indexes
• Efficient filtering of data by skipping irrelevant partitions using SQL WHERE clauses
• Simplified writes without the performance overhead of manifest file merging

Multi-table transactions: Unlike other lake formats that operate on individual tables, DuckLake supports ACID transactions across multiple related tables, better reflecting how organizations think about databases as collections of inter-related tables.

Simplified architecture: No additional catalog server required, just a standard transactional database that most organizations already have expertise managing.

DuckLake vs. other lake formats

Performance differences

Table formats like Apache Iceberg and Delta Lake store metadata in file-based structures. Read and write operations must traverse these file-based metadata structures, which can create latency that increases with scale.

File-based metadata challenges:

• Sequential file scanning for metadata discovery
• Complex manifest file merging for writes
• Limited query optimization due to metadata access patterns
• Catalog server complexity for coordination

DuckLake approach:

• Database indexes provide faster metadata lookups
• Transactional writes reduce manifest merging overhead
• SQL-based partition pruning and query optimization
• Standard database operations for metadata management

Scale and capability comparison

Capability	DuckLake	Iceberg/Delta Lake
Data Scale	Petabytes	Petabytes
Metadata Storage	Database tables with indexed access	File-based structures requiring sequential traversal
Metadata Performance	Database index lookups	Additional catalog required
Write Operations	Database transactions	Manifest file merging
Multi-table Operations	Full ACID transactions across tables	Limited cross-table coordination
Infrastructure Requirements	Standard transactional databases	Separate catalog servers
Schema Evolution	Coordinated multi-table schema evolution	Individual table-level changes

Use cases and applications

When to choose DuckLake as your open table format

DuckLake is particularly well-suited for:

Large-scale analytics: Organizations with petabytes of historical data, high-volume event streams, or analytics requirements that exceed traditional data warehouse storage or processing capabilities.

Multi-table workloads: Applications requiring coordinated schema evolution, cross-table constraints, or transactional consistency across related tables.

Metadata-intensive workloads: Scenarios where file-based metadata access patterns may impact query performance.

Reduced infrastructure complexity: Organizations seeking lake-scale capabilities with fewer separate catalog servers and metadata management components.

Storage comparison: MotherDuck native vs DuckLake storage

For loading data, MotherDuck and DuckLake perform very similarly.

However, when reading data, MotherDuck native storage format is 2x-10x faster than DuckLake, for both cold & hot runs.

Migration considerations

From data warehouses: DuckLake provides a scaling option when warehouse storage limits or costs become constraining, while maintaining SQL interfaces and compatibility.

From other lake formats: DuckLake may provide performance improvements for metadata-intensive workloads, though migration requires consideration of existing tooling and processes.

Hybrid architectures: Organizations can use MotherDuck for traditional data warehouse workloads while graduating specific databases to DuckLake as scale requirements increase.

Performance characteristics

Metadata operations

DuckLake's database-backed metadata provides different performance characteristics:

• Partition discovery: Index-based vs. file scanning
• Schema evolution: Transactional vs. eventual consistency
• Query planning: Index-based vs. file traversal
• Concurrent access: Database locks vs. file coordination

Data inlining

DuckLake supports data inlining, an optimization that stores small data changes directly in the metadata catalog rather than creating individual Parquet files. This feature is particularly valuable for high-frequency, small-batch inserts common in streaming and transactional workloads.

Starting with DuckLake 0.4, deletion inlining extends this concept to delete operations: small deletes are stored in the metadata catalog rather than creating separate deletion files.

For implementation details and examples, see the DuckLake integration guide.

Storage lifecycle

DuckLake databases follow most of the same storage lifecycle stages as native storage databases:

1. Active bytes: Data that is part of the current state of the database
2. Historical bytes: Data retained by snapshots that is no longer part of the active state
3. Failsafe bytes: Data retained as system backups after snapshots expire (7-day retention)
4. Deleted: Data fully removed from the system

Unlike native storage databases, DuckLake does not have a "retained for clone" stage because DuckLake does not support zero-copy cloning.

Storage optimization and snapshot expiration on DuckLake databases are performed by the maintenance operations below, rather than the native storage garbage collector. For retention defaults and plan-specific details, see Storage lifecycle and management.

Maintenance

note

MotherDuck does not run automatic background maintenance on DuckLake databases. The operations below are available to run manually, from either MotherDuck or your own compute. Automatic maintenance is planned for a future release.

DuckLake databases benefit from periodic maintenance to optimize storage layout and manage the data lifecycle. You can run these operations yourself using the DuckLake maintenance functions.

File layout optimization

These operations keep query performance high by organizing data files:

Operation	Description
Flush inlined data	Converts small inlined data stored in the metadata catalog to Parquet files. This may produce small files, which the merge operation consolidates.
Merge small files	Combines adjacent small Parquet files into larger files, reducing the number of files scanned during queries.
Rewrite data files	Rewrites data files that have accumulated deleted rows to reclaim space and remove delete overhead.

Merge and rewrite operate on disjoint sets of files: merge handles files without deletes, while rewrite handles files with deletes.

Snapshot lifecycle management

These operations manage time travel snapshots and clean up files that are no longer needed:

Operation	Description
Expire snapshots	Removes snapshots older than the configured retention period and queues their associated files for deletion.
Clean up old files	Physically deletes files that have been queued for deletion by expire, merge, or rewrite operations. Files are kept for at least 12 hours after queuing, allowing in-flight queries to finish.

Snapshot retention

The SNAPSHOT_RETENTION_DAYS database option configures how long snapshots are retained before they become eligible for expiration. By default it is NULL (infinite retention), meaning no snapshots are eligible to expire.

To set a retention period:

ALTER DATABASE <database_name> SET SNAPSHOT_RETENTION_DAYS = 7;

To revert to infinite retention:

ALTER DATABASE <database_name> SET SNAPSHOT_RETENTION_DAYS = NULL;

Because MotherDuck does not expire snapshots automatically, run the expire snapshots operation manually to remove snapshots older than the configured retention period.

note

The merge and rewrite operations modify table metadata, which can occasionally conflict with concurrent write transactions on the same table. Re-run the operation if it conflicts with an in-flight write.

Future capabilities

MotherDuck continues expanding DuckLake support with planned features including:

External catalog integration: Access to customer-managed DuckLake catalogs hosted in cloud databases

Local storage access: Direct access to MotherDuck-managed storage from local DuckDB instances for hybrid workloads

Enhanced Iceberg support: Continued improvements to Iceberg integration alongside DuckLake development

Architecture implications

Catalog database requirements

DuckLake catalogs require a transactional database with:

• ACID transaction support
• Concurrent read/write access
• Standard SQL interface
• Backup and recovery capabilities

Thankfully, this is all supported as part of MotherDuck without adding an additional catalog, although in self-hosted scenarios, an alternative database like Postgres, MySQL, or SQLite can be used.

Storage considerations

DuckLake data storage follows similar patterns to other lake formats:

• Columnar file formats (Parquet)
• Partitioned directory structures
• Object storage compatibility
• Compression and encoding optimizations