How to Truncate a Partitioned Table in BigQuery

Learn how to truncate partitioned tables in BigQuery with this step-by-step guide. Find out why using the TRUNCATE TABLE statement is better than using DELETE operations.

How to Truncate a Partitioned Table in BigQuery

If you want to truncate a partitioned table in BigQuery, you must first update the table to remove the partition filter requirement. Then, you can run the BigQuery TRUNCATE TABLE command, which will delete all rows from the table but leave its metadata intact, including the table's schema, description, and labels. TRUNCATE TABLE is now supported in BigQuery, which is great news for those in an on-premises environment. Unlike a DELETE DML statement in BigQuery, which involves a scanning cost, TRUNCATE is included in BigQuery's growing list of free operations.

This makes it a great option for quickly deleting all rows from a table. The TRUNCATE TABLE statement is a DDL (data definition language) operation that deletes all rows in a table but leaves the table's metadata intact, including the table's schema, description, and labels. When you need to delete all rows from a table, it's best to use the TRUNCATE TABLE statement instead of a DELETE operation. This allows you to apply changes to the data in a table more frequently and keep your datastore up to date with changes in data sources.

For partitioned tables, if the table requires a partition filter, the TRUNCATE TABLE statement will fail.

Charlotte Wilson
Charlotte Wilson

Friendly travel advocate. Freelance zombie scholar. Extreme web practitioner. Evil coffee buff. Professional beer practitioner.

Leave Reply

Required fields are marked *