DP-600 Implementing Analytics Solutions Using Microsoft Fabric Question and Answers

Yes, the DESCRIBE HISTORY statement does meet the goal. It provides information on the history of operations, including maintenance tasks, performed on a Delta table. References = The functionality of the DESCRIBE HISTORY statement can be verified in the Delta Lake documentation.

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 .

To meet the technical requireme nt 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).

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 sca n 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

Comprehensive Detailed Explanation

We are tasked with writing a T-SQL query to denormalize the tables Employee and Contract in a Fabric warehouse.

Requirements Breakdown

Include attributes from matching rows in the Contract table

This means we must join Employee and Contract on EmployeeID .

Ensure that all rows from the Employee table are preserved

This requires a LEFT OUTER JOIN from Employee to Contract. If an employee has no contract, we still want the employee record included.

Return the total number of employees per contract type for all contract types that have more than two employees

This means we need to:

Group by ContractType

Count distinct Empl oyeeID

Filter groups where that count > 2.

To filter aggregated results, we use HAVING , not WHERE.

Completed Query

WITH result AS (

SELECT

e.EmployeeID,

e.EmployeeName,

e.EmployeePosition,

c.ContractType,

c.Star tDate

FROM Employee AS e

LEFT OUTER JOIN Contract AS c

ON c.EmployeeID = e.EmployeeID

)

SELECT

ContractType,

COUNT ( DISTINCT EmployeeID) AS TotalEmployees

FROM result

GROUP BY ContractType

HAVING COUNT ( DISTINCT EmployeeID) > 2 ;

Why This is Correct

LEFT OUTER JOIN ensures all employees are included, regardless of contracts.

HAVING allows filtering aggregated groups by employee counts.

The query returns ContractType , StartDate (as requested in denormalization), and aggregated employee counts.

References

T-SQL JOINs in Microsoft Fabric

Aggregate functions and HAVING clause

Denormalization best practices in Fabric warehouses

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 t ime (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.

Finance department users can create and edit items in Workspace1 →

The correct role is Contributor .

To minimize effort, assign this role to the Microsoft Entra group (Group1) instead of assigning it to each user individually.

So answer A is correct, not B .

Workspace1 can securely access storagel (Azure Storage) to read and write data →

To connect a Fabric workspace to external resources securely, you use a Workspace identity (a managed identity for the workspace).

This allows Fabric items to authenticate to Azure Storage without embedding credentials.

So answer D is correct.

You are the only admin of Workspace1 →

By default, the workspace creator (you) is the admin. You do not need to explicitly reassign the admin role to yourself (so C is unnecessary).

Minimize administrative effort →

Assigning Contributor role to the group (A) is minimal effort compared to assigning it individually to each user (B).

Final Answer:

A. Assign the Contributor role to Group1

D. Create a workspace identity

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 the se 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 en vironment (E).

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 Se lections:

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

Both a dataflo w (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.

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 appropriat e pricing group. This view will standardize the pricing group logic across different databases and semantic models.

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, fulfill ing 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.

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, yo u 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.

For ingesting customer data into the data store in the AnalyticsPOC workspace, a dataflow (D) should be recommended. Dataflows are designed within the Power B I 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.

For the semantic model design required for the customer satisfaction report, the choices for data source authentication method and mode should be made b ased 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 authentica tion 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 n eeds 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 da ta. 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

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

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.

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

ï‚· 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 .

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.ResearchPro duct " )

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.ResearchProduc t " )

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.Res earchProduct " )

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 Explan ation

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

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 i ntegration (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 o f the Research division workspaces to use an Azure Repos repository.

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 .

Lookup activity can execute a SQL query or stored procedure and retrieve a single row or dataset. The returned values can then be passed to downstream pipeline activities .

Switch is for branching logic, not executing procedures.

Append variable just adds values to a variable.

KQL activity executes Kusto queries, not SQL stored procedures.

Therefore, the correct choice is Lookup .

You need to perform an inner join and ignore spaces in the values.

In Power Query, to handle joins where values may contain spaces or minor mismatches, you use fuzzy matching in the Merge Queries operation.

Append queries combines rows, not columns, so it does not apply here.

Lookup table is not required since the transformation can be handled natively in Power Query with fuzzy matching.

Correct solution: Merge the queries by using fuzzy matching .

When working with Power BI Desktop and Git integration for version contro l, report and semantic model definitions should be saved in the PBIX format. PBIX is the Power BI Desktop file format that contains definitions for reports, data models, and queries, and it can be easily saved and tracked in a version-controlled environmen t. The solution should minimize development and maintenance effort, and saving in PBIX format allows for the easiest transition from development to deployment, especially when using Azure Pipelines for CI/CD (continuous integration/continuous deployment) p ractices.

In the ROW_NUMBER() function, choose OVER (PARTIT ION BY CustomerID ORDER BY LastUpdated DESC) .

In the WHERE clause, choose WHERE X = 1 .

To select the most recent row for each customer ID, you use the ROW_NUMBER() window function partitioned by CustomerID and ordered by LastUpdated in descending order. Th is will assign a row number of 1 to the most recent update for each customer. By selecting rows where the row number ( X ) is 1, you get the latest update per customer.

References =

Use the OVER clause to aggregate data per partition

Use window functions

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.

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.

o add new data to the Delta table while meeting the specified requirements:

You should use the append mode to ensure that all new data is added to the table without affe cting the existing rows.

You should set the mergeSchema option to true to allow the schema of the Delta table to be updated with the new columns found in the DataFrame.

The completed code would look like this:

df.write.format( " delta " ).mode( " append " )

option( " mergeSchema " , " true " )

saveAsTable( " Lakehouse1.TableName " )