Motherduck Ducklake

Use DuckLake on MotherDuck

Use this skill when the storage decision is genuinely about open table format and object-store behavior, not just about where to put another analytical table.

Source Of Truth

• Prefer current MotherDuck DuckLake docs first.
• Use the upstream DuckLake and DuckDB extension docs only to clarify extension-level behavior that MotherDuck docs reference.
• Keep the guidance aligned with the documented product posture:
• native MotherDuck first
• upstream DuckLake v1.0 is production-ready, but MotherDuck's DuckLake docs define the MotherDuck product surface and preview/compatibility limits; verify the current DuckDB/DuckLake version matrix instead of assuming a pinned version
• fully managed, BYOB, and own-compute paths are distinct
• maintenance and compaction are explicit operations, not background magic

Default Posture

• Start with native MotherDuck storage unless there is a concrete DuckLake requirement.
• Reach for DuckLake when you need open-table-format semantics, object storage as the source of truth, BYOB, or file-aware maintenance.
• Do not recommend DuckLake just because a workload is "large"; MotherDuck's docs explicitly note native storage is often faster for reads.
• Choose the operating mode deliberately: fully managed for easiest evaluation, BYOB for customer bucket ownership, own compute only when the compute boundary matters too.
• Document the fallback to native MotherDuck storage if the DuckLake requirement is weak, unverified, or only about future portability.
• For DuckLake v1.0, data inlining, sorted tables, bucket partitioning, deletion vectors, or extension behavior, verify the current MotherDuck DuckLake docs and DuckDB/DuckLake version matrix before giving syntax guarantees.
• Do not infer MotherDuck client/runtime support from upstream DuckDB release notes alone; check the MotherDuck lifecycle docs when the exact DuckDB version matters.
• Keep the MotherDuck product surface separate from raw DuckLake-extension assumptions.

Workflow

1. Confirm why native MotherDuck storage is insufficient. 2. Pick the operating mode: fully managed, BYOB with MotherDuck compute, or BYOB with own compute. 3. Verify regional and bucket constraints before proposing BYOB. 4. Define the ingestion and maintenance posture up front, including data inlining, file compaction, and cleanup expectations. 5. Validate who will query the data and from which compute surface before finalizing the architecture.

Open Next

• Read references/DUCKLAKE_PLAYBOOK.md for the mode decision matrix, MotherDuck-specific SQL patterns, BYOB constraints, data-inlining behavior, maintenance functions, and common DuckLake mistakes

Related Skills

• motherduck-connect for choosing native DuckDB versus Postgres-endpoint access paths
• motherduck-load-data when the real issue is ingestion rather than storage format
• motherduck-model-data when the user still needs analytical table design after the storage decision
• motherduck-build-data-pipeline when DuckLake is just one part of a broader ingestion-to-serving workflow

DuckLake Playbook

Use this reference when the question is no longer "what is DuckLake?" but "should we use it here, and if so, how?"

Contents

Upstream DuckLake v1.0 is a production-ready lakehouse specification supported by current DuckDB releases (verify the exact version matrix in current docs). MotherDuck has announced managed DuckLake 1.0 support, but the MotherDuck DuckLake docs still define what is available on MotherDuck and currently label DuckLake as a preview product surface. Treat DuckLake as an opt-in open-table-format path, not as the default storage posture for every analytical workload.

MotherDuck-first position

Start with native MotherDuck storage unless there is a concrete requirement for:

• open-table-format posture
• object storage as the source of truth
• bring-your-own-bucket ownership
• own-compute writes against a MotherDuck-backed DuckLake catalog
• file-aware maintenance and explicit compaction behavior

Do not move a workload to DuckLake just because it is large. MotherDuck's public docs explicitly note that native MotherDuck storage is often 2x-10x faster for reads than DuckLake.

Choose the mode deliberately

Use own compute only when the compute boundary matters operationally. It adds credential handling, metadata attach steps, and a clearer maintenance burden.

Default decision rules

Choose native MotherDuck when:

• the workload is mostly BI, dashboards, ad hoc analytics, or serving tables
• the team wants the simplest operating model
• read performance matters more than open-format posture
• no one actually needs bucket ownership or file-level operations

Choose DuckLake when:

• object storage must remain the durable data boundary
• you need open-table-format semantics
• you want to register or keep operating on Parquet-backed lake data
• you are prepared to own explicit maintenance
• the architecture benefits from a MotherDuck catalog plus lake storage split

SQL patterns you should actually use

Fully managed DuckLake

CREATE DATABASE my_ducklake (TYPE DUCKLAKE);

Use this to evaluate DuckLake with the fewest decisions. Treat it as the default first step when the user wants to try DuckLake but does not yet require a customer-owned bucket.

