How to TRUNCATE TABLE in Redshift

What does TRUNCATE TABLE do in Redshift?

TRUNCATE TABLE performs a fast, metadata-only delete that drops every row in the specified table(s). Because Redshift rewrites block maps instead of logging row deletions, the command finishes almost instantly and immediately frees disk space.

When should I use TRUNCATE TABLE instead of DELETE?

Use TRUNCATE TABLE when you need a full refresh—such as staging loads, backfills, or test resets. DELETE is better when you must keep some rows, rely on predicates, or need to trigger constraints that TRUNCATE bypasses.

What is the syntax for TRUNCATE TABLE?

The basic form accepts one or more table names separated by commas. Optional CASCADE automatically truncates dependent tables.

TRUNCATE TABLE [ONLY] table_name [, ...]
[ CASCADE | RESTRICT ];


How do I truncate multiple tables at once?

List each table, or use CASCADE to include child tables. This avoids looping through individual statements.

TRUNCATE TABLE staging_orders, staging_orderitems;


Example: refreshing the "Orders" table

-- Remove all historical orders before reloading
TRUNCATE TABLE orders RESTRICT;
-- Now load the latest snapshot
COPY orders
FROM 's3://bucket/orders_snapshot/'
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftRole'
FORMAT AS PARQUET;


Best practices for safe truncation

Run inside a transaction when possible; ROLLBACK will fully restore rows. Verify no other sessions are reading the table to avoid unexpected empty result sets. Grant TRUNCATE privileges sparingly.

Common mistakes to avoid

Forgetting dependent tables

TRUNCATE fails with RESTRICT (default) if foreign keys point to the target. Add CASCADE or truncate the children first.

Using TRUNCATE on production fact tables

Unlike DELETE, TRUNCATE is irreversible outside a transaction—be sure you have a reload plan or backup.

Need to undo a TRUNCATE?

If executed inside BEGIN/COMMIT, use ROLLBACK to restore. After commit, recovery requires unloading from backups or snapshots.