← PDE·Google Cloud

PDE Storing the Data Practice Question

A data engineer is designing a BigQuery table that will store billions of rows of application logs. Queries will almost always filter on a log_date column and then join on a user_id column, and the team wants to minimize both storage cost and bytes scanned. The table is append-only and grows by about 50 GB per day. Which two design choices should the engineer make? (Choose two.)

⚠ Common exam trap

The trap here is believing that clustering alone or a materialized view can substitute for partitioning when queries filter on a date column, when partition pruning is what removes whole segments of data from the scan.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Cluster the table by user_id.

Partitioning by log_date lets date-filtered queries prune partitions, and clustering by user_id reduces the blocks read for user_id filters and joins. Together they shrink bytes scanned and lower query cost for the dominant access pattern. Streaming inserts, table expiration, and materialized views do not address the general filtering and join pattern and can add cost or risk data loss.

Answer analysis

Option-by-option breakdown

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

✓
Cluster the table by user_id.
Why this is correct
Clustering by user_id sorts data within each partition so that filters and joins on user_id read fewer blocks. Combined with partitioning by log_date, it addresses both the date filter and the user_id join in the stated workload. Clustering also improves the efficiency of the join by co-locating related rows within partitions.
✗
Set the table's expiration to 30 days to reduce storage cost.
Why it's wrong here
A table expiration deletes the entire table after the interval and is not a retention strategy for individual rows. It would destroy data rather than reduce cost selectively. While storage cost matters, the scenario asks for design choices that minimize bytes scanned for date-filtered queries, and expiring the whole table does not achieve that and risks data loss.
✗
Enable streaming inserts for the log ingestion pipeline.
Why it's wrong here
Streaming inserts let rows become queryable immediately, but they incur additional streaming cost and place data in a buffer that is not immediately optimized for columnar scanning or clustering. For an append-only batch-style log pipeline, loading via load jobs is cheaper and produces better-organized storage. Streaming does not reduce bytes scanned for the stated query patterns.
✓
Partition the table by log_date.
Why this is correct
Partitioning by log_date allows BigQuery to prune partitions when queries filter on that column, which is the dominant access pattern. This reduces bytes scanned and therefore query cost, and it also makes it easy to expire old data by partition. It directly supports the stated requirement to minimize bytes scanned for date-filtered queries.
✗
Create a materialized view that aggregates logs by user_id.
Why it's wrong here
A materialized view can accelerate specific aggregate queries, but it does not reduce bytes scanned for the general filtered and joined log queries described, and it adds its own storage and refresh cost. It also cannot replace the need to organize the base table for the dominant access pattern. This is an optimization for a narrower query shape than the scenario describes.

Go deeper

PDE Storing the Data Practice Question

Why each option matters

Option-by-option breakdown

Related to this question