Fully managed DuckLake with custom inlining

CREATE DATABASE my_ducklake (
    TYPE DUCKLAKE,
    DATA_INLINING_ROW_LIMIT 100
);

Use custom inlining only when the ingest pattern justifies it. Small, frequent writes are the main reason to tune this.

BYOB DuckLake

CREATE DATABASE my_ducklake (
    TYPE DUCKLAKE,
    DATA_PATH 's3://my-bucket/my-prefix/'
);

MotherDuck docs require the S3 bucket to be in the same AWS region as the MotherDuck org:

• US orgs: us-east-1
• EU orgs: eu-central-1

Other clouds are not supported today for BYOB DuckLake storage.

Additional options to verify against current docs before using:

• DATA_INLINING_ROW_LIMIT
• SNAPSHOT_RETENTION_DAYS
• encryption-related options

DuckLake databases should not be presented as transient databases unless current docs explicitly add that support.

Attach the metadata database for own-compute access

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

Important:

• the metadata database attach is an own-compute pattern, not the default MotherDuck operating surface
• only the database owner can attach the metadata database
• verify the DuckDB and DuckLake version matrix before direct metadata-catalog access; newer DuckLake spec versions can require newer DuckDB clients
• do not recommend this path unless the user actually needs their own DuckDB client to read and write the lake directly

Data inlining posture

MotherDuck docs say DuckLake data inlining is experimental and requires explicit enablement with DATA_INLINING_ROW_LIMIT when creating the DuckLake database. Upstream DuckLake v1.0 has broader default small-write inlining behavior; do not assume raw extension defaults apply unchanged on MotherDuck.

Use it when:

• inserts arrive in very small batches
• the workload is append-heavy and frequent
• the cost of creating many tiny Parquet files would dominate the write path

Do not lead with inlining as a universal optimization. It is a write-shape optimization, not the main reason to choose DuckLake.

If the user accumulates too much inlined data, flush it explicitly:

SELECT ducklake_flush_inlined_data('my_ducklake');
SELECT ducklake_flush_inlined_data('my_ducklake.my_schema');
SELECT ducklake_flush_inlined_data('my_ducklake.my_schema.my_table');

Maintenance is explicit

MotherDuck docs explicitly say DuckLake maintenance is not automatic.

That means you should define:

• who runs maintenance
• from which compute surface
• how often compaction or cleanup runs
• what freshness or file-count thresholds trigger it

If the design has no answer for maintenance, it is not ready for DuckLake.

Use CHECKPOINT as the current high-level maintenance wrapper when current docs recommend it for the DuckLake operation in question. Keep lower-level maintenance functions as targeted tools for cases where the docs call for them directly.

For file compaction, upstream DuckLake documents ducklake_merge_adjacent_files(...) and related auto-compaction options. DuckLake v1.0 also adds features such as sorted tables, bucket partitioning, data inlining, and deletion vectors. Treat those as explicit design and maintenance choices, not background behavior, and verify MotherDuck support plus schema-evolution/time-travel constraints before recommending a policy.

Sharing and write constraints

Current MotherDuck docs call out a few important limits:

• sharing is limited compared with native databases and is tied to existing share functionality
• only auto-update shares are supported for DuckLake read-only sharing
• write permissions are effectively single-account at the database level today
• concurrent append-only writes can work, but concurrent updates, deletes, or DDL are much more constrained

Do not promise a broad multi-writer lakehouse collaboration model unless the current docs explicitly confirm it.

Gotchas

• Upstream DuckLake v1.0 features such as sorted tables, bucket partitioning, deletion vectors, and default inlining may not map one-for-one to the current MotherDuck DuckLake surface. Verify MotherDuck docs before relying on new extension features.
• Keep the MotherDuck product surface separate from raw DuckLake extension assumptions. The extension can expose behaviors that MotherDuck does not expose the same way.
• BYOB region restrictions apply to DuckLake storage, not to ordinary remote reads from S3-compatible storage.
• Do not use DuckLake as a generic "big data" answer when native MotherDuck would be simpler and faster.
• Do not hide maintenance costs. File-aware storage shifts operational responsibility upward.
• If the user only needs ingestion from object storage, motherduck-load-data may be the real skill they need, not motherduck-ducklake.

Escalate to higher-level skills when needed

• Use motherduck-build-data-pipeline when DuckLake is only one layer of a broader ingestion-to-serving design.
• Use motherduck-security-governance when BYOB, ownership, or regional placement decisions are driving the storage choice.
• Use motherduck-pricing-roi when the real question is whether the operational overhead of DuckLake is worth it.