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

In a Microsoft Fabric data warehouse, row-level security is enforced by creating a filter predicate function and binding it to a security policy. The policy filters the rows returned to a user according to logic you define, for example returning only rows where Region = 'EU' for members of the EuropeanFinance group.

To hide a single sensitive column while letting users read the remaining columns, grant SELECT permissions only on the allowed columns and withhold (or explicitly deny) SELECT on the UnitCost column. Fabric's SQL engine supports column-level permissions identical to those in Azure Synapse Analytics and SQL Server. Dynamic data masking would still reveal masked values and does not satisfy a 'no access' requirement, and workspace-level roles or item-level permissions cannot express per-row filters or per-column grants. Therefore, creating a row-level security policy together with explicit column-level SELECT grants is the correct solution.

The Monitor hub surfaces operational details for Fabric items. Selecting the Dataflows tab and opening the most recent refresh run shows metrics such as total rows written, records processed per entity, file-level status, and any errors encountered. Lineage view only shows dependencies, not run metrics. The Capacity Metrics app provides tenant-level resource consumption, not per-refresh row counts. OneLake Data Explorer lists files in storage but does not display ingestion statistics.

Spark can avoid expensive shuffle joins by broadcasting small tables to every executor, turning the operation into a more efficient map-side join. Spark decides to broadcast a table only if its size is below the value specified in the spark.sql.autoBroadcastJoinThreshold configuration setting, whose default is 10 MB. Because each dimension table is about 100 MB, you must raise this threshold-setting it to 134,217,728 bytes (128 MB) will allow the three 100 MB dimension tables to qualify for automatic broadcasting and eliminate the large shuffle. Adjusting spark.sql.shuffle.partitions merely changes the number of shuffle partitions and does not enforce broadcasting. Enabling adaptive query execution can improve some plans but will not override the broadcast threshold by itself. Changing spark.sql.files.maxPartitionBytes affects input file partitioning, not join strategy. Therefore, increasing spark.sql.autoBroadcastJoinThreshold is the correct action.

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.

Applying a default sensitivity label at the workspace level causes any newly created or uploaded item to inherit that label automatically, satisfying the requirement that every new item start as Contoso Confidential. Because the default-label feature can be enabled without also enforcing mandatory labeling, authors remain free to replace the default with a different label.

Enabling a mandatory label would not automatically stamp the item; it only blocks saving until a user chooses a label, so the requirement for automatic assignment is unmet. Enabling inheritance from source data applies only when content is created from existing labeled data, not when authors create brand-new items. Relying solely on tenant-level auto-labeling policies does not guarantee that every new workspace item immediately receives the required label, and it ignores the need for authors to change the label on demand. Therefore, setting a default sensitivity label while allowing overrides is the correct choice.

When you run a deployment pipeline, Fabric automatically copies the definitions (metadata) of supported items-such as lakehouses, warehouses, and notebooks-from the source stage to the target stage. However, for storage-based items (lakehouses and warehouses), the data itself is not migrated; only the metadata is deployed. Therefore, no special action is needed to prevent data movement-simply selecting Deploy from Development to Test will promote schema and object definitions only, leaving the data in the Test stage unchanged. Snapshot settings, manual data exports, or disabling Git integration do not affect this behavior.

The partial() dynamic data-masking function reveals a specified number of prefix and suffix characters and substitutes the remaining middle characters with a custom padding string. In this case the parameters are:

• Prefix to expose: 0 characters
• Padding string: "XXXX-XXXX-XXXX-"
• Suffix to expose: 4 characters

Therefore, all 16 digits except the final four are replaced by the literal text "XXXX-XXXX-XXXX-". Because the analyst lacks the UNMASK permission, the mask is applied and the query returns:

XXXX-XXXX-XXXX-5678

The other options do not match the behavior of the specified mask: the default() mask would return a string of zeros, while rearranged or partially hidden values are not produced by the given parameters.

In a lakehouse, Delta tables are queried through Apache Spark or the SQL analytics endpoint. The most granular way to hide specific rows from a subset of users-without copying data or creating separate views-is to create a Row-Level Security (RLS) policy on the Delta table. RLS filters rows at query time based on predicates tied to user identities, so data analysts querying SalesOrders will automatically be prevented from seeing rows where TerritoryID = 60. Workspace roles alone cannot filter individual rows because they apply permissions at the item (object) level only. Column-level security targets specific columns, not rows. Converting the table to a secured Power BI semantic model would introduce an additional object and require separate maintenance, which is unnecessary when RLS can be applied directly on the Delta table.

The Contributor role is designed for users who need to create, edit, and delete content such as datasets, notebooks, lakehouses, and pipelines within a Microsoft Fabric (Power BI) workspace, including setting refresh schedules. Contributors cannot manage workspace permissions, delete the workspace itself, or publish or update an app, which aligns with the restrictions placed on the data scientists.

The Admin role is incorrect because it allows full control, including updating workspace permissions and deleting the workspace.

The Member role is incorrect because, in addition to content creation rights, Members can also add or remove users and publish or update the workspace app-capabilities you want to restrict.

The Viewer role is incorrect because it provides read-only access and does not allow creating or modifying any content.

Detailed, step-level progress-such as the start, duration, and outcome of each partition processed during an incremental refresh-is surfaced in the Semantic model refresh activity page that opens from Data pipelines. The Refresh history pane on the model's settings page lists only a high-level status and overall duration, without partition-by-partition detail. The Capacity Metrics app focuses on capacity health, not individual model refresh steps, and the Monitoring hub's Workspace Activity view targets pipelines and dataflows rather than semantic model partitions.

SqlErrorNumber 262 in Azure SQL Database indicates that the caller lacks the required permission on the referenced object. Because the Copy activity only reads data from the dbo.Orders table, the workspace's managed identity must have SELECT rights (or broader read rights such as membership in the db_datareader role) on the Sales database. Granting INSERT permission, changing the sink, or enabling staged copy does not satisfy the missing authorization check on the source table, so the activity would still fail.

Apply a dynamic data mask directly to the column and grant the UNMASK permission to the administrative group. The partial() masking function exposes the first character (prefix 1) and the last 12 characters (suffix 12 - the string "@contoso.com"), while padding the middle with the string *****. Granting UNMASK overrides the mask for authorized users.

Enabling transparent data encryption, applying a sensitivity label, or building a masking view do not implement dynamic data masking or satisfy the visibility requirement.

The Azure SQL Database deployment task (or an equivalent Script task that runs the SqlPackage CLI) can build and publish a DACPAC. During the Publish action SqlPackage connects to the Fabric Warehouse by using the workspace's T-SQL endpoint, compares the DACPAC schema to the target, and then applies only the incremental changes that are required to bring the warehouse in line with the project. Tasks that copy raw .sql files or invoke Power BI or generic Azure CLI commands cannot generate a DACPAC or perform the schema comparison and publish operation that database projects rely on.

Spark notebooks in Microsoft Fabric run on Apache Spark, allowing PySpark and native Python packages such as pandas and scikit-learn for complex data preparation and machine-learning tasks. Notebooks provide an interactive environment for iterative development and visualization, can write directly to Delta Lake tables, and are first-class activities that can be scheduled within Fabric Data Factory pipelines. Dataflow Gen2 offers a low-code Power Query experience but cannot execute arbitrary Python or advanced ML libraries. KQL update policies are suited to streaming ingestion and Kusto databases, not large batch processing with external Python packages. T-SQL stored procedures in a warehouse are limited to SQL operations and do not support Python libraries or Spark-based scalability.

A SQL database project created in Visual Studio Code (or Azure Data Studio) with the SQL Database Projects extension enables developers to design the Fabric warehouse schema offline, use the build process for T-SQL validation, and produce a DACPAC file. The .sqlproj and generated DACPAC can be checked in to Git and deployed to other Fabric workspaces through CI/CD pipelines, satisfying all requirements.

Although a Visual Studio SQL Server Database Project can also target Microsoft Fabric warehouses and generate a DACPAC, the option presented relies on publishing directly from the IDE, bypassing source control and automated pipelines, so it does not meet the requirement. Exporting T-SQL scripts from the Fabric portal or using notebooks lacks built-in validation, structured project artifacts, and automated deployment support, making them unsuitable for robust lifecycle management.

KQL streaming in Microsoft Fabric's Real-Time Analytics is built on Azure Data Explorer technology. It can ingest well above one million events per second, automatically scales compute, stores the data in OneLake, and lets users run both real-time and historical queries with the same Kusto Query Language (KQL) without writing notebook code. Spark Structured Streaming would require writing code in notebooks, Eventstream alone does not provide a unified query surface for historical analysis, and T-SQL streaming into a lakehouse SQL endpoint cannot currently sustain multi-million-events-per-second ingestion.

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.

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 watermark-based incremental load keeps track of the highest UpdateSequence value that was successfully loaded. During each run, the pipeline filters the source by selecting rows with an UpdateSequence greater than this watermark, minimizing the volume of data copied. The pipeline then executes a MERGE INTO statement on the Delta Lake table, which updates existing rows and inserts new ones in an idempotent manner. Because the watermark is updated only after the MERGE finishes, a failed run can be safely retried without introducing duplicates. Truncating and reloading the entire table wastes resources. Simply appending rows or dividing the table by an arbitrary modulo would not update changed records and could still duplicate data.

Event-based automation in Microsoft Fabric is available through Data Factory pipelines, not through notebook schedules. A pipeline can be configured with a storage event trigger that fires when a blob is created in a specified ADLS Gen2 path. Inside the pipeline you add a Copy activity to move the blob into OneLake, and then a Notebook activity that executes the existing parameterized notebook. Notebook schedules can only run on a fixed time basis and cannot listen for storage events, while SQL jobs and dataflows cannot orchestrate notebook execution after the copy finishes. Therefore, using a pipeline with an event trigger best meets the requirements and minimises custom code.