How to Truncate Table in Snowflake: Syntax and Examples
To truncate a table in Snowflake, use the
TRUNCATE TABLE <table_name> command. This removes all rows quickly without logging individual row deletions, leaving the table structure intact.Syntax
The basic syntax to truncate a table in Snowflake is:
TRUNCATE TABLE <table_name>: Removes all rows from the specified table.
This command does not delete the table itself, only its data.
sql
TRUNCATE TABLE my_table;
Example
This example shows how to remove all data from a table named employees using the truncate command.
sql
CREATE OR REPLACE TABLE employees (id INT, name STRING); INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob'); SELECT * FROM employees; TRUNCATE TABLE employees; SELECT * FROM employees;
Output
ID | NAME
---|------
1 | Alice
2 | Bob
-- After TRUNCATE TABLE employees;
-- No rows returned
Common Pitfalls
Common mistakes when truncating tables in Snowflake include:
- Trying to truncate a table that does not exist, which causes an error.
- Expecting
TRUNCATE TABLEto reset identity columns; it does not reset sequences. - Using
DELETE FROMinstead ofTRUNCATE TABLEfor faster row removal.
Always ensure you have the right table name and understand that truncate is irreversible and removes all data immediately.
sql
/* Wrong: Table does not exist */ TRUNCATE TABLE unknown_table; /* Right: Check table exists before truncating */ -- Use SHOW TABLES or INFORMATION_SCHEMA to verify /* Wrong: Expecting identity reset */ TRUNCATE TABLE employees; -- Identity columns keep their values /* Right: To reset identity, drop and recreate the table or reset sequence explicitly */
Quick Reference
| Command | Description |
|---|---|
| TRUNCATE TABLE | Removes all rows from the table quickly. |
| DELETE FROM | Deletes rows but slower and logs each deletion. |
| DROP TABLE | Deletes the table and its data completely. |
| CREATE OR REPLACE TABLE | Creates or replaces a table, resetting data and structure. |
Key Takeaways
Use TRUNCATE TABLE to quickly remove all rows without deleting the table.
TRUNCATE TABLE does not reset identity or sequence values.
Ensure the table exists before truncating to avoid errors.
TRUNCATE TABLE is faster than DELETE for removing all data.
Truncation is irreversible; back up data if needed before running.
