Ultimate access to all questions.
Answer-first summary for fast verification
Answer: Use the 'repartition' command to redistribute the data evenly across the cluster before applying the window functions, ensuring optimal resource utilization and minimizing data shuffling.
The 'repartition' command is the BEST approach because it ensures data is evenly distributed across the cluster, which is crucial for the efficient execution of window functions. This method directly addresses the root cause of performance issues in complex queries by minimizing data shuffling and optimizing resource utilization. While rewriting the query with subqueries and temporary tables (A) can offer some performance benefits, it may not be as effective for queries heavily reliant on window functions. Caching tables (B) can improve performance but does not address the uneven data distribution issue inherent in window functions. Adding indexes (C) is less effective in a distributed computing environment like Spark, where data is processed in-memory and distributed across nodes.
Author: LeetQuiz Editorial Team
No comments yet.
As a Microsoft Fabric Analytics Engineer Associate, you are optimizing a complex SQL query in a Spark notebook within Azure Databricks. The query involves multiple window functions and is experiencing performance issues. Considering the need for cost efficiency, compliance with data governance policies, and scalability, which of the following approaches would BEST improve the query's performance? (Choose one option.)
A
Rewrite the query to use subqueries and temporary tables, ensuring that the temporary tables are created with appropriate partitioning to leverage parallel processing.
B
Use the 'cache' command to store the tables involved in the query in memory, and apply dynamic filtering to reduce the dataset size before processing.
C
Add more indexes to the tables involved in the query, focusing on columns used in the window functions to speed up data access.
D
Use the 'repartition' command to redistribute the data evenly across the cluster before applying the window functions, ensuring optimal resource utilization and minimizing data shuffling.