A retail company uses BigQuery for analyzing customer purchasing behavior. To comply with GDPR, they must ensure that customer data is deleted after 7 years, but aggregated, anonymized purchasing trends need to be retained indefinitely. How should you manage the data lifecycle in BigQuery to meet these requirements?
- ASet a default table expiration of 7 years on the raw customer data table and manually create new tables for aggregated data.
- BUse a scheduled query to regularly move raw customer data older than 7 years to Cloud Storage and apply a separate lifecycle policy there.
- CConfigure a table expiration of 7 years on the raw customer data table and use a scheduled query to write aggregated data to a permanent table before expiration.
- DImplement a Data Retention Policy on the dataset containing raw customer data for 7 years and create a separate dataset for aggregated data.
Show answer & explanationAnswer & explanation
Correct answer: C. Configure a table expiration of 7 years on the raw customer data table and use a scheduled query to write aggregated data to a permanent table before expiration.
Setting a table expiration on the raw data table ensures automatic deletion after 7 years, meeting the GDPR requirement. A scheduled query can then extract and write the necessary aggregated, anonymized data to a separate, permanent table before the raw data expires, ensuring indefinite retention of trends.
Why the other options are wrong
- A. This option correctly uses table expiration for raw data but implies manual creation of aggregated tables, which is not scalable or automated for ongoing trend analysis.
- B. Moving raw data to Cloud Storage before deleting it in BigQuery adds unnecessary complexity, storage costs, and potential for data duplication if not managed carefully. The goal is to retain *aggregated* data, not raw data in another location.
- D. BigQuery does not have a 'Data Retention Policy' feature at the dataset level in the way described for automatic deletion and separate retention. Table expiration is the correct mechanism for automatic deletion of tables.
BigQuery Table Expiration & Scheduled Queries
BigQuery table expiration automatically deletes tables after a specified duration. Scheduled queries allow you to run recurring SQL queries to transform or move data, enabling automated data lifecycle management.
- Table expiration is set at table creation or updated afterward.
- Scheduled queries can run at defined intervals (e.g., daily, weekly).
- Combine them for automated data retention and aggregation pipelines.
- Essential for compliance with data retention policies like GDPR.
Memory trick: Expire the old, aggregate the new, BigQuery's lifecycle makes compliance true!