How to Reset a Database in ClickHouse

Galaxy Glossary

How do I reset a ClickHouse database without losing schema?

Resetting a database in ClickHouse removes all objects (tables, views, data) and optionally recreates the database, returning it to a clean state.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Table of Contents

Why would you reset a ClickHouse database?

Teams reset databases when they need a clean slate for testing, staging refreshes, or after destructive experiments. The operation drops every object in the target database, freeing disk space and eliminating residual data.

What is the recommended reset approach?

ClickHouse has no single RESET keyword. The safe pattern is: DROP DATABASE IF EXISTS, then CREATE DATABASE with the same name. Dropping with the SYNC option guarantees completion before the command returns.

Step-by-step reset workflow

1) Revoke user access to stop new writes. 2) Back up schemas if needed. 3) RUN DROP DATABASE db_name SYNC. 4) Recreate the database. 5) Restore schemas or run new DDL. 6) Re-grant access.

Example: resetting `ecommerce_db`

The snippet below drops ecommerce_db, recreates it, and immediately rebuilds core tables (Customers, Orders, Products, OrderItems) so applications can reconnect without errors.

What permissions are required?

You need the DROP DATABASE and CREATE DATABASE privileges, or the higher-level ALL. Grant these to an admin-only role to prevent accidental resets.

Best practices to follow

Always run resets in a transaction-safe environment such as maintenance windows. Keep recent backups. Use SYNC to block until metadata is removed; otherwise, subsequent CREATE might race and fail.

Common pitfalls and how to avoid them

Dropping the wrong database, forgetting backups, and leaving users connected are typical errors. Double-check the database name, test on staging first, and disable connections during the operation.

Why How to Reset a Database in ClickHouse is important

How to Reset a Database in ClickHouse Example Usage


-- Full reset of ecommerce_db for a nightly QA refresh
DROP DATABASE IF EXISTS ecommerce_db SYNC;
CREATE DATABASE ecommerce_db;

-- Quickly restore minimal schema so automated tests pass
CREATE TABLE ecommerce_db.Customers (
    id UInt32,
    name String,
    email String,
    created_at DateTime
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE ecommerce_db.Orders (
    id UInt32,
    customer_id UInt32,
    order_date Date,
    total_amount Decimal(10,2)
) ENGINE = MergeTree() ORDER BY id;

How to Reset a Database in ClickHouse Syntax


-- Drop the entire database and wait until files are removed
DROP DATABASE IF EXISTS ecommerce_db SYNC;

-- Recreate the database
CREATE DATABASE ecommerce_db;

-- (Optional) Rebuild required tables
CREATE TABLE ecommerce_db.Customers (
    id UInt32,
    name String,
    email String,
    created_at DateTime
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE ecommerce_db.Products (
    id UInt32,
    name String,
    price Decimal(10,2),
    stock UInt32
) ENGINE = MergeTree() ORDER BY id;

-- Repeat for Orders and OrderItems

Common Mistakes

Running DROP DATABASE without SYNC. Without SYNC the command returns immediately and background removal may still run, causing a following CREATE DATABASE to fail with "Directory already exists". Always add SYNC or wait until metadata disappears.
Dropping production instead of staging. Similar names like ecommerce_db and ecommerce_db_staging cause confusion. Verify the current database with SELECT currentDatabase() and use fully-qualified names in scripts.

Frequently Asked Questions (FAQs)

Can I truncate all tables instead of dropping the database?

Yes. Use TRUNCATE TABLE database_name.* to empty every table while keeping metadata. This is safer if downstream apps rely on existing schemas.

Does DROP DATABASE delete data immediately?

Files are removed asynchronously, but the SYNC modifier forces the server to wait for completion before returning control.

How do I back up schemas before a reset?

Run SHOW CREATE TABLE database_name.table_name for each table and save the output, or copy the metadata files from /var/lib/clickhouse/metadata.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.