Microsoft Updated DP-600 Exam Questions and Answers by kamil

Requirement: “The number of rows added to the Orders table during refreshes must be minimized.”

The Orders table has a sequential OrderID.

To minimize refresh size, you must only import new rows, not the entire table.

This requires retrieving the maximum OrderID currently loaded, then only loading rows with a greater OrderID.

So, the correct solution is to use a pipeline (ADF) with a dataflow to retrieve the maximum OrderID.

Answer: C. an Azure Data Factory pipeline that executes a dataflow to retrieve the maximum value of the OrderID column in the destination lakehouse

Scenario:

Dev = Workspace1 with Dataflow Gen2 (source = blob storage CSV).

Prod = Workspace2, different storage account.

Need: ensure deployed dataflow points to the production storage location.

Analysis:

Data source rules: used to remap data sources between environments (e.g., blob storage dev → blob storage prod).

Parameter rules: used when the data source location is parameterized (for example, a parameter storing the file path or connection string).

Best practice: use parameters in the dataflow for connection strings, then apply parameter rules in deployment pipelines.

In this case, since the requirement is about ensuring the reference updates correctly, only parameter rules are needed (not data source rules).

Correct Answer:

B. Create a parameter rule only.

Comprehensive Detailed Explanation

We need to write a query that summarizes order quantities by year and product, and also include the yearly total for all products in each row.

Step 1: What the query needs

Extract the year from SalesOrderDetail.ModifiedDate.

Join with the Product table to get the product name.

Aggregate with SUM(OrderQty).

Return grouped data by year and product, with an additional subtotal row per year (all products combined).

Step 2: Evaluate the SELECT Clause

We must extract the year using:

YEAR(so.ModifiedDate) AS OrderDate

This converts the ModifiedDate column into a year value for grouping.

Step 3: Evaluate the GROUP BY options

CUBE(YEAR, P.Name) → Produces all combinations of year totals, product totals, and grand totals. Too many combinations, not required.

GROUPING SETS → Could achieve the result but requires explicitly listing the sets. Less direct.

ROLLUP(YEAR, P.Name) → Produces grouping by (Year, Product) and then a subtotal per Year. Exactly what is required.

YEAR only → Would group only by year, losing per-product breakdown.

Correct: ROLLUP(YEAR(so.ModifiedDate), P.Name)

Step 4: Completed Query

SELECT

YEAR(so.ModifiedDate) AS OrderDate,

p.Name,

SUM(so.OrderQty) AS OrderQty

FROM dbo.SalesOrderDetail so

INNER JOIN dbo.Product p

ON p.ProductID = so.ProductID

GROUP BY ROLLUP(YEAR(so.ModifiedDate), p.Name);

Why This Works

YEAR(so.ModifiedDate) extracts year for grouping.

ROLLUP(YEAR, P.Name) provides both product-level totals and yearly subtotals.

Ensures the requirement: “include the yearly sum of order quantities for all the products in each row.”

References

GROUP BY ROLLUP in T-SQL

Aggregate functions in Microsoft Fabric warehouses

We are analyzing this KQL query:

Sales

| where Status != "Cancelled"

| where OrderDate >= ago(30d)

| summarize TotalSales = sum(SalesAmount) by ProductCategory

| where TotalSales > 0

Statement 1:

"The query excludes sales that have a Status of Cancelled."

Yes → where Status != "Cancelled" filters them out.

Statement 2:

"The query calculates the total sales of each product category for the last 30 days."

Yes → where OrderDate >= ago(30d) ensures only last 30 days.

summarize TotalSales = sum(SalesAmount) by ProductCategory groups by category.

Statement 3:

"The query includes product categories that have had zero sales during the last 30 days."

No → The final filter where TotalSales > 0 excludes categories with zero sales.

Final Answer:

Excludes Cancelled sales → Yes

Calculates total sales by product category for last 30 days → Yes

Includes product categories with zero sales → No