ALTER DATABASE

The ALTER DATABASE statement updates storage-related settings for an existing MotherDuck database.

Syntax

ALTER DATABASE <database_name> SET <option> = <value>[, <option> = <value> ...];

Values are string literals, integers, or NULL.

Options

Native storage databases

These options apply to standard and transient databases backed by MotherDuck native storage.

Name	Data type	Description
SNAPSHOT_RETENTION_DAYS	INTEGER	Number of days to retain automatic and unnamed snapshots. Must be a non-negative integer within your plan limits. 0 disables historical snapshots. Named snapshots are retained until unnamed.

DuckLake databases

These options apply to DuckLake databases (fully managed and BYOB).

Name	Data type	Description
SNAPSHOT_RETENTION_DAYS	INTEGER or NULL	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.

Iceberg databases

These options apply to attached Iceberg catalogs. The change takes effect immediately: MotherDuck reattaches the catalog with the updated configuration, so the next query uses it.

Name	Value	Description
secret	String	Name of the MotherDuck Iceberg or S3 secret holding catalog credentials. Can't be cleared once set.
default_schema	String	Schema used to resolve unqualified table names. Must exist in the catalog. Can't be cleared once set.
default_region	String	Per-catalog region override.
access_delegation_mode	'vended_credentials' or 'none'	Whether to request vended credentials from the catalog.
stage_create_tables	'true' or 'false'	Create tables through the catalog's stage-create flow. Turn it off for catalogs that don't support staged creates.
skip_create_table_metadata_updates	'true' or 'false'	Skip the follow-up metadata update after a non-staged CREATE TABLE, for catalogs that reject it.
disable_multi_table_commit	'true' or 'false'	Commit tables one at a time instead of using the catalog's multi-table commit endpoint.
remove_files_on_delete	'true' or 'false'	Delete the underlying data files when a table is dropped. Turn it off for catalogs that handle their own cleanup, such as S3 Tables.
purge_requested	'true' or 'false'	Ask the catalog to purge table data on DROP TABLE.
support_nested_namespaces	'true' or 'false'	Address nested catalog namespaces as multi-level schema names.
encode_entire_prefix	'true' or 'false'	Send the catalog prefix as a single URL-encoded path component.

Options that identify the catalog - endpoint, warehouse, endpoint_type, and read_only - can't be altered, because changing them points the database at a different catalog: that's a different database, not a reconfigured one. To change one of them, drop the database and create it again.

Notes

ALTER DATABASE requires write access. It fails with a permission error on connections made with read-only credentials, such as a read scaling token.

Use ALTER DATABASE SET SNAPSHOT to restore a native storage database to a snapshot.

Refer to the snapshots guide for snapshot behavior and to Storage lifecycle for plan limits on retention.

Plan limits for native storage

For standard and transient databases, SNAPSHOT_RETENTION_DAYS is limited by plan:

• Business: 0-90 days
• Lite (paid): 1 day (min/max)
• Lite (free): 0 days (min/max)

Examples

Native storage

ALTER DATABASE my_db SET SNAPSHOT_RETENTION_DAYS = 7;

DuckLake

Set a snapshot retention period for a DuckLake database:

ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = 7;

Revert to infinite snapshot retention:

ALTER DATABASE my_ducklake SET SNAPSHOT_RETENTION_DAYS = NULL;

Iceberg

Point the catalog at a different default schema:

ALTER DATABASE my_datalake SET default_schema = 'analytics';

Rotate catalog credentials by swapping in another secret:

ALTER DATABASE my_datalake SET secret = 'my_new_iceberg_secret';

Use the secret's own credentials instead of vended ones, and stop deleting data files when a table is dropped:

ALTER DATABASE my_datalake SET
    access_delegation_mode = 'none',
    remove_files_on_delete = 'false';