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

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.

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

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.

The df.explain() method does not meet the goal of evaluating data to calculate statistical functions. It is used to display the physical plan that Spark will execute. References = The correct usage of the explain() function can be found in the PySpark documentation.

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.

For a Type 1 SCD, you should include actions that update rows when non-key attributes have changed (A), and insert new records when the natural key is a new value in the table (D). A Type 1 SCD does not track historical data, so you always overwrite the old data with the new data for a given key. References = Details on Type 1 slowly changing dimension patterns can be found in data warehousing literature and Microsoft ' s official documentation.

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.

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.

When using Power BI Project (.pbip) with Git integration:

The .pbip project separates model metadata (schema, measures, relationships, etc.) from actual data values.

The localSettings.json file stores machine-specific and environment-specific settings, including data source credentials and local configurations. These should not be checked into version control to avoid exposing sensitive information or environment-specific configs.

Other options:

unappliedChanges.json → tracks unsaved changes, not data values.

model.bim → defines the semantic model schema (measures, relationships, metadata) and must be included in version control.

cache.abf → stores Analysis Services cache data, not part of source control.

Therefore, the correct file to exclude is localSettings.json.

The goal is to denormalize the data into a customer dimension with one row per distinct CustomerID, minimizing query processing time and resources. Since each customer is associated with a single SalesRegion and a single CustomerAddress, and all foreign key values are non-null, a left outer join is appropriate to ensure all customers from the Customer table are included, even if there are any unexpected missing addresses (though the problem states each customer has a single address). However, given the non-null foreign key constraint, an inner join could also work, but left outer join is safer and aligns with denormalization to ensure completeness.

The join condition should link the Customer table (C) with the CustomerAddress table (CA) using CustomerID, as this ensures each customer ' s address data is included.

The join with SalesRegion (SR) on SalesRegionID is already included in the query, and since each customer has a single SalesRegion, it fits the denormalization requirement.

The where CA.AddressType = ' Main Office ' filter ensures only the main office address is included, which is a valid constraint for a customer dimension.

Thus, the completed T-SQL statement would effectively denormalize the data while minimizing resource use by leveraging the existing relationships and constraints.

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.

To ensure that users assigned the SalesRegionManager role cannot see the Address column in the SalesAddress table, follow these steps in sequence:

Open the model in Tabular Editor.

Select the Address column in SalesAddress.

Set Object Level Security to None for SalesRegionManager.

Step 1 – Understanding star schema design

Fact tables → contain transactional, numeric, and foreign key columns (e.g., TransactionID, Date, SalesAmount, ProductID).

Dimension tables → contain descriptive attributes and business keys (e.g., ProductID, ProductName, ProductColor).

Step 2 – Column classification

TransactionID → Fact (surrogate key for the fact table).

Date → Goes into a Date Dimension (not into DimProduct).

ProductID → Business key → belongs in DimProduct.

ProductColor → Descriptive attribute → belongs in DimProduct.

ProductName → Descriptive attribute → belongs in DimProduct.

SalesAmount → Fact (measure, belongs in FactSales).

Step 3 – Select DimProduct columns

So DimProduct should include:

ProductID

ProductName

ProductColor

That matches option A.

Comprehensive Detailed Explanation

Workspace Roles in Microsoft Fabric (same as Power BI workspaces):

Viewer

Can only read/view content.

Cannot create or edit.

Contributor

Can create and write data (datasets, reports, semantic models).

Cannot manage permissions or workspace settings.

Member

Can create and edit content.

Can also add/remove people to the workspace (except Admins).

Cannot delete the workspace itself.

Admin

Full control: can configure settings, add/remove members, and delete the workspace.

Step 1: Group1 Requirements

Must be able to write data.

Must NOT be able to add members.

→ Contributor (can write but cannot manage membership).

Step 2: Group2 Requirements

Must be able to configure and maintain workspace settings.

→ Only Admin role allows configuring workspace-level settings.

→ Admin.

Step 3: Group3 Requirements

Must be able to write data.

Must be able to add members.

Must NOT be able to delete the workspace.

→ This matches the Member role.

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

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.

The suggested replacement does not improve performance. COUNTROWS( ' Order Item ' ) < 0 is always false (row counts cannot be negative), so the NOT will always evaluate to true. This means the filter condition becomes non-selective and would return all customers, not just those with orders. Therefore, it does not meet the optimization goal.

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

In Microsoft Fabric, the OneLake endpoint allows users with appropriate permissions to read data from a lakehouse, leveraging the unified data storage system. Since DBUser has been granted access to Lakehouse1, they can utilize the OneLake endpoint for reading data. For querying, the OneLake file explorer provides a user interface to interact with and query data within the lakehouse, aligning with DBUser ' s access rights without requiring broader tenant permissions.

To meet the requirements, developers should have Admin access to the deployment pipeline (B), Contributor access to the Development and Test workspaces (E), and Viewer access to the Production workspace (D). This setup ensures they can perform necessary actions in development and test environments without having the ability to affect production. References = The Power BI documentation on workspace access levels and deployment pipelines provides guidelines on assigning appropriate permissions.

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

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

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

The PoC is planned to be completed using a Fabric trial capacity, which implies that users involved in the PoC should be able to try paid features. However, this should be limited to specific security groups involved in the PoC to prevent the entire organization from accessing these features before the trial is proven successful (A). The ability for users to create Fabric items should also be enabled for specific security groups to ensure that only the relevant team members participating in the PoC can create items in the Fabric environment (E).

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

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.

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.

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.

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.

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