DP-600 Sample Questions Answers

The measure should use the AVERAGE function to calculate the average value.

It should reference the Response Value column from the 'Survey' table.

The 'Number of months' should be used to define the period for the average calculation.

To calculate the average overall satisfaction score using DAX, you would need to use the AVERAGE function on the response values related to satisfaction questions. The DATESINPERIOD function will help in calculating the rolling average over the last 12 months.

A lakehouse (C) should be recommended for the AnalyticsPOC workspace. It combines the capabilities of a data warehouse with the flexibility of a data lake. A lakehouse supports semi-structured and unstructured data and allows for T-SQL and Python read access, fulfilling the technical requirements outlined for Litware. References = For further understanding, Microsoft's documentation on the lakehouse architecture provides insights into how it supports various data types and analytical operations.

For ingesting customer data into the data store in the AnalyticsPOC workspace, a dataflow (D) should be recommended. Dataflows are designed within the Power BI service to ingest, cleanse, transform, and load data into the Power BI environment. They allow for the low-code ingestion and transformation of data as needed by Litware's technical requirements. References = You can learn more about dataflows and their use in Power BI environments in Microsoft's Power BI documentation.

To meet the technical requirement that data loading activities must ensure the raw and cleansed data is updated completely before populating the dimensional model, you would need a mechanism that allows for ordered execution. A pipeline in Microsoft Fabric with dependencies set between activities can ensure that activities are executed in a specific sequence. Once set up, the pipeline can be scheduled to run at the required intervals (hourly or daily depending on the data source).

You should use CREATE VIEW to make the pricing group logic available for T-SQL queries.

The CASE statement should be used to determine the pricing group based on the list price.

The T-SQL statement should create a view that classifies products into pricing groups based on the list price. The CASE statement is the correct conditional logic to assign each product to the appropriate pricing group. This view will standardize the pricing group logic across different databases and semantic models.

Comprehensive Detailed Explanation

The requirement is to logically group the Research division workspaces and support filtering in the OneLake data hub.

Why Domain?

Microsoft Fabric allows you to assign workspace domains (e.g., Finance, HR, Research).

This provides a logical grouping of workspaces within the organization.

Domains are the only supported way to enable filtering in OneLake data hub by department.

Capacity or tenant grouping would not allow filtering by department in OneLake.

Why OneLake data hub?

The OneLake data hub is the centralized interface to browse and discover data assets across the organization.

With workspace domains configured, users can filter directly in the OneLake data hub by domain (e.g., “Research”).

The Fabric Admin portal and Entra admin center are used for administration, not for workspace filtering in data discovery.

Correct Selection for the Hotspot

Grouping method: Domain

Tool: OneLake data hub

References

Workspace domains in Microsoft Fabric

Discover and filter data in OneLake data hub

Final Answer:

Grouping method = Domain

Tool = OneLake data hub

In Azure DevOps workspace (project) security settings, role assignments determine access levels for groups:

Viewer (equivalent to the Readers security group): Grants read-only access to work items, code, pipelines, and other artifacts. Ideal for review-only scenarios where users need to view but not edit content.

Contributor (equivalent to the Contributors security group): Allows full edit and contribute privileges, such as updating work items, code reviews, and pipeline runs. Suitable for active participation in reviews that may involve feedback or changes.

Based on the group names implying review-focused access, Viewer fits Group1 for passive observation, while Contributor fits Group2 for interactive review tasks. These align with default Azure DevOps permissions for secure, least-privilege access.

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

 Calculation groups are a Tabular Object Model (TOM) feature.

 They cannot be authored in Power BI Desktop or the service.

 They are implemented using Tabular Editor.

You need to ensure that Contoso can use version control to meet the data analytics requirements and the general requirements.

Requirement: “All the semantic models and reports for the Research division must use version control that supports branching.”

Fabric supports Git integration (Azure Repos or GitHub) for semantic models and reports.

Storing in Data Lake Gen2 or OneDrive does not provide version control with branching.

The correct action is to integrate workspaces with a Git repository.

Answer: D. Modify the settings of the Research division workspaces to use an Azure Repos repository.

You need to recommend which type of Fabric capacity SKU meets the data analytics requirements for the Research division.

