CREATE DATABASE

The CREATE DATABASE statement creates a new database in MotherDuck.

It can be used for the following operations:

Create a MotherDuck database from a local DuckDB database.
Create a MotherDuck database from a remote DuckDB database file in cloud storage (for example, a file in Amazon S3).
Create a MotherDuck database from another MotherDuck database or share using zero-copy clone (without physically copying data).

Copy to local database

To copy a MotherDuck database to a local database, use the COPY FROM DATABASE statement.

Zero-copy clone

When the source is another MotherDuck database or a share, CREATE DATABASE ... FROM performs a zero-copy clone. The command completes almost instantly because no data is physically duplicated. When the source is a local file, a remote file, or CURRENT_DATABASE(), data is physically copied to MotherDuck.

warning

A share that carries an include pattern can't be a zero-copy clone source. A clone copies the underlying snapshot, which would carry the tables the pattern hides, so CREATE DATABASE ... FROM rejects a filtered share whether you name its attached alias or its share URL. To copy the tables the share does expose, use COPY FROM DATABASE instead.

Syntax

CREATE [ OR REPLACE ] DATABASE [ IF NOT EXISTS ] <database name>
[
    FROM <database name> |
    FROM <snapshot_name> | <snapshot_id> | <snapshot_time>
    FROM '<local/file/path.db>' |
    FROM '<remote/file/path.db>' |
    FROM 'md:_share/...' |
    FROM CURRENT_DATABASE() -- Important: this command does not work with attached shares
]

[(
    [ TYPE { DEFAULT | DUCKLAKE | ICEBERG }, ]
    <option> [ <value> ] [, ...]
)];

TYPE selects the database type and defaults to DEFAULT, a native storage database. Which other options you can pass depends on the type, see Database options.

You can also pass the name of an attached share or a share URL as the database name, for example CREATE DATABASE FROM my_share or CREATE DATABASE FROM 'md:_share/...'.

If the database name already exists, the statement returns an error unless you specify IF NOT EXISTS.

Similar to DuckDB table name conventions, database names that start with a number or contain special characters must be double-quoted when used. Example: CREATE DATABASE "123db"

Creating a database does not change the active database. Run USE DATABASE <database name> to switch.

Database options

Databases on MotherDuck are native storage databases, DuckLake databases, or Iceberg catalogs. Each type has its own set of options you can configure at creation time.

Note that SNAPSHOT_RETENTION_DAYS exists for both native storage and DuckLake databases, but means something different in each.

Native storage options

All native storage databases have a transient status and a historical retention period. These properties are inherited on new databases created with the CREATE DATABASE dest_db FROM source_db syntax.

Set the historical retention period at creation, or change it later with ALTER DATABASE. You can set the transient status when creating a database, but it can't be altered after. Transient databases have a different failsafe period than non-transient databases.

Name	Description
TYPE DEFAULT	Optional. Any database created without a `TYPE` is a native storage database.
TRANSIENT	Specify `TRANSIENT` at database creation to enable transient storage. Refer to the Storage lifecycle management overview for more details.
SNAPSHOT_RETENTION_DAYS	Provide an integer to specify the number of days to retain automatic and unnamed snapshots as `historical_bytes`. Named snapshots are retained until unnamed. Refer to the Storage lifecycle management overview for more details.

-- A native storage database:
CREATE DATABASE cloud_db;

-- A transient database:
CREATE DATABASE scratch_db (TRANSIENT);

-- A database that keeps seven days of snapshots:
CREATE DATABASE archive_db (SNAPSHOT_RETENTION_DAYS 7);

DuckLake options

Name	Description
TYPE DUCKLAKE	Specify `TYPE DUCKLAKE` at database creation to create a fully managed DuckLake. Refer to the DuckLake overview for more details.
DATA_PATH	Optional data path for DuckLake storage (for example, `DATA_PATH 's3://bucket/prefix'`). Buckets must be in the same AWS region as your MotherDuck org (`us-east-1` or `us-west-2` for US, `eu-central-1` or `eu-west-1` for EU).
ENCRYPTED	Enables encryption for DuckLake storage. To enable it, specify `ENCRYPTED` at database creation. Refer to Encryption for more details.
DATA_INLINING_ROW_LIMIT	Row-size threshold (bytes) for inline data storage. Provide an integer value.
SNAPSHOT_RETENTION_DAYS	Number of days to retain DuckLake snapshots before they are eligible for expiration. Defaults to `NULL` (infinite retention). DuckLake snapshots are expired by running maintenance operations manually; MotherDuck does not expire them automatically.

