Free Preparation Discussions
MENU
Google Professional Data Engineer Exam - Topic 3 Question 91 Discussion
Topic #: 3
Your company's customer_order table in BigOuery stores the order history for 10 million customers, with a table size of 10 PB. You need to create a dashboard for the support team to view the order history. The dashboard has two filters, countryname and username. Both are string data types in the BigQuery table. When a filter is applied, the dashboard fetches the order history from the table and displays the query results. However, the dashboard is slow to show the results when applying the filters to the following query:
How should you redesign the BigQuery table to support faster access?
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
Rose
9 months agoTanesha
9 months agoFrederic
10 months agoGraham
10 months agoRima
10 months agoAron
10 months agoDarrel
11 months agoAhmad
11 months agoLauran
11 months agoLemuel
11 months agoCiara
11 months agoGail
11 months agoMoon
11 months agoKing
11 months agoGracia
11 months agoWilliam
11 months agoMajor
2 years agoEdelmira
2 years agoGoldie
2 years agoTuyet
2 years agoJohnna
2 years agoBeckie
2 years agoKaitlyn
2 years agoBeckie
2 years agoAlex
2 years agoCamellia
2 years agoSimona
2 years agoErnestine
2 years agoAshanti
2 years agoBerry
2 years agoNichelle
2 years agoChristiane
2 years ago