Requirement: “The Research division workspaces must use a dedicated, on-demand capacity that has per-minute billing.”

Fabric capacity SKUs:

F (Fabric) = dedicated Fabric capacity, available on pay-as-you-go with per-minute billing.

P (Premium) and EM are Power BI capacities (not Fabric-native).

A refers to Azure Analysis Services capacity.

The only correct option is F SKU.

Comprehensive Detailed Explanation

From the case study:

Research division data for Productline2 is currently in CSV format in storage2.

Requirement: “All the Research division data in the lakehouses must be presented as managed tables in Lakehouse explorer.”

In Fabric lakehouses, managed tables are stored in Delta format inside the Tables folder.

Step 1: Reading the source

df = spark.read.format("csv") \

options(header="true", inferSchema="true") \

load("abfss://storage1.dfs.core.windows.net/files/productline2")

This correctly ingests the CSV source from ADLS Gen2.

Step 2: Writing to Lakehouse as a managed table

You must write the data in Delta format to ensure it is queryable and managed within the lakehouse.

The correct path is under Tables/, because this is where Fabric automatically manages Lakehouse managed tables.

The target table should be named productline2, so the correct path is:

df.write.mode("overwrite").format("delta").save("Tables/productline2")

Why not other options?

CSV or Parquet formats would not create a managed Lakehouse table; they would just create files.

Writing to productline2 directly (without Tables/) would store unmanaged files in the Lakehouse Files area, not managed tables.

Writing to Tables/research/productline2 adds an unnecessary subdirectory and is not the standard structure for managed tables.

Correct Final Code

df.write.mode("overwrite").format("delta").save("Tables/productline2")

References

Managed tables in Microsoft Fabric Lakehouse

Delta Lake support in Fabric

Spark write to Lakehouse

Comprehensive Detailed Explanation

The question asks: Which syntax should you use in a Fabric notebook to access the Research division data for Productline1?

Key Background from the Case

In Productline1ws, a lakehouse named Lakehouse1 is created.

In Lakehouse1, a shortcut is created to storage1, named ResearchProduct.

Storage1 contains the Research division data for Productline1 in Delta format.

Requirement: All data in lakehouses must be presented as managed tables in Lakehouse explorer.

Analyzing the Syntax Options

Option A:

spark.sql("SELECT * FROM Lakehouse1.ResearchProduct")

This syntax directly queries the ResearchProduct shortcut within Lakehouse1 using Spark SQL.

Since the shortcut points to Delta data, Spark can directly query it.

This is the correct way to retrieve Productline1 data from Lakehouse1.

Option B:

spark.sql("SELECT * FROM Lakehouse1.productline1.ResearchProduct")

This introduces an extra schema-like path (productline1) that is not part of the shortcut name.

Incorrect, because the shortcut was created as ResearchProduct inside Lakehouse1, not under another schema.

Option C:

external_table('Tables/ResearchProduct')

external_table is not the correct way to access a Lakehouse shortcut.

Shortcuts in Lakehouses appear as tables and can be queried using Spark SQL directly.

Option D:

spark.sql("SELECT * FROM Lakehouse1.productline1.ResearchProduct")

Same issue as Option B, includes a schema path that does not exist.

Correct Choice

Since the shortcut to ResearchProduct was created inside Lakehouse1, and Spark SQL can query it directly, the correct syntax is:

spark.sql("SELECT * FROM Lakehouse1.ResearchProduct")

That matches Option A.

References

Microsoft Fabric Lakehouse – Shortcuts

Query data in a lakehouse using Spark SQL

Delta format support in Microsoft Fabric

XMLA endpoint connectivity (read/write) requires the workspace to be in Fabric (Premium) capacity.

A Pro workspace does not support XMLA connections.

Therefore, to enable XMLA for Model1, you must change the License mode from Pro to Premium/Fabric.

Correct answer: B

The command:

DESCRIBE DETAIL Customer

Returns metadata about the Delta table, such as format, schema, partitioning, size, number of files, and creation/modification timestamps.

However, it does not provide information about whether maintenance tasks (such as OPTIMIZE, VACUUM, or Z-Ordering) were performed on the table.

To check maintenance history, you would typically use:

DESCRIBE HISTORY Customer

