Pass the Microsoft Certified: Fabric Analytics Engineer Associate DP-600 Questions and answers with Dumpstech

Folding in Power Query refers to operations that can be translated into source queries. In this case, "some" of the steps can be folded, which means that some transformations will be executed at the data source level. The steps that cannot be folded will be executed within the Power Query engine. Custom steps, especially those that are not standard query operations, are usually executed within Power Query engine rather than being pushed down to the source system.

References =

Query folding in Power Query

Power Query M formula language

ISEMPTY(CALCULATETABLE('Order Item')) checks if there are no related rows in 'Order Item' for a customer. Wrapping this in NOT returns true if the customer has at least one order. This avoids calculating row counts and instead just checks existence, which is more efficient for performance. This reduces execution time and meets the goal.

Requirement Analysis

Sales table → Users should only see sales data for their own region.

This requires filtering rows based on a condition (e.g., Region = User’s Region).

The correct feature: Row-level security (RLS).

Employees table → Restrict access to Personally Identifiable Information (PII) while allowing access to non-sensitive columns.

This requires restricting access to specific columns (such as SSN, phone, address, etc.), but leaving others visible.

The correct feature: Column-level security.

Maintain access to unrestricted data for all users → Both RLS and column-level security allow users to continue accessing non-restricted data.

Why Other Options Are Not Correct

Item permissions: Control access at the object level (e.g., table or report), not at the row or column level. Too coarse for this requirement.

Workspace permissions: Apply at the workspace level, not suitable for table-level data filtering.

Completed Answer

Employees → Column-level security

Sales → Row-level security (RLS)

References

Row-level security in Microsoft Fabric

Column-level security in Microsoft Fabric

o add new data to the Delta table while meeting the specified requirements:

You should use the append mode to ensure that all new data is added to the table without affecting the existing rows.

You should set the mergeSchema option to true to allow the schema of the Delta table to be updated with the new columns found in the DataFrame.

The completed code would look like this:

df.write.format("delta").mode("append")

option("mergeSchema", "true")

saveAsTable("Lakehouse1.TableName")

The two languages you can use to perform model scoring in a Fabric notebook using the predict function are Spark SQL (option C) and PySpark (option D). These are both part of the Apache Spark ecosystem and are supported for machine learning tasks in a Fabric environment. References = You can find more information about model scoring and supported languages in the context of Fabric notebooks in the official documentation on Azure Synapse Analytics.

Based on the duration breakdown provided, the major contributor to the rendering duration is categorized as "Other," which is significantly higher than DAX Query and Visual display times. This suggests that the issue is less likely with the DAX calculation or visual rendering times and more likely related to model performance or the complexity of the visual. However, of the options provided, optimizing the DAX query can be a crucial step, even if "Other" factors are dominant. Using DAX Studio, you can analyze and optimize the DAX queries that power your visuals for performance improvements. Here’s how you might proceed:

Open DAX Studio and connect it to your Power BI report.

Capture the DAX query generated by the Orders By Date visual.

Use the Performance Analyzer feature within DAX Studio to analyze the query.

Look for inefficiencies or long-running operations.

Optimize the DAX query by simplifying measures, removing unnecessary calculations, or improving iterator functions.

Test the optimized query to ensure it reduces the overall duration.

Scenario:

Workspace1 contains: Pipeline1 and Lakehouse1.

A folder named Folder1 is created in Workspace1.

Lakehouse1 is moved into Folder1.

A deployment pipeline DeployPipeline1 deploys from Workspace1 → Workspace2.

Key Behavior:

Deployment pipelines in Microsoft Fabric maintain the folder hierarchy of items when deploying between workspaces.

If an item (Lakehouse1) is placed in a folder (Folder1), the same folder structure is replicated in the target workspace.

Items not placed in the folder (Pipeline1) remain at the root level.

Result in Workspace2 after deployment:

Pipeline1 remains at the root.

Lakehouse1 is placed inside Folder1.

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

A lakehouse (A) is the type of data store you should use. It supports dataflows to load and append data and ensures that Delta tables are Z-Order optimized and compacted automatically. References = The capabilities of a lakehouse and its support for Delta tables are described in the lakehouse and Delta table documentation.

 The statement uses SUM(CASE WHEN RefundStatus = 'Refunded' THEN SalesAmount ELSE 0 END) AS TotalRevenue, which calculates TotalRevenue by summing SalesAmount only when RefundStatus is 'Refunded', and 0 otherwise. This means Region values are returned regardless of RefundStatus, but the TotalRevenue reflects refunded items.

 The WHERE (TransactionDate) - YEAR(GETDATE()) condition is incomplete and lacks proper comparison (e.g., no equality or range check), so it does not filter for the current year.

 The TotalRevenue calculation does not aggregate all SalesAmount values with RefundStatus of 'Refunded'; it sums SalesAmount only for refunded items within the grouped data, with 0 for non-refunded items.