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. Business plan only. 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
Changing SNAPSHOT_RETENTION_DAYS on a standard or transient database is only available on the Business plan, where standard databases accept 0-90 days and transient databases accept 0-1 days.
On Lite plans, retention is fixed at the plan default when the database is created: 1 day on Lite (paid), 0 days on Lite (free). Running ALTER DATABASE ... SET SNAPSHOT_RETENTION_DAYS on a Lite plan fails with:
Plan: lite does not support updating snapshot retention
See Storage lifecycle for the defaults and limits per plan and database type.
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';