Skip to main content

DuckLake

DuckLake is an integrated data lake and catalog format for large scale data analytics.

note

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

DuckLake is an integrated data lake and catalog format. DuckLake delivers advanced data lake features without traditional lakehouse complexity by using Parquet files and a SQL database.

MotherDuck provides two main options for creating and integrating with DuckLake databases:

Creating a fully managed DuckLake database​

Create a fully managed DuckLake with the following command:

CREATE DATABASE my_ducklake (TYPE DUCKLAKE);

MotherDuck manages both data and metadata storage. The underlying storage is not directly accessible from your own compute. Use BYOB when you need direct access to the data files.

The my_ducklake database can be accessed like any other MotherDuck database, including over the Postgres endpoint for clients that don't use the DuckDB SDK. To inspect the metadata catalog backing the DuckLake, see Performing metadata operations on a DuckLake.

You can attach the DuckLake metadata with:

ATTACH 'md:__ducklake_metadata_<database_name>' AS <database_alias>;
note

The metadata database can only be attached by the database owner.

Data inlining​

Data inlining is an optimization feature that stores small data changes directly in the metadata catalog rather than creating individual Parquet files for every insert operation. This eliminates the overhead of creating small Parquet files while maintaining full query and update capabilities.

Creating a DuckLake database with custom inlining​

To create a (fully managed) DuckLake database with a custom inlining threshold:

CREATE DATABASE my_ducklake (
    TYPE DUCKLAKE,
    DATA_INLINING_ROW_LIMIT 100
);

This configuration lets DuckLake inline inserts of up to 100 rows directly into the metadata catalog.

How data inlining works​

Data inlining is enabled by default with a row limit of 10. Small inserts are stored in the metadata catalog instead of separate Parquet files. Small deletes from existing Parquet files can also be stored in the catalog instead of separate deletion files.

You can customize the threshold with the DATA_INLINING_ROW_LIMIT parameter. For example, if you set it to 100, inserts of up to 100 rows can be stored inline, while larger inserts create Parquet files. Set it to 0 to disable inlining.

The inlining threshold applies per insert operation. For example, if the limit is set to 100, four separate inserts of 50 rows each will all be stored inline (200 total rows), because each individual insert is below the threshold.

When an insert exceeds the threshold, that insert writes directly to a Parquet file, but any previously inlined data remains in the metadata catalog. Larger inserts do not automatically flush existing inlined data.

note

For BYOB databases, inlined data is stored in the MotherDuck-managed metadata catalog, not in your bucket. Small inserts only appear in your bucket as Parquet files after they are flushed. Set DATA_INLINING_ROW_LIMIT to 0 before writing data if table rows must be stored as Parquet files in your bucket. This does not change where metadata is stored or move rows that are already inlined.

Flushing inlined data​

Use ducklake_flush_inlined_data to write inlined inserts and deletions to Parquet files:

-- Flush inlined data for a specific table
CALL ducklake_flush_inlined_data('my_ducklake', schema_name => 'my_schema', table_name => 'my_table');

-- Flush all inlined data in a schema
CALL ducklake_flush_inlined_data('my_ducklake', schema_name => 'my_schema');

-- Flush all inlined data in the database
CALL ducklake_flush_inlined_data('my_ducklake');

ducklake_flush_inlined_data returns one row per table with flushed data. Use SELECT * FROM instead of CALL when you need to filter or aggregate the result.

For workloads with frequent small inserts, schedule regular flushes to prevent excessive inlined data accumulation.

MotherDuck does not run these flushes automatically. See DuckLake maintenance.

Configuring inlining​

Use set_option to persist an inlining limit in the catalog. These examples assume my_table exists in the main schema:

-- Disable inlining across the database
CALL my_ducklake.set_option('data_inlining_row_limit', 0);

-- Disable inlining for a specific table
CALL my_ducklake.set_option('data_inlining_row_limit', 0, table_name => 'my_table');

