Professional Cloud ArchitectAnalyze and optimize technical and business processesHard
A data analytics company uses BigQuery for its core data warehousing needs. They have several large tables (tens of terabytes) that are queried frequently by various business intelligence (BI) tools and data scientists. Data scientists often perform ad-hoc queries, which sometimes lead to high query costs and slow performance. You need to recommend a strategy to optimize query performance and control costs for these large tables. What should you advise?
- AImplement BigQuery reservations for consistent pricing and resource allocation.
- BExport frequently queried data to Cloud Storage and query it using external tables.
- CPartition and cluster the large tables based on common query filters and join keys.
- DUse BigQuery BI Engine to accelerate queries from BI tools.
Show answer & explanationAnswer & explanation
Correct answer: C. Partition and cluster the large tables based on common query filters and join keys.
Partitioning and clustering BigQuery tables are fundamental optimization techniques. Partitioning reduces the amount of data scanned by queries, while clustering further sorts data within partitions, leading to significant improvements in query performance and cost reduction by minimizing scanned data.
Why the other options are wrong
- A. BigQuery reservations provide flat-rate pricing and dedicated slots, which helps with cost predictability and consistent performance, but it doesn't optimize the underlying query efficiency or reduce the amount of data scanned, which is key for performance and cost.
- B. Exporting data to Cloud Storage and querying via external tables would likely introduce more latency and complexity, and wouldn't offer the same performance or cost benefits as BigQuery's native partitioning and clustering features for large, frequently queried datasets.
- D. BI Engine accelerates queries for BI tools by caching frequently accessed data, but it doesn't directly optimize the underlying table structure for all ad-hoc queries by data scientists, nor does it inherently reduce the amount of data processed at the storage layer.
BigQuery Table Optimization
Techniques used to improve query performance and reduce costs in BigQuery by structuring data efficiently.
- Partitioning: Divides a table into smaller segments based on a column (e.g., date, integer range).
- Clustering: Sorts data within partitions based on up to four columns.
- Reduces data scanned, leading to lower costs and faster queries.
- Consider common query filters and join keys when choosing partition/cluster columns.
Memory trick: Structure your BigQuery tables smartly to slash costs and speed up scans.