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

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

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.

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

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

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.

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.

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

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

The KEEPFILTERS function modifies the way filters are applied in calculations done through the CALCULATE function. It can be particularly beneficial to replace the FILTER function with KEEPFILTERS when the filter context is being overridden by nested CALCULATE functions, which may remove filters that are being applied on a column. This can potentially reduce execution time because KEEPFILTERS maintains the existing filter context and allows the nested CALCULATE functions to be evaluated more efficiently.

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.

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 create a diagram that contains only the Sales table and related tables, you should use the Model view (C) in Microsoft Power BI Desktop. This view allows you to visualize and manage the relationships between tables within your semantic model. References = Microsoft Power BI Desktop documentation outlines the functionalities available in Model view for managing semantic models.

You need to configure OneLake security for two users with the following requirements:

User1: Read all the Spark data

User2: Read all the SQL endpoint data

The available permissions are: Read, ReadAll, and ReadData.

Understanding the permissions:

Read → Grants access to metadata but not the data itself.

ReadData → Grants access to query data through SQL endpoints (SQL-based access).

ReadAll → Grants access to read data across all engines (including Spark and SQL endpoints).

Applying least privilege principle:

User1 (Spark access): Needs to read all the Spark data → Assign ReadAll because Spark requires this permission to access data in OneLake.

User2 (SQL endpoint access): Needs to read SQL endpoint data only → Assign ReadData, as this grants access to SQL endpoints without over-provisioning Spark access.

Final Answer:

User1: ReadAll

User2: ReadData

For the Customers table, where the requirement is to create a new version of the row, you would use:

Type 2 SCD: This type allows for the creation of a new record each time a change occurs, preserving the history of changes over time.

For the Products table, where the requirement is to overwrite the existing value in the latest row, you would use:

Type 1 SCD: This type updates the record directly, without preserving historical data.

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.

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 should complete the code as follows:

Table

NativeQuery

EnableFolding

In Power Query, using Table before the SQL statement ensures that the result of the SQL query is treated as a table. NativeQuery allows a native database query to be passed through from Power Query to the source database. The EnableFolding option ensures that any subsequent transformations that can be folded will be sent back and executed at the source database (Microsoft SQL Server engine in this case).

You should use the Parquet format (B) for the sales data files because it is optimized for performance with large datasets in analytical processing and create a shortcut in the Tables section (D) to facilitate SQL queries through the lakehouse's SQL endpoint. References = The best practices for working with file formats and shortcuts in a lakehouse environment are covered in the lakehouse and SQL endpoint documentation provided by the cloud data platform services.

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.

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

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

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

Requirement

Fabric workspace: Workspace1

Contains: Warehouse1, Model1, Report1

Task: Allow User1 to access a single table in Warehouse1.

Must follow principle of least privilege → grant only the exact permissions required.

Step 1: Evaluate Options

A. Assign object level permissions to User1 for Warehouse1.

In Fabric warehouses, you can grant permissions at the object level (table, schema, or column).

If the requirement is access to only one table, the correct approach is to grant SELECT permissions on that specific table.

This satisfies least privilege.

Correct. ✅

B. Assign the db_datareader role to User1 for Warehouse1.

db_datareader provides read access to all tables in the database/warehouse.

This violates least privilege.

Not correct.

C. Share Warehouse1 with User1.

Sharing grants access to the whole warehouse.

Too broad, not least privilege.

Not correct.

D. Assign the Viewer role to User1 for Workspace1.

Viewer role allows seeing all items in the workspace (warehouse, model, reports).

This would expose more than the single table.

Not correct.

Step 2: Correct Action

Use object-level permissions:

GRANT SELECT ON dbo.TableName TO [User1];

This ensures User1 can only query that specific table, nothing else.

References

Manage object-level security in Microsoft Fabric warehouses

Principle of least privilege in Fabric

Step 1: Requirement

We need to calculate total sales amount by OrderDate.

Total Sales Amount = Quantity × UnitPrice per row.

Then SUM over all rows per OrderDate.

Only include rows where the OrderDate matches a public holiday in Australia.

Step 2: Calculation Logic

If we select s.Quantity * s.UnitPrice, it only computes row-level values.

To calculate the total sales amount per OrderDate, we need:

SUM(s.Quantity * s.UnitPrice) AS TotalSalesAmt

Thus, the correct calculation: SUM(s.Quantity * s.UnitPrice)

Step 3: Join Type

We want to return only those sales that occurred on public holidays in Australia.

If we used a LEFT JOIN, we’d include all sales regardless of holiday.

A FULL OUTER JOIN would include non-matching rows, not required.

A CROSS JOIN would duplicate results unnecessarily.

A RIGHT OUTER JOIN would include holidays with no sales, which is not needed.

The correct choice is an INNER JOIN on matching dates.

Step 4: Final Query

SELECT

s.OrderDate,

SUM(s.Quantity * s.UnitPrice) AS TotalSalesAmt

FROM dbo.sales AS s

INNER JOIN dbo.publicholidays AS ph

ON s.OrderDate = ph.Date

WHERE ph.countryOrRegion = 'Australia'

GROUP BY s.OrderDate;

Why This is Correct

SUM(s.Quantity * s.UnitPrice) → ensures sales are aggregated correctly.

INNER JOIN → ensures only rows where OrderDate matches a holiday are included.

WHERE ph.countryOrRegion = 'Australia' → filters holidays to Australia only.

References

SUM (Transact-SQL)

JOIN operations in T-SQL

For users to create and publish custom Direct Lake semantic models using external tools, following the principle of least privilege, the actions to be included are enabling XMLA Endpoints (A), editing data models in Power BI service (C), and setting XMLA Endpoint to Read-Write in the capacity settings (D). References = More information can be found in the Admin portal of the Power BI service documentation, detailing tenant and capacity settings.

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.

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

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.