which shows operations executed on the table (e.g., OPTIMIZE, VACUUM, MERGE).

Since DESCRIBE DETAIL does not satisfy the requirement, the solution does not meet the goal.

Lookup activity can execute a SQL query or stored procedure and retrieve a single row or dataset. The returned values can then be passed to downstream pipeline activities.

Switch is for branching logic, not executing procedures.

Append variable just adds values to a variable.

KQL activity executes Kusto queries, not SQL stored procedures.

Therefore, the correct choice is Lookup.

Requirements:

Revert to previous versions of Model1.

Identify differences between saved versions.

Publish from Power BI Desktop.

Edit item definition files with Visual Studio Code.

Analysis:

To meet version control and difference tracking requirements, you must use Git integration in the Fabric workspace.

To enable editing with VS Code, you must use PBIP format (Power BI Project), which saves report/model as a folder with definition JSON files (not PBIX).

PBIX is a single binary file, not suitable for Git versioning or editing definition files.

"Enable users to edit in service" is irrelevant.

Correct Answers:

A. Enable Git integration for Workspace1.

C. Save Model1 in Power BI Desktop as a PBIP file.

The df.show() method also does not meet the goal. It is used to show the contents of the DataFrame, not to compute statistical functions. References = The usage of the show() function is documented in the PySpark API documentation.

No, the REFRESH TABLE statement does not provide information on whether maintenance tasks were performed. It only updates the metadata of a table to reflect any changes on the data files. References = The use and effects of the REFRESH TABLE command are explained in the Spark SQL documentation.

Both a dataflow (A) and a Stored procedure activity in a pipeline (B) are capable of creating and populating a date dimension table. A dataflow can perform the transformation needed to create the date dimension, and it aligns with the preference for using low-code tools for data ingestion when possible. A Stored procedure could be written to generate the necessary date dimension data and executed within a pipeline, which also adheres to the technical requirements for the PoC.

To view column information like the one shown in the exhibit in Power Query, you need to select the options that enable profiling and display quality and distribution details. These are: A. Enable column profile - This option turns on profiling for each column, showing statistics such as distinct and unique values. B. Show column quality details - It displays the column quality bar on top of each column showing the percentage of valid, error, and empty values. E. Show column value distribution - It enables the histogram display of value distribution for each column, which visualizes how often each value occurs.

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")

VACUUM in Delta Lake removes unreferenced files older than a retention period (default 7 days, but configurable, e.g., 30 days).

It ensures that the transaction log remains consistent and ACID properties are maintained.

OPTIMIZE with V-Order or Z-Order improves file organization and query performance, but it does not remove unused files.

Deleting files manually from OneLake would break the transaction log consistency.

Correct answer: C. Run the VACUUM command.

 First blank: CALCULATE

 Second blank: _LYSales

Step 1 – Defining the variable

We want to calculate sales for the same period in the prior year. The correct function here is CALCULATE, because it allows applying a filter context modification (with SAMEPERIODLASTYEAR).

So:

VAR _LYSales =

CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Orders'[Order Date] ) )

Step 2 – Returning the value

We then need to RETURN the value of the variable. From the dropdown options, we select _LYSales.

So:

EVALUATE

VAR _LYSales =

CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Orders'[Order Date] ) )

RETURN

ROW ( "LastYearSales", _LYSales )

Note: In XMLA DAX queries, you usually need a table expression, so wrapping with ROW or SELECTCOLUMNS is common, but based on the provided answer box, just returning _LYSales is sufficient here.

Final Answer:

First blank: CALCULATE

Second blank: _LYSales

The issue: USERNAME() returns blank because Fabric uses Microsoft Entra (Azure AD) identities, not on-premises AD DS identities.

Adding user objects to sync via Entra Connect does not solve the issue by itself.

The correct fix is to ensure the UPN is synchronized and properly mapped (usually via Entra Connect attribute mappings).

What is needed is to ensure the userPrincipalName attribute is synced, not just “user objects.”

Correct answer: No.

We are designing a star schema for sales transactions. In a star schema:

Dimension tables contain descriptive attributes (e.g., Customer, Product, Date).

Fact tables contain measurable, quantitative data (e.g., SalesTransactionID, Quantity, UnitPrice).

