DP-700 Practice Questions

The high concurrency feature in Microsoft Fabric is specifically designed to allow multiple notebooks to share a single Apache Spark session. When a session is started with high concurrency enabled, subsequent notebooks from the same user can attach to this existing session instead of creating a new one. This significantly reduces the startup time for the second notebook (Notebook2) and optimizes the use of cluster resources, directly fulfilling the requirement to attach Notebook2 to the same session as Notebook1.

B. Enable dynamic allocation for the Spark pool.

Dynamic allocation manages the number of executors within a single Spark session based on workload, but it does not enable sharing the session across multiple notebooks.

C. Change the runtime version.

Changing the runtime version alters the Spark and library versions for the session but does not enable the mechanism for sharing a session between different notebooks.

D. Increase the number of executors.

Increasing executors scales the computational resources for a Spark session. This is a performance tuning measure and is unrelated to enabling session sharing.

1. Microsoft Fabric Documentation

"Get started with notebook session management in Microsoft Fabric": Under the "High-concurrency sessions" section

it states

"High-concurrency sessions create sessions that can be shared by notebooks of the same user. If you select the high-concurrency option

you can run multiple notebooks on a single session."

2. Microsoft Fabric Documentation

"Apache Spark session management in Microsoft Fabric": This document details session-level properties

including spark.ms.highConcurrency. It explains that setting this property to true enables the high-concurrency mode for session sharing.

3. Microsoft Azure Synapse Analytics Documentation

"Share Apache Spark pools in Azure Synapse Analytics": As Fabric's Spark engine is built upon Synapse

this documentation is relevant. It clarifies

"High-concurrency sessions allow multiple notebooks to run on the same session... By enabling high concurrency

you can reduce the Spark session start-up time and conserve cluster resources."

The VACUUM command is specifically designed to clean up and remove old, unreferenced data files from the underlying storage of a Delta Lake table. When data in a Delta Lake table is updated or deleted, the old data files are not immediately removed to support features like time travel. The VACUUM command purges these stale files that are older than the specified retention threshold, which directly addresses the requirement of handling old files in a safe, transactionally-aware manner. This is a crucial table maintenance task for managing storage costs and cleaning up data.

A. A Copy data activity is used for data movement and transformation. It does not have a built-in function to identify and remove old, unreferenced files from a source table.

C. The OPTIMIZE command is used for performance tuning by compacting many small files into fewer, larger files. It reorganizes existing data but does not delete old versions.

D. A Delete data activity performs a physical deletion of files or folders. Using it on a Delta Lake table is dangerous as it bypasses the transaction log, risking table corruption.

1. Microsoft Learn

Azure Synapse Analytics Documentation. "Use VACUUM on a Delta Lake table". This document states

"Recursively vacuum directories associated with the Delta table and remove data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold."

2. Delta Lake Documentation. "Table utility commands - vacuum". Section: vacuum. The official documentation specifies

"Remove files from the table that are no longer referenced by a Delta table and are older than the retention threshold."

3. Microsoft Learn

Azure Synapse Analytics Documentation. "What is Delta Lake?". Section: "Time travel". This document explains that Delta Lake maintains historical versions of data

which necessitates a mechanism like VACUUM to eventually remove old

unneeded file versions.

The performance degradation is directly linked to the significant growth of the fact table to 500 million rows. V-Order is a write-time optimization specific to the Microsoft Fabric storage engine that reorders data within Parquet files. This optimization improves data compression and creates a more efficient data layout, which significantly accelerates read operations by compute engines like Power BI (using Direct Lake) and the SQL endpoint. Applying V-Order to the large fact table will directly address the performance degradation caused by increased data volume, especially for scans and filters common in year-over-year analysis. This is a built-in feature that improves resource utilization, thus meeting the requirement to minimize operational costs.

A. Change the MD5 hash to SHA256: This would increase the size of the surrogate key from 128 bits to 256 bits, which would worsen join performance and increase storage costs.

B. Increase the capacity: This is a brute-force approach that would increase operational costs, directly violating one of the solution's primary requirements.

D. Modify the surrogate keys to use a different data type: While changing from a hash to an integer-based key is a data modeling best practice, V-Order is a more direct optimization for the physical data layout, which is the likely bottleneck as data volume grows. V-Order is a less disruptive, Fabric-specific feature designed to solve this exact problem.

E. Create views: Views are saved logical queries and do not alter the underlying physical storage or query execution plan. They will not improve the performance of inefficient queries on the base tables.

1. Microsoft Learn - Fabric Decision Guide: Data Warehouse vs. Lakehouse: In the "Performance" section

it states

"The warehouse (and the SQL endpoint of the lakehouse) uses a query engine that is optimized for fast queries on large volumes of data... It also includes built-in performance optimization

such as V-ordering of Parquet files..." This highlights V-Order as a core

built-in performance feature for Fabric warehouses.

2. Microsoft Learn - What is V-Order?: "V-Order is a write time optimization to the parquet file format that enables lightning-fast reads under the Microsoft Fabric compute engines

such as Power BI

SQL

and Spark... V-Order improves performance for all Microsoft Fabric engines and doesn't require any other setting from the user." This document explicitly states V-Order's purpose is to enable fast reads and improve performance across Fabric.

3. Microsoft Learn - Performance tuning with OPTIMIZE: "The OPTIMIZE command optimizes the layout of data in Delta Lake tables in Microsoft Fabric... For existing tables

you can enable V-Order by running the OPTIMIZE command." This shows the practical step to apply the V-Order optimization to the existing large table.

To implement a solution for book reviews, assuming the data resides in an external storage location like Azure Data Lake Storage (ADLS) Gen2, a shortcut is the most efficient method. In Microsoft Fabric, a OneLake shortcut acts as a symbolic link to the external data source. This approach allows you to access and analyze the book review data directly within your lakehouse without ingesting or duplicating it. This avoids data movement, reduces storage costs, and ensures that analyses are always performed on the most current data from the source system.

A. A Dataflow Gen2 is used for data ingestion and transformation. If the requirement is simply to access the data for analysis, a dataflow is an unnecessary step that adds complexity.

C. External data sharing is a feature used to share data from your Fabric workspace with external organizations, not for accessing external data within your workspace.

D. A data pipeline would be used to orchestrate data movement, typically by copying the data into the lakehouse. This creates a duplicate copy, which is less efficient than a shortcut.

1. Microsoft Fabric Documentation - OneLake shortcuts: "Shortcuts in Microsoft Fabric allow you to connect to existing data without having to directly copy it... A shortcut is a symbolic link

which creates a reference between locations. When you access data through a shortcut

the data remains in its original location." This directly supports using a shortcut to access external data without duplication.

Source: Microsoft Learn

OneLake shortcuts

"What is a shortcut?" section.

2. Microsoft Fabric Documentation - Data pipelines: "A data pipeline is used to orchestrate data movement and transformation." This highlights that a pipeline's primary role involves activities like copying

which is what a shortcut is designed to avoid.

Source: Microsoft Learn

Pipelines in Microsoft Fabric

"Overview" section.

3. Microsoft Fabric Documentation - Dataflow Gen2: "Dataflow Gen2 is the new generation of dataflows... you can use it to ingest

clean

and transform data." This confirms that a dataflow is for transformation

not just for referencing data.

Source: Microsoft Learn

What is Dataflow Gen2 in Microsoft Fabric?

"Introduction" section.

Row-Level Security (RLS) in a Microsoft Fabric warehouse is implemented by creating a security policy that applies a filter predicate to a table. This predicate logic, which determines whether a user can access a specific row, must be encapsulated within an inline table-valued function (TVF). The function evaluates each row against the current user's context (e.g., USERNAME()) and returns a result that the security policy uses to either show or hide the row. Therefore, a function is the essential T-SQL object required to define the core filtering logic for RLS.

A. STORED PROCEDURE: Stored procedures are used to execute pre-compiled sets of T-SQL statements, not to define the continuous, row-by-row filtering logic required by an RLS security policy.

