ASSOCIATE-DATA-PRACTITIONER practice question 21 of 25

Your organization uses a BigQuery table that is partitioned by ingestion time. You need to remove data that is older than one year to reduce your organization’s storage costs. You want to use the most efficient approach while minimizing cost. What should you do?

Full set: 106 questions
Answer options
A: Create a scheduled query that periodically runs an update statement in SQL that sets the “deleted" column to “yes” for data that is more than one year old. Create a view that filters out rows that have been marked deleted.
B: Create a view that filters out rows that are older than one year.
C: Require users to specify a partition filter using the alter table statement in SQL.
D: Set the table partition expiration period to one year using the ALTER TABLE statement in SQL. Correct answer
Correct Answer:
D. Set the table partition expiration period to one year using the ALTER TABLE statement in SQL.
Explanation

The most efficient and cost-effective method to automatically remove old data from a partitioned table is to set the partition expiration. The ALTER TABLE statement with the partitionexpirationdays option configures BigQuery to automatically delete partitions older than the specified period (365 days for one year). This approach requires no ongoing manual intervention or query processing costs. It directly removes the data and its associated storage costs, fulfilling all requirements of the question.

Why Incorrect

A. This method does not remove data; it only marks it. Storage costs remain, and running periodic UPDATE queries incurs additional processing costs.
B. A view only filters data at query time and does not remove the underlying data from storage, so it fails to reduce storage costs.
C. Requiring a partition filter is a best practice for querying to reduce costs, but it does not remove any data from the table itself.

References

1. Google Cloud Documentation, "Introduction to partitioned tables", section "Partition expiration". States, "You can control how long BigQuery keeps data in a partition by configuring a partition expiration for a partitioned table... When a partition expires, BigQuery deletes the data in that partition."

2. Google Cloud Documentation, "Modifying table schemas", section "Set partition expiration". Provides the exact ALTER TABLE ... SET OPTIONS(partitionexpirationdays=...) syntax for implementing this solution.

3. Google Cloud Documentation, "Control BigQuery costs", section "Avoid running queries that update data". Recommends using features like partition expiration instead of DML statements to manage data lifecycle to avoid processing costs.

Scroll to Top