Free Preparation Discussions
MENU
Google Professional Data Engineer Exam - Topic 5 Question 103 Discussion
Topic #: 5
You store and analyze your relational data in BigQuery on Google Cloud with all data that resides in US regions. You also have a variety of object stores across Microsoft Azure and Amazon Web Services (AWS), also in US regions. You want to query all your data in BigQuery daily with as little movement of data as possible. What should you do?
To improve the performance of querying a large BigQuery table with filters on countryname and username, clustering the table by these fields is the most effective approach. Here's why option C is the best choice:
Clustering in BigQuery:
Clustering organizes data based on the values in specified columns. This can significantly improve query performance by reducing the amount of data scanned during query execution.
Clustering by countryname and username means that data is physically sorted and stored together based on these fields, allowing BigQuery to quickly locate and read only the relevant data for queries using these filters.
Filter Efficiency:
With the table clustered by countryname and username, queries that filter on these columns can benefit from efficient data retrieval, reducing the amount of data processed and speeding up query execution.
This directly addresses the performance issue of the dashboard queries that apply filters on these fields.
Steps to Implement:
Redesign the Table:
Create a new table with clustering on countryname and username:
CREATE TABLE project.dataset.new_table
CLUSTER BY countryname, username AS
SELECT * FROM project.dataset.customer_order;
Migrate Data:
Transfer the existing data from the original table to the new clustered table.
Update Queries:
Modify the dashboard queries to reference the new clustered table.
BigQuery Clustering Documentation
Optimizing Query Performance
Denae
3 months agoJulene
3 months agoAnna
3 months agoMarsha
4 months agoMargery
4 months agoMarva
4 months agoIzetta
4 months agoChanel
4 months agoBuck
5 months agoLarae
5 months agoAron
5 months agoTy
5 months agoVivienne
5 months agoKeena
10 months agoChaya
8 months agoMaurine
9 months agoClemencia
9 months agoClay
9 months agoVal
9 months agoCherelle
10 months agoMaryln
10 months agoTamar
9 months agoDesmond
9 months agoVan
9 months agoTwila
10 months agoMelynda
10 months agoGilma
11 months agoElroy
11 months agoLuis
11 months agoNana
11 months agoThersa
10 months agoSherron
10 months agoCyril
10 months agoKallie
10 months ago