Professional Data EngineerBuilding and operationalizing data processing systemsHard
A retail company is analyzing customer purchase data stored in BigQuery. The `transactions` table contains billions of rows, with columns like `transaction_id`, `customer_id`, `transaction_timestamp`, `product_id`, and `amount`. Analysts frequently query this table to find transactions for specific `customer_id` values within a given date range. Queries are becoming slow and expensive. The team needs to optimize query performance and reduce costs for these common analytical patterns. Which BigQuery table optimization strategy should they implement?
- AIncrease the number of BigQuery slots allocated to the project.
- BPartition the table by `transaction_timestamp` and cluster by `customer_id`.
- CCluster the table by `transaction_id`.
- DCreate a materialized view on `transaction_id`.
Show answer & explanationAnswer & explanation
Correct answer: B. Partition the table by `transaction_timestamp` and cluster by `customer_id`.
Partitioning by `transaction_timestamp` allows BigQuery to prune scan data based on date ranges, reducing data scanned. Clustering by `customer_id` within each partition further organizes data for specific customer lookups, significantly improving performance and reducing costs for the described query pattern.
Why the other options are wrong
- A. Increasing slots might improve performance by providing more compute, but it doesn't optimize the underlying data storage or reduce data scanned, thus not being cost-effective for recurring queries.
- C. Clustering by `transaction_id` alone would not help with date range filters or efficient lookups by `customer_id` across the entire table.
- D. A materialized view on `transaction_id` would not optimize queries filtering by `customer_id` and `transaction_timestamp` effectively; it's useful for pre-aggregating common queries.
BigQuery Partitioning and Clustering
BigQuery features that organize table data to improve query performance and reduce costs by minimizing the amount of data scanned. Partitioning divides a table into segments based on a column (e.g., date), while clustering sorts data within partitions by specified columns.
- Partitioning reduces data scanned by filtering on partition keys
- Clustering sorts data within partitions for faster filtering/aggregation on cluster keys
- Crucial for cost and performance optimization in large tables
Memory trick: Partition by time, cluster by ID, make queries fly.