How to optimize slow external table queries with many files?
Short answer
C Upgrade the external table to a BigLake table. Enable metadata caching for the table.
Question
Key Takeaways
This question tests the ability to optimize external table performance using BigLake features, specifically metadata caching, rather than falling for the trap of changing storage classes or bucket locations.
Upgrading to a BigLake table and enabling metadata caching significantly improves query performance for external tables containing many files. The community consensus identifies this as the most effective solution for optimizing Hive partitioned data in Cloud Storage.
Common mistake: A common mistake is selecting Option A or D, assuming that storage class or bucket region determines query speed, whereas the bottleneck is actually the metadata processing overhead for numerous files.
Expert Analysis
Why the Answer Is Correct
Upgrading to a BigLake table allows for better integration and performance features compared to standard external tables. Enabling metadata caching stores the table's metadata in BigQuery, eliminating the need to repeatedly list and read the manifest of thousands of files in Cloud Storage for every query, which drastically speeds up execution.
Why the Other Options Are Wrong
Changing storage class (Option A) primarily affects data retrieval costs and availability, not the query execution speed related to metadata parsing. Creating individual tables (Option B) introduces administrative complexity and does not inherently speed up metadata scanning. Migrating to a multi-region bucket (Option D) might slightly reduce network latency but fails to address the significant overhead of processing metadata for a large number of files.
Community Comment Notes
Commenters explicitly highlight that BigLake handles large datasets more effectively than standard external tables. They emphasize that metadata caching is the specific mechanism required to reduce the time taken to read and process metadata from a large number of files, confirming Option C as the correct choice.
Official Reference
https://cloud.google.com/bigquery/docs/biglake-intro
This link opens the official vendor documentation in a new tab.
Exam Strategy
When encountering questions about slow external table queries with many files, prioritize options involving BigLake tables and metadata caching. Avoid distractors related to storage classes or bucket regions, as these do not solve metadata processing bottlenecks.
Community Votes
100% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.
Discussion 5 contributions
Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.
From public community threads 5
-
raaad 👍 8 Answered C- BigLake Table: BigLake allows for more efficient querying of data lakes stored in Cloud Storage. It can handle large datasets more effectively than standard external tables. - Metadata Caching: Enabling metadata caching can significantly improve query performance by reducing the time taken to read and process metadata from a large number of files.
-
GCP001 👍 2C. Upgrade the external table to a BigLake table. Enable metadata caching for the table. Check ref - https://cloud.google.com/bigquery/docs/biglake-intro
-
Sofiia98 👍 1 Answered Cagree with C
-
JyoGCP 👍 1 Answered COption C
-
hanoverquay 👍 1 Answered Cvote C
Reader comments & corrections
No reader corrections yet — spotted an error or have a note? Share it below.