Google Professional Data Engineer
Get started today
Ultimate access to all questions.
Ultimate access to all questions.
You are utilizing Google BigQuery as your data warehouse solution. Users have raised concerns about the performance of a particular query which persists in running sluggishly, irrespective of the time of execution. The query in question is:
SELECT country, state, city FROM [myproject:mydataset.mytable] GROUP BY country
Upon examining the query plan within the Read section of Stage:1, you observe certain details that could be indicative of the underlying issue. What is the most probable reason for the delay in the execution of this query?
Explanation:
The most likely cause of the delay for the query is option D: 'Most rows in the [myproject:mydataset.mytable] table have the same value in the country column, causing data skew'. Group by queries in BigQuery can run slowly when there is significant data skew on the grouped columns. Since the query is grouping by country, if most rows have the same country value, all that data will need to be shuffled to a single reducer to perform the aggregation, causing a data skew slowdown. This is verified by the user comments and the discussion of color codes in the execution plan.