GCP Associate Cloud Engineer Practice Question 20: BigQuery Partition Filter

Google Cloud Associate Cloud Engineer · Test 2 · question 10 of 10

BigQueryhard

A 40 TB BigQuery table holds five years of events. Analysts almost always query one or two days of data with WHERE event_date = ..., but on-demand query costs are high because each query scans most of the table. You want to reduce the bytes billed and prevent accidental full-table scans.

What should you do?

Select one answer
Show answer and explanation

Answer:

  • C. Recreate the table partitioned by event_date and set the option that requires a partition filter in queries

In a table partitioned by date, a query that filters on the partitioning column reads only the matching partitions, so bytes billed drop sharply (partition pruning). Setting require_partition_filter makes BigQuery reject queries that do not filter on the partitioning column, which prevents accidental full scans.

Option A does not reduce the data scanned in a table that is not clustered; LIMIT limits the rows returned, not the bytes billed. Option B moves the data to a format that BigQuery has to read in full for every query and removes many optimizations. Option D is wrong because table expiration deletes data (and long-term storage pricing applies automatically to unmodified data); neither affects query scan costs.

Reference: Introduction to partitioned tables

Practice Test 2 ›

Study guides for Google Cloud Associate Cloud Engineer