Microsoft DP-600 - Implementing Analytics Solutions Using Microsoft Fabric

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

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

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

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.

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.

When using XMLA endpoints to manage and update semantic models in Microsoft Fabric, the performance of write operations (such as processing, structural changes, or metadata deployments from Tabular Editor) is directly influenced by the storage format and how the model is persisted.

Why Option A is Correct

By default, Fabric semantic models use the Small semantic model storage format.

To improve write operations performance through XMLA, you must change the workspace setting to use the Large semantic model storage format.

The large format uses more efficient storage techniques, supports partitioning, and handles larger models with optimized write capabilities.

This setting is applied at the workspace level and impacts all semantic models within that workspace, including Model1.

This is explicitly documented in Microsoft’s guidance: Large semantic model storage format is required when using XMLA write operations for large or frequently updated models.

Why the Other Options Are Incorrect

B. Configure Model1 to use the Direct Lake storage format.

Direct Lake mode is designed for query performance (reading data directly from OneLake in delta format without import/duplication).

It improves query latency and freshness but does not improve XMLA write operations, which deal with model metadata and structural updates.

C. Delete any unused semantic models from Workspace1.

Deleting unused semantic models helps manage capacity and storage but does not increase the speed of XMLA endpoint write operations.

Workspace storage overhead does not directly impact the write throughput of XMLA operations.

D. Delete any unused columns from Model1.

Removing unused columns reduces the memory footprint and can improve query performance.

However, it does not directly improve the speed of XMLA write operations. The bottleneck in XMLA writes is tied to the storage format, not the model size alone.

Summary

To increase the speed of XMLA write operations on semantic models, you must enable the Large semantic model storage format at the workspace level. This setting ensures better handling of writes and metadata operations via the XMLA endpoint.

References

Large models in Power BI and Microsoft Fabric

Use the XMLA endpoint in Microsoft Fabric

Manage capacities in Microsoft Fabric

CTAS creates a new table from the results of a query, automatically inheriting the column types of the source tables. It is the simplest and most efficient way to create a point-in-time (PIT) table.

INSERT INTO requires a table to already exist.

CREATE MATERIALIZED VIEW keeps results up to date but is heavier and not required for PIT.

CLONE creates a shallow copy of an existing table, but we are joining two tables, so clone is not applicable.

Correct: A .