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

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

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.

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.

User1 must be able to:

Create a new domain.

Create two subdomains.

Assign Workspace1 to subdomain1.

Assign Workspace2 to subdomain2.

Key Role Definitions in Fabric:

Domain admin: Can manage domain settings, add/remove workspaces, manage contributors.

Domain contributor: Can contribute to an existing domain but cannot create a domain.

Workspace admin: Only controls permissions/settings inside a workspace, not domains.

Fabric admin: Tenant-wide admin, broader than required.

Requirement: Follow least privilege.

To create a new domain → Requires Domain admin.

To assign workspaces to subdomains → Domain admin rights are sufficient.

Fabric admin is excessive and breaks least privilege.

Correct Answer:

A. Domain admin

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

 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

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

The correct code to populate the results DataFrame with minimal data shuffling is Option A. Using the broadcast function in PySpark is a way to minimize data movement by broadcasting the smaller DataFrame (customers) to each node in the cluster. This is ideal when one DataFrame is much smaller than the other, as in this case with customers. References = You can refer to the official Apache Spark documentation for more details on joins and the broadcast hint.

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

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.

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.

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.

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

 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

Comprehensive Detailed Explanation

Step 1: Requirement

We need to compare the average miles per trip for:

Statutory holidays (when IsHoliday = 1)

Non-statutory holidays (when IsHoliday = 0)

Step 2: Formula for average miles per trip

Average miles per trip = total miles ÷ number of trips

Total miles → SUM(t.tripDistance)

Number of trips → COUNT(t.tripID)

So the calculation is:

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

Step 3: Grouping

We need a comparison by holiday status.

So we must group the results by:

GROUP BY d.IsHoliday

This ensures we get two rows: one for IsHoliday = 1 and one for IsHoliday = 0.

Step 4: Final Query

SELECT

d.IsHoliday,

(SUM(t.tripDistance) / COUNT(t.tripID)) AS MilesPerTrip

FROM DimDate d

INNER JOIN Trips t ON d.DateID = t.DateID

GROUP BY d.IsHoliday;

Why This is Correct

The formula ensures average miles per trip.

Grouping ensures comparison between holidays vs non-holidays.

Efficient aggregation, minimal computation.

References

Aggregate functions in T-SQL

GROUP BY clause

In Microsoft Fabric, a shortcut lets you reference data from another workspace’s Lakehouse or external storage without duplicating the data.

A view would only apply within the same database/Lakehouse, not across workspaces.

A dataflow transforms and loads data but creates a new copy.

A managed table is a physical table stored in the Lakehouse itself, not a cross-reference.

So, to query dbo.Sales (Lakehouse1) and dbo.Customers (Lakehouse2) in a single SQL query, you use shortcuts.

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.

Before specifying the partition column in the Destination settings of the Copy activity, you should set Mode to Append (A). This will allow the Copy activity to add data to the table while taking the partition column into account. References = The configuration options for Copy activities and partitioning in Azure Data Factory, which are applicable to Fabric dataflows, are outlined in the official Azure Data Factory documentation.

A replica of dbo.Sales is created in the test schema by copying the metadata only. - Yes

Additional schema changes to dbo.FactSales will also apply to test.FactSales. - No

Additional data changes to dbo.FactSales will also apply to test.FactSales. - No

The CREATE TABLE AS CLONE statement creates a copy of an existing table, including its data and any associated data structures, like indexes. Therefore, the statement does not merely copy metadata; it also copies the data. However, subsequent schema changes to the original table do not automatically propagate to the cloned table. Any data changes in the original table after the clone operation will not be reflected in the clone unless explicitly updated.

References =

CREATE TABLE AS SELECT (CTAS) in SQL Data Warehouse

We need to enable version control for a Fabric workspace (Workspace1) and ensure that Model1 (a semantic model) is added to an Azure DevOps repository.

Key facts:

Workspace1 is in Pro mode. To enable Git integration, the workspace must be assigned to a Fabric capacity.

Git integration steps in Fabric:

Assign the workspace to a Fabric capacity.

Connect the workspace to a Git provider (e.g., Azure DevOps or GitHub).

Sync the workspace with the repository (to push artifacts like semantic models).

Branch policies and deployment pipelines are not needed for simply enabling version control.

Correct Sequence:

Assign Workspace1 to a Fabric capacity.

Connect Workspace1 to a Git provider.

Sync Workspace1 with the repository.

Final Answer:

Step 1 → Assign Workspace1 to a Fabric capacity

Step 2 → Connect Workspace1 to a Git provider

Step 3 → Sync Workspace1 with the repository

When working with Power BI Desktop and Git integration for version control, report and semantic model definitions should be saved in the PBIX format. PBIX is the Power BI Desktop file format that contains definitions for reports, data models, and queries, and it can be easily saved and tracked in a version-controlled environment. The solution should minimize development and maintenance effort, and saving in PBIX format allows for the easiest transition from development to deployment, especially when using Azure Pipelines for CI/CD (continuous integration/continuous deployment) practices.

The Table.Max function should be used in a Power Query step to identify the maximum values for the numeric columns. This function is designed to calculate the maximum value across each column in a table, which suits the requirement of finding maximum values for numeric columns. References = For detailed information on Power Query functions, including Table.Max, please refer to Power Query M function reference.

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.

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

For the semantic model design required for the customer satisfaction report, the choices for data source authentication method and mode should be made based on security and performance considerations as per the case study provided.

Authentication method: The data should be accessed securely, and given that row-level security (RLS) is required for users executing T-SQL queries, you should use an authentication method that supports RLS. Service principal authentication is suitable for automated and secure access to the data, especially when the access needs to be controlled programmatically and is not tied to a specific user's credentials.

Mode: The report needs to show data as soon as it is updated in the data store, and it should only contain data from the current and previous year. DirectQuery mode allows for real-time reporting without importing data into the model, thus meeting the need for up-to-date data. It also allows for RLS to be implemented and enforced at the data source level, providing the necessary security measures.

Based on these considerations, the selections should be:

Authentication method: Service principal authentication

Mode: DirectQuery

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.

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

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