PDE Preparing and Using Data for Analysis Practice Question

You are designing a BigQuery table to store clickstream events. Queries will frequently filter by a user_id column and a TIMESTAMP column named event_time, and the table will grow to several petabytes. You want to minimize bytes scanned and cost for these queries. Which TWO actions should you take? (Choose two.)

⚠ Common exam trap

The trap here is treating BI Engine or materialized views as general-purpose cost reducers, when they only accelerate specific cached or aggregated query patterns and do not replace partitioning and clustering.

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

✓

Partition the table by the DATE of event_time using time-unit partitioning, and require queries to include a filter on event_time.

For a petabyte-scale clickstream table filtered by event_time and user_id, partitioning on event_time enables partition pruning, and clustering on user_id enables block-level pruning within partitions. Together they minimize bytes scanned. Materialized views, table expiration, and BI Engine do not provide the same structural cost reduction for arbitrary filters.

Answer analysis

Option-by-option breakdown

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

✗
Create a materialized view that pre-aggregates events by user_id and event_time, and query the view instead of the base table.
Why it's wrong here
A materialized view helps only for recurring aggregate queries that match its definition. It does not reduce bytes scanned for arbitrary filters on user_id and event_time, and it adds storage and refresh cost. For raw clickstream filtering, partitioning and clustering on the base table are more effective.
✓
Partition the table by the DATE of event_time using time-unit partitioning, and require queries to include a filter on event_time.
Why this is correct
Partitioning by event_time allows BigQuery to prune partitions when queries filter on that column, dramatically reducing bytes scanned. Requiring a partition filter prevents accidental full-table scans. This is the primary cost-control mechanism for large time-series tables and directly addresses the frequent event_time filters.
✗
Set the table's expiration time to 30 days so that older partitions are automatically deleted, reducing the data volume.
Why it's wrong here
Table expiration deletes data, which may violate retention requirements and does not optimize query performance for the data that remains. It also does not help filter by user_id or event_time. This is a data lifecycle decision, not a query cost optimization for the specified filters.
✓
Cluster the table on the user_id column so that queries filtering on user_id scan only the relevant blocks within each partition.
Why this is correct
Clustering sorts data within partitions by the clustering columns. When queries filter on user_id, BigQuery can skip blocks that do not contain matching values. Combining clustering on user_id with partitioning on event_time optimizes both filter dimensions and reduces scanned bytes further.
✗
Enable the BigQuery BI Engine reservation and route all queries through it to cache results in memory.
Why it's wrong here
BI Engine accelerates interactive dashboards by caching frequently accessed data in memory, but it has a memory limit and does not optimize petabyte-scale ad hoc filters on user_id. It also does not reduce bytes scanned for queries outside the cached set. Partitioning and clustering are the correct structural optimizations.

Go deeper

PDE Preparing and Using Data for Analysis Practice Question

Why each option matters

Option-by-option breakdown

Related to this question