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

Microsoft Fabric lakehouses do not presently support sensitivity-label inheritance or protection actions such as encryption and content marking for files exported from the lakehouse. These capabilities are currently limited to Power BI items in specific export scenarios. Because of this limitation, no configuration change in Fabric (including labeling the lakehouse or workspace, changing table properties, or enabling Azure Information Protection on the underlying OneLake storage) will automatically apply encryption or markings to Excel files generated from lakehouse data. Therefore, the requirement cannot be met with the product's current functionality.

The error indicates that a transformation step still expects a column named CustomerID, but the source schema now exposes the column as CustID. Opening the dataflow in the Power Query editor and updating the Source (or subsequent) step to reference the new column name realigns the query with the current schema. Merely enabling performance features, extending time-outs, or changing gateway settings does not correct the missing column reference, so the refresh would continue to fail until the query steps are fixed.

A Data Factory pipeline in Microsoft Fabric can attach a storage event trigger, pass the triggering file path as a parameter to an Execute Notebook activity, and follow it with a Copy Data activity to load the warehouse. Pipelines expose configurable retries and monitoring/alert options without additional custom code. Stand-alone Spark notebooks lack event subscriptions, SQL pipelines cannot run PySpark, and Dataflow Gen2 centers on Power Query rather than notebook orchestration.

A SQL database project is the workspace item intended for treating a lakehouse or warehouse schema as source code. After you create the project, you can script tables, views, and stored procedures into .sql files, commit them to Git, and use Azure DevOps or GitHub pipelines to compare environments and deploy incremental changes. Simply enabling Git integration does not extract object definitions, exporting Delta files captures data rather than schema, and notebooks have no built-in schema compare or deployment functionality.

To fulfill all requirements you must:

• Specify Delta format with .format("delta").
• Enable automatic schema evolution using .option("mergeSchema", "true").
• Append, not overwrite, by using .mode("append").
• Create physical partitions with .partitionBy("Country"). Only the correct choice includes all four elements in one statement; the other options miss Delta format, use overwriteSchema instead of mergeSchema, omit partitionBy, or overwrite existing data.

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.

Shortcuts in Microsoft Fabric are metadata pointers to an existing location in OneLake. They do not duplicate the data and they rely on the underlying OneLake security model. If a user does not have permission on the source location, the shortcut does not grant it; access will still be denied. Moving or renaming the source path breaks the shortcut, and the data appears immediately without any refresh jobs because no materialization occurs. Shortcuts can be created inside any folder structure within a lakehouse.

Running the Delta Lake OPTIMIZE command rewrites the table into a smaller number of large files, reducing metadata overhead and improving scan efficiency. Adding the ZORDER BY clause on a frequently filtered column such as OrderDate further clusters related rows in the same data pages, minimizing file-skipping and I/O during predicate pushdown. VACUUM only deletes obsolete files and does not reorganize current data. Converting to plain Parquet would remove Delta functionality and still leave many small files if singleFile cannot handle large data volumes. Increasing notebook driver memory may speed Spark jobs but does not address the root cause-file fragmentation in storage-so it yields little benefit for downstream queries executed by other engines.

A KQL database in Real-Time Analytics uses the same Kusto Query Language that analysts already know from Azure Data Explorer. It can ingest high-throughput event streams, stores both hot and historical data in the same engine, provides built-in time-series and windowing functions, and lets you configure retention policies without writing code.

Spark Structured Streaming would require notebooks and Python/Scala code and lacks KQL's built-in analytical functions. Eventstream on its own is a data ingestion service; to query data you would still need to land it elsewhere and use another engine. Azure Stream Analytics jobs can output to Fabric but run outside the service and are managed separately, so they do not meet the requirement for a native Fabric engine with KQL querying.

Changing the sales fact table to a hash distribution on the surrogate key that joins to the Date dimension will colocate matching rows on the same distribution, eliminating the shuffle that currently occurs with a ROUND_ROBIN table. Because clustered columnstore remains in place, segment elimination will still prune rowgroups for the filtered calendar years, further reducing the amount of data scanned. Simply switching to a clustered index or replicating a 4-TB table is impractical due to size, and materializing the yearly subset would not remove the data-movement overhead of the join.

