DP-600 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 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

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

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

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.

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.

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.

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.

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

Comprehensive Detailed Explanation

We have a table with:

CustomerID → unique ID

CustomerName → customer’s name

VersionDate → timestamp representing version history

The requirement:

Keep only the latest record per customer .

Ensure no new technical columns (like max version date) remain in the semantic model.

Step 1: Identify latest version per customer

We need to determine the latest VersionDate for each CustomerID .

Use Group By CustomerID and calculate Max of VersionDate .

This creates a mapping of CustomerID → latest VersionDate.

Step 2: Filter to keep only latest records

Join or filter original data so that only rows with VersionDate = Max(VersionDate) are retained.

This ensures that only the most recent record is kept per customer.

Step 3: Remove the helper column

The column holding Max VersionDate is only needed for filtering.

It must be removed because the requirement is that no new columns are loaded to the semantic model.

Step 4: Remove duplicates

As a final safeguard, remove duplicates by CustomerID , ensuring that each customer appears only once.

Final Ordered Actions

Group by CustomerID and calculate the max version date per customer ID.

Filter the query where the version date value equals the max version date value.

Remove the max version date column.

Remove duplicates based on CustomerID.

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.

We are optimizing model1 in Fabric with two very large columns:

TransactionKey

Cardinality: 2.5 billion

Size: 160 GB

Description: surrogate key for SalesTransaction fact table.

SaleDateTime

Cardinality: 6.8 million

Size: 120 GB

Description: datetime (to the second) of when a sale occurred.

Goal:

Reduce model size.

Improve refresh performance (Import mode).

Ensure datetime values remain available .

Analysis per column

1. TransactionKey (very high cardinality)

Surrogate keys with extremely high cardinality do not add analytical value in a semantic model because they are not used in measures or grouping.

They take a lot of memory and reduce refresh performance.

Best practice: remove the column from the model.

2. SaleDateTime

Needed to analyze transactions by date and time.

However, storing the full datetime at second-level cardinality causes huge memory usage.

To optimize: split the column into Date and Time columns.

Reduces cardinality significantly.

Preserves full datetime information (can recombine if needed).

Improves compression and refresh speed.

Final Answer:

TransactionKey: Remove the column.

SaleDateTime: Split the column.

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.

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.

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.

Scenario Recap

Fabric tenant with workspaces: Development, Test, QA, Production .

All workspaces are already in Premium Per User (PPU) mode (which is required for deployment pipelines).

Task: Set up a release pipeline to move through Dev → Test → QA → Production.

Step 1: Create a deployment pipeline

The first step is always to create a new deployment pipeline in the Fabric/Power BI service. This establishes the structure for Dev → Test → Prod (and optionally QA).

Step 2: Create the QA stage

By default, pipelines have 3 stages (Dev, Test, Prod) .

Since we need a QA stage in addition, we must explicitly add it as a stage to the pipeline.

Step 3: Assign workspaces

Once the pipeline structure is ready (with QA included), we assign each workspace to the corresponding stage:

Development → Development

Test → Test

QA → QA

Production → Production

Why Not the Other Actions?

Move each workspace to Pro license mode → Incorrect, because deployment pipelines require Premium capacity or PPU , and the workspaces already are in PPU mode.

Create a deployment rule → Deployment rules (e.g., parameter or data source rules) are optional for environment-specific changes, but not part of the initial required setup sequence.

References

Deployment pipelines overview

Assigning workspaces to deployment pipelines

You should set Repeat for the schedule to Weekly (C). This allows you to specify the pipeline to run on specific days of the week, in this case, every four hours on Mondays and Fridays. References = Scheduling options for data pipelines are available in the Azure Data Factory documentation, which includes details on configuring recurring triggers.

PBIR-Legacy format → No

Located in Power BI service → No

Opens in full edit mode → Yes

Comprehensive Detailed Explanation

We are analyzing a Power BI Report (PBIR) definition file in JSON format:

