Microsoft DP-600 Implementing Analytics Solutions Using Microsoft Fabric Exam Practice Test

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.

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

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.

Comprehensive Detailed Explanation

Step 1: Reviewing the provided DAX expression

SWITCH(

SELECTEDVALUE(Metric[Metric]),

"# of Customers", "#",

"Gross Margin %", "0.00%",

"#,###"

)

This is applied as a dynamic format string.

It changes the display formatting of the Value measure depending on the selected metric.

Step 2: Identify the feature

The screenshot shows Format = Dynamic in the Power BI Modeling tab.

This confirms that a dynamic format string has been applied.

So, for the first blank → dynamic format string.

Step 3: Identify the output formats

If the metric = # of Customers → format is "#" → whole numbers.

If the metric = Gross Margin % → format is "0.00%" → percentages.

Fallback → "#,###" → also whole numbers.

Thus, the measure supports both whole numbers and percentages.

So, for the second blank → percentages or whole numbers.

References

Dynamic format strings in Power BI

SWITCH function in DAX

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

To create a calculation item that changes the context from the selected date to month-to-date (MTD), the appropriate DAX expression involves using the CALCULATE function to alter the filter context and the DATESMTD function to specify the month-to-date context.

The correct completion for the DAX expression would be:

In the first dropdown, select CALCULATE.

In the second dropdown, select SELECTEDMEASURE.

This would create a DAX expression in the form:

CALCULATE(

SELECTEDMEASURE(),

DATESMTD('Date'[DateColumn])

)

Step 1: Understand the scenario

Workspace1 contains a semantic model and two Power BI reports.

A Microsoft 365 Data Loss Prevention (DLP) policy (DLP1) already exists.

Requirement: Apply DLP1 to the workspace items.

In Microsoft Fabric and Power BI, DLP policies are enforced through Microsoft Purview Information Protection (MIP) sensitivity labels.

Step 2: Evaluate each option

A. Apply a master data endorsement to the semantic model.

Endorsements (promoted, certified) help users find and trust datasets but do not enforce DLP policies.

Not correct.

B. Apply a certified endorsement to the semantic model.

Certification improves discoverability and trust, but again, it does not trigger DLP policy enforcement.

Not correct.

C. Create a workspace identity.

Workspace identity (service principal) is used for connecting to external resources with managed identity.

It does not relate to DLP policy application.

Not correct.

D. Apply sensitivity labels to the semantic model and reports.

Sensitivity labels (from Microsoft Purview) integrate with Microsoft 365 DLP policies.

When sensitivity labels are applied to Power BI datasets, reports, dashboards, and workspaces, DLP1 can enforce protections such as preventing sharing or exporting sensitive data.

This is the correct answer.

Step 3: Correct Action

To apply DLP1 to Workspace1 items, you must:

Configure sensitivity labels in Microsoft Purview.

Apply those sensitivity labels to the semantic model and reports inside Workspace1.

The existing DLP1 policy will then govern data handling for those items.

References

Sensitivity labels in Power BI

Apply sensitivity labels to Power BI items

DLP policies in Microsoft Purview

Remove the files: Run the VACUUM command on a schedule.

Combine the files: Set the optimizeWrite table setting. or Run the OPTIMIZE command on a schedule.

To remove files that are no longer used, the VACUUM command is used in Delta Lake to clean up invalid files from a table. To combine smaller files into larger ones, you can either set the optimizeWrite setting to combine files during write operations or use the OPTIMIZE command, which is a Delta Lake operation used to compact small files into larger ones.

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

User-defined aggregations (A) and query caching (C) are two features that can help reduce query execution time. User-defined aggregations allow precalculation of large datasets, and query caching stores the results of queries temporarily to speed up future queries. References = Microsoft Power BI documentation on performance optimization offers in-depth knowledge on these features.

In Microsoft Fabric, semantic models (formerly datasets) can be managed using external tools like Tabular Editor. To push or publish changes directly to a Fabric workspace from Tabular Editor, the workspace must support XMLA endpoint connectivity with read-write access.

