DP-700 Hard Questions — 25 Difficult Questions

Question 1

You are using Dataflow Gen2 to ingest data from multiple sources. You need to perform a complex transformation that involves merging data from a SQL database and a flat file based on a common key. Which step should you take to achieve this efficiently?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Use the Merge transformation. In Dataflow Gen2, the 'Merge' transformation allows users to join tables from different sources within the Power Query interface. By selecting the common key, you can combine datasets into a single model for downstream processing. This approach is highly efficient as it leverages the underlying mashup engine to perform the heavy lifting, ensuring the transformation is processed in a unified, maintainable data pipeline.

Answer

Write a custom Python script

Answer

Create a temporary SQL view

Answer

Export both to CSV first

Question 2

You are auditing data access in a Microsoft Fabric workspace and notice unexpected 'Access Denied' errors in the OneLake logs for a service principal that should have read-only access. The service principal has the 'Viewer' role in the workspace. What is the most likely cause?

Accepted Answer

OneLake data access control (preview) is restricting specific folders.. OneLake security is governed by both workspace roles and item-level permissions. If 'OneLake data access control' is enabled for a Lakehouse, the 'Viewer' role alone may not be sufficient if specific folder-level permissions have not been granted. Understanding the intersection of these security layers is critical for troubleshooting access issues in complex environments.

Answer

The service principal needs the 'Contributor' role to read data.

Answer

The service principal has exceeded its OneLake throughput quota.

Answer

OneLake does not support service principal authentication.

Question 3

You are managing a Microsoft Fabric capacity. You need to identify which users are consuming the most compute resources and determine if any throttled requests are occurring. Which TWO tools should you use?

Accepted Answer

Fabric Capacity Metrics app. Monitoring capacity health requires a combination of real-time metrics and long-term diagnostic logs. The Fabric Capacity Metrics app provides granular visual insights into capacity utilization and throttling, while the Log Analytics workspace offers detailed diagnostic logs for query-level analysis. Using both tools allows engineers to correlate high-level capacity health with specific user-driven workloads that may trigger system-wide throttling or degradation.

Answer

Microsoft Purview

Answer

Service Health Dashboard

Answer

Power BI Desktop Performance Analyzer

Question 4

Refer to the exhibit. An administrator is reviewing the properties of a Lakehouse via the Fabric REST API. The property 'hasManagedPrivateEndpoint' is set to true. What does this indicate about the security configuration for this specific item?

Accepted Answer

The Lakehouse uses a private link to connect to its underlying data source.. Managed Private Endpoints in Fabric allow for secure communication between Fabric and other Azure services without exposing data to the public internet. This is a key feature for high-security environments. When this property is true, it signifies that the Lakehouse is configured to use a private connection to its data source, enhancing the overall security posture.

Answer

The Lakehouse is only accessible from within a specific Azure VNET.

Answer

The Lakehouse has been shared with an external guest user.

Answer

OneLake shortcuts are disabled for this specific Lakehouse.

Question 5

You are implementing a security model where users should only see rows matching their assigned department. Which Fabric feature enables this?

Accepted Answer

Row-Level Security (RLS). Row-Level Security (RLS) is essential for ensuring data compliance, especially when sharing reports across different organizational departments. By defining security predicates, data engineers can control access at the record level. This ensures that sensitive data is only accessible to authorized users, which is a fundamental requirement for secure data sharing and analytics governance in Microsoft Fabric.

Answer

Object-Level Security (OLS)

Answer

Workspace Permissions

Answer

Data Masking

Question 6

You are configuring a Power BI DirectLake connection to a Fabric Lakehouse. Which THREE conditions must be met to ensure DirectLake mode functions as intended?

Accepted Answer

The data must be stored in Delta Lake format.. Direct Lake mode functions by loading Delta Lake (Parquet) files directly from OneLake into the Power BI engine memory without a traditional refresh. For this to work: 1) The data must be in Delta Lake format (including the Delta log), 2) The workspace must be assigned to a Fabric capacity (F-SKU) or a Power BI Premium capacity (P-SKU), and 3) The semantic model must reside in the same workspace as the Lakehouse or Warehouse source. While cross-workspace support has been introduced in some specific scenarios, the standard architectural requirement for Direct Lake (and the default semantic model) remains the same workspace.

Answer

The connection must be configured to use the Gateway.

Answer

The data must be cached in the Power BI Service using Import mode.

Question 7

Your organization wants to automate the deployment of Fabric items using a CI/CD pipeline in Azure DevOps. You need to programmatically trigger the deployment of a Fabric workspace from the 'Development' stage to the 'Test' stage in a deployment pipeline. Which approach should you use?

Accepted Answer

Use the Fabric REST API 'Deploy' endpoint for pipelines.. Microsoft Fabric provides a REST API that allows for the programmatic management of deployment pipelines. This enables integration with external CI/CD tools like Azure Pipelines or GitHub Actions. By using the 'Deploy' API endpoint, organizations can automate their release process, ensuring that changes are moved through the lifecycle stages based on successful tests or approvals in their DevOps tooling.

Answer

