DP-600 Implementing Analytics Solutions Using Microsoft Fabric Question and Answers

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.

DF1 will be deployed to Workspace_TEST: Yes

Data from Model1 will be deployed to Workspace_TEST: No

The scheduled refresh policy for Model1 will be deployed to Workspace_TEST: No

DF1 is a Dataflow Gen1 with a configured scheduled refresh policy, so it will be deployed to Workspace_TEST.

Data from Model1 will not be deployed as Model1's scheduled refresh policy is not configured, indicating it is not set up for deployment.

The scheduled refresh policy for Model1 will not be deployed since it is listed as "Not configured."

Requirements:

Parallel processing → PySpark runs distributed across Spark clusters.

Minimize duplication of data → OneLake shortcut + Spark queries avoid unnecessary copies.

Time series analysis, anomaly detection, visualization → can be done in PySpark within Fabric Notebooks.

pandas (C) is single-threaded and not efficient for billions of items.

Power BI visuals (A) are good for visualization but not for transformation or large-scale anomaly detection.

Correct: B.

Supports complex calculated columns (with aggregate functions, calculated tables, MDX hierarchies).

Minimizes page rendering times.

Step 1 – Choosing the Mode

Direct Lake → Best for near real-time queries, avoids duplication, but has limitations (e.g., some complex calculated columns, MDX user hierarchies are not fully supported).

DirectQuery → Sends queries to the source each time. It supports complex expressions but is slow (not optimal for minimizing page rendering times).

Import → Data is loaded into VertiPaq in-memory engine, supports full DAX capabilities, calculated tables, MDX hierarchies, and provides fastest query performance.

→ Correct choice: Import.

Step 2 – Choosing Query Caching

Capacity default → Relies on the workspace/capacity setting.

Off → Disables caching, which could slow down report rendering.

On → Ensures queries are cached for faster page rendering times.

→ Correct choice: On.

Final Answer:

Mode: Import

Query Caching: On

Comprehensive Detailed Explanation

We need to calculate the proportion of the total quantity of each sales order represented by each product within the sales order.

Step 1: Analyze the requirement

Table: SalesOrderDetail

Columns: OrderQty, ProductID, SalesOrderID

Each row = one product in a sales order.

Requirement: For each SalesOrderID, calculate what percentage each product contributes to the total order quantity.

This means we must:

Calculate the total order quantity per SalesOrderID.

Divide each product’s OrderQty by that total.

Express it as a percentage.

Step 2: Evaluate the options

A. Uses OVER(ORDER BY ProductID)

Wrong: ORDER BY does not segment data, it only defines sequence. We need grouping by sales order.

B. Uses OVER(PARTITION BY ProductID)

Wrong: This would calculate percentages per product across all sales orders, not per sales order.

C. Uses OVER(PARTITION BY SalesOrderID)

Correct: This computes the total OrderQty per SalesOrderID and divides each product’s quantity by that total. Exactly what the requirement asks.

D. Uses OVER(ORDER BY SalesOrderID)

Wrong: Again, ORDER BY just sequences rows, does not group them.

Step 3: Correct Query

SELECT

SalesOrderID,

ProductID,

OrderQty,

CAST(1. * OrderQty / SUM(OrderQty)

OVER(PARTITION BY SalesOrderID) * 100 AS DECIMAL(5,2))

AS PercentByProductID

FROM Sales.SalesOrderDetail;

Why Option C is Correct

PARTITION BY SalesOrderID ensures the denominator is the total quantity for the current order.

This produces the correct percentage breakdown of each product inside its sales order.

References

T-SQL Window Functions

Aggregate Functions with PARTITION BY

Microsoft Fabric Warehouse T-SQL Support

Scenario:

Semantic model contains a measure: TotalSalesAmount.

This measure returns sales revenue translated into a selected currency.

Requirement: Format the result using the correct currency symbol dynamically.

Analysis:

