Question

A retail analytics team stores five years of point-of-sale transactions in a single BigQuery table (~8 TB). Nearly all analyst queries filter on a transaction_date range (usually the last 30-90 days) and frequently also filter or group by store_id. Current queries scan the entire table, driving up on-demand query costs. The team wants to minimize bytes scanned without rewriting the analysts' query patterns. What table design should you recommend?

Accepted Answer

Partition the table by transaction_date and cluster by store_id.

Answer

Cluster the table by transaction_date and cluster by store_id, with no partitioning.

Answer

Partition the table by store_id and cluster by transaction_date.

Answer

Create a separate table per month and use a wildcard query across the monthly tables.