What is the difference between DELETE and TRUNCATE?

DELETE is a Data Manipulation Language (DML) command that removes specific rows from a table, logging each deletion individually, while TRUNCATE is a Data Definition Language (DDL) command that deallocates all data pages associated with a table, effectively removing all rows quickly with minimal logging.

Mechanics and Implications

DELETE:
As a DML operation, DELETE processes row by row (or in batches), writing each affected row's removal to the transaction log. This allows for conditional deletion using a WHERE clause, enables triggers to fire, and makes the operation fully rollbackable. It does not reset identity (auto-increment) columns. Because it operates at the row level and logs extensively, DELETE can be slower on large tables and consume significant transaction log space. In systems like Delta Lake, a DELETE operation might rewrite entire data files containing the deleted records, creating new versions in the transaction log.

TRUNCATE:
As a DDL operation, TRUNCATE is fundamentally different. It deallocates the data pages or extents occupied by the table, rather than deleting individual rows. This is a metadata operation, making it significantly faster, especially for large tables. TRUNCATE generates minimal transaction log entries (typically just the page deallocation), does not support a WHERE clause, and implicitly commits the transaction, making it generally non-rollbackable. It also resets identity columns to their seed value. In distributed systems like Snowflake, TRUNCATE is a metadata operation that marks micro-partitions as logically deleted, similar to dropping and recreating the table.

Key Trade-offs and Use Cases

The primary trade-offs revolve around performance, logging, and transactional control. TRUNCATE is much faster and uses fewer resources for clearing an entire table, making it ideal for staging tables before a fresh data load or resetting development environments. DELETE is necessary when you need to remove specific records (e.g., for GDPR compliance), require audit trails of individual deletions, need triggers to fire, or must maintain the ability to roll back the operation.

-- Example: Conditional deletion
DELETE FROM sales_data WHERE order_date < '2023-01-01';
-- Example: Full table clear
TRUNCATE TABLE staging_area;

DELETE can cause extensive locking on large tables, potentially blocking other operations, whereas TRUNCATE is near-instant and typically involves only schema locks.

In the interview, also mention that the DDL nature of TRUNCATE means it often implicitly commits any pending transactions, which is a critical distinction from DELETE's DML behavior within a transaction.