Dynamic format string in DAX is specifically designed for applying formats (like currency symbols) dynamically based on slicers or calculation context.

WINDOW function → used for windowing calculations, not formatting.

Linguistic schema → used for Q&A natural language, not for formatting.

Field parameter → used to swap fields/measures dynamically, not for formatting.

Correct Answer:

A. A dynamic format string

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.

There is no USEROBJECT() function in DAX. The correct functions available are USERNAME() and USERPRINCIPALNAME().

USERPRINCIPALNAME() is the one that returns the UPN directly.

Since the solution refers to a non-existent function (USEROBJECT()), it cannot solve the problem.

Correct approach: Update the measure to use USERPRINCIPALNAME(), not USEROBJECT().

A possible cause for the failure is that query folding is NOT occurring (D). Query folding helps optimize refresh by pushing down the query logic to the source system, reducing the amount of data processed and transferred, hence conserving resources. References = The Power BI documentation on incremental refresh and query folding provides detailed information on this topic.

Scenario Recap

You are working in Dataflow Gen2 inside a Fabric workspace.

Dataflow1 returns 500 rows.

You want to identify the minimum and maximum values for each column in the query results.

Key Concept: Column Profiling in Power Query (Fabric Dataflows)

In Power Query (used in Dataflows), profiling tools help you inspect and understand data. The following profiling features are available:

Column quality → Shows % valid, % error, % empty values.

Does not show min/max values.

Column distribution → Shows distinct and unique value distribution (frequency).

Does not show min/max values.

Column profile → Provides statistics for each column including:

Minimum value

Maximum value

Average, Standard deviation, Count, etc.

This is the feature required here.

Details pane → Displays the profiling results when profiling options (such as column profile) are enabled.

Correct Answer Breakdown

B. Show column profile in details pane

Must be selected to display min/max, averages, and distribution in the details pane.

C. Enable details pane

Required to actually display the profiling results. Without enabling the details pane, you cannot see column profiles.

E. Enable column profile

Enables the calculation of statistics (min, max, avg, etc.) for each column.

These three together give you the ability to see min and max values.

Why the Other Options Are Incorrect

A. Show column quality details

Shows only valid/empty/error statistics. No min/max.

D. Show column value distribution

Displays distinct/unique values and counts but does not calculate min/max.

Summary

To identify min and max values per column in a Fabric Dataflow, you must:

Enable the details pane

Enable column profile

Show column profile in details pane

Correct answers: B, C, E

References

Data profiling tools in Power Query

Column profiling options in Power Query

Fabric Dataflows Gen2 overview

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.

Step 1 – Read the Parquet file into a DataFrame

df = spark.read.parquet("abfss://fs1@storage1.dfs.core.windows.net/files/sales.parquet")

This correctly loads the Parquet data into Spark.

Step 2 – Write into the Lakehouse as a managed table

If we want the result to be registered as a Lakehouse table and automatically appear in Lakehouse Explorer, we must:

Write the data in delta format (because Fabric Lakehouse tables are Delta tables).

Save the table under the tables folder, not files.

So the correct code is:

df.write.mode("overwrite").format("delta").saveAsTable("tables/sales")

Final Answer:

Format: delta

SaveAsTable Path: tables/sales

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.

ï‚· 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.

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

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.

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

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

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.

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.

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.

Comprehensive Detailed Explanation

We need to query a KQL database table named Readings to generate output that includes both the current reading and the previous reading values and datetime for each row.

Step 1: Understanding Requirements

The output table shows:

City, Area, MeterReading, Datetime

PrevMeterReading, PrevDatetime

The prev() function in KQL is used to access the value of a column from the previous row (based on order).

Step 2: Query Construction

We start with filtering:

Readings

| where City == "Copenhagen"

| sort by Datetime

To add columns that calculate the previous reading and previous datetime, we use extend:

| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)

extend creates new columns.