Write a Spark notebook that uses 'mssparkutils' to move items.

Answer

Manually click the 'Deploy' button in the Fabric portal for every release.

Answer

Configure a 'Data Factory' pipeline with a 'Copy' activity to move metadata.

Question 8

You are optimizing a Direct Lake semantic model in Power BI that connects to a Fabric Lakehouse. You notice that some queries are falling back to 'DirectQuery' mode instead of staying in 'Direct Lake' mode. What is the most likely cause for this fallback?

Accepted Answer

The semantic model contains a calculated column defined using DAX.. Calculated columns are supported in Direct Lake mode. The fallback to DirectQuery occurs when a model uses features that cannot be evaluated by the Direct Lake engine, such as calculated tables, certain DAX functions that require the VertiPaq engine (like those involving complex relationships or specific time intelligence), or when the model exceeds the memory limit for the Direct Lake cache. Calculated columns themselves do not force a fallback to DirectQuery.

Answer

The Lakehouse has too many OneLake shortcuts to external Azure regions.

Answer

The capacity has 'Bursting' enabled, which interferes with memory caching.

Answer

The user viewing the report has only 'Viewer' permissions on the workspace.

Question 9

A data engineering team is setting up Git integration for a Microsoft Fabric workspace. They need to ensure that the synchronization process is robust and follows best practices for collaborative development. Which TWO actions should the team perform to successfully manage version control for Fabric items? (Choose TWO)

Accepted Answer

Connect the workspace to an Azure DevOps Git repository and branch.. Git integration in Fabric requires both a connection to a supported remote repository and an understanding of how workspace items are serialized into metadata. Proper synchronization involves committing local workspace changes to the remote branch and managing conflicts through standard Git workflows. This process bridges the gap between low-code workspace editing and professional software development practices for data teams.

Answer

Manually export item .bim files and upload them to a GitHub repository.

Answer

Assign the Workspace Admin role to the Service Principal used for Git.

Answer

Convert all Lakehouses to Warehouses to enable Git compatibility.

Question 10

A large enterprise wants to implement a 'Hub and Spoke' data architecture in Microsoft Fabric. They need to share a validated 'Gold' Lakehouse from a central workspace with multiple departmental workspaces without duplicating the underlying storage in OneLake. What is the most effective method to achieve this?

Accepted Answer

Use OneLake shortcuts in departmental Lakehouses.. OneLake shortcuts allow for the virtualization of data across different workspaces and items without moving or copying the data. This supports a 'single source of truth' architecture where data is managed centrally but consumed locally. Using shortcuts minimizes storage costs, reduces data latency, and simplifies the management of data lineage across the entire Fabric tenant.

Answer

Create a Data Pipeline to copy Delta tables daily.

Answer

Export the central Lakehouse as a template and re-import it.

Answer

Configure cross-workspace Power BI DirectQuery models.

Question 11

Refer to the exhibit. Given the retry policy configuration, what happens if the source SQL database is temporarily unavailable during the first attempt?

Accepted Answer

The pipeline retries 3 times with 60-second intervals.. The retry policy defined in the activity configuration instructs the Fabric pipeline to automatically attempt the copy operation again after the specified interval. This is a critical feature for building resilient pipelines that can gracefully handle transient network errors or brief service outages without failing the entire job. It ensures higher pipeline success rates and reduces the need for manual monitoring and intervention in complex, real-world cloud environments.

Answer

The pipeline fails immediately.

Answer

The pipeline retries indefinitely.

Answer

The pipeline executes the next activity immediately.

Question 12

Refer to the exhibit. You are receiving OOM errors in your Fabric Notebook. What is the best way to optimize the memory usage for this task?

Accepted Answer

Enable AQE (Adaptive Query Execution) to dynamically adjust partitions.. The explanation discusses data skew, broadcasting, and salting, but the correct answer (B) is about Adaptive Query Execution (AQE). While AQE can help with skew, the explanation should explicitly mention how AQE dynamically coalesces small partitions and optimizes join strategies to prevent OOM errors, rather than focusing on manual techniques like salting.

Answer

Increase the number of partitions to 10,000.

Answer

Upgrade the Fabric capacity to the highest SKU.

Answer

Use the .collect() method before writing the output.

Question 13

Your organization wants to implement near real-time data ingestion from an Azure SQL Database into Fabric for reporting. You must minimize the impact on the source database's performance and avoid manual pipeline scheduling. Which solution should you implement?

Accepted Answer

Fabric Mirroring for Azure SQL Database.. Mirroring in Fabric provides a low-latency, continuous synchronization path from supported databases. It uses the source database's transaction log to capture changes (CDC), which is much more efficient than traditional polling and does not require the user to manage complex schedules or orchestration pipelines to keep data synchronized.

Answer

A Pipeline with a Copy Activity using a 'Tumbling Window' trigger.

Answer

A Dataflow Gen2 with incremental refresh enabled.

Answer

A Spark Notebook using the 'JDBC' connector in a loop.

Question 14

A data engineering team is using a service principal to automate the deployment and management of Fabric items via the REST API. Which THREE of the following actions must be completed to enable a service principal to work with Fabric APIs?

