Snowflake TRUNCATE TABLE: Clear Table Data
The TRUNCATE TABLE command in Snowflake is a command used to clear all data from a table while maintaining its structure, including any defined schemas, privileges, or constraints. This makes it useful for scenarios where you need to reset the table data without altering its setup, which is often needed in development or testing environments.
Syntax
_10TRUNCATE [ TABLE ] [ IF EXISTS ] <name>
where:
TABLE: This keyword is optional and is typically used for clarity.IF EXISTS: This clause prevents errors by only attempting to truncate the table if it already exists.- name: The name of the table to truncate. If the table’s name includes spaces or special characters, enclose it in double quotes (e.g., "My Table"). The name can be fully-qualified (including the database and schema names) or just the table name if the database and schema context is set for the session.
Usage Notes
- Data Removal: Truncating a table removes all rows but does not remove the table itself. The metadata defining the table’s structure, such as its columns and constraints, remains unchanged.
- Load Metadata Deletion: Unlike other data deletion commands (like
DELETE),TRUNCATE TABLEalso deletes the table’s load metadata. This allows the same files to be reloaded into the table without conflict. - Time Travel: While the data and load metadata are removed, the deleted data is still temporarily accessible through Snowflake’s Time Travel (opens in a new tab) capability for the duration of the data retention period set for your account or table. However, once this period expires, the data cannot be recovered.
- Performance:
TRUNCATE TABLEis generally faster than deleting rows using aDELETEstatement, especially for large tables, because it does not scan the table to apply row-level operations.
Examples
Truncating a Table
Create and Populate a Table
_10CREATE OR REPLACE TABLE synthetic_data (i NUMBER);_10INSERT INTO synthetic_data SELECT seq8() FROM table(generator(rowcount=>20));
Verify Data Insertion
Check that the data was inserted correctly:
_10SELECT COUNT(*) FROM synthetic_data;
This should return 20, indicating there are twenty rows in the table.
Truncate Table
Now, truncate the table:
_10TRUNCATE TABLE IF EXISTS synthetic_data;
This command clears all data in the temp table.
Verify the Table Is Empty
Finally, verify that the table is empty:
_10SELECT COUNT(*) FROM synthetic_data;
This should return 0, showing that all rows have been removed.
