PDE Preparing and Using Data for Analysis Practice Question

A financial services firm stores customer transaction data in a BigQuery table. The table contains a column `customer_id` that is frequently used in WHERE clauses, but the table is not partitioned. Queries filtering on a specific `customer_id` scan the entire table, which is large and costly. The data engineer wants to reduce the bytes scanned for these queries without changing the table's partitioning scheme. What should the engineer do?

⚠ Common exam trap

Candidates often confuse clustering with partitioning, and assuming that any column can be used as a partition key, when in fact partitioning is limited to date/timestamp or integer range columns and high-cardinality columns like customer_id are best suited for 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

✓

Create a clustered table on `customer_id` by using a CREATE TABLE ... CLUSTER BY customer_id statement and loading the data into it.

Clustering on `customer_id` organizes the table data so that BigQuery can skip blocks that do not contain the requested customer ID, thereby reducing bytes scanned and improving query performance. Partitioning is not suitable for a high-cardinality identifier like `customer_id`, and materialized views or partition filters do not address the specific need. Clustering is the correct technique for optimizing filters on a non-partitioned table.

Answer analysis

Option-by-option breakdown

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

✗
Enable the `require_partition_filter` option on the table and add a partition on a date column.
Why it's wrong here
The `require_partition_filter` option forces queries to include a partition filter, but it requires the table to be partitioned. The scenario states the table is not partitioned and the goal is to reduce bytes scanned for filters on `customer_id`. Adding a date partition would not help queries that filter only on `customer_id`, because those queries would still scan all partitions unless they also filter on the date. This does not meet the requirement.
✗
Create a materialized view that selects all columns and filters on `customer_id`.
Why it's wrong here
A materialized view cannot be defined with an arbitrary WHERE clause on a high-cardinality column like `customer_id`; materialized views are intended for pre-aggregation and have restrictions. Even if it were possible, it would not automatically prune data for ad-hoc queries filtering on different customer IDs. The view would not reduce bytes scanned for the original table's queries unless the queries are rewritten to use the view.
✗
Add a partition on `customer_id` using a CREATE TABLE ... PARTITION BY customer_id statement.
Why it's wrong here
BigQuery partitioning requires a DATE, TIMESTAMP, or DATETIME column, or an INTEGER column for range partitioning. `customer_id` is likely a STRING or INTEGER, but partitioning by an arbitrary high-cardinality column such as `customer_id` is not supported for STRING, and even for INTEGER it would create too many partitions, exceeding the limit of 4,000 partitions. This approach is not feasible and would not solve the problem.
✓
Create a clustered table on `customer_id` by using a CREATE TABLE ... CLUSTER BY customer_id statement and loading the data into it.
Why this is correct
Clustering sorts the data by the specified column and stores it in blocks, allowing BigQuery to prune unnecessary blocks when a query filters on that column. This reduces the bytes scanned and improves performance for queries that filter on `customer_id`. Since the table is not partitioned, clustering is the appropriate technique to achieve the goal without altering the partitioning scheme.

Go deeper

PDE Preparing and Using Data for Analysis Practice Question

Why each option matters

Option-by-option breakdown

Related to this question