Professional Data EngineerDesigning data processing systemsMedium
A financial institution processes millions of transactions daily. Due to regulatory requirements, all transaction data must be retained for 7 years for auditing purposes, but only the most recent 90 days are frequently accessed for operational reporting. The remaining historical data is rarely accessed, perhaps once or twice a year for compliance audits. The institution wants to minimize storage costs while ensuring data availability and integrity over the entire retention period. Which BigQuery feature should they leverage for cost optimization?
- ABigQuery flex slots
- BBigQuery Long-Term Storage
- CBigQuery materialized views
- DBigQuery BI Engine
Show answer & explanationAnswer & explanation
Correct answer: B. BigQuery Long-Term Storage
BigQuery's Long-Term Storage automatically moves tables or partitions that haven't been edited for 90 consecutive days to a lower-cost storage class, reducing storage costs by 50% for that data. This aligns perfectly with the requirement to retain data for 7 years but frequently access only the most recent 90 days, optimizing costs without sacrificing availability or integrity.
Why the other options are wrong
- A. BigQuery flex slots are a pricing model for compute capacity, not a feature for optimizing storage costs for historical data.
- C. BigQuery materialized views optimize query performance by pre-computing results, not primarily for reducing long-term storage costs for rarely accessed data.
- D. BigQuery BI Engine is for accelerating BI dashboards, not for storage cost optimization of historical data.
BigQuery Long-Term Storage
BigQuery's Long-Term Storage is an automatic storage cost optimization feature where data that has not been modified for 90 consecutive days is automatically moved to a lower-cost storage tier, reducing costs by 50%.
- Automatic transition after 90 days of no modification.
- 50% cost reduction for long-term storage.
- Data remains immediately accessible with no performance impact.
- Applies per table or partition.
Memory trick: BigQuery's 'Long-Term Plan' saves your data money.