B. CONSTRAINT: Constraints (e.g., CHECK, PRIMARY KEY) are used to enforce data integrity and rules on the data itself, not to control user access to specific rows.

C. SCHEMA: A schema is a container for organizing database objects and managing object-level permissions (e.g., GRANT SELECT ON SCHEMA::Sales), not for implementing row-level filtering.

1. Microsoft Fabric Documentation

"Row-level security in Fabric data warehousing": This official guide explicitly lists the steps for implementing RLS. Step 2 is: "Create an inline table-valued function to define the filter predicate." This confirms that a function is a required component.

Source: Microsoft Learn

learn.microsoft.com/en-us/fabric/data-warehouse/row-level-security

Section: "Implement row-level security in Fabric Warehouse".

2. Microsoft T-SQL Documentation

"CREATE SECURITY POLICY (Transact-SQL)": The official syntax and documentation for creating the security policy itself show that the ADD FILTER PREDICATE clause requires the name of a function. The documentation states

"The predicate is an inline table-valued function that is bound to the target table."

Source: Microsoft Learn

learn.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql?view=sql-server-ver16

Section: "Arguments"

subsection for [ FILTER PREDICATE = ].

3. Microsoft Azure Synapse Analytics Documentation

"Row-Level Security": As Fabric Warehouse is built upon the Synapse engine

this documentation is also applicable. It provides a complete example where a function (dbo.fnsecuritypredicate) is created first to hold the logic before the security policy is applied.

Source: Microsoft Learn

learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-row-level-security

Section: "Step 2: Create a function to filter the rows".

The user is required to perform several tasks, the first of which is to "Create a new domain for the sales department." According to Microsoft Fabric documentation, the creation of a new, top-level domain is a tenant-level operation. This action can only be performed by users assigned the Fabric admin, Power BI admin, or Global administrator role. While a domain admin can create subdomains and a domain contributor can assign workspaces, neither role has the necessary permissions to create the initial domain. Therefore, to accomplish all the specified tasks, the user must be assigned the Fabric admin role, which is the minimum role that satisfies all requirements.

A. workspace Admin: This role is scoped to a specific workspace and has no permissions to create or manage domains at the tenant level.

B. domain admin: This role can manage an existing domain, including creating subdomains, but it does not have the permission to create a new top-level domain.

C. domain contributor: This role has the most limited permissions, allowing only the assignment of workspaces to an existing domain, but not the creation of domains or subdomains.

1. Microsoft Learn

"Domains in Microsoft Fabric": Under the section "Create a domain

" the documentation explicitly states: "To create a domain

you must be a Fabric administrator (or higher)." This directly supports the requirement for the Fabric admin role to perform the first task.

2. Microsoft Learn

"Roles in domains": This section details the capabilities of domain-level roles. It clarifies that a Domain admin can "Create and update subdomains" and "Add/remove workspaces from the domain

" but these actions presuppose the domain already exists. The role does not include permissions for top-level domain creation.

3. Microsoft Learn

"Understand Fabric admin roles": This document outlines the responsibilities of the Fabric administrator. It states

"Fabric administrators are responsible for...setting up and managing domains." This confirms that domain setup is a key function of this role.

The fundamental purpose of a Microsoft Fabric deployment pipeline is to manage the lifecycle of content by promoting it between different stages (workspaces). When you deploy content from a source workspace to a target workspace that already contains items, the pipeline's default behavior is to overwrite the existing items in the target with the versions from the source. Since the items are already paired by name, simply running the deployment pipeline will automatically update the items in Workspace2 with the modified versions from Workspace1. This is the most direct method and requires the minimum effort.

A. Delete all the items in Workspace2, and then run deployPipeline1.

This is an unnecessary manual step. The deployment pipeline is designed to handle updates and overwrites automatically, making pre-deletion of items redundant.

B. Rename each item in Workspace2 to have the same name as the items in Workspace1.

The question states that items with the same name are already paired. Therefore, renaming is not required and would be a wasted effort.

