# DuckLake > Use DuckLake on MotherDuck with managed storage or your own S3, R2, Azure Blob Storage, or Google Cloud Storage across regions and clouds. ::::note 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](/about-motherduck/cloud-regions/#client-version-support). :::: [DuckLake](https://ducklake.select) 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: - **[Fully managed](#creating-a-fully-managed-ducklake-database)**: Create a DuckLake database where MotherDuck manages both data storage and metadata - **[Bring your own bucket (BYOB)](#bring-your-own-bucket)**: Use your own Amazon S3, Cloudflare R2, Azure Blob Storage, or Google Cloud Storage across regions and clouds, with: - **[MotherDuck compute + MotherDuck catalog](#using-motherduck-compute)**: Use MotherDuck for both compute and catalog services - **[Own compute + MotherDuck catalog](#using-own-compute)**: Use your own DuckDB client for compute while MotherDuck provides catalog services ## Creating a fully managed DuckLake database Create a fully managed DuckLake with the following command: ```sql 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](#bring-your-own-bucket) 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](/key-tasks/authenticating-and-connecting-to-motherduck/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](#performing-metadata-operations-on-a-ducklake). You can attach the DuckLake metadata with: ```sql ATTACH 'md:__ducklake_metadata_' AS ; ``` ::::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: ```sql 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](#bring-your-own-bucket) 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`](https://ducklake.select/docs/stable/duckdb/advanced_features/data_inlining#flushing-inlined-data) to write inlined inserts and deletions to Parquet files: ```sql -- 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](/concepts/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: ```sql -- 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`: ```sql 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: ```sql -- 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](https://ducklake.select/docs/stable/duckdb/usage/configuration). ## 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](/integrations/cloud-storage/amazon-s3/), [Cloudflare R2](/integrations/cloud-storage/cloudflare-r2/), [Azure Blob Storage](/integrations/cloud-storage/azure-blob-storage/), or [Google Cloud Storage (GCS)](/integrations/cloud-storage/google-cloud-storage/). 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](#using-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](/about-motherduck/cloud-regions/). That region also holds the metadata catalog, including table statistics and any [inlined rows](#data-inlining). ### 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](#required-permissions-for-ducklake). - Replace the `` values with your storage details. Keep `SCOPE` and `DATA_PATH` aligned so the secret matches the files DuckLake reads and writes. ### Amazon S3 Set `REGION` to the **bucket's region**, which can differ from your MotherDuck organization's region. ```sql CREATE SECRET ducklake_s3 IN MOTHERDUCK ( TYPE S3, KEY_ID '', SECRET '', REGION '', SCOPE 's3:////' ); CREATE DATABASE my_ducklake ( TYPE DUCKLAKE, DATA_PATH 's3:////' ); ``` For temporary AWS credentials, also include `SESSION_TOKEN ''` in the secret. See [Amazon S3 credentials](/integrations/cloud-storage/amazon-s3/). ### Cloudflare R2 Use the access key ID and secret access key from an R2 API token. `ACCOUNT_ID` identifies the Cloudflare account that owns the bucket. R2 does not require a `REGION` parameter. ```sql CREATE SECRET ducklake_r2 IN MOTHERDUCK ( TYPE R2, KEY_ID '', SECRET '', ACCOUNT_ID '', SCOPE 'r2:////' ); CREATE DATABASE my_ducklake ( TYPE DUCKLAKE, DATA_PATH 'r2:////' ); ``` R2 [location hints](https://developers.cloudflare.com/r2/reference/data-location/) do not guarantee residency. For a bucket with a [jurisdiction restriction](https://developers.cloudflare.com/r2/reference/data-location/#jurisdictional-restrictions), replace `ACCOUNT_ID` with the jurisdiction-specific `ENDPOINT` in the secret. For example, use `ENDPOINT '.eu.r2.cloudflarestorage.com'` for the EU jurisdiction. Omit `https://` from this parameter. ### Azure Blob Storage Use an [Azure storage account connection string](https://learn.microsoft.com/en-us/azure/storage/common/storage-configure-connection-string). The connection string identifies the account, and `DATA_PATH` identifies the container and prefix within it. ```sql CREATE SECRET ducklake_azure IN MOTHERDUCK ( TYPE AZURE, CONNECTION_STRING '', SCOPE 'azure:////' ); CREATE DATABASE my_ducklake ( TYPE DUCKLAKE, DATA_PATH 'azure:////' ); ``` Use credentials that MotherDuck compute can access, such as an account-key connection string. A local Azure CLI sign-in is not available on MotherDuck compute. See [Azure Blob Storage credentials](/integrations/cloud-storage/azure-blob-storage/). ### Google Cloud Storage Create a [GCS HMAC key](https://docs.cloud.google.com/storage/docs/authentication/hmackeys) for a service account with access to the bucket. Use its access ID and secret, rather than a service account JSON key. GCS uses its S3-compatible API for these credentials. ```sql CREATE SECRET ducklake_gcs IN MOTHERDUCK ( TYPE GCS, KEY_ID '', SECRET '', SCOPE 'gcs:////' ); CREATE DATABASE my_ducklake ( TYPE DUCKLAKE, DATA_PATH 'gcs:////' ); ``` See [Google Cloud Storage credentials](/integrations/cloud-storage/google-cloud-storage/). Creating the database alone does not verify that the credentials can read and write data files. After setup, follow [Using MotherDuck compute](#using-motherduck-compute) to create and query a table. Small inserts can remain in the metadata catalog through [data inlining](#data-inlining), so flush them when testing access to your storage. #### Required permissions for DuckLake ### Amazon S3 The minimum required IAM permissions are: ```json { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket" ], "Resource": "arn:aws:s3:::" }, { "Effect": "Allow", "Action": [ "s3:PutObject", "s3:GetObject", "s3:DeleteObject" ], "Resource": "arn:aws:s3::://*" } ] } ``` ### Cloudflare R2 Create an R2 API token with **Object Read & Write** permission for the selected bucket. See the [Cloudflare R2 API tokens documentation](https://developers.cloudflare.com/r2/api/tokens/). ### Azure Blob Storage The credentials must allow reading, creating, listing, and deleting blobs in the container. An account-key connection string grants access to the account's storage. For authentication through Microsoft Entra ID, the [Storage Blob Data Contributor role](https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/storage#storage-blob-data-contributor) includes the required data permissions. ### Google Cloud Storage Grant the HMAC key's service account permission to create, read, list, and delete objects in the bucket. The [Storage Object User role](https://docs.cloud.google.com/storage/docs/access-control/iam-roles) (`roles/storage.objectUser`) includes these permissions. ### Using MotherDuck compute Connect to MotherDuck: ```bash > duckdb md: ``` Create your first DuckLake table from a hosted Parquet file: ```sql 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: ```sql 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](#setup) step, you can skip ahead to attaching the DuckLake. For AWS SSO on your own compute, run `aws sso login --profile ` on the machine running DuckDB, then create a local secret there: ```sql CREATE SECRET ducklake_s3_local ( TYPE S3, PROVIDER credential_chain, CHAIN 'sso', PROFILE '', REGION '', SCOPE 's3:////' ); ``` 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](#setup). See [using a local IAM role or SSO session](/integrations/cloud-storage/amazon-s3/#use-your-local-iam-role-or-sso-session) for details. Load the DuckLake extension and attach the existing MotherDuck-managed metadata catalog: ```sql INSTALL ducklake; LOAD ducklake; ATTACH 'ducklake:md:__ducklake_metadata_' AS ; ``` This tells DuckLake to: - Use `ducklake:md:__ducklake_metadata_` 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](https://ducklake.select/docs/stable/duckdb/usage/paths) 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_'` (without the prefix) gives you the metadata catalog for inspection only. See [Performing metadata operations on a DuckLake](#performing-metadata-operations-on-a-ducklake). Create a table using your own compute: ```sql CREATE TABLE .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](https://ducklake.select/2026/04/13/ducklake-10/) and the [MotherDuck announcement](https://motherduck.com/blog/announcing-ducklake-1-0-on-motherduck/) 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](https://duckdb.org/docs/sql/statements/create_macro.html). ## 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](https://ducklake.select/docs/stable/duckdb/usage/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): ```sql ATTACH 'md:__ducklake_metadata_' AS ; ``` ::::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](#using-own-compute). :::: ## Time travel Query a historical snapshot of a DuckLake table with the `AT` clause, by snapshot version or timestamp: ```sql -- 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](https://ducklake.select/docs/stable/duckdb/usage/snapshots): ```sql 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](#using-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()`: ### Snapshot version ```sql ATTACH 'ducklake:md:__ducklake_metadata_' AS historical_ducklake (SNAPSHOT_VERSION 12, READ_ONLY); ``` ### Snapshot timestamp ```sql ATTACH 'ducklake:md:__ducklake_metadata_' AS historical_ducklake (SNAPSHOT_TIME '2026-01-15 10:30:00', 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](https://ducklake.select/docs/stable/duckdb/usage/time_travel). ## Limitations - **Limited sharing options**: Read-only sharing is supported through the [existing share functionality](/key-tasks/sharing-data/), restricted to auto-update shares only. [Table-level security](/key-tasks/sharing-data/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](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/). ::: --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Ffile-formats%2Fducklake%2F&page_title=DuckLake&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.