BigQuery Best Practices: Optimizing Storage

This page provides best practices for optimizing BigQuery storage.

Use the expiration settings to remove unneeded tables and partitions

Best practice: Configure the default table expiration for your datasets. Configure the partition expiration for date-partitioned tables.

You can control storage costs and optimize storage usage by setting the default table expiration for newly created tables in a dataset. If you set the property when the dataset is created, any table created in the dataset is deleted after the expiration period. If you set the property after the dataset is created, only new tables are deleted after the expiration period.

For example, if you set the default table expiration to 7 days, older data is automatically deleted after 1 week.

This option is useful if you need access to only the most recent data. It is also useful if you are experimenting with data and do not need to preserve it.

If your tables are partitioned by date, the dataset's default table expiration applies to the individual partitions. You can also control partition expiration using the time_partitioning_expiration flag in the CLI or the expirationMs configuration setting in the API. For example, the following command would expire partitions after 3 days:

bq mk --time_partitioning_type=DAY --time_partitioning_expiration=259200 [DATASET].[TABLE]

Take advantage of long-term storage

Best practice: Keep your data in BigQuery.

You can load data into BigQuery at no cost. Rather than exporting your older data to another storage option (such as Google Cloud Storage), take advantage of BigQuery's long-term storage pricing.

If you have a table that is not edited for 90 consecutive days, the price of storage for that table automatically drops by 50 percent to $0.01 per GB, per month. This is the same cost as Cloud Storage Nearline.

Each partition of a partitioned table is considered separately for long-term storage pricing. If a partition hasn't been modified in the last 90 days, the data in that partition is considered long term storage and is charged at the discounted price.

Use the pricing calculator to estimate storage costs

Best practice: Estimate your storage costs using the Google Cloud Platform Pricing Calculator.

To estimate storage costs in the Google Cloud Platform Pricing Calculator, enter the number of bytes that are stored as MB, GB, TB, or PB. BigQuery provides 10GB of storage free per month.

Pricing calculator

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...