Professional Data EngineerManaging and securing dataMedium
A retail company uses BigQuery for analyzing customer purchasing behavior. They have a dataset containing sensitive customer PII that needs to be retained for 7 years for auditing purposes, but older, less frequently accessed data should be moved to a cheaper storage class after 3 years. Additionally, specific tables containing temporary analytical results should be automatically deleted after 30 days to save costs. Which BigQuery feature or combination of features should they use to manage this data lifecycle effectively?
- ABigQuery Authorized Views with time-based filters
- BBigQuery Table Expiration and Data Retention policies
- CManually run scheduled queries to move/delete data
- DCloud Storage Lifecycle Management policies on BigQuery external tables
Show answer & explanationAnswer & explanation
Correct answer: B. BigQuery Table Expiration and Data Retention policies
BigQuery Table Expiration automatically deletes tables or partitions after a specified duration, addressing the 30-day deletion. Data Retention policies can be set at the dataset or table level to define how long data is kept, fulfilling the 7-year retention and 3-year transition requirements for BigQuery tables.
Why the other options are wrong
- A. Authorized Views control access to data, they do not manage data retention or deletion.
- C. Manual processes are inefficient and prone to errors, not a scalable solution for automated lifecycle management.
- D. BigQuery tables are not directly managed by Cloud Storage Lifecycle Management; this applies to data in Cloud Storage buckets.
BigQuery Data Lifecycle Management
The process of managing BigQuery data from creation to archival or deletion, often using features like table expiration and data retention policies to optimize storage costs and meet compliance.
- Table expiration automatically deletes tables or partitions.
- Data retention policies define minimum data age before deletion or transition.
- Can be set at dataset or table level.
Memory trick: Expire the temporary, retain the old, manage with policies, and save on the gold.