Professional Data EngineerBuilding and operationalizing data processing systemsMedium
A logistics company uses a BigQuery data warehouse for analyzing shipment data. They notice that queries on their `shipments` table, which contains billions of rows, are becoming increasingly slow, especially when filtering by `destination_country` and `shipment_date`. The table is partitioned by `shipment_date`. What BigQuery feature should they implement to improve query performance specifically for filters on `destination_country`?
- ARow-level Security
- BExternal Tables
- CMaterialized Views
- DClustering
Show answer & explanationAnswer & explanation
Correct answer: D. Clustering
BigQuery clustering sorts data within partitions based on the values of specified clustering columns. When queries filter or aggregate on a clustered column, BigQuery can prune blocks of data more efficiently, significantly reducing the amount of data scanned and improving query performance. Since `destination_country` is frequently filtered, clustering by it within the `shipment_date` partitions will optimize performance.
Why the other options are wrong
- A. Row-level security restricts which rows a user can see, which is a security feature and has no direct impact on query performance for filtering.
- B. External tables allow querying data stored outside BigQuery, but they don't offer performance optimizations like clustering or partitioning for the data itself.
- C. Materialized views pre-compute and store query results, which can improve performance for specific aggregate queries, but they don't directly optimize filtering on a non-partitioned column within the base table itself.
BigQuery Clustering
BigQuery clustering organizes data within table partitions based on the values of specified columns, improving query performance by enabling more efficient data pruning for filters and aggregations on those columns.
- Optimizes query performance for specific columns
- Works within partitions (after partitioning)
- Reduces data scanned by queries
Memory trick: BigQuery's speed: Partition first, then cluster for laser focus.