C. Back up the items in Workspace2, and then run deployPipeline1.

While backing up content is a good practice, it is not a required action to perform the overwrite. This option adds an extra step that doesn't contribute to the core task.

1. Microsoft Fabric Documentation

"Understand the deployment process in Fabric": Under the section "Deploy content to an existing stage

" the documentation states

"When you deploy content to a stage that has existing content

Fabric compares the content in the source stage with the content in the target stage and then deploys the new or updated content to the target stage. Items of the same name and type are considered to be the same. The content in the target stage is overwritten with the content from the source stage." This directly supports the overwrite behavior by simply running the pipeline.

2. Microsoft Fabric Documentation

"Get started with deployment pipelines": In the "Deploy content" section

it describes the process of deploying from one stage to another. The documentation implicitly confirms that running the deployment is the standard procedure for updating content

stating

"When you're ready to deploy your content to the next stage

select the deploy button for that stage."

The OPTIMIZE command is specifically designed to improve the performance of queries on a Delta table by compacting small files into fewer, larger ones. This process, known as file compaction, directly addresses the requirement to "consolidate the underlying Parquet files." Consolidating files reduces the metadata overhead and improves I/O efficiency, leading to faster read operations. The command can be run on a table to rewrite the data files for better layout and size.

A. VACUUM: This command is used for garbage collection. It removes data files that are no longer referenced by a Delta table and are older than the retention threshold, but it does not consolidate active files.

B. BROADCAST: This is a join hint in Apache Spark. It instructs the query optimizer to broadcast a smaller table to all worker nodes for a join operation, which is unrelated to file management.

D. CACHE: This command loads a table or DataFrame into the memory of the Spark cluster. It improves performance for subsequent queries on the same data but does not alter or consolidate the physical files in storage.

1. Microsoft Learn Documentation: In the article "Delta Lake table optimization and V-Order

" it states

"The OPTIMIZE command is a file compaction operation. It's binary-compatible

meaning you can run OPTIMIZE on a Delta Lake table

and then read it with a lower Databricks Runtime version. It aims to produce evenly-sized files... coalescing many small files into fewer

larger ones."

Source: Microsoft Learn

"Delta Lake table optimization and V-Order"

Section: "What is OPTIMIZE?".

2. Delta Lake Official Documentation: The documentation for OPTIMIZE clearly defines its purpose: "Optimizes the layout of Delta Lake data. Optionally optimizes a subset of data or reorders data by column. If you do not specify reordering

bin-packing optimization is performed." Bin-packing is the process of consolidating small files.

Source: Delta.io Documentation

"OPTIMIZE"

Command syntax and description.

3. Delta Lake Official Documentation: The VACUUM command's purpose is contrasted with OPTIMIZE: "Recursively vacuum directories associated with the Delta table and remove data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold." This confirms it is for cleanup

not consolidation.

Source: Delta.io Documentation

"VACUUM"

Command syntax and description.

4. Apache Spark Documentation: The documentation for caching explains its function: "Spark SQL can cache tables using an in-memory columnar format... Spark SQL will scan only required columns and will automatically tune compression to minimize memory usage and GC pressure." This highlights its role in memory management

not physical file consolidation.

Source: Apache Spark™ 3.5.0 Documentation

"SQL Reference - Caching Data".

The most effective and precise way to meet all requirements is to use item-level sharing for the Lakehouse. Sharing Lakehouse1 directly with User1 and granting the Read all SQL endpoint data permission achieves the specific goals. This permission allows User1 to connect to the SQL analytics endpoint and run SELECT queries on all tables, fulfilling the primary requirement. Crucially, this method does not grant permissions to the Lakehouse explorer or the underlying files for Apache Spark access. Furthermore, because this is an item-specific permission, it does not grant any access to other items within Workspace1, such as notebooks or pipelines, thus adhering to the principle of least privilege.

B. Assigning the Viewer role for Workspace1 grants read-only access to all items in the workspace, which directly violates the requirement to prevent access to other items.

