How to TRUNCATE TABLE in Snowflake

Galaxy Glossary

How do I quickly delete all rows from a Snowflake table?

TRUNCATE TABLE instantly removes all rows from one or more Snowflake tables without logging individual row deletions.

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

Example H2

Example H3

What does TRUNCATE TABLE do in Snowflake?

TRUNCATE TABLE deletes every row in the specified table(s) in a single, fast metadata operation. Storage is reclaimed immediately and no undo log is generated, making it ideal for quickly resetting staging or temporary data.

When should I use TRUNCATE TABLE instead of DELETE?

Choose TRUNCATE when you need to wipe an entire table, want the fastest possible execution, and do not require filters, triggers, or individual row recovery. Use DELETE when you must retain some rows or fire DELETE triggers.

What is the exact Snowflake syntax?

See the full syntax below, including optional clauses, multiple tables, and transactional behavior.

How do I truncate multiple ecommerce tables at once?

You can list up to 10 tables in a single statement. Snowflake processes them in the order provided and treats the whole operation as one transaction.

Can I undo a TRUNCATE TABLE?

Yes. Because Snowflake uses zero-copy cloning under the hood, you can recover truncated data with TIME TRAVEL or UNDROP within your retention period.

Best practice: run in a transaction

Wrap TRUNCATE TABLE in a BEGIN / COMMIT block when combining it with INSERT or COPY statements. This guarantees an all-or-nothing load pipeline.

Best practice: limit privileges

Grant TRUNCATE (or the broader OWNERSHIP) privilege only to service roles or experienced engineers to avoid accidental data loss.

Common mistakes and fixes

Using TRUNCATE without IF EXISTS

Snowflake throws an error if the table is missing. Add IF EXISTS to make the command idempotent in CI/CD pipelines.

Misunderstanding CASCADE

CASCADE removes rows from child tables that reference the truncated table via FKs. Omit or use RESTRICT if you want the statement to fail instead.

Quick reference

Syntax, parameters, and examples follow for copy-paste use.

Why How to TRUNCATE TABLE in Snowflake is important

How to TRUNCATE TABLE in Snowflake Example Usage


-- Reset staging tables before nightly load
BEGIN;
TRUNCATE TABLE IF EXISTS Orders_stage, OrderItems_stage;
INSERT INTO Orders_stage SELECT * FROM Orders WHERE order_date >= CURRENT_DATE - 7;
COMMIT;

How to TRUNCATE TABLE in Snowflake Syntax


TRUNCATE [ TABLE ] [ IF EXISTS ] <table_name> [ , <table_name> ... ] [ { CASCADE | RESTRICT } ];

-- Example for ecommerce schema
TRUNCATE TABLE IF EXISTS Customers RESTRICT;
TRUNCATE Orders, OrderItems CASCADE;

Common Mistakes

Running TRUNCATE on a non-existent table without IF EXISTS causes a statement failure. Add IF EXISTS for idempotent scripts.
Assuming TRUNCATE bypasses foreign-key checks by default. Snowflake enforces FKs unless CASCADE is specified; otherwise the statement fails.