-- Set a custom threshold for a specific table
CALL my_ducklake.set_option('data_inlining_row_limit', 50, table_name => 'my_table');

The DuckDB setting below changes the default for DuckLake connections without an explicit inlining limit. It does not replace limits set on the attachment or persisted with set_option:

SET ducklake_default_data_inlining_row_limit = 0;

DuckLake configuration​

DuckLake provides configuration options that you can persist at the database, schema, or table level using the set_option function. For example, you can adjust the parquet_row_group_size to control how data is organized in Parquet files:

-- Set row group size for the entire database
CALL my_ducklake.set_option('parquet_row_group_size', 50000);

-- Set row group size for a specific table in the main schema
CALL my_ducklake.set_option('parquet_row_group_size', 50000, table_name => 'my_table');

A table-level option takes precedence over a schema-level option, which takes precedence over a database-level option. Persisted options take precedence over attachment settings and DuckDB defaults. For example, a table-specific data_inlining_row_limit can override a database-wide limit of 0.

For the full list of options and their scope, see the DuckLake configuration reference.

Bring your own bucket (BYOB)​

You can use MotherDuck as a compute engine and managed DuckLake catalog with data files in your own Amazon S3, Cloudflare R2, Azure Blob Storage, or Google Cloud Storage (GCS).

BYOB also works with any object storage that has an S3-compatible API, such as Hetzner Object Storage, Tigris, Backblaze B2, Wasabi, or a self-hosted MinIO server. For these providers, see the Other S3-compatible tab in Setup.

BYOB supports cross-region and cross-cloud storage. Your bucket or container does not need to be in the same region or cloud as your MotherDuck organization. You can also use your own compute to read and write the data files directly with a DuckDB client.

Use BYOB when you need to store data files in a specific region, for example to meet storage-residency requirements. When that storage is in a different region or cloud from MotherDuck compute, expect higher query latency and lower performance than a fully managed DuckLake. Your storage provider may also charge for cross-region or cross-cloud data transfer.

MotherDuck compute processes your data in your organization's region. That region also holds the metadata catalog, including table statistics and any inlined rows.

Setup​

Connect to MotherDuck, then choose your storage provider below. Each example creates a secret and a DuckLake database named my_ducklake.

Before running an example:

  • Create a bucket or container and choose a separate, unused prefix for this DuckLake's files.
  • Grant the credentials the required permissions.
  • Replace the <placeholder_name> values with your storage details. Keep SCOPE and DATA_PATH aligned so the secret matches the files DuckLake reads and writes.

Set REGION to the bucket's region, which can differ from your MotherDuck organization's region.

CREATE SECRET ducklake_s3 IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<bucket_region>',
    SCOPE 's3://<bucket_name>/<ducklake_prefix>/'
);

CREATE DATABASE my_ducklake (
    TYPE DUCKLAKE,
    DATA_PATH 's3://<bucket_name>/<ducklake_prefix>/'
);

For temporary AWS credentials, also include SESSION_TOKEN '<aws_session_token>' in the secret. See Amazon S3 credentials.

Creating the database alone does not verify that the credentials can read and write data files. After setup, follow Using MotherDuck compute to create and query a table. Small inserts can remain in the metadata catalog through data inlining, so flush them when testing access to your storage.

Required permissions for DuckLake​

The minimum required IAM permissions are:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket"
      ],
      "Resource": "arn:aws:s3:::<bucket_name>"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:DeleteObject"
      ],
      "Resource": "arn:aws:s3:::<bucket_name>/<ducklake_prefix>/*"
    }
  ]
}

Using MotherDuck compute​

Connect to MotherDuck:

> duckdb md:

Create your first DuckLake table from a hosted Parquet file:

CREATE TABLE my_ducklake.air_quality AS
SELECT * FROM 'https://us.data.motherduck.com/who_ambient_air_quality/parquet/who_ambient_air_quality_database_version_2024.parquet';

