Derik Hammer notes that you cannot rollback a TRUNCATE TABLE
operation in Redshift:
In SQL Server, or PostgreSQL for that matter, the TRUNCATE command is allowed in a transaction and it will commit or rollback like any other DML operation. In all of the scripts, below, I will do the following.
Check my row counts
Begin a transaction
TRUNCATE the table
INSERT one row
Check my row counts from within the transaction
ROLLBACK
Check to see if my row checks revert or not
That’s an important behavioral difference when loading data using a truncate-and-reload pattern.