Microsoft Fabric Data Engineer Associate Practice Test (DP-700)

Using the Delta format provides ACID transactions and exactly-once guarantees when a checkpoint location is specified. Writing directly to a managed Delta table in the lakehouse (Tables.clicks) makes the data instantly queryable through DirectLake for Power BI. Append mode is the recommended setting for streaming inserts. Parquet lacks transactional guarantees, complete mode rewrites the entire table each micro-batch, and triggerOnce turns the job into a batch rather than a continuously running stream.

Contributors cannot override inherited permissions for an individual lakehouse. To restrict access, you must ask a workspace Owner or Admin to open the lakehouse's Manage permissions pane, disable inherited permissions, remove other groups and users, and then grant the Viewer role only to the DataScienceTeam group. Changing the workspace access mode to Private would hide every item in the workspace, and applying a sensitivity label or enabling dynamic data masking does not control access to the lakehouse object.

When you create a shortcut from a Fabric lakehouse to an external ADLS Gen2 location, you can choose Azure Active Directory (Azure AD) passthrough as the authentication method. With Azure AD passthrough, OneLake uses each caller's own Azure AD identity to access the underlying storage. Therefore any user who has the necessary Azure RBAC role assignments (for example, Storage Blob Data Reader) on the ADLS Gen2 container will automatically be able to read the data when they query through the lakehouse's SQL endpoint. Because access is enforced by the storage account, you do not need to provision or share a service principal, nor can you satisfy the requirement by selecting anonymous access or by copying the data into OneLake. Azure AD passthrough is the only option that keeps authorization in ADLS Gen2 while avoiding data duplication, so enabling Azure AD passthrough during shortcut creation is the correct choice.

Hash distributing SalesTransactions on CustomerID ensures that rows sharing the same CustomerID are stored in the same distribution as the dimension table that is also hashed on CustomerID. This co-location avoids shuffle operations during joins, providing the highest performance for large fact tables. Replicating a 500-million-row table would exceed practical limits for replicate distribution. Round-robin distributes rows without regard to join keys, so data movement would still occur. Hashing on Date would not align the distributions of the two tables, again forcing expensive data shuffles.

In the Fabric (Power BI) service, each semantic model (dataset) has a Scheduled refresh settings pane. In that pane you can turn on refresh failure notifications and specify one or more email addresses in the Email recipients (also surfaced as Email these contacts or Failure notification recipients) box. When a scheduled refresh fails, the service automatically sends an alert email to every address listed. Creating Azure Monitor alerts or capacity-level alerts does not target the semantic model's refresh operation, and dashboard data alerts can only monitor numeric values rendered in a visual-refresh status is not exposed that way. Therefore, enabling the built-in refresh failure notification and adding the distribution list is the correct and simplest solution.

A Spark notebook is best suited when you need flexible, code-driven data processing in Fabric. Notebooks let you write and test Python (or Scala, SQL, R) interactively, import third-party libraries, loop over input data, and invoke REST APIs. After development, the notebook can be scheduled or invoked from a pipeline for orchestration. Data Factory pipelines, Copy activities, and Dataflow Gen2 are low-/no-code tools optimized for declarative data movement and transformations; they cannot easily perform row-by-row API calls or use arbitrary Python packages. Stored procedures in a warehouse also lack the ability to call external web services or use Python libraries. Therefore, the notebook option meets all requirements.

Live-connected Fabric workspaces are not under source control. To put every artifact (lakehouse, pipeline, dataflow, notebook, etc.) under version control while preserving current work, you must convert the workspace from Live mode to Git mode and connect it to a repository and branch. This enables commit, pull-request, and merge workflows for every subsequent change. Creating a new workspace, exporting items, or building a deployment pipeline would not meet the requirement because the source items would remain outside Git until the workspace itself is placed under Git integration; deployment pipelines manage promotion, not version history.

Data Factory pipelines in Microsoft Fabric are designed for low-code orchestration. An event-based trigger can watch a OneLake folder and start the pipeline when a file is created. The pipeline can contain a Notebook activity to execute the PySpark notebook and a subsequent Teams (Web activity or Logic App) step that runs only if the notebook fails, providing conditional branching, retries, and monitoring in the Fabric portal. Running the whole process inside a standalone notebook would require custom polling and error-handling code, while Delta Live Tables and SQL job objects are not available or appropriate in Microsoft Fabric. Therefore, a Data Factory pipeline with an event trigger is the most suitable choice.

Dataflow Gen2 uses the web-based Power Query editor, enabling analysts to create transformations without writing code and to output the results directly to a lakehouse table. Spark notebooks require PySpark skills, KQL targets Real-Time Analytics rather than scheduled batch processing into lakehouses, and T-SQL stored procedures require writing SQL code.

The _sys.diag_execution_log system table records detailed execution information for every Dataflow Gen2 refresh, including each Power Query applied step, its duration, input and output row counts, and any error messages. By filtering this table for the failed run ID, you can pinpoint the step where the refresh stopped and inspect the row statistics right before the failure.

