Professional Data EngineerDesigning data processing systemsEasy
A data analytics team uses BigQuery for its data warehouse. They have several tables containing historical data that are accessed infrequently (e.g., once a quarter for compliance reports) but must be retained for 10 years. These tables consume a significant amount of storage, leading to high costs. The team wants to optimize storage costs while maintaining the ability to query this data when needed. Which BigQuery feature should they leverage?
- ABigQuery Long-Term Storage
- BBigQuery Reservations
- CBigQuery ML
- DBigQuery Data Transfer Service
Show answer & explanationAnswer & explanation
Correct answer: A. BigQuery Long-Term Storage
BigQuery automatically transitions tables or partitions that have not been modified for 90 consecutive days from active (streaming/standard) storage to long-term storage, which costs 50% less. This is ideal for infrequently accessed historical data that needs to be retained for long periods, directly addressing the cost optimization requirement.
Why the other options are wrong
- B. BigQuery Reservations are for managing query costs (flat-rate pricing), not for optimizing storage costs of infrequently accessed data.
- C. BigQuery ML is for creating and executing machine learning models directly within BigQuery, unrelated to storage cost optimization.
- D. BigQuery Data Transfer Service is for automating data movement from other sources into BigQuery, not for optimizing existing BigQuery storage costs.
BigQuery Long-Term Storage
A BigQuery feature that automatically reduces the storage cost of tables or partitions that have not been modified for 90 consecutive days by 50%, without any change in performance or availability.
- Automatic cost optimization for inactive data.
- 50% reduction in storage price after 90 days of no modification.
- Data remains fully available and queryable.
- Ideal for historical, infrequently accessed data retention.
Memory trick: For a 'Big Query' warehouse, if data sits 'long-term' on the shelf, it gets a 'discount' automatically.