How to TRUNCATE TABLE in BigQuery

What does TRUNCATE TABLE do in BigQuery?

TRUNCATE TABLE deletes every row in a table without scanning it, so the operation finishes almost instantly and does not consume slots. The table’s structure, indexes, and permissions remain unchanged.

When should I use TRUNCATE TABLE?

Use it when you need to reset staging, scratch, or aggregate tables between pipeline runs.It is safer and faster than dropping and recreating a table because dependent views and permissions stay intact.

How do I write the TRUNCATE TABLE statement?

Add the optional IF EXISTS clause to avoid errors if the table is already empty or missing. Specify the fully-qualified name: project.dataset.table. Wildcards, subqueries, and partitions are not supported.

Can I truncate multiple tables at once?

No. BigQuery only allows one table per TRUNCATE statement.Wrap multiple statements in a script or orchestration tool if you need batch truncation.

Does TRUNCATE TABLE affect quotas or billing?

Because BigQuery rewrites metadata instead of scanning data, you are not charged for bytes processed.The command counts only toward the daily 1,000‐statement quota for Data Definition Language.

Best practices for TRUNCATE TABLE

Grant bigquery.tables.deleteData rather than broader roles; wrap the command in a transaction with downstream inserts to keep data windows consistent; always qualify the project and dataset to avoid surprises in shared environments.

Common mistakes and how to avoid them

Missing project or dataset: Omitting them can target a table in an unexpected default dataset.Always use fully-qualified names.

Using TRUNCATE on a production table: Add IAM checks or environment variables that block truncation on critical datasets.

Examples in an ecommerce schema

You want to clear yesterday’s aggregate revenue table before recalculating it.

-- Clear daily aggregates
TRUNCATE TABLE IF EXISTS myshop.analytics.daily_revenue;

After truncation, insert the fresh aggregates:

INSERT INTO myshop.analytics.daily_revenue
SELECT order_date, SUM(total_amount) AS daily_total
FROM myshop.sales.Orders
GROUP BY order_date;

FAQ

Is TRUNCATE TABLE reversible?

No.Once executed, the data is gone unless you have table snapshots or backups.

Does TRUNCATE TABLE reset auto-generated column IDs?

BigQuery has no auto-increment feature, so IDs remain unaffected.

.