A company has a BigQuery dataset with a growing fact table (500 million rows, added daily). Queries that filter on a date column and group by a product ID are slow. The team wants to optimise query performance without increasing slot costs. Which two actions should they take? (Choose TWO.)
Trap 1: Switch to BigQuery Editions with Autoscaling slots
Changing slot pricing models does not inherently improve query performance; it may increase cost without performance gain.
Trap 2: Use SELECT * only when necessary
Avoiding SELECT * is a best practice but does not address the performance issue for the described query.
Trap 3: Create a materialised view that pre-aggregates the data
Materialised views can improve performance but may increase storage costs. The question asks to optimise without increasing slot costs, but materialised views do not directly affect slot costs; they use storage. The question's focus is on performance optimisation without increasing slot costs, and materialised views are not strictly necessary if partitioning and clustering are applied.
- A
Cluster the table by the product ID column
Clustering co-locates rows with similar product IDs, reducing the amount of data scanned for GROUP BY queries.
- B
Switch to BigQuery Editions with Autoscaling slots
Why it fails: Changing slot pricing models does not inherently improve query performance; it may increase cost without performance gain.
- C
Use SELECT * only when necessary
Why it fails: Avoiding SELECT * is a best practice but does not address the performance issue for the described query.
- D
Create a materialised view that pre-aggregates the data
Why it fails: Materialised views can improve performance but may increase storage costs. The question asks to optimise without increasing slot costs, but materialised views do not directly affect slot costs; they use storage. The question's focus is on performance optimisation without increasing slot costs, and materialised views are not strictly necessary if partitioning and clustering are applied.
- E
Partition the table by the date column
Partitioning allows BigQuery to scan only the relevant partitions (e.g., recent days) rather than the entire table.