Designating ts as the event-time column tells Eventstream to sequence and process data by when the sensor reading actually occurred instead of by its arrival time. You then set a 2-minute out-of-order tolerance on ts and configure the late-arrival events policy to Drop so that any record whose ts timestamp is more than two minutes older than the current watermark is discarded. Once ordering and late-arrival handling are in place, a SQL query that applies a 1-minute tumbling window on ts and groups by deviceId can safely compute AVG(temperature) per device and route the results to a KQL table. Filtering with a WHERE clause or relying on arrival time would not guarantee correct ordering or late-event removal.

Rebuilding both large fact tables with HASH distribution on the common join column causes rows that share the key value to be stored on the same distribution. When two hash-distributed tables are collocated on the same key, the engine can perform a shuffle-free local join, eliminating the costly data-movement step and reducing execution time. Result-set caching only helps when identical queries are rerun and does not remove data movement. B-tree indexes are ignored by the distributed query optimizer when it chooses a data-movement strategy. Ordering a clustered columnstore index may aid segment elimination on range predicates, but it has no effect on which compute node each row resides, so it cannot eliminate the shuffle.

To stream Fabric workspace activities into a target that supports ad-hoc KQL queries, you must configure an Azure Monitor diagnostic setting for the capacity (or individual workspace) and route the Fabric Activity log to Log Analytics. Enabling the diagnostic setting is the prerequisite that turns on continuous export; once the data reaches the Log Analytics workspace, the SOC team can query it with KQL or connect Sentinel for alerting. Downloading the admin audit log provides only a static file, not a streaming feed. Assigning the Log Analytics Contributor role does not itself activate logging. Creating a deployment pipeline does not generate or stream activity logs.

In Microsoft Fabric, Git integration is configured at the workspace level. The correct first step is to open Workspace settings, choose Git integration, and connect the workspace to the FabricData repository. During this connection you specify Azure DevOps as the provider, set develop as the default branch, and enter a folder path that matches the workspace name. This immediately enables the Sync and Commit buttons so engineers can pull and push without leaving the portal. Creating a deployment pipeline, changing the default branch in Azure DevOps, or enabling audit logging do not establish the required Git link.

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.

In Microsoft Fabric, every workspace has a OneLake section in its settings.

• The Default storage location property is used to choose the Azure region where the files that the workspace writes to OneLake will be stored. Selecting West Europe guarantees that any new lakehouse or other OneLake-backed item created in the workspace stores its data in the West Europe OneLake region.
• The Download files from OneLake toggle controls whether users who have access to the workspace can download the physical files that back datasets, lakehouses, or other items. Setting this toggle to Disabled blocks file download while still allowing data exploration and queries through Fabric experiences (for example, SQL analytics endpoint or Power BI).
  Other options-such as turning off the OneLake shortcut feature, changing the default data format, or disabling V-Order optimization-do not affect regional storage or file-download permissions, so they cannot satisfy the requirements.

Only users who belong to the security group that the Fabric (Power BI) admin designates for certification can apply the Certified badge. Any user with write access to an item can apply the Promoted badge, provided the admin has not disabled endorsement in tenant settings. Therefore, adding the governance team to the designated certification group and leaving endorsement enabled for everyone else satisfies both conditions. Making analysts workspace admins is unnecessary, and tenant-wide disabling of promotion would prevent analysts from promoting their content.

The Lakehouse Delta table schema must match the list of columns produced by the Eventstream SELECT statement. When a new field appears in the incoming data, the Eventstream continues to forward it, but the Lakehouse write fails if the target table lacks that column. Adding the new column to the existing table with an ALTER TABLE statement brings the schemas back into alignment. Auto-create only works when no table exists, removing the column would discard required data, and changing the batch interval does not affect schema mismatches.

Column-level security in a Microsoft Fabric Warehouse is implemented with the GRANT and DENY T-SQL statements. The simplest approach is to separate users into two roles:

• Add all analysts who should not see the sensitive data to a database role named AnalystsNoSensitive and DENY SELECT on the SensitiveAmount column to that role.
• Grant SELECT on the entire Orders table (or explicitly on the SensitiveAmount column) to the FinanceLeads group. Because the DENY is scoped only to the AnalystsNoSensitive role-not to the public role-it blocks access for ordinary analysts while FinanceLeads retains full visibility. Dynamic data masking leaves the column visible (only masks its values), row-level security filters rows not columns, and object-level security in a Power BI model cannot protect direct SQL access to the warehouse.

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.