Google ACE Practice Question: A BigQuery table in a data pipeline receives…

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Set a dataset-level default table expiration of 180 days in the dataset properties

  Why it's wrong here

  Setting a dataset-level default table expiration of 180 days only affects tables created after the property is configured—it does not retroactively apply to existing tables. Furthermore, this option sets an expiration for entire tables, meaning a date-partitioned table would be dropped altogether 180 days after its creation timestamp, not have stale partitions removed while newer data remains. The question specifically asks for table-level configuration that preserves recent data while removing older data, so this dataset-level default is insufficient and misaligned.

• ✗

  Use a Cloud Scheduler job to run a DELETE statement on rows older than 180 days nightly

  Why it's wrong here

  Running a nightly Cloud Scheduler job that executes a DELETE statement on rows older than 180 days would technically remove the rows, but it is an inefficient and costly approach: DELETE queries consume slot resources and incur query execution costs, and the deleted rows still occupy storage until the underlying storage is rewritten or compacted. Unlike native BigQuery retention mechanisms, this requires ongoing operational overhead—creating, scheduling, monitoring, and troubleshooting a job—and does not guarantee timely cleanup if the job fails. For time-series data, managed partition expiration is the recommended zero-maintenance, cost-effective alternative.

• ✓

  Configure partition expiration on a date-partitioned table to expire partitions after 180 days

  Why this is correct

  Configuring partition expiration directly on a date-partitioned table is correct because BigQuery automatically deletes entire partitions whose partition boundary is older than the configured number of days, without any user-initiated queries or jobs. For example, setting partition expiration to 180 days makes BigQuery drop any daily partition whose date is more than 180 days in the past, effectively retaining the most recent 180 days of rolling data. This is the most efficient, low-cost, and fully managed approach, as it uses no query slots and requires no scheduler, making it ideal for time-series and log data.

• ✗

  Set a table-level TTL using BigQuery's TTL API with a 180-day value

  Why it's wrong here

  BigQuery does not expose a product feature called a 'TTL API'; instead, table expiration is configured through the table's expiration_time metadata property, which removes the entire table after a fixed duration from its last modification time—not a rolling row-level or partition-level TTL. Using such a 180-day table expiration would delete the whole table after 180 days of inactivity, which is not the desired behavior for retaining recent rows and expiring old ones. For per-partition retention, the correct mechanism is partition expiration on a date-partitioned table, which BigQuery manages automatically at the partition granularity.

Go deeper

Related to this question