How to minimize BigQuery storage costs for time-series data?

PDE By islever Editorial Last updated

Short answer

B Create a BigQuery table partitioned by datetime value of the weather date. Set up partition expiration to 30 days.

Question

You work for an airline and you need to store weather data in a BigQuery table. Weather data will be used as input to a machine learning model. The model only uses the last 30 days of weather data. You want to avoid storing unnecessary data and minimize costs. What should you do?
  1. Create a BigQuery table where each record has an ingestion timestamp. Run a scheduled query to delete all the rows with an ingestion timestamp older than 30 days.
  2. Create a BigQuery table partitioned by datetime value of the weather date. Set up partition expiration to 30 days. Correct Answer
  3. Create a BigQuery table partitioned by ingestion time. Set up partition expiration to 30 days.
  4. Create a BigQuery table with a datetime column for the day the weather data refers to. Run a scheduled query to delete rows with a datetime value older than 30 days.

Key Takeaways

The exam tests the ability to select the most cost-effective data lifecycle management strategy, specifically distinguishing between manual deletion queries and automated partition expiration based on the data's actual timestamp.

To minimize storage costs for weather data used in ML, partition the BigQuery table by the weather date column and set a partition expiration of 30 days. This approach automatically removes outdated data without the overhead of manual deletion queries.

Common mistake: Choosing Option D (scheduled query) is a common mistake because users overlook the operational cost and complexity of running DML statements compared to the native, automatic partition expiration feature.

Expert Analysis

Why the Answer Is Correct

Option B is correct because partitioning by the datetime value of the weather date allows the data to be organized exactly how the ML model consumes it. Setting the partition expiration to 30 days ensures that BigQuery automatically drops partitions older than 30 days, minimizing storage costs and query scanning without requiring manual intervention or expensive DML operations.

Why the Other Options Are Wrong

Options A and D suggest running scheduled queries to delete rows. While this works, it incurs query processing costs for the DML operation and requires management overhead. Option C partitions by ingestion time, which is less precise if data ingestion is delayed or if historical backfills occur; the requirement specifies retaining data based on the "weather date," not when it was loaded.

Community Comment Notes

Commenters confirmed Option B is correct based on recent exam experiences. One comment clarified that since the requirement is based on the "last 30 days of weather data," partitioning by the actual weather date is preferred over ingestion time. Another comment addressed a misconception about partition limits, noting that BigQuery allows daily, monthly, or yearly granularity, easily handling 30 days of data without hitting the 4000 partition limit.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When facing questions about data retention or minimizing storage costs in BigQuery, always prioritize native features like partition expiration or table expiration over manual deletion strategies like scheduled queries.

Community Votes

B
100%

100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 8 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 8

  1. AllenChen123 👍 6 Answered B
    Partitioned based on weather date, with partition expiration set
  2. iooj 👍 6 Answered B
    got this one on the exam, aug 2024, passed
  3. Sofiia98 👍 4 Answered B
    We need the last 30 days, we don't care about ingestion time
  4. d11379b 👍 3
    https://cloud.google.com/bigquery/docs/partitioned-tables Here it mentions “ For TIMESTAMP and DATETIME columns, the partitions can have either hourly, daily, monthly, or yearly granularity.l So you should not calculate the amount of partitions on second granularity
  5. chambg 👍 1 Answered D
    Skeptical about Option B as maximum partitions in a BQ table is 4000.Since Datetime value is a timestamp it will have more than 4000 values in a duration for 30 days (302460*60 = 259,200 ). So Option D is right imo
Show 3 more comments
  1. juliorevk 👍 1 Answered B
    B BQ partitioning with partition expiration of 30 days allows you to only filter for the last 30 days and delete days that are beyond 30 days.
  2. desertlotus1211 👍 1 Answered C
    Partitioning by ingestion time is simpler and sufficient if data retention is based on load time, not the data’s internal timestamp
  3. JyoGCP 👍 1 Answered B
    Option B

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide