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

The default resources that an interactive Spark session receives in a Microsoft Fabric workspace are controlled by the Default engine size setting. Setting this value to Large (16 cores, 128 GB) makes every new notebook or interactive Spark job start on that size unless the user explicitly picks another size when they run the code. Other settings such as Session timeout, Enable custom libraries, or Max parallel sessions influence when sessions shut down, whether extra libraries can be installed, or how many sessions a user can run, but none of them determine the initial number of cores and memory an engine receives.

Tumbling window triggers are time-based and keep state for every discrete, contiguous time slice (window). If a window execution is skipped because the service is unavailable, the trigger automatically backfills the missed windows in chronological order once connectivity is restored, ensuring each window is processed exactly once. Schedule triggers fire only at the specified times and do not automatically replay missed runs. Event triggers respond to external events such as blob creation and are not suitable for strict hourly scheduling. Manual triggers require external calls and provide no automatic recurrence or backfill capability.

Delta Lake offers ACID transactions and the MERGE INTO command, which performs atomic upserts based on key columns. By creating a temporary view that assigns ROW_NUMBER() over (PARTITION BY OrderID, LineNumber ORDER BY EventDate DESC) and filtering for the first row, you keep just the newest version of each order line. Merging this view into sales.Order updates existing rows and inserts new ones, so repeated notebook runs remain idempotent. COPY INTO does not enforce primary-key uniqueness, OPTIMIZE ZORDER BY only reorders data without removing duplicates, and a TRUNCATE followed by INSERT reloads all data and discards late-arriving rows.

Open the Monitoring pane from the semantic model's settings page. The Monitoring pane lists every refresh attempt made in the last 30 days, showing the start and end times, total duration, final status, and any error details captured for failed refreshes. Other tools such as the Refresh history pane, Capacity Metrics app, and Microsoft 365 audit logs provide only limited or aggregate data and do not expose per-refresh duration together with detailed error information.

The Member role permits users to view and interact with all items in a workspace and to monitor existing Data Factory pipeline runs. However, Members cannot run pipelines, create or edit items, schedule or trigger data-flow refreshes, or modify workspace settings or access-capabilities reserved for higher roles such as Admin. Viewer lacks pipeline-monitoring rights, and Contributor or Admin would provide more privileges than required. Therefore, Member is the least-privilege role that meets the stated requirements.

Microsoft Fabric data warehouses are compatible with SQL database projects. By using the SQL Database Projects extension in Visual Studio Code (or Visual Studio) you can import the existing SalesDW schema and automatically generate a .dacpac file. The .dacpac (Data-tier Application Component Package) is a portable artifact that represents the entire database schema and can be committed to a Git repository and deployed to other Fabric workspaces through CI/CD pipelines. Exporting a Power BI project captures reports and models, not warehouse objects; ARM template exports do not include Fabric warehouse schemas; and BACKUP DATABASE produces a backup, not a schema-focused deployment package suitable for source control and incremental deployments.

Delta Lake supports the MERGE INTO statement, which performs an atomic upsert by matching source and target rows on a key column. Executing MERGE in a Spark notebook (or job) will insert new rows and update existing ones in a single operation, while automatically recording a new version of the table so that historical snapshots remain available. Loading with append-only modes, blindly deleting and re-appending data, or streaming in append mode would either duplicate data, lose history, or require additional logic to handle updates, making them less suitable for the required incremental pattern.

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.

Executing a Delta MERGE operation allows you to perform idempotent upserts. The MERGE statement compares incoming rows to the existing Delta Lake table on a business key such as TransactionID; matching rows are updated and non-matching rows are inserted. Because the command is executed every load, duplicates introduced by retransmitted files are eliminated without rewriting the whole table. A distinct transformation inside the dataflow would only remove duplicates within each individual batch, not across historical data. Auto-compaction optimises small files but does not remove logical duplicates. Loading into a new table every day and using UNION ALL merely propagates the duplicates rather than removing them.

The error is raised because the query mixes an aggregated column (COUNT()) with a non-aggregated column (OrderDate) without grouping. Adding OrderDate to a GROUP BY clause resolves the violation of the SQL aggregation rules and produces one summary row per distinct date. Merely switching to COUNT(OrderDate) does not remove the need to group; DISTINCT would still leave the aggregate unresolved; using a windowed COUNT() OVER (PARTITION BY OrderDate) calculates counts but returns a row for every order, not the required single row per day. Therefore, adding GROUP BY OrderDate is the correct and minimal correction.

When the "Auto create new lakehouse for dataflow output" setting is enabled in the Data workflow section of a Fabric workspace, every new Dataflow Gen2 published to that workspace automatically provisions a dedicated lakehouse as its destination and configures the required staging area. This eliminates the need for authors to pre-create or select a lakehouse manually. The other options control how dataflows are refreshed or validated but do not cause automatic lakehouse creation.

Shortcuts that point to external storage such as Azure Data Lake Storage Gen2 are mounted in Microsoft Fabric as read-only paths. Spark users in the destination lakehouse can read the data through the shortcut, but any attempt to create, update, or delete files under the shortcut will fail because Fabric blocks write operations to external locations. Only shortcuts that target another OneLake location are writable. Therefore, choosing an external shortcut automatically guarantees that the finance team's files remain unchanged, whereas the other options either describe writable shortcuts, unsupported behaviors, or unrelated features.

The error appears because more than one row in the source can match a single row in the target on CustomerID, so the MERGE would update the same target row multiple times. Selecting only one row per CustomerID in the source eliminates the conflict. Adding DISTINCT (or similar deduplication logic) in the USING clause guarantees each target row can match at most one source row, allowing the MERGE to complete. Using WITH (NOLOCK) can change the set of rows returned and does not guarantee removal of duplicates; SET IDENTITY_INSERT affects identity columns and is unrelated to this error; and INSERT … ON ERROR is not valid T-SQL syntax.

The error message indicates that the ingestion request references a JSON mapping called sensorMapping, but no mapping with that name exists on the sensor_data table. Eventhouses (built on the Azure Data Explorer engine) require an ingestion mapping when the incoming data is JSON and a specific mapping name is supplied. Defining a JSON ingestion mapping with that exact name and column-to-path assignments lets the eventhouse correctly parse each record and the ingestion pipeline succeeds. Switching sinks, enabling automatic schema updates, or changing the output format do not address the missing mapping reference and therefore will not eliminate the error.

In Microsoft Fabric, Spark compute used by notebooks is managed through the workspace's Spark session settings. The "Session inactivity timeout" setting defines how long an interactive Spark session can remain idle before Fabric automatically terminates it, releasing the capacity back to the pool. Adjusting this timeout at the workspace level enforces the automatic shutdown behavior for all notebooks created in that workspace. Other settings-such as enabling data workflow items, assigning a default capacity, or linking a OneLake workspace-do not control when idle Spark sessions are stopped.

Shortcuts let a KQL database in Real-Time Intelligence reference data that already resides in OneLake or a Fabric Lakehouse without copying the files. Because the data remains in its original Delta format, no regulatory-sensitive duplicates are created yet the database can still deliver low-latency, sub-second query performance. Native ingestion or mirroring would both create additional physical copies, and exporting then re-ingesting the data duplicates it again while adding unnecessary complexity.

The Copy data activity used in Microsoft Fabric pipelines (based on Azure Data Factory) includes an incremental copy feature. By enabling change tracking and selecting a high-watermark column such as LastModifiedDate, the activity automatically filters the source query to retrieve only rows whose watermark value is greater than the one stored from the previous run. This satisfies the requirement to load only new and changed rows while reusing the existing activity with minimal additional development. Switching to a Spark notebook adds unnecessary complexity, appending full data does not reduce load time, and a non-incremental Dataflow Gen2 would still rebuild the entire table.

A sudden rise in the "Failed to write events" metric while input volume is unchanged indicates that events reach the Eventstream but are rejected when written to the Eventhouse table. The Eventstream automatically stores such rejected events in the error store (dead-letter folder) of the Eventhouse destination. Inspecting the error store lets you examine sample payloads and error details-such as schema mapping or data-type mismatches-so you can determine why the write operation fails. Refreshing the Eventstream schema, restarting the Eventstream, or recreating the output does not provide the necessary diagnostic information before you understand the rejection reason.

A OneLake shortcut virtualizes the external JSON files so they appear in the Lakehouse immediately and can be referenced by multiple Lakehouses without copying or conversion. A notebook provides a PySpark runtime that can read the shortcut-linked files, perform complex joins and calculations, and write the results as a Delta table. A pipeline Copy activity would duplicate the data, Dataflow Gen2 cannot run arbitrary PySpark, mirroring targets relational sources, and KQL transformations do not support PySpark logic.

A Delta Lake MERGE statement lets you compare the incoming rows with the existing dimension on the business (natural) key. When a match is found and attribute values have changed, the MERGE can update the current row by setting its end-date and insert a new version that gets a new surrogate key. When no match exists, the MERGE simply inserts a brand-new row with a new surrogate key. This guarantees that each historical version of the dimension member keeps a unique surrogate key and that the latest version is flagged as current-requirements for a type-2 slowly changing dimension.

Appending data without comparison or overwriting the table would either create duplicate keys or lose history. COPY INTO by itself does not handle updates, and relying on identity values generated in the source system would break surrogate-key independence.