Professional Data EngineerBuilding and operationalizing data processing systemsMedium
A logistics company uses BigQuery to analyze shipment data. They have a `shipments` table with billions of rows, including columns for `shipment_id`, `origin_city`, `destination_city`, `shipment_date`, and `weight_kg`. Analysts frequently query data filtered by `shipment_date` and `destination_city`, and often aggregate results by `origin_city`. They want to optimize query performance and reduce costs for these common queries. Which BigQuery table optimization strategy should they implement?
- APartition the table by `shipment_date` and cluster by `destination_city`, `origin_city`.
- BPartition the table by `shipment_id` and cluster by `weight_kg`.
- CCluster the table by `shipment_date` and `weight_kg` without partitioning.
- DPartition the table by `destination_city` and cluster by `shipment_date`.
Show answer & explanationAnswer & explanation
Correct answer: A. Partition the table by `shipment_date` and cluster by `destination_city`, `origin_city`.
Partitioning by `shipment_date` significantly reduces the amount of data scanned for time-based queries. Clustering by `destination_city` and `origin_city` further organizes data within partitions, accelerating queries filtered by or aggregated on these columns and reducing scan costs.
Why the other options are wrong
- B. Partitioning by a high-cardinality ID is inefficient; clustering by `weight_kg` alone doesn't cover primary filter/aggregation columns.
- C. Clustering without partitioning is less effective for very large tables where date-based filtering can prune entire partitions. Also, `weight_kg` is not a primary filter/aggregation column mentioned.
- D. While `destination_city` is used for filtering, `shipment_date` is often filtered first. Partitioning by date is generally more effective for time-series data.
BigQuery Partitioning and Clustering
BigQuery partitioning segments tables by a date, timestamp, or integer column, reducing data scanned for filtered queries. Clustering further sorts data within partitions by specified columns, improving performance for filters and aggregations.
- Partitioning reduces data scanned by segmenting data.
- Clustering sorts data within partitions, optimizing scan for filters/aggregations.
- Combining both offers significant performance and cost benefits for large tables.
Memory trick: Partition by time, cluster by common queries.