Professional Data EngineerBuilding and operationalizing data processing systemsMedium
A data analytics team is analyzing a large dataset in BigQuery. They have a table with over a billion rows of customer transactions, including `transaction_date` (DATE), `customer_id` (STRING), `product_category` (STRING), and `amount` (NUMERIC). Analysts frequently query the data to filter by `transaction_date` for specific date ranges and also often filter and group by `product_category`. They want to optimize query performance and reduce query costs. How should the table be designed?
- APartition by `customer_id` and cluster by `transaction_date`.
- BPartition by `product_category` and cluster by `transaction_date`.
- CPartition by `transaction_date` and cluster by `product_category`.
- DPartition by `transaction_date` and cluster by `customer_id`.
Show answer & explanationAnswer & explanation
Correct answer: C. Partition by `transaction_date` and cluster by `product_category`.
Partitioning by `transaction_date` allows BigQuery to prune data based on date range filters, reducing scanned data. Clustering by `product_category` within each partition further organizes data, improving performance and reducing costs for queries filtering or grouping by product category.
Why the other options are wrong
- A. Partitioning by `customer_id` (high cardinality string) is generally not recommended as it can lead to too many small partitions, and clustering by date would be less effective than partitioning on date.
- B. Partitioning by a high-cardinality string like `product_category` is less efficient for pruning than date partitioning, and clustering by date is less effective than partitioning.
- D. Clustering by `customer_id` might be useful if many queries filter by `customer_id`, but `product_category` is explicitly mentioned for filters/groups.
BigQuery Partitioning and Clustering
BigQuery partitioning divides a table into smaller segments based on a column (e.g., date), while clustering sorts data within partitions by specified columns, both optimizing query performance and reducing costs.
- Partitioning prunes scanned data based on partition column filters.
- Clustering sorts data within partitions, improving performance for filters/aggregations on cluster columns.
- Date/Timestamp columns are ideal for partitioning.
- Frequently filtered/grouped columns are good candidates for clustering.
Memory trick: Partition the date, cluster by type, save on scan, query light.