Pass the Microsoft Certified: Fabric Analytics Engineer Associate DP-600 Questions and answers with ExamsMirror

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

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

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

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.

Step 1 – Understanding star schema design

Fact tables → contain transactional, numeric, and foreign key columns (e.g., TransactionID, Date, SalesAmount, ProductID).

Dimension tables → contain descriptive attributes and business keys (e.g., ProductID, ProductName, ProductColor).

Step 2 – Column classification

TransactionID → Fact (surrogate key for the fact table).

Date → Goes into a Date Dimension (not into DimProduct).

ProductID → Business key → belongs in DimProduct.

ProductColor → Descriptive attribute → belongs in DimProduct.

ProductName → Descriptive attribute → belongs in DimProduct.

SalesAmount → Fact (measure, belongs in FactSales).

Step 3 – Select DimProduct columns

So DimProduct should include:

ProductID

ProductName

ProductColor

That matches option A.

For the HighestSellingPrice, you should use the GREATEST function to find the highest value from the given price columns. However, T-SQL does not have a GREATEST function as found in some other SQL dialects, so you would typically use a CASE statement or an IIF statement with nested MAX functions. Since neither of those are provided in the options, you should select MAX as a placeholder to indicate the function that would be used to find the highest value if combining multiple MAX functions or a similar logic was available.

For the TradePrice, you should use the COALESCE function, which returns the first non-null value in a list. The COALESCE function is the correct choice as it will return AgentPrice if it's not null; if AgentPrice is null, it will check WholesalePrice, and if that is also null, it will return ListPrice.

The complete code with the correct SQL functions would look like this:

SELECT ProductID,

MAX(ListPrice, WholesalePrice, AgentPrice) AS HighestSellingPrice, -- MAX is used as a placeholder

COALESCE(AgentPrice, WholesalePrice, ListPrice) AS TradePrice

FROM Sales.Products

Select MAX for HighestSellingPrice and COALESCE for TradePrice in the answer area.

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.

For a Type 2 slowly changing dimension (SCD), you typically need to add the following types of columns that do not exist in the source system:

An effective start date and time (E): This column records the date and time from which the data in the row is effective.

An effective end date and time (A): This column indicates until when the data in the row was effective. It allows you to keep historical records for changes over time.

A surrogate key (C): A surrogate key is a unique identifier for each row in a table, which is necessary for Type 2 SCDs to differentiate between historical and current records.

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.