prev() pulls the previous row’s values.

Finally, we only want specific columns in the output. For this, we use project:

| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime

Step 3: Completed Query

Readings

| where City == "Copenhagen"

| sort by Datetime

| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)

| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime

Why This is Correct

extend adds calculated columns.

prev() gives access to the previous row.

project selects only the required columns.

This matches exactly the table shown in the question.

References

Kusto Query Language - extend operator

Kusto Query Language - project operator

prev() function in KQL

The Direct Lake fallback behavior is set to: DirectQueryOnly

The query for the table visual is executed by using: DirectQuery

In the context of Microsoft Power BI, when using DirectQuery in Direct Lake mode, there is no caching of data and all queries are sent directly to the underlying data source. The Performance Analyzer tool shows the time taken for different operations, and from the options provided, it indicates that DirectQuery mode is being used for the visuals, which is consistent with the Direct Lake setting. DirectQueryOnly as the fallback behavior ensures that only DirectQuery will be used without reverting to import mode.

The pickupLongitude column has duplicate values. This can be inferred because the 'Distinct count' is 935 while the 'Count' is 1000, indicating that there are repeated values within the column. References = Microsoft Power BI documentation on data profiling could provide further insights into understanding and interpreting column statistics like these.

Upon Success: downstream runs only if the first activity succeeds.

Upon Failure: downstream runs only if the first activity fails.

Upon Skip: downstream runs if the activity was skipped.

Upon Completion: downstream runs regardless of whether the first activity succeeded or failed.

Since we want the second activity to run even if the first fails, the correct answer is Upon Completion.

When creating a shortcut to an Azure Data Lake Storage Gen2 account in a lakehouse, you should use the abfss (Azure Blob File System Secure) connection string and the dfs (Data Lake File System) endpoint. The abfss is used for secure access to Azure Data Lake Storage, and the dfs endpoint indicates that the Data Lake Storage Gen2 capabilities are to be used.

For the PeriodDate that returns the first day of the month for OrderDate, you should use DATEFROMPARTS as it allows you to construct a date from its individual components (year, month, day).

For the DayName that returns the name of the day for OrderDate, you should use DATENAME with the weekday date part to get the full name of the weekday.

The complete SQL query should look like this:

SELECT OrderID, CustomerID,

DATEFROMPARTS(YEAR(OrderDate), MONTH(OrderDate), 1) AS PeriodDate,

DATENAME(weekday, OrderDate) AS DayName

FROM Sales.Orders

Select DATEFROMPARTS for the PeriodDate and weekday for the DayName in the answer area.

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.

Comprehensive Detailed Explanation

When assigning permissions to roles in a Fabric workspace for analytics workloads, you must align permissions with the responsibilities and tools typically used by each persona:

1. Data Engineers

Their primary tasks include building and transforming data pipelines, as well as making datasets usable by others.

The appropriate permission is Build Reports on the default dataset, because this allows them to create and manage reports using the curated semantic model while preparing data for analysts and scientists.

2. Data Analysts

Analysts work heavily with Apache Spark for exploratory analysis, cleansing, and shaping data.

The correct permission is Read All Apache Spark, as this grants them the ability to query and analyze Spark-based data directly without elevated permissions that are unnecessary for their role.

3. Data Scientists

Data scientists often use SQL analytics endpoints to run queries, train models, and integrate data into machine learning workflows.

The correct permission is Read All SQL analytics endpoint data, since it enables direct programmatic access to the SQL endpoint, which is needed for advanced modeling and experimentation.

Summary of Selections:

DataEngineers → Build Reports on the default dataset

DataAnalysts → Read All Apache Spark

DataScientists → Read All SQL analytics endpoint data

References (Microsoft Fabric – DP-600 exam scope):

Workspace roles and permissions in Microsoft Fabric

SQL analytics endpoint in Fabric

Apache Spark in Microsoft Fabric

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