The Refresh history/run-details pane surfaces high-level error information but does not include per-step row counts. Query diagnostics only captures design-time traces inside the Power Query editor. Pipeline activity run details are available only when the dataflow is invoked from a pipeline and also lack internal transformation-step metrics.

Spark Structured Streaming keeps track of processed offsets and any intermediate aggregation state in a persistent checkpoint directory. By adding the checkpointLocation option to the writeStream definition, the query writes its metadata and state to reliable storage so that, after any failure or restart, Spark can recover exactly where it left off and continue processing without data loss or duplication. The other options serve different purposes: mergeSchema controls schema evolution when writing to Delta, ignoreChanges is used for Change Data Feed reads, and maxFilesPerTrigger simply limits the number of source files per micro-batch. None of those settings enable recovery after a failure.

In a domain workspace, only users or groups that are already members of the workspace can be assigned to privileged domain roles such as Data Curators. To delegate curation to an external security group, you must first add that group as a workspace member. After the group is added, you can promote it to the Data Curators role. Changing the default capacity, enabling Git integration, or switching the workspace type does not affect role assignment eligibility.

A Microsoft Fabric Warehouse is a managed, SQL-based analytical store built on OneLake. Warehouses provide full ACID transaction support, time-travel (via delta logs), and an engine that automatically maintains indexes and statistics so complex T-SQL queries from Power BI run efficiently. A Lakehouse also offers ACID and time-travel through Delta Lake but does not manage relational indexes or statistics, making it less suitable when many joined queries are expected. A KQL database is optimized for log and time-series data rather than relational joins, and Azure Cosmos DB targets operational NoSQL scenarios, not large analytical fact tables queried with T-SQL.

Assigning the Build permission (displayed in the UI as Read, reshare, and build) on a single dataset lets a user connect from external tools, create new reports, and publish those reports to other workspaces. Build automatically includes Read but does not grant dataset edit rights, workspace-level privileges, or access to other items. Read alone would block report creation, while Contribute or Admin would allow modifying the dataset or managing the workspace.

Eventhouses are backed by the Azure Data Explorer engine, which stores data in compressed columnar extents. When ingestion creates many small extents, each query must touch a large number of files, increasing both scan time and CPU overhead. By increasing the target extent (batch) size through the table's ingestion batching policy, the service accumulates more data before committing an extent, resulting in fewer, larger extents. This reduces the number of extents that queries must open and also improves compression efficiency, giving better overall performance without additional compute. Shortening retention only affects how long data is kept, not how it is laid out. Materialized views help for pre-aggregated workloads but do not accelerate raw-data queries that filter on recent time ranges. "Delta ingestion mode" is not a supported optimization in Fabric Eventstreams/Eventhouses.

Broadcasting very small lookup tables is a well-known Spark optimization. When you call broadcast() (or use the BROADCAST join hint) on ProductSubcategory and ProductCategory, the driver ships their data to every executor node. Each executor can then join its partition of the large Sales DataFrame locally, eliminating shuffle of the 500-million-row fact table. Repartitioning Sales to one partition forces single-threaded work, disabling AQE does not reduce shuffle, and unionByName() appends rows rather than joins.

Native tables use Fabric's internal OneLake storage, are optimized for high-throughput streaming ingestion, and allow very low-latency KQL queries. Because the events are not already held in another operational store, mirroring would add unnecessary replication overhead. Shortcuts simply reference existing files and therefore cannot ingest high-volume streaming data that does not yet exist elsewhere. Selecting native storage therefore delivers the required ingestion performance and interactive analytics while supporting a short, 30-day retention period.

An event trigger listens for file-creation events in Azure Data Lake Storage Gen2, which backs a Lakehouse folder. It starts the pipeline once for each new file, ensuring the PySpark notebook runs immediately and only for that file.

A scheduled trigger runs even when no files arrive, risking duplicate or unnecessary processing. A tumbling-window trigger is time-window based and not intended for instant per-file execution. A manual trigger relies on human initiation and cannot guarantee immediate processing.

The Monitoring hub in Microsoft Fabric includes an Activator that can create threshold-based alerts for Data Factory pipelines, including run-duration conditions. By configuring an Activator alert that triggers when the pipeline run time exceeds 15 minutes, the notification is handled entirely inside Fabric without additional code or external services. Azure Monitor metric alerts cannot target a Fabric Data Factory workspace or evaluate the PipelineRunDuration metric. Power BI dashboard data alerts do not track pipeline run duration, and adding a SQL trigger to a lakehouse table would require custom logging and maintenance.

A broadcast join distributes the very small CategoryDim table to every executor, allowing each partition of the larger ProductDim table to join locally without shuffling ProductDim across the cluster. This pattern scales well as ProductDim grows. Creating a view keeps the tables normalized, repartitioning to a single partition can throttle performance, and a cross join with a filter still causes a large shuffle.