Microsoft Fabric Analytics Engineer Associate DP-600
Get started today
Ultimate access to all questions.
You are working as a Fabric Analytics Engineer and have been tasked with optimizing the performance of a SQL query in a Fabric lakehouse environment. The dataset contains customer orders with fields such as order ID, product ID, quantity, and order date. Your goal is to retrieve the total quantity of each product ordered in the last 30 days, grouped by product ID, while ensuring the query is efficient and cost-effective. Consider the following constraints: the solution must minimize compute resources, adhere to compliance standards by not exposing sensitive data, and be scalable for large datasets. Which of the following SQL queries best meets these requirements? (Choose one option)
You are working as a Fabric Analytics Engineer and have been tasked with optimizing the performance of a SQL query in a Fabric lakehouse environment. The dataset contains customer orders with fields such as order ID, product ID, quantity, and order date. Your goal is to retrieve the total quantity of each product ordered in the last 30 days, grouped by product ID, while ensuring the query is efficient and cost-effective. Consider the following constraints: the solution must minimize compute resources, adhere to compliance standards by not exposing sensitive data, and be scalable for large datasets. Which of the following SQL queries best meets these requirements? (Choose one option)
Explanation:
Option A is the correct choice because it efficiently filters the data for orders within the last 30 days using the WHERE clause with the DATEADD() function, groups the results by product ID, and uses the SUM() function to calculate the total quantity for each product. This approach minimizes compute resources by only processing relevant data, adheres to compliance by not exposing sensitive data beyond what's necessary, and is scalable for large datasets. Options B and C use COUNT(*) and AVG(Quantity) respectively, which do not meet the requirement of calculating the total quantity. Option D's subquery approach is less efficient and does not directly filter for the last 30 days, making it less optimal for the given constraints.