C. The Build reports on the default semantic model permission is primarily for creating Power BI content. It does not grant the necessary SELECT permissions on tables for direct ad-hoc SQL analysis.

D. Assigning the Member role for Workspace1 grants excessive permissions, including the ability to edit and publish content, which violates the requirement to prevent access to other items.

---

1. Microsoft Fabric Documentation

"Share your warehouse and manage permissions." This document details the specific permissions available when sharing a Lakehouse or Warehouse. It explicitly states: "Read all SQL endpoint data: This permission grants read access to the SQL analytics endpoint. This permission allows users to connect to the SQL analytics endpoint and read data from all the tables and views in the Lakehouse." This directly supports option A as the correct way to grant SQL-only read access.

Source: Microsoft Learn

learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions#share-a-warehouse-or-lakehouse

2. Microsoft Fabric Documentation

"Workspace roles." This document outlines the capabilities of each workspace role. It specifies that the Viewer role "can view all items in the workspace" and "can't connect to SQL analytics endpoints for lakehouses or warehouses." This confirms that options B and D are incorrect because they grant access to all workspace items

and the Viewer role (in option B) is insufficient on its own for SQL access.

Source: Microsoft Learn

learn.microsoft.com/en-us/fabric/get-started/roles-workspaces#workspace-roles

3. Microsoft Fabric Documentation

"Permissions for the SQL analytics endpoint in Microsoft Fabric." This page clarifies the relationship between workspace roles

item permissions

and SQL granular permissions. It reinforces that for SQL-only access without workspace-level visibility

direct sharing of the Lakehouse with the appropriate SQL endpoint permission (Read all SQL endpoint data) is the correct approach.

Source: Microsoft Learn

learn.microsoft.com/en-us/fabric/data-warehouse/sql-endpoint-security#permissions

Dataset1: A Dataflow Gen2 dataflow Dataflow Gen2 (which uses the Power Query engine) provides a built-in, low-code transformation called "Add Index Column". This feature directly satisfies the requirement to create a unique integer primary key starting from 1 with an increment of 1, minimizing development effort.

Dataset2: A notebook Notebooks are the primary Fabric item for handling semi-structured data at scale using Spark. Crucially, the notebook environment is designed for iterative development and allows developers to use code-based custom visualizations (using libraries like Plotly, Seaborn, or Matplotlib) to explore and understand the data during the transformation process.

Dataset3: A T-SQL statement The data resides in a Lakehouse, which exposes a SQL-T-SQL endpoint. T-SQL is the native language designed to perform complex analytical operations, such as row-based windowing functions (e.g., ROW_NUMBER(), RANK(), LAG(), LEAD()). This transformation can be applied efficiently during the bulk loading process using T-SQL commands like CREATE TABLE AS SELECT (CTAS) or INSERT...SELECT.

Microsoft. (2024, September 24). Add an index column (Power Query). Microsoft Learn. Retrieved October 29, 2025, from https://learn.microsoft.com/en-us/power-query/add-index-column

Details: This document details the "Index Column" feature in Power Query, which is the engine for Dataflow Gen2. It explicitly shows options to set the starting index and increment, matching the requirement for Dataset1.

Microsoft. (2024, June 13). Notebook visualization in Microsoft Fabric. Microsoft Learn. Retrieved October 29, 2025, from https://learn.microsoft.com/en-us/fabric/data-science/notebook-visualization

Details: This official documentation confirms that Fabric notebooks support "advanced visualization" using "popular open-source libraries like Plotly, Matplotlib, and seaborn," directly addressing the requirement for custom visuals in development mode for Dataset2.

Microsoft. (2024, October 22). Window functions (Transact-SQL). Microsoft Learn. Retrieved October 29, 2025, from https://learn.microsoft.com/en-us/sql/t-sql/functions/window-functions-transact-sql

Details: This documentation lists the T-SQL window functions (like ROW_NUMBER) available in the Fabric SQL analytics endpoint (used for both Lakehouse and Warehouse). This confirms T-SQL is the correct tool for the requirement in Dataset3.