Professional Data EngineerDesigning data processing systemsEasy
A retail company uses BigQuery for its data warehouse and needs to optimize costs. They have identified several tables containing historical data that is accessed infrequently (less than once a month) but must be retained for compliance reasons. These tables do not require the same query performance as frequently accessed data. Which BigQuery feature should be utilized to store this infrequently accessed data more cost-effectively?
- ATransition the tables to BigQuery's long-term storage.
- BImplement materialized views on the infrequently accessed tables.
- CUse BigQuery ML to predict access patterns and move data.
- DExport the data to Cloud Storage Nearline and query it from there.
Show answer & explanationAnswer & explanation
Correct answer: A. Transition the tables to BigQuery's long-term storage.
BigQuery offers a tiered storage pricing model. Data that has not been edited for 90 consecutive days automatically transitions to 'long-term storage,' which is priced at a lower rate than active storage. This is ideal for infrequently accessed historical data that still needs to be queryable within BigQuery.
Why the other options are wrong
- B. Materialized views optimize query performance for frequently accessed data, not for reducing storage costs of infrequently accessed data.
- C. BigQuery ML is for machine learning, not for managing data storage tiers for cost optimization.
- D. Exporting to Cloud Storage Nearline and then querying from there would involve re-importing data or using external tables, which adds complexity and latency, and might not be as cost-effective as BigQuery's native long-term storage for data that still needs to be queried within BigQuery.
BigQuery Long-Term Storage
BigQuery's long-term storage is a cost-optimization feature where data that has not been edited for 90 consecutive days automatically receives a 50% discount on storage costs, while remaining immediately available for queries.
- Automatic transition for data not edited for 90 days.
- Provides a 50% discount on storage costs.
- Data remains immediately queryable.
- Ideal for historical, infrequently accessed data.
Memory trick: BigQuery: Save on Storage, Save on Scans.