Question

A retail analytics team stores a large fact table in BigQuery keyed by a numeric customer_id (values 1 to roughly 5,000,000). Nearly all analytical queries filter on a specific range of customer_id to process a subset of customers at a time, but the table has no meaningful timestamp column that queries filter on. The team wants to prune data scanned and reduce query cost without changing how analysts write their WHERE clauses. Which BigQuery design should they use?

Accepted Answer

Create an integer-range partitioned table on customer_id with a defined start, end, and interval.

Answer

Add an ingestion-time (_PARTITIONTIME) partition and instruct analysts to also filter on _PARTITIONTIME.

Answer

Cluster the table on customer_id without partitioning and rely on clustering to prune scanned bytes.

Answer

Create a DATE-partitioned table using a synthetic date derived by hashing customer_id into buckets.