-- A fully managed DuckLake:
CREATE DATABASE cloud_ducklake (TYPE DUCKLAKE);

-- A DuckLake that keeps seven days of snapshots:
CREATE DATABASE my_ducklake (TYPE DUCKLAKE, SNAPSHOT_RETENTION_DAYS 7);

-- A DuckLake in your own bucket, with encryption:
CREATE DATABASE my_encrypted_ducklake
(
    TYPE DUCKLAKE,
    DATA_PATH 's3://my-bucket/ducklake',
    ENCRYPTED
);

Iceberg catalog options

Refer to Apache Iceberg for the full set of catalog options and examples.

Name	Description
TYPE ICEBERG	Specify `TYPE ICEBERG` to attach an Iceberg REST catalog as a persisted MotherDuck database.
SECRET	Required. Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. `secret` is a reserved word, so quote it as `"secret"`.
DEFAULT_SCHEMA	Required. Schema used to resolve unqualified table names. Must exist in the catalog.
ENDPOINT	URL of the Iceberg REST catalog. Required unless set in the secret or derived from `endpoint_type`.
WAREHOUSE	Catalog warehouse identifier. For S3 Tables, the bucket ARN.
ENDPOINT_TYPE	Selects a well-known catalog flavor, for example `'s3_tables'` or `'glue'`.
DEFAULT_REGION	Per-catalog region override. Defaults to your MotherDuck org region.
READ_ONLY	Attach the catalog as read-only.
ACCESS_DELEGATION_MODE	Controls vended-credential delegation, for example `'none'`.

-- Attach an Iceberg REST catalog as a persisted database:
CREATE DATABASE my_datalake
(
    TYPE ICEBERG,
    "secret" my_iceberg_secret,
    ENDPOINT 'https://my-catalog.example.com',
    WAREHOUSE 'my_warehouse',
    DEFAULT_SCHEMA 'default'
);

Source database options

These options are only available for native MotherDuck databases. They apply to the source database that is being cloned.

Snapshot selectors are only supported when cloning a native MotherDuck database. They are not supported for DuckLake databases. Cloning a DuckLake database with CREATE DATABASE ... FROM is supported, but the resulting database is a native storage database containing a copy of the data, not a DuckLake.

Name	Data Type	Value
SNAPSHOT_TIME	TIMESTAMP	Selects the newest snapshot created before or at this timestamp
SNAPSHOT_ID	UUID	ID of the snapshot to clone
SNAPSHOT_NAME	STRING	Name of the snapshot to clone

Example usage

To create an empty database:

CREATE DATABASE empty_ducks;

If the database name already exists, the statement fails unless you use OR REPLACE or IF NOT EXISTS.

CREATE DATABASE ducks;
-- Succeeds if 'ducks' does not exist

CREATE DATABASE ducks;
-- Error: Failed to create database: database with name 'ducks' already exists

CREATE OR REPLACE DATABASE ducks; -- Replaces existing 'ducks' with an empty database

CREATE DATABASE IF NOT EXISTS ducks; -- No-op if 'ducks' already exists

To copy an entire database from your local DuckDB instance into MotherDuck:

USE ducks_db;
CREATE DATABASE ducks FROM CURRENT_DATABASE();

-- Or alternatively, use the following command - if ducks_db exists, even if populated, it will be replaced with an empty one:
CREATE OR REPLACE DATABASE ducks FROM ducks_db;

-- In the following, if ducks_db exists, the operation will be skipped, but it will not error:
CREATE DATABASE IF NOT EXISTS ducks_db;

For examples of the per-type creation options, see Database options.

To zero-copy clone a database that is already attached in MotherDuck:

CREATE DATABASE cloud_db FROM another_cloud_db;

To zero-copy clone a past snapshot of a database in MotherDuck

CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_NAME 'prod_backup');
CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_ID '3f2504e0-4f89-11d3-9a0c-0305e82c3301');
CREATE DATABASE cloud_db FROM another_cloud_db (SNAPSHOT_TIME '2025-07-29 14:30:25.123456');

To upload a local DuckDB database file:

CREATE DATABASE flying_ducks FROM './databases/local_ducks.db';

To create a database from a remote DuckDB database file in cloud storage:

CREATE DATABASE flying_ducks FROM 's3://my-bucket/ducks.db';

Reading from private cloud storage uses a configured secret.

To upload an attached local DuckDB database:

ATTACH './databases/local_ducks.db';
CREATE DATABASE flying_ducks FROM local_ducks;

Syntax​

Database options​

Native storage options​

DuckLake options​

Iceberg catalog options​

Source database options​

Example usage​