Explanation of Each Option

A. For Workspace1, enable Git integration.

Git integration allows source control and versioning of items in a Fabric workspace. It is intended for lifecycle management of artifacts and does not provide the connectivity required for Tabular Editor to publish semantic model changes.

B. For Workspace1, create a managed private endpoint.

A managed private endpoint provides secure connectivity between Fabric and external data sources. It is not related to the ability to publish semantic models through XMLA.

C. For Model1, enable external sharing.

External sharing governs who can access the model outside the organization. This does not affect publishing workflows from developer tools.

D. For Capacity1, set XMLA Endpoint to Read Write.

This is the correct choice. The XMLA endpoint provides a programmatic connection point for external tools like Tabular Editor. By default, the XMLA endpoint is enabled only in read-only mode. Changing the setting to Read Write allows developers to push changes, update metadata, and deploy semantic models directly from Tabular Editor into the Fabric workspace.

Summary

To publish changes to a Fabric semantic model using Tabular Editor, the XMLA endpoint of the assigned capacity must be set to Read Write. Without this configuration, Tabular Editor can only connect in read-only mode and cannot deploy updates.

References

Use the XMLA endpoint in Microsoft Fabric

Manage capacities in Microsoft Fabric

Tabular Editor and XMLA endpoint

When creating a OneLake shortcut to Amazon S3, authentication uses the standard AWS access key ID + secret access key pair.

SAS token is for Azure Storage, not AWS.

Certificate thumbprint is not used for S3.

Access ID is incorrect naming; the correct term is access key ID.

Correct answers: B and D.

Scenario:

Dev = Workspace1 with Dataflow Gen2 (source = blob storage CSV).

Prod = Workspace2, different storage account.

Need: ensure deployed dataflow points to the production storage location.

Analysis:

Data source rules: used to remap data sources between environments (e.g., blob storage dev → blob storage prod).

Parameter rules: used when the data source location is parameterized (for example, a parameter storing the file path or connection string).

Best practice: use parameters in the dataflow for connection strings, then apply parameter rules in deployment pipelines.

In this case, since the requirement is about ensuring the reference updates correctly, only parameter rules are needed (not data source rules).

Correct Answer:

B. Create a parameter rule only.

When you need to return all rows that are present in both tables, you use a full outer join. This type of join combines the results of both left and right outer joins and returns all rows from both tables, with matching rows from both sides where available. If there is no match, the result is NULL on the side of the join where there is no match.

INSERT INTO … SELECT and SELECT INTO both physically copy all rows, which is heavy and not minimal.

CREATE TABLE AS SELECT (CTAS) also copies the data.

CREATE TABLE … AS CLONE OF creates a shallow clone in Fabric Warehouse, meaning it references the existing data without duplicating it, minimizing storage and copying.

Correct answer: B.

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.

Comprehensive Detailed Explanation

We need to create a computed column PriceRange based on the UnitPrice column in the dbo.Product table.

Step 1: Requirements

If UnitPrice = 0 → "Not for resale"

If UnitPrice < 50 → "Under $50"

If UnitPrice >= 50 AND UnitPrice < 250 → "Under $250"

Otherwise → "$250+"

This matches a CASE expression in T-SQL.

Step 2: CASE Expression Structure

The syntax is:

CASE

WHEN condition THEN result

WHEN condition THEN result

ELSE result

END

Step 3: Apply to Problem

SELECT

Item,

UnitPrice,

PriceRange = CASE

WHEN UnitPrice = 0 THEN 'Not for resale'

WHEN UnitPrice < 50 THEN 'Under $50'

WHEN UnitPrice >= 50 AND UnitPrice < 250 THEN 'Under $250'

ELSE '$250+'

END

FROM [Warehouse1].[dbo].[Product];

Step 4: Why This is Correct

CASE starts the conditional evaluation.

ELSE handles the default branch ($250+).

END closes the expression.

Meets all requirements exactly.

References

CASE expression in T-SQL

Computed columns in T-SQL

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

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

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

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.

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.

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

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

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.

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.

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.

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