Query using MotherDuck:

SELECT
    year,
    AVG(pm25_concentration::double) AS avg_pm25,
    AVG(pm10_concentration::double) AS avg_pm10,
    AVG(no2_concentration::double) AS avg_no2
FROM my_ducklake.air_quality
WHERE city = 'Berlin/DEU'
GROUP BY year
ORDER BY year DESC;

Using own compute​

Connect your DuckDB client to MotherDuck as the database owner. To use your own compute, you must:

  1. Ensure you have appropriate credentials in your compute environment to read/write to your defined DATA_PATH (specified at database creation)
  2. Attach the DuckLake using the ducklake: prefix so compute runs locally against the MotherDuck-managed metadata catalog

Secrets created IN MOTHERDUCK are stored in MotherDuck and automatically available to your DuckDB client, so a single static-key secret can serve both MotherDuck compute and your own compute. If you already created one in the Setup step, you can skip ahead to attaching the DuckLake.

For AWS SSO on your own compute, run aws sso login --profile <aws_sso_profile> on the machine running DuckDB, then create a local secret there:

CREATE SECRET ducklake_s3_local (
    TYPE S3,
    PROVIDER credential_chain,
    CHAIN 'sso',
    PROFILE '<aws_sso_profile>',
    REGION '<bucket_region>',
    SCOPE 's3://<bucket_name>/<ducklake_prefix>/'
);

This secret lives only for your DuckDB session. Recreate it after your AWS credentials expire or when you start a new session. To store credentials across sessions, use a secret IN MOTHERDUCK as shown in Setup. See using a local IAM role or SSO session for details.

Load the DuckLake extension and attach the existing MotherDuck-managed metadata catalog:

INSTALL ducklake;
LOAD ducklake;

ATTACH 'ducklake:md:__ducklake_metadata_<database_name>' AS <database_alias>;

This tells DuckLake to:

  • Use ducklake:md:__ducklake_metadata_<database_name> as the metadata catalog (through MotherDuck)
  • Read and write data files using the DATA_PATH already stored in the catalog
  • Run DuckLake data-file scans and writes on your DuckDB client, with metadata operations handled by MotherDuck

DuckLake reuses the stored data path when attaching an existing lake, so you do not need to repeat DATA_PATH.

The ducklake: prefix is what enables local compute. Attaching with ATTACH 'md:__ducklake_metadata_<database_name>' (without the prefix) gives you the metadata catalog for inspection only. See Performing metadata operations on a DuckLake.

Create a table using your own compute:

CREATE TABLE <database_alias>.air_quality AS
SELECT * FROM read_parquet(
    'https://us.data.motherduck.com/who_ambient_air_quality/parquet/who_ambient_air_quality_database_version_2024.parquet',
    MD_RUN = LOCAL
);

MD_RUN = LOCAL also keeps the HTTPS source read on your client. Without it, MotherDuck routes cloud-file reads to its cloud compute by default.

With this configuration, your own compute can directly access or write data to your DuckLake (assuming appropriate credentials are configured). Data uploaded using your own compute will appear in the MotherDuck catalog and be queryable as a standard MotherDuck database.

DuckLake 1.0 features​

DuckLake 1.0 is the first production-ready release, with a stable specification and backward-compatibility guarantees. Highlights include:

  • Stable specification and multi-engine support: The DuckLake 1.0 spec is stable with backward-compatibility guarantees going forward, and is designed to be used across multiple query engines.
  • Full inlining for inserts, updates, and deletes: Updates join inserts and deletes in being inlined into the metadata catalog when under the row threshold (10 by default). Customize with DATA_INLINING_ROW_LIMIT.
  • Clustering with SET SORTED BY: Declare sort keys on columns or arbitrary SQL expressions. DuckLake applies the sort during compaction and inline flush (and optionally on insert), improving row-group and file pruning for filtered queries.
  • Bucket partitioning: Iceberg-compatible bucket(N, column) transforms for high-cardinality columns, giving a middle ground between traditional partitioning and avoiding the small-files problem.
  • GEOMETRY enhancements: Per-file bounding-box statistics enable file pruning on spatial filters, and GEOMETRY can be nested inside STRUCT, LIST, and MAP.
  • VARIANT type with shredded statistics: VARIANT sub-fields receive file-level statistics, enabling filter pushdown and faster selective queries over semi-structured data.
  • Deletion vectors (experimental): Iceberg v3-compatible deletion vectors, stored as Puffin files as an alternative to delete files.

