A large enterprise uses BigQuery as its primary data warehouse. They have a dataset containing highly sensitive financial transaction data that needs to be retained for 7 years due to regulatory compliance. However, after 2 years, the data is rarely accessed, and after 5 years, it's almost never accessed. The data engineering team needs a cost-effective solution to manage the lifecycle of this data, minimizing storage costs while still meeting the 7-year retention requirement. Which BigQuery feature combination should they implement?
- ASet BigQuery table expiration to 7 years.
- BUse BigQuery table expiration to 2 years and export older data to Coldline Storage.
- CImplement a scheduled query to move data older than 2 years to a separate, colder BigQuery table and another scheduled query to move data older than 5 years to Cloud Storage Archive.
- DUse BigQuery table expiration to 5 years and export older data to Archive Storage.
Show answer & explanationAnswer & explanation
Correct answer: C. Implement a scheduled query to move data older than 2 years to a separate, colder BigQuery table and another scheduled query to move data older than 5 years to Cloud Storage Archive.
This option provides the most granular control and cost optimization. Moving rarely accessed data (after 2 years) to a colder BigQuery table (which still allows BigQuery queries but might be partitioned differently or in a cheaper region) and then moving almost never-accessed data (after 5 years) to the extremely cost-effective Cloud Storage Archive is the best approach for minimizing costs while meeting the 7-year retention.
Why the other options are wrong
- A. Setting expiration to 7 years keeps all data in BigQuery for the full duration, which is expensive for rarely accessed data.
- B. Exporting to Coldline after 2 years might be too early for data still needed occasionally, and Coldline retrieval costs can be higher if data is accessed even once a year. This doesn't explicitly guarantee 7 years retention and might not be the most cost-effective for the 'almost never accessed' phase.
- D. Exporting to Archive after 5 years is better, but it misses the opportunity to optimize for the 'rarely accessed' phase between 2 and 5 years within BigQuery itself.
BigQuery Data Lifecycle Management
BigQuery data lifecycle management involves strategies like table expiration, partitioning, and data movement to optimize storage costs and performance based on data access patterns and retention policies.
- Table expiration automatically deletes tables/partitions.
- Scheduled queries can move data between tables or export to Cloud Storage.
- Combines with Cloud Storage lifecycle for multi-tier archiving.
Memory trick: Retain Data, Reduce Dollars, Right Rules.