A BigQuery table is queried mainly by date, and most queries scan the full table. What do you do?
Why D is the answer
Partition on the date column and cluster on common filters
Partitioning prunes what is scanned, which reduces both cost and latency.
In detail
- Partitioning limits the scan.
- Clustering narrows it further.
- Clustering sorts data within partitions.
Use case
An illustrative scenario, not a real incident.
Most queries ask for one week, but each scans ten years. Partitioning by date reads only that week.
See it in different ways
stateDiagram-v2 [*] --> QueryPlan QueryPlan --> PartitionAndCluster: scans too much QueryPlan --> MaterializedView: repeated aggregation QueryPlan --> BiEngine: dashboards need sub-second PartitionAndCluster --> [*] MaterializedView --> [*] BiEngine --> [*]
flowchart LR S0["Partition by date"] --> S1["Cluster on filters"] --> S2["Scan less"] --> S3["Pay less"]
Key point: Partition, then cluster.
Why the other options are wrong
- A. Add another project and move the table into it
- A new project does not change the scan.
- B. Create a new dataset with a longer table expiry
- Expiry does not change what is scanned.
- C. Move the table to Cloud SQL and add an index on date
- Cloud SQL is not for this scale.