See the DuckLake 1.0 release post and the MotherDuck announcement for more detail.

Additional DuckLake features​

DuckLake on MotherDuck also supports:

  • Stats-only COUNT(*): Simple COUNT(*) queries are answered directly from metadata statistics without scanning data files.
  • TopN file pruning: LIMIT queries with an ORDER BY skip data files that fall outside the requested range, making paginated and top-N queries faster.
  • Expressions as default values: Column defaults can use expressions like now(), not only literal values.
  • Macros: DuckLake catalogs can store macros.

Performing metadata operations on a DuckLake​

Using DuckLake provides additional metadata operations for introspection and maintenance. These operations can be performed from both MotherDuck and your own compute environments. For example, you can list the snapshots backing your DuckLake.

Each DuckLake in MotherDuck has a corresponding metadata database that stores internal state, including schema definitions, snapshots, file mappings, and more. To inspect this metadata catalog directly from any DuckDB session (this works for both fully managed and BYOB databases):

ATTACH 'md:__ducklake_metadata_<database_name>' AS <database_alias>;
note

The metadata database can only be attached by the database owner. This form attaches the metadata catalog for inspection only. To run DuckLake compute locally against your data, use the ducklake: ATTACH form shown in Using own compute.

Time travel​

Query a historical snapshot of a DuckLake table with the AT clause, by snapshot version or timestamp:

-- Query a specific snapshot by version
SELECT * FROM my_ducklake.main.my_table
    AT (VERSION => 12);

-- Query as of a timestamp
SELECT * FROM my_ducklake.main.my_table
    AT (TIMESTAMP => TIMESTAMP '2026-01-15 10:30:00');

List available snapshots with the database's snapshots() macro:

SELECT * FROM my_ducklake.snapshots();

Use a snapshot ID or timestamp from this result in the AT examples.

For MotherDuck compute, use the per-query AT clause. SNAPSHOT_VERSION and SNAPSHOT_TIME are DuckLake extension attachment options. They do not pin a native ATTACH 'md:...' connection.

For your own compute with BYOB, you can pin the DuckLake attachment to a snapshot. Choose one of these alternatives and replace the example version or timestamp with a value from snapshots():

ATTACH 'ducklake:md:__ducklake_metadata_<database_name>' AS historical_ducklake
    (SNAPSHOT_VERSION 12, READ_ONLY);

Query tables through historical_ducklake to use the selected snapshot. A per-query AT clause overrides the attachment's snapshot selection. See DuckLake time travel.

Limitations​

  • Limited sharing options: Read-only sharing is supported through the existing share functionality, restricted to auto-update shares only. Table-level security isn't available on shares of DuckLake databases. CREATE SHARE and ALTER SHARE ... SET INCLUDE_PATTERN reject a DuckLake source, for both fully managed and BYOB databases. Unfiltered DuckLake shares are unaffected
  • Single-account write access: Write permissions are limited to one account per database. This account can perform multiple concurrent writes, as long as they are append-only. If multiple queries attempt to update or delete from the same table concurrently, only the first to commit will succeed. Concurrent DDL operations are also not allowed. Support for multi-account write access is planned for a future release.
info

For multiple concurrent readers to a MotherDuck DuckLake database, you can create a read scaling token.