Practice Free DP-600 Implementing Analytics Solutions Using Microsoft Fabric Exam Questions Answers With Explanation

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

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.

Comprehensive Detailed Explanation

Requirement

Tenant: Fabric tenant Tenant1

Data target: Lakehouse1

Data source: CSV file in an Azure Storage account (outside of Fabric)

Requirement: Minimize development effort

Step 1: Understand options

A. Dataflow Gen2

Used for ETL (transformations, cleansing, ingestion).

More effort because you need to build Power Query transformations.

Not minimal effort for simply exposing a file.

B. Shortcut

Fabric supports creating shortcuts to external data sources (OneLake, ADLS Gen2, Azure Blob, Dataverse, etc.).

Shortcut directly exposes external files/tables inside the lakehouse without copying or moving data .

This is the lowest-effort solution to access external CSV.

C. Copy job

Pipelines have a copy activity to physically copy data into Fabric.

Requires setup and execution. More development effort than a shortcut.

D. Pipeline

Used for orchestrating complex ETL workflows.

Overhead for this scenario.

Step 2: Correct Answer

Since the goal is simply to access a CSV in external storage with minimal development effort , the right solution is:

B. a shortcut

References

Shortcuts in Microsoft Fabric OneLake

Dataflow Gen2 overview

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.

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

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.

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

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

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

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.

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

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

No, the REFRESH TABLE statement does not provide information on whether maintenance tasks were performed. It only updates the metadata of a table to reflect any changes on the data files. References = The use and effects of the REFRESH TABLE command are explained in the Spark SQL 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.

To convert CSV files into the delta format with Z-Order optimization enabled, you should use the Optimize feature (D) from Lakehouse Explorer. This will allow you to optimize the file organization for the most efficient querying. References = The process for converting and optimizing file formats within a lakehouse is discussed in the lakehouse management documentation.

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.

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.

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.

The results will form a hierarchy of folders for each partition key. - Yes

The resulting file partitions can be read in parallel across multiple nodes. - Yes

The resulting file partitions will use file compression. - No

Partitioning data by columns such as year, month, and day, as shown in the DataFrame write operation, organizes the output into a directory hierarchy that reflects the partitioning structure. This organization can improve the performance of read operations, as queries that filter by the partitioned columns can scan only the relevant directories. Moreover, partitioning facilitates parallelism because each partition can be processed independently across different nodes in a distributed system like Spark. However, the code snippet provided does not explicitly specify that file compression should be used, so we cannot assume that the output will be compressed without additional context.

References =

DataFrame write partitionBy

Apache Spark optimization with partitioning

To view column information like the one shown in the exhibit in Power Query, you need to select the options that enable profiling and display quality and distribution details. These are: A. Enable column profile - This option turns on profiling for each column, showing statistics such as distinct and unique values. B. Show column quality details - It displays the column quality bar on top of each column showing the percentage of valid, error, and empty values. E. Show column value distribution - It enables the histogram display of value distribution for each column, which visualizes how often each value occurs.

The Vertipaq Analyzer tool (B) and querying the $system.discovered_STORAGE_TABLE_COLUMNS_IN_SEGMENTS dynamic management view (DMV) (C) can help identify which columns are frequently loaded into memory. Both methods provide insights into the storage and retrieval aspects of the semantic model. References = The Power BI documentation on Vertipaq Analyzer and DMV queries offers detailed guidance on how to use these tools for performance analysis.

We are designing a star schema for sales transactions. In a star schema:

Dimension tables contain descriptive attributes (e.g., Customer, Product, Date).

Fact tables contain measurable, quantitative data (e.g., SalesTransactionID, Quantity, UnitPrice).

Factless fact tables capture events without numeric measures.

Junk dimensions store miscellaneous low-cardinality attributes.

The requirement says dimension tables will use Type 2 Slowly Changing Dimensions (SCD) ? so Customer and Date attributes belong to Dimension tables .

Field-by-field classification

CustomerCode

A business key (natural key) for the customer dimension.

Belongs in the Customer Dimension table.

Answer: Dimension .

CustomerName

A descriptive attribute of the customer.

Belongs in the Customer Dimension table.

Answer: Dimension .

SaleDate

A time attribute used for analysis.

Belongs in the Date Dimension table.

Answer: Dimension .

Final Answer:

CustomerCode ? Dimension

CustomerName ? Dimension

SaleDate ? Dimension

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.