{

" version " : " 1.0 " ,

" byPath " : {

" path " : " ../Sales.Dataset "

}

}

}

Step 1: Is this PBIR-Legacy?

PBIR-Legacy format is used when referencing a dataset hosted in the Power BI service (cloud).

In this case, the JSON uses byPath , which references a local dataset file ( Sales.Dataset ) in the project folder , not a service dataset.

Therefore: No , it is not PBIR-Legacy.

Step 2: Where is the semantic model located?

The datasetReference points to ../Sales.Dataset .

That means the semantic model is stored locally in the project structure , not in the Power BI service.

Therefore: No , it is not located in the Power BI service.

Step 3: What happens when opening in Power BI Desktop?

Since the dataset reference is local ( byPath ), Power BI Desktop opens the semantic model in full edit mode (allowing schema, relationships, measures, etc. to be modified).

If it were PBIR-Legacy with a service model, only Live Connection mode would be available.

Therefore: Yes , Power BI Desktop opens it in full edit mode.

References

PBIR file structure in Power BI projects

Semantic model references: byPath vs byConnection

Final Answer:

PBIR-Legacy format → No

Located in Power BI service → No

Opens in full edit mode → Yes

The command:

DESCRIBE DETAIL Customer

Returns metadata about the Delta table, such as format, schema, partitioning, size, number of files, and creation/modification timestamps.

However, it does not provide information about whether maintenance tasks (such as OPTIMIZE , VACUUM , or Z-Ordering) were performed on the table.

To check maintenance history, you would typically use:

DESCRIBE HISTORY Customer

which shows operations executed on the table (e.g., OPTIMIZE, VACUUM, MERGE).

Since DESCRIBE DETAIL does not satisfy the requirement, the solution does not meet the goal.

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

Comprehensive Detailed Explanation

We need to query a KQL database table named Readings to generate output that includes both the current reading and the previous reading values and datetime for each row.

Step 1: Understanding Requirements

The output table shows:

City, Area, MeterReading, Datetime

PrevMeterReading, PrevDatetime

The prev() function in KQL is used to access the value of a column from the previous row (based on order).

Step 2: Query Construction

We start with filtering:

Readings

| where City == " Copenhagen "

| sort by Datetime

To add columns that calculate the previous reading and previous datetime , we use extend :

| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)

extend creates new columns.

prev() pulls the previous row’s values.

Finally, we only want specific columns in the output. For this, we use project :

| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime

Step 3: Completed Query

Readings

| where City == " Copenhagen "

| sort by Datetime

| extend PrevMeterReading = prev(MeterReading), PrevDatetime = prev(Datetime)

| project City, Area, MeterReading, Datetime, PrevMeterReading, PrevDatetime

Why This is Correct

extend adds calculated columns.

prev() gives access to the previous row.

project selects only the required columns.

This matches exactly the table shown in the question.

References

Kusto Query Language - extend operator

Kusto Query Language - project operator

prev() function in KQL

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

To implement row-level security (RLS) in a Fabric Warehouse (like in Azure Synapse or SQL Server):

You must define a predicate function (usually an inline table-valued function) that filters rows for each user → Function .

Then you bind that function to the table using a Security Policy → Security Policy .

A Database Role is used for group-based access control but not specifically for implementing RLS.

Stored procedures and constraints are not used for RLS.

Correct answers: A and D

In a Fabric Data Factory pipeline, to execute a stored procedure and make the returned values available for downstream activities, the Lookup activity is used. This activity can retrieve a dataset from a data store and pass it on for further processing. Here’s how you would use the Lookup activity in this context:

Add a Lookup activity to your pipeline.

Configure the Lookup activity to use the stored procedure by providing the necessary SQL statement or stored procedure name.

In the settings, specify that the activity should use the stored procedure mode.

Once the stored procedure executes, the Lookup activity will capture the results and make them available in the pipeline’s memory.

Downstream activities can then reference the output of the Lookup activity.

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.