TRUNCATE TABLE

Removes all rows from a Delta table while preserving the table schema, properties, and transaction history.

Category: dml

Syntax

TRUNCATE TABLE <table>

Description

## Overview Removes all rows from a Delta table in a single operation while preserving the table schema, table properties, constraints, and the full transaction history. The truncated data remains accessible through time travel until removed by VACUUM. ## Behavior TRUNCATE is implemented as a DELETE with no predicate. The engine removes all existing data files by recording `remove` actions in the transaction log without rewriting any files. No new data files are added. This produces a new table version where the table contains zero rows. Because no files are rewritten (only removed), TRUNCATE is generally faster than a conditional DELETE that matches all rows, since it avoids reading and rewriting data. ### Schema Preservation The table schema, partition columns, table properties, constraints, and all other metadata remain intact after truncation. Only the data files are removed. Subsequent INSERT operations write data into the same table structure. ### Transaction Log TRUNCATE creates a new version in the Delta transaction log with `remove` actions for every data file. The previous versions (with data) remain available for time travel queries until VACUUM removes the underlying Parquet files. ### In-Memory Tables For in-memory tables (not backed by Delta storage), TRUNCATE replaces the table data with an empty dataset while preserving the schema. ## Access Control | Privilege | Object | Notes | |-----------|--------|-------| | write | Table | Required. Permits INSERT, UPDATE, and DELETE on the table. | ## Compatibility TRUNCATE TABLE follows standard SQL semantics. The optional TABLE keyword is supported for compatibility with engines that require or omit it. Unlike some SQL engines, DeltaForge TRUNCATE is transactional and creates a new version rather than resetting the table to version zero.

Parameters

Name	Type	Description
`table`		Specifies the name or path of the Delta table to truncate. The table must already be registered via CREATE DELTA TABLE or OPEN DELTA TABLE. Supports fully qualified names (zone.schema.table). The TABLE keyword before the name is optional.

Examples

-- Truncate a staging table after loading data into production
TRUNCATE TABLE warehouse.staging.daily_load;

-- Truncate without the optional TABLE keyword
TRUNCATE warehouse.staging.temp_data;

-- Truncate and verify the table is empty
TRUNCATE TABLE warehouse.staging.raw_events;

SELECT COUNT(*) AS row_count
FROM warehouse.staging.raw_events;

-- Truncate before re-loading (full refresh pattern)
TRUNCATE TABLE gold.reporting.monthly_summary;

INSERT INTO gold.reporting.monthly_summary
SELECT region, month, SUM(revenue) AS total_revenue
FROM warehouse.sales.orders
GROUP BY region, month;

-- Previous data is still accessible via time travel after truncation
TRUNCATE TABLE warehouse.sales.archive;

-- Query the version before truncation
SELECT COUNT(*) AS pre_truncate_rows
FROM warehouse.sales.archive VERSION AS OF 5;

Pitfalls

TRUNCATE removes all rows unconditionally. There is no confirmation prompt. Use DELETE with a WHERE clause if you need to remove only a subset of rows.
Truncated data files remain on storage until VACUUM runs with a retention period shorter than the age of the truncated version. Until then, time travel queries can still access the pre-truncation data, and storage costs are not reduced.
TRUNCATE TABLE cannot be rolled back after commit. However, RESTORE TABLE TO VERSION can recover the pre-truncation state by reverting to a previous version, provided the data files have not been removed by VACUUM.