Question

A retail analytics team has a 12 TB BigQuery table already partitioned by DATE(order_timestamp). Most dashboard queries filter on a specific date range AND a specific customer_id (there are roughly 8 million distinct customer IDs). Currently these queries scan the entire day's partition even when only one customer is needed, driving up on-demand costs. Without changing the partitioning strategy, what is the most effective way to reduce bytes scanned for these filtered queries?

Accepted Answer

Add clustering on customer_id so that within each date partition, data is sorted by customer_id and blocks can be pruned during filtered queries

Answer

Repartition the table by customer_id instead of by date to align physical storage with the filter column

Answer

Create a materialized view aggregated by date only, since dashboards read daily totals

Answer

Add an integer-range partition on a hash of customer_id to spread customers evenly across partitions