Explanation
Why the waste happens and who it affects.
Google's documentation is explicit: if no default table expiration is set on the dataset and no expiration is set on the table when it is created, the table never expires and must be deleted manually. Scratch tables from ad hoc analysis, intermediate tables written by ELT pipelines, staging copies from loads and large query-result destination tables therefore stay in storage, and keep billing, long after the job that produced them has finished.
In shared analytics projects many people and tools create tables and few delete them. Because each table is small relative to the total, the growth goes unnoticed. BigQuery's cost best practices recommend using the default table expiration time on destination tables for large query results so the data is removed automatically when no longer needed.
Billing model
The pricing dimensions that drive this cost.
Temporary tables are billed like any other BigQuery storage.
- Active storage
- Tables or partitions modified in the last 90 days are billed at the active storage rate per GiB, on the dataset's logical or physical billing model
- Long-term storage
- Tables or partitions not modified for 90 consecutive days drop to roughly half the price but are still billed
- Table expiration
- A table with an expiration time is deleted automatically, with its data, when the time is reached
- Time travel and fail-safe
- Deleted or expired data remains recoverable for the time travel window (2 to 7 days) plus fail-safe, and on physical billing those bytes are billed
How to detect
5 checks to find it in your estate.
- Query INFORMATION_SCHEMA.TABLE_STORAGE for large tables in datasets used for scratch, staging, sandbox or temporary data, using total_logical_bytes or total_physical_bytes with creation_time and storage_last_modified_time
- Query INFORMATION_SCHEMA.TABLE_OPTIONS for tables with no expiration_timestamp, and check dataset metadata for datasets with no default table expiration
- Find tables not read recently by joining TABLE_STORAGE with INFORMATION_SCHEMA.JOBS referenced_tables over the last 30 to 90 days
- Look for naming patterns such as tmp_, temp_, scratch_, staging_ or date-suffixed copies that are older than the pipelines that create them
- Identify scheduled queries and pipelines that write destination tables without setting an expiration
How to fix
5 ways to remove the waste.
- Set a default table expiration on scratch, staging and sandbox datasets (in days in the console, seconds in bq, milliseconds in the API); new tables inherit it unless they set their own
- Apply expiration to tables that already exist, because changing a dataset's default does not affect existing tables: set expiration_timestamp with ALTER TABLE SET OPTIONS or bq update, or delete tables that are no longer needed
- Have pipelines set an expiration on intermediate and destination tables when they create them, or drop them at the end of the run
- For partitioned tables that only need recent data, use a partition expiration so old partitions are removed automatically
- Before expiring data that must be retained for audit or compliance, export it to Cloud Storage in an appropriate storage class
Documentation
Vendor references for pricing and configuration.