Use the Query Profile to Analyze the Query and Identify Bottlenecks, Such as Bad Data Skipping, Inefficient Types of Joins, and Data Shuffling. DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 1)

Questions 1–5

1. 1application · medium

   A query profile shows that the 'Exchange' operator has a 'Shuffle Write' of 50 GB and a 'Shuffle Read' of 50 GB. The query is a join between two tables, each with 10 billion rows. The join key is not skewed. Which of the following is the most likely bottleneck?

   • AThe join is the bottleneck because it is a SortMergeJoin
   • BThe scan is the bottleneck because it reads too many files
   • CThe shuffle is the bottleneck because the join requires moving a large amount of data
   • DThe filter is the bottleneck because it is not selective
   Reveal Solution Discussion Select an answer first
2. 2application · medium

   A data engineer is analyzing a query that aggregates data by a high-cardinality column. The query profile shows that the 'Shuffle Write' metric is 500 GB and the 'Shuffle Read' metric is 500 GB. The query takes 30 minutes. Which of the following is the most likely cause of the high shuffle?

   • AThe query is using a broadcast join
   • BThe aggregation is performed on a column with many distinct values, causing a large amount of data to be shuffled across executors
   • CThe query is reading from a Delta table with many small files
   • DThe cluster is under-provisioned with too few executors
   Reveal Solution Discussion Select an answer first
4. 3foundation · easy

   Which condition is a common cause of poor data skipping in Delta Lake?

   • AUsing a filter on a partition column
   • BFiltering on a column that is not part of the table's statistics
   • CUsing a filter with a constant value
   • DHaving a large number of files in the table
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   Which metric in the query profile directly measures the amount of data written to disk during a shuffle?

   • ABytes read
   • BShuffle write size
   • CDuration (sum)
   • DNumber of output rows
   Reveal Solution Discussion Select an answer first
6. 5application · medium

   A query joins a large fact table with a small dimension table. The query profile shows that the join is a BroadcastHashJoin, but the broadcast of the dimension table takes 5 minutes and the join itself takes 1 minute. The dimension table is 10 GB. Which of the following is the most likely issue?

   • AThe fact table is too large, causing the join to be slow
   • BThe cluster has too few executors, causing the broadcast to be slow
   • CThe join key is skewed, causing the join to be slow
   • DThe dimension table is too large to broadcast efficiently, causing the broadcast to be the bottleneck
   Reveal Solution Discussion Select an answer first