Accepted Answer

Register an application in Microsoft Entra ID.. Using service principals for automation is a standard practice in enterprise environments. It requires a specific set of configuration steps across both Microsoft Entra ID and the Fabric Admin portal. Ensuring all these steps are completed is necessary for successful authentication and authorization of the automated scripts.

Answer

Grant the service principal the 'Global Administrator' role in Entra ID.

Answer

Assign a Power BI Pro license directly to the service principal.

Question 15

You are implementing a multi-workspace architecture in Fabric. You need to enable a 'Managed Identity' for a specific Fabric workspace to allow it to securely access an Azure Data Lake Storage (ADLS) Gen2 account without managing service principal secrets. What is a key requirement for using Workspace Managed Identities in Fabric?

Accepted Answer

The workspace must be hosted on a Fabric (F-SKU) or Power BI Premium (P-SKU) capacity.. Workspace Managed Identities (WMI) provide a secure way for Fabric to interact with other Azure services. This feature eliminates the need for developers to handle credentials. Understanding the prerequisites, such as the capacity type and the role of the identity, is crucial for a secure and automated analytics infrastructure.

Answer

The workspace must be assigned to a Pro or Premium Per User (PPU) license.

Answer

The identity must be manually created in Microsoft Entra ID before it can be assigned to the workspace.

Answer

The Managed Identity can only access resources within the same Azure region as the Fabric capacity.

Question 16

You need to automate the deployment of Fabric items across environments. Which THREE components are essential for a robust CI/CD pipeline in Microsoft Fabric? (Choose THREE)

Accepted Answer

Git integration for source control versioning.. A robust CI/CD pipeline in Microsoft Fabric relies on Git integration for versioning, Deployment Pipelines for promoting content between environments, and Service Principals to facilitate automated, non-interactive authentication for API-driven deployment tasks. Git integration ensures code is versioned in a repository, while Deployment Pipelines provide a managed way to move items through Dev/Test/Prod stages. Service Principals are the standard for securing automated workflows in enterprise environments.

Answer

Manual file copying using the OneLake file system explorer.

Answer

Hardcoded connection strings in Notebook code.

Question 17

You have a large Delta table that is frequently queried. You notice that queries are slow due to small file overhead. Which command should you run to optimize this table?

Accepted Answer

OPTIMIZE table_name. The OPTIMIZE command is the standard Delta Lake maintenance operation that compacts small files into larger ones, drastically improving read performance. When combined with Z-Ordering, it also organizes data physically based on column values, which further accelerates query performance for filters. This is a critical task for maintaining high-performance analytical tables in a Lakehouse environment, preventing the performance degradation caused by file fragmentation.

Answer

VACUUM table_name

Answer

ANALYZE TABLE table_name COMPUTE STATISTICS

Answer

REORG TABLE table_name APPLY

Question 18

Refer to the exhibit. You have configured a Microsoft Fabric pipeline copy activity to ingest paginated data from a REST API endpoint. During execution, the activity fails intermittently when fetching large pages due to response timeouts. Which parameter should you add to the RestSource definition in the pipeline JSON configuration to resolve pagination timeout issues?

Answer

Add "requestInterval": "00:00:05" within the RestSource typeProperties section to throttle rapid API calls.

Answer

Add "paginationRules": {"supportRFC5988": "true"} to automatically handle next-link headers provided by the API.

Answer

Add "maxConcurrentConnections": 10 within the sink storeSettings to increase parallel write throughput.

Answer

Add "compressionType": "GZip" to the source dataset definition to reduce payload size over the network.

Question 19

Refer to the exhibit. You are reviewing the JSON definition for a Spark Job Definition in Microsoft Fabric. The job is failing after exhausting all retries during periods of high capacity utilization. To ensure the job has a better chance of succeeding without increasing the maxRetryCount, which change should you implement in the job configuration?

Accepted Answer

Add a 'retryIntervalInSeconds' property to the retryPolicy.. When a Spark job fails due to capacity constraints or resource contention, the failure often happens quickly. By adjusting the retry policy to include an interval or delay, the job can wait for transient capacity spikes to subside before attempting the next run. This increases the likelihood of success in a busy Fabric environment where resources are shared.

Answer

Increase the 'numExecutors' value to 10.

Answer

Change the 'runtimeVersion' to 1.3.

Answer

Change the 'driverSize' to 'Large'.

Question 20

You are optimizing a Fabric Warehouse for complex analytical queries involving large fact tables. Which TWO actions should you perform to improve query performance?

Accepted Answer

Implement clustered columnstore indexes on the fact table.. Optimizing warehouse performance requires a combination of effective indexing strategies and data distribution management. By choosing the right clustering columns, you reduce data scanning requirements, while partition pruning ensures the engine only reads relevant segments. These techniques are fundamental for scaling analytical workloads in Microsoft Fabric, as they directly influence the amount of data processed during query execution and help maintain consistent performance under high concurrency.

Answer

Convert all tables to heap structures.

Answer

Increase the number of rows per file to 1 billion.

Answer

Disable statistics collection for all columns.