A data team is optimizing the cost of their BigQuery data warehouse. They have several tables that store historical logs, where data older than 90 days is very rarely accessed, but must be retained for compliance purposes. Queries on this older data are acceptable to run with slightly higher latency. The team wants to reduce storage costs for this older, infrequently accessed data without moving it out of BigQuery. Which BigQuery feature should they use?
- AClustering
- BPartitioning
- CTable Snapshots
- DTable Expiration
Show answer & explanationAnswer & explanation
Correct answer: B. Partitioning
BigQuery partitioning allows you to divide a table into segments, called partitions, based on a date/timestamp column or an integer range. For time-series data, partitioning by date is common. Critically, BigQuery charges for storage based on the logical size of the data. For partitioned tables, you can set a default partition expiration, or manage partitions to move older, less-accessed partitions to 'long-term storage' pricing after 90 days, which is cheaper. This directly addresses the requirement to reduce storage costs for older data while keeping it in BigQuery.
Why the other options are wrong
- A. Clustering improves query performance by co-locating data with similar values, but it doesn't directly reduce storage costs for older data by moving it to a cheaper storage tier.
- C. Table snapshots create a point-in-time copy of a table, which could increase storage costs if not managed carefully, and doesn't automatically tier older data to cheaper storage.
- D. Table expiration automatically deletes tables or partitions after a specified duration, which is for data deletion, not for retaining data at a lower cost.
BigQuery Partitioning for Cost
Using BigQuery table partitioning, especially by date, to leverage automatic cost reductions for older, less-accessed data that qualifies for long-term storage pricing.
- Divides table into smaller segments
- Data in partitions older than 90 days gets cheaper pricing
- Improves query performance by pruning partitions
Memory trick: Partition your BigQuery tables to pay less for old logs.