How to Reset a Database in Snowflake

How do I reset or recreate a Snowflake database?

Resetting a database in Snowflake recreates it from scratch, removing all schemas, tables, and data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why would you reset a Snowflake database?

Teams reset databases to refresh development environments, clear test data, or rebuild schemas after major model changes. A clean slate ensures repeatable tests and prevents legacy objects from lingering.

Which Snowflake command actually performs the reset?

Snowflake has no literal RESET DATABASE statement. Instead, use CREATE OR REPLACE DATABASE for an atomic drop-and-recreate or use DROP DATABASE … CASCADE followed by CREATE DATABASE for more control.

How does CREATE OR REPLACE DATABASE work internally?

The command deletes the existing database (if present) and immediately recreates it with the same name. All previous schemas, tables such as Customers, Orders, and Products disappear. Time-Travel history is lost unless DATA_RETENTION_TIME_IN_DAYS is explicitly set.

Can I undo an accidental reset?

Yes—use UNDROP DATABASE <db_name> within your Time-Travel retention window. This restores the database and all dropped objects.

Step-by-step reset example

1️⃣ Back up critical objects with CREATE TABLE … CLONE or COPY INTO @stage. 2️⃣ Run CREATE OR REPLACE DATABASE ecommerce_dev. 3️⃣ Recreate schemas and tables. 4️⃣ Reload seed data for Customers, Orders, and OrderItems.

Best practices for safe resets

• Always confirm the target account & warehouse before executing.
• Snapshot vital tables to another database.
• Use ROLE-based access control to limit who can run CREATE OR REPLACE or DROP … CASCADE.

What happens to grants after a reset?

All object-level grants vanish. Re-run your grant automation scripts or leverage Snowsight "Access History" to regenerate permissions.

Why How to Reset a Database in Snowflake is important

How to Reset a Database in Snowflake Example Usage


-- Reset the dev database for the ecommerce platform
CREATE OR REPLACE DATABASE ecommerce_dev COMMENT = 'Fresh start for sprint 14';

-- Recreate core tables
USE DATABASE ecommerce_dev;
USE SCHEMA public;
CREATE TABLE Customers (
    id          NUMBER PRIMARY KEY,
    name        STRING,
    email       STRING,
    created_at  TIMESTAMP
);
CREATE TABLE Products (
    id     NUMBER PRIMARY KEY,
    name   STRING,
    price  NUMBER(10,2),
    stock  NUMBER
);
-- Continue with Orders and OrderItems ...

How to Reset a Database in Snowflake Syntax


-- Atomic drop and recreate
CREATE OR REPLACE DATABASE <db_name>
  [DATA_RETENTION_TIME_IN_DAYS = <integer>]
  [MAX_DATA_EXTENSION_TIME_IN_DAYS = <integer>]
  [COMMENT = '<string>'];

-- Separate drop + create with full control
DROP DATABASE [IF EXISTS] <db_name> [CASCADE];
CREATE DATABASE <db_name>
  [DATA_RETENTION_TIME_IN_DAYS = <integer>]
  [COMMENT = '<string>'];

-- Roll back an accidental reset (within retention period)
UNDROP DATABASE <db_name>;

Common Mistakes

Running CREATE OR REPLACE DATABASE in production. This wipes all data instantly. Fix: restrict the command to dev roles and double-check the active role & account before executing.
Forgetting to reapply grants after a reset. All table and schema privileges disappear. Fix: store grant scripts in version control and run them immediately after recreating the database.