Factless fact tables capture events without numeric measures.

Junk dimensions store miscellaneous low-cardinality attributes.

The requirement says dimension tables will use Type 2 Slowly Changing Dimensions (SCD) → so Customer and Date attributes belong to Dimension tables.

Field-by-field classification

CustomerCode

A business key (natural key) for the customer dimension.

Belongs in the Customer Dimension table.

Answer: Dimension.

CustomerName

A descriptive attribute of the customer.

Belongs in the Customer Dimension table.

Answer: Dimension.

SaleDate

A time attribute used for analysis.

Belongs in the Date Dimension table.

Answer: Dimension.

Final Answer:

CustomerCode → Dimension

CustomerName → Dimension

SaleDate → Dimension

The Spark engine will read only the 'SalesOrderNumber', 'OrderDate', 'CustomerName', 'UnitPrice' columns from Sales_raw.csv. - Yes

Removing the partition will reduce the execution time of the query. - No

Adding inferSchema='true' to the options will increase the execution time of the query. - Yes

The code specifies the selection of certain columns, which means only those columns will be read into the DataFrame. Partitions in Spark are a way to optimize the execution of queries by organizing the data into parts that can be processed in parallel. Removing the partition could potentially increase the execution time because Spark would no longer be able to process the data in parallel efficiently. The inferSchema option allows Spark to automatically detect the column data types, which can increase the execution time of the initial read operation because it requires Spark to read through the data to infer the schema.

The correct query to use in order to return data for the year 2023 that displays ProductID, ProductName, and has a summarized Amount greater than 10,000 is Option B. The reason is that it uses the GROUP BY clause to organize the data by ProductID and ProductName and then filters the result using the HAVING clause to only include groups where the sum of Amount is greater than 10,000. Additionally, the DATEPART(YEAR, SaleDate) = '2023' part of the HAVING clause ensures that only records from the year 2023 are included. References = For more information, please visit the official documentation on T-SQL queries and the GROUP BY clause at T-SQL GROUP BY.

The Vertipaq Analyzer tool (B) and querying the $system.discovered_STORAGE_TABLE_COLUMNS_IN_SEGMENTS dynamic management view (DMV) (C) can help identify which columns are frequently loaded into memory. Both methods provide insights into the storage and retrieval aspects of the semantic model. References = The Power BI documentation on Vertipaq Analyzer and DMV queries offers detailed guidance on how to use these tools for performance analysis.

Yes, the df.summary() method does meet the goal. This method is used to compute specified statistics for numeric and string columns. By default, it provides statistics such as count, mean, stddev, min, and max. References = The PySpark API documentation details the summary() function and the statistics it provides.

Step 1: Understand the scenario

Workspace1 contains a semantic model and two Power BI reports.

A Microsoft 365 Data Loss Prevention (DLP) policy (DLP1) already exists.

Requirement: Apply DLP1 to the workspace items.

In Microsoft Fabric and Power BI, DLP policies are enforced through Microsoft Purview Information Protection (MIP) sensitivity labels.

Step 2: Evaluate each option

A. Apply a master data endorsement to the semantic model.

Endorsements (promoted, certified) help users find and trust datasets but do not enforce DLP policies.

Not correct.

B. Apply a certified endorsement to the semantic model.

Certification improves discoverability and trust, but again, it does not trigger DLP policy enforcement.

Not correct.

C. Create a workspace identity.

Workspace identity (service principal) is used for connecting to external resources with managed identity.

It does not relate to DLP policy application.

Not correct.

D. Apply sensitivity labels to the semantic model and reports.

Sensitivity labels (from Microsoft Purview) integrate with Microsoft 365 DLP policies.

When sensitivity labels are applied to Power BI datasets, reports, dashboards, and workspaces, DLP1 can enforce protections such as preventing sharing or exporting sensitive data.

This is the correct answer.

Step 3: Correct Action

To apply DLP1 to Workspace1 items, you must:

Configure sensitivity labels in Microsoft Purview.

Apply those sensitivity labels to the semantic model and reports inside Workspace1.

The existing DLP1 policy will then govern data handling for those items.

References

Sensitivity labels in Power BI

Apply sensitivity labels to Power BI items

DLP policies in Microsoft Purview