Professional Data EngineerBuilding and operationalizing data processing systemsHard
A large e-commerce company uses BigQuery as its primary data warehouse. They have a daily sales table containing billions of rows, with columns such as `order_id`, `customer_id`, `order_timestamp`, `product_id`, and `order_total`. Analysts frequently query this table, often filtering by `order_timestamp` (for specific dates or date ranges) and `customer_id`. The table is currently partitioned by `order_timestamp`. To further optimize these common queries for both performance and cost, what additional BigQuery feature should be applied?
- AChange the partitioning scheme to `customer_id`.
- BExport the entire table to Cloud Storage for faster access.
- CImplement clustering on `customer_id`.
- DCreate a materialized view based on `customer_id`.
Show answer & explanationAnswer & explanation
Correct answer: C. Implement clustering on `customer_id`.
Since the table is already partitioned by `order_timestamp`, clustering on `customer_id` will further organize data *within* each partition. This optimizes queries that filter on both `order_timestamp` (partition pruning) and `customer_id` (cluster pruning), significantly improving performance and reducing scan costs.
Why the other options are wrong
- A. Changing partitioning to `customer_id` would break the existing optimization for `order_timestamp` filtering, which is also crucial.
- B. Exporting to Cloud Storage does not improve BigQuery query performance and adds an unnecessary data transfer step.
- D. Materialized views pre-compute results but don't optimize the underlying table scans for ad-hoc filtering on `customer_id`.
BigQuery Clustering with Partitioning
Combining BigQuery partitioning and clustering to optimize a table. Partitioning segments the table by a primary key (e.g., date), and clustering further organizes data within each partition by secondary keys, improving multi-column query performance.
- Partitioning prunes entire partitions.
- Clustering prunes blocks within partitions.
- Effective for queries filtering on both partition and cluster keys.
- Reduces bytes scanned and improves query speed.
Memory trick: First divide the boxes (partitions), then sort items inside (clusters).