Free DP-700 Practice Test Questions and Answers (2026) | Cert Empire Practice Questions

In Microsoft Fabric, a Data Pipeline is the purpose-built item for data orchestration. It allows you to create, schedule, and manage workflows composed of a sequence of activities. This directly meets the requirement to "load each item one after the other." Data Pipelines have native scheduling features that can be configured to run at specific intervals, such as every 15 minutes. Therefore, a data pipeline is the correct item for developing a scheduled orchestration solution in Fabric.

A. warehouse: A warehouse is a data storage and analytics service for SQL-based workloads; it does not provide orchestration or scheduling capabilities.

C. Dataflow Gen2 dataflow: A dataflow is a data transformation tool for preparing and shaping data. It is an activity that can be orchestrated by a pipeline, not the orchestrator itself.

D. notebook: While a notebook can be scheduled, it is primarily a code-based environment for data engineering and analysis, not the dedicated low-code tool for orchestrating multiple, diverse Fabric items.

1. Microsoft Fabric Documentation

"What is a data pipeline in Microsoft Fabric?": This document states

"A data pipeline is a logical grouping of activities that together perform a task... With data pipelines

you can... Schedule a pipeline to run on a schedule." This confirms its role as a scheduled orchestrator.

2. Microsoft Fabric Documentation

"Orchestrate data movement and transformation in Microsoft Fabric with pipelines": This guide explicitly positions pipelines for orchestration. The overview section states

"Microsoft Fabric provides data pipelines to allow you to orchestrate your data movement and data transformation at scale."

3. Microsoft Fabric Documentation

"How to schedule a data pipeline in Microsoft Fabric": This document provides detailed instructions on setting up a schedule for a pipeline

confirming its ability to meet the "run every 15 minutes" requirement. The "Create a scheduled trigger" section details the process.

The requirement is to analyze historical sales data using the customer's location as it was at the time of each sale. This necessitates preserving a complete history of changes to the customer dimension. A Type 2 Slowly Changing Dimension (SCD) is designed for this exact purpose. It tracks historical data by creating a new record for each change to a specific attribute. The ValidFrom and ValidTo columns, along with a surrogate key (ID), are used to define the time period during which each version of the customer record was active. This allows fact table records (sales) to be joined to the correct historical version of the customer dimension, ensuring analytical accuracy.

B. Type 0: This type is used for fixed dimensions where attributes are assumed to never change. It is unsuitable for tracking customer address changes.

C. Type 1: This type overwrites the old attribute value with the new one, thereby losing all historical context. This would make it impossible to analyze sales by past locations.

D. Type 3: This type tracks limited history by adding a new column to store only the previous value of an attribute. It does not maintain a full history of all changes over time.

1. Microsoft Learn

"Tutorial: Load data to a slowly changing dimension type 2 in Microsoft Fabric": This document explicitly describes the use case for SCD Type 2. It states

"A slowly changing dimension (SCD) Type 2 is a dimension that manages and stores data that changes over time. It keeps a full history of changes." This directly aligns with the question's requirement to analyze historical data.

2. Microsoft Learn

"Slowly changing dimensions": This documentation provides a clear definition of SCD types. For Type 2

it explains

"When the value of a source attribute changes

a new dimension record is added... The versioning columns are updated to identify the effective date ranges." This matches the table structure (ValidFrom

ValidTo) proposed in the question.

3. Kimball

R.

& Ross

M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). Wiley. In Chapter 5

"Dimensional Modeling Techniques

" the authors detail the design of SCDs. Type 2 is presented as the standard method for adding new rows to capture a complete history of attribute changes

which is the core requirement of the scenario.

To orchestrate this workflow in Microsoft Fabric, a data pipeline is the correct tool. The pipeline's schedule must be configured to run daily at 07:00 UTC (E). Inside the pipeline, the Notebook activity for Notebook1 needs its retry policy set to 2 to handle transient failures as required (A). To ensure the semantic model is refreshed only upon the successful completion of the data ingestion and transformation steps, the Semantic model refresh activity must be linked to the Notebook activity using the "On success" dependency condition (C). This creates a sequential, conditional workflow that meets all specified requirements.

B. The "On completion" condition would trigger the refresh regardless of whether the notebook succeeded or failed, which violates the requirement to refresh only after successful execution.

D. The orchestration and scheduling are managed by the pipeline, not the individual notebook's schedule settings. The pipeline's schedule controls the entire workflow's execution time.

F. The requirement explicitly states that Notebook1 should be retried, not the semantic model refresh activity. Applying the retry setting to the wrong activity fails to meet the requirement.

1. Pipeline Scheduling (Option E): Microsoft Learn. (2024). Schedule data pipelines in Microsoft Fabric. "You can run a data pipeline on-demand or on a schedule... You can also select a specific time zone that the schedule's start and end dates will be based on." This confirms that scheduling

including time zone

is a pipeline-level setting.

Source: Microsoft Fabric Documentation

"Schedule data pipelines in Microsoft Fabric".

2. Activity Retry Policy (Option A): Microsoft Learn. (2024). Activity policy settings in Data Factory for Microsoft Fabric. "You can use activity policies to configure the runtime behavior of an activity... Retry: Number of retries. The maximum is 10." This shows that retry settings are configured per activity within a pipeline.

Source: Microsoft Fabric Documentation

"Activity policy settings in Data Factory for Microsoft Fabric".

3. Activity Dependencies (Option C): Microsoft Learn. (2024). How to add activities to a pipeline. "You can chain two activities by dragging the output of one activity to the input of another... The default dependency is On success." This documentation explains how to link activities and specifies the "On success" condition

which ensures the second activity runs only if the first one succeeds.

Source: Microsoft Fabric Documentation

"How to add activities to a pipeline".

4. Notebook and Refresh Activities: Microsoft Learn. (2024). How to use Notebook activity in a pipeline and Semantic model refresh activity in Data Factory for Microsoft Fabric. These documents confirm that both running a notebook and refreshing a semantic model are available as distinct activities that can be added to and orchestrated within a Fabric data pipeline.

Source: Microsoft Fabric Documentation.

The Fabric Monitoring Hub (referred to as Fabric Monitor in the question) is the centralized location for tracking all user activities and job executions within a Fabric workspace. To identify the Delta version used by a specific notebook execution, you would navigate to the Monitoring Hub, locate the run history for Notebook1, and open its detailed view. This view provides access to the Spark application details, including the Spark UI. The "Environment" tab within the Spark UI lists all the libraries and their specific versions loaded for that session, which would include the Delta Lake library version.

A. Real-Time hub is designed for managing and acting on real-time, streaming data from sources like Eventstreams and KQL databases, not for monitoring notebook job executions.

B. OneLake data hub is a centralized catalog for discovering, managing, and using data items within Fabric. It does not provide details about job execution environments.

C. The Admin monitoring workspace is used by capacity administrators to monitor overall capacity health, utilization, and performance, not the specific runtime details of individual jobs.

E. The Microsoft Fabric Capacity Metrics app is a Power BI application that visualizes data from the Admin monitoring workspace for capacity planning and reporting, not for technical job inspection.

1. Microsoft Learn: Monitoring Hub. This document establishes the Monitoring Hub as the central place to monitor Fabric activities

including notebook runs. It shows how to access the run history and details for specific items.

Source: Microsoft Fabric Documentation

"Admin monitoring

" "Monitoring Hub."

Reference: https://learn.microsoft.com/en-us/fabric/admin/monitoring-hub

Section: "Introduction" and "View run history."

2. Microsoft Learn: Monitor notebook and Spark jobs. This guide details how to access the Spark UI from a notebook's run details page

which is found via the Monitoring Hub. The Spark UI is where specific environment details

like library versions

are located.

Source: Microsoft Fabric Documentation

"Data Engineering

" "Monitor notebook and Spark jobs."

Reference: https://learn.microsoft.com/en-us/fabric/data-engineering/notebook-monitor-spark-jobs

Section: "Monitor Spark jobs in notebook run details."

3. Microsoft Learn: Admin monitoring workspace. This document clarifies that the admin monitoring workspace is for capacity-level oversight

such as throttling

overloads

and usage patterns

distinguishing it from job-level monitoring.

Source: Microsoft Fabric Documentation

"Admin monitoring

" "Admin monitoring workspace."

Reference: https://learn.microsoft.com/en-us/fabric/admin/monitoring-workspace

Section: "Overview."

The performance degradation is directly linked to the significant growth of the fact table to 500 million rows. V-Order is a write-time optimization specific to the Microsoft Fabric storage engine that reorders data within Parquet files. This optimization improves data compression and creates a more efficient data layout, which significantly accelerates read operations by compute engines like Power BI (using Direct Lake) and the SQL endpoint. Applying V-Order to the large fact table will directly address the performance degradation caused by increased data volume, especially for scans and filters common in year-over-year analysis. This is a built-in feature that improves resource utilization, thus meeting the requirement to minimize operational costs.

A. Change the MD5 hash to SHA256: This would increase the size of the surrogate key from 128 bits to 256 bits, which would worsen join performance and increase storage costs.

B. Increase the capacity: This is a brute-force approach that would increase operational costs, directly violating one of the solution's primary requirements.

D. Modify the surrogate keys to use a different data type: While changing from a hash to an integer-based key is a data modeling best practice, V-Order is a more direct optimization for the physical data layout, which is the likely bottleneck as data volume grows. V-Order is a less disruptive, Fabric-specific feature designed to solve this exact problem.

E. Create views: Views are saved logical queries and do not alter the underlying physical storage or query execution plan. They will not improve the performance of inefficient queries on the base tables.

1. Microsoft Learn - Fabric Decision Guide: Data Warehouse vs. Lakehouse: In the "Performance" section

it states

"The warehouse (and the SQL endpoint of the lakehouse) uses a query engine that is optimized for fast queries on large volumes of data... It also includes built-in performance optimization

such as V-ordering of Parquet files..." This highlights V-Order as a core

built-in performance feature for Fabric warehouses.

2. Microsoft Learn - What is V-Order?: "V-Order is a write time optimization to the parquet file format that enables lightning-fast reads under the Microsoft Fabric compute engines

such as Power BI

SQL

and Spark... V-Order improves performance for all Microsoft Fabric engines and doesn't require any other setting from the user." This document explicitly states V-Order's purpose is to enable fast reads and improve performance across Fabric.

3. Microsoft Learn - Performance tuning with OPTIMIZE: "The OPTIMIZE command optimizes the layout of data in Delta Lake tables in Microsoft Fabric... For existing tables

you can enable V-Order by running the OPTIMIZE command." This shows the practical step to apply the V-Order optimization to the existing large table.

The medallion architecture involves a sequential data flow (Bronze -> Silver -> Gold) where each layer's population is dependent on the successful completion of the previous one. The most robust and manageable way to implement this is through orchestration. Scheduling a single "master" or "controller" data pipeline that, in turn, calls other pipelines for each specific layer (e.g., a Bronze pipeline, a Silver pipeline) ensures proper dependency management. This pattern allows the Silver pipeline to run only after the Bronze pipeline succeeds, and the Gold pipeline only after Silver succeeds, meeting the technical requirements for data integrity and process flow.

B. Schedule a notebook.

Scheduling a single notebook creates a monolithic process, which is difficult to debug, manage, and reuse for individual layers.

C. Schedule an Apache Spark job.

This is similar to scheduling a notebook; it packages the logic into a single job, lacking the high-level orchestration and dependency management features of a pipeline.

D. Schedule multiple data pipelines.

Scheduling pipelines independently (e.g., based on time) does not enforce dependencies. If the Bronze pipeline is delayed or fails, the Silver pipeline might run with stale or incomplete data.

1. Microsoft Docs

"Execute Pipeline activity in Azure Data Factory and Azure Synapse Analytics": This document describes the activity used to invoke one pipeline from another. It states

"The Execute Pipeline activity allows a Synapse pipeline or Data Factory pipeline to invoke another pipeline." This is the core mechanism for the correct answer

enabling a master pipeline to orchestrate child pipelines.

2. Microsoft Learn

"Orchestrate data movement and transformation in Azure Synapse Pipelines": This learning module explains how to build complex workflows. It details using triggers to start a pipeline and chaining activities to ensure sequential execution. The concept of a master pipeline calling other pipelines is a primary pattern for managing complex

dependent workflows like populating a medallion architecture.

3. Microsoft Cloud Adoption Framework

"Data management and analytics scenario data platform operations": In the section on data orchestration

it discusses the need for "orchestration of data pipelines to run in a dependent order." This principle directly supports using a master pipeline to control the sequence of layer population in the medallion architecture.

The queryinsights schema in Microsoft Fabric is specifically designed for analyzing historical query activity and performance within a warehouse. It contains views like queryinsights.execrequestshistory which store detailed information about completed queries, including the query text, the user who submitted it (loginname), start time, and end time. By querying this view with a T-SQL statement and filtering on the starttime for the last 60 minutes, you can precisely isolate the required queries and user information to investigate the performance degradation.

A. the Microsoft Fabric Capacity Metrics app: This app monitors capacity-level metrics like utilization and throttling, not the specific details of individual historical queries such as the full SQL statement and submitting user.

C. Query activity: While the Query activity page in the Monitoring Hub shows recent queries, the queryinsights schema provides a more powerful and flexible programmatic T-SQL interface for detailed and custom analysis, which is better suited for in-depth performance troubleshooting.

D. the sys.dmexecrequests dynamic management view: This DMV only shows information about requests that are currently executing. It does not provide historical data for queries that have already completed within the last 60 minutes.

1. Query insights in Microsoft Fabric data warehousing: Microsoft Learn. "Query insights is a feature of Microsoft Fabric data warehousing that provides a holistic view of the historical query activity in the Warehouse... You can use T-SQL to query the Query insights views to find details on historical query executions." This document details the purpose and usage of views like queryinsights.execrequestshistory.

Source: Microsoft Learn

"Query insights in Microsoft Fabric data warehousing"

Section: "Query insights views".

2. Monitoring connections

sessions

and requests using DMVs: Microsoft Learn. "For information on queries that have completed

failed

or been canceled

see queryinsights.execrequestshistory... sys.dmexecrequests shows all requests currently executing in the service." This explicitly distinguishes the use case for sys.dmexecrequests (current) from queryinsights (historical).

Source: Microsoft Learn

"Monitor connections

sessions

and requests using DMVs in Microsoft Fabric"

Section: "Monitor requests".

3. Microsoft Fabric Capacity Metrics: Microsoft Learn. This documentation describes the metrics app's purpose for monitoring capacity usage

performance

and identifying bottlenecks at the capacity level

not for detailed query-level analysis.

Source: Microsoft Learn

"Microsoft Fabric Capacity Metrics"

Section: "Overview".

Enabling OneLake availability for a Fabric eventhouse initiates a continuous process to make its data accessible in OneLake. This process begins by exporting all existing data from the eventhouse into the Delta Lake format within OneLake. Subsequently, any new data ingested into the eventhouse is also automatically and continuously exported to OneLake. This ensures that a complete and up-to-date mirror of the eventhouse data is available for use by other Fabric engines and tools like OneLake file explorer.

A. The OneLake availability setting applies to the entire eventhouse, including all its existing and future databases, not just new ones.

B. The process is continuous, ensuring that newly ingested data is also made available, not just a one-time copy of existing data.

C. The explicit purpose of enabling "OneLake availability" is to copy data to OneLake; selecting "no data" contradicts the feature's function.

E. The initial step of the process is to export all historical data, so it is not limited to only new data ingested after activation.

1. Microsoft. (2024). Manage an eventhouse in Microsoft Fabric. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/fabric/real-time-intelligence/eventhouse-manage#onelake-availability.

Reference Details: In the "OneLake availability" section

the documentation states

"When you enable OneLake availability... the process starts to export all existing data from your eventhouse to OneLake... After the initial export

any newly ingested data is exported as it arrives." This directly supports that both existing and new data are included.

The VACUUM command is used in Delta Lake to clean up and remove data files that are no longer referenced by a Delta table's transaction log and are older than a specified retention period. When data in a Delta table is updated or deleted, the old files are not immediately removed to support features like time travel. The VACUUM command permanently deletes these stale files. By default, the retention period is seven days, which directly aligns with the requirement to remove files older than seven days that are no longer in use. This is a critical maintenance operation for managing storage costs and data lifecycle in a Fabric lakehouse.

B. COMPUTE: The COMPUTE STATISTICS command is used to collect statistics about the data in a table, which helps the query optimizer generate more efficient plans. It does not remove any files.

C. OPTIMIZE: The OPTIMIZE command improves query performance by compacting many small files into fewer, larger files. It reorganizes active data but does not delete old, unreferenced data files.

D. CLONE: The CLONE command creates a copy of an existing Delta table at a specific version. It is used for data replication or creating development/test environments, not for cleaning up the source table.

1. Microsoft Fabric Documentation - Table maintenance: "The VACUUM command is a crucial table maintenance operation that helps manage storage costs and maintain compliance by removing old and unreferenced data files... By default

VACUUM sets a seven-day retention threshold." This source directly supports the use of VACUUM for removing files older than seven days.

Source: Microsoft Learn. (2024). Table maintenance in Microsoft Fabric. Section: "VACUUM".

2. Delta Lake Documentation - VACUUM: "Recursively vacuum directories associated with the Delta table and remove data files that are no longer in the latest state of the transaction log for the table and are older than a retention threshold."

Source: Delta.io Documentation. Vacuum a Delta table (garbage collection).

3. Microsoft Fabric Documentation - OPTIMIZE: "The OPTIMIZE command is a table maintenance operation that aims to enhance query performance and reduce storage costs by compacting small files into larger ones." This confirms OPTIMIZE is for file compaction

not deletion of old versions.

Source: Microsoft Learn. (2024). Table maintenance in Microsoft Fabric. Section: "OPTIMIZE".

4. Microsoft Fabric Documentation - CLONE: "The CLONE command creates a copy of a source table to a destination at a specific version. There are two types of clones: deep and shallow." This shows CLONE is for copying

not cleaning.

Source: Microsoft Learn. (2024). CLONE.

The scenario involves ingesting data from Azure Event Hubs, which is defined in Microsoft Fabric as an external data source for an eventstream. Therefore, the Eventstream1 artifact is an "eventstream with an external data source."

To meet the requirement of filtering data before it reaches the destination (the lakehouse) with minimal effort, you must use the built-in eventstream processor. This feature provides a no-code visual editor within the eventstream artifact itself, allowing you to add operators, such as the "Filter" operator, to transform and filter the data in real-time as it flows through the stream.

Microsoft Learn: "Add and manage eventstream sources."

Section: "External data sources"

Content: This document explicitly lists "Azure Event Hubs" as an "external data source" from which an eventstream can get data. This supports selecting "An eventstream with an external data source" as the data processor artifact.

Microsoft Learn: "Process event streams with the event processor editor."

Section: "Event processor operators"

Content: This documentation states, "The Fabric eventstream's event processor feature provides a no-code experience... to process... and filter data... before you send it to a destination." It specifically lists the "Filter" operator as the tool used "to filter out events based on a condition," confirming "An eventstream processor" is the correct component for filtering.

Statement 1: No. When User1 creates Workspace3, User1 automatically becomes the Admin of that workspace. Access to a workspace (as Admin, Member, Contributor, or Viewer) must be granted explicitly at the workspace level. The prompt does not state that User1 ever added User3 or Group1 to Workspace3. Being a domain contributor (as explained in Statement 2) does not automatically grant access to the workspaces within that domain.

Statement 2: Yes. The prompt states that Group1 (which contains User1 and User3) is added to Domain1. In Microsoft Fabric, there are two primary domain-level roles: Domain admin and Domain contributor. Since User1 is already the Domain admin, the action of "adding Group1" is interpreted as assigning the Domain contributor role to all members of that group. Therefore, User3 gains Domain contributor access to Domain1.

Statement 3: No. User2 is the admin for Workspace2 and Domain2. User1 is the admin for Workspace3. There is no action described in the prompt that connects User2 to Workspace3 or Domain1. Therefore, User2 has no access to Workspace3.

Microsoft Fabric Documentation | Workspace roles: This document confirms that workspace roles (Admin, Member, Contributor, Viewer) are separate from domain roles and must be explicitly assigned within the workspace's "Access" pane. It also confirms that the creator of a workspace automatically becomes its Admin.

Source: https://learn.microsoft.com/en-us/fabric/governance/roles (See sections "Workspace roles" and "Create a workspace").

Microsoft Fabric Documentation | Domains: This document defines the "Domain admin" and "Domain contributor" roles. It clarifies that a Domain contributor can assign workspaces (in which they are an Admin or Member) to the domain. This role does not grant implicit access to view or contribute to other workspaces already in the domain.

Source: https://learn.microsoft.com/en-us/fabric/governance/domains (See section "Roles in domains").

Microsoft Fabric deployment pipelines are designed to move item metadata (definitions and structure), not the data itself.

1. Data: The data within semantic models is not copied to the target stage. The semantic model structure is deployed, but it must be refreshed in the target workspace (Workspace1_TEST) to be populated with data.
2. Dataflow Gen1: Dataflow Gen1 (the legacy Power BI dataflow) is not a supported item for deployment pipelines. Only Dataflow Gen2 is supported for deployment.
3. Refresh Policies: Specific item configurations, including scheduled refresh policies for semantic models, are not copied during deployment. These must be manually reconfigured in the target stage.

Microsoft Fabric Documentation: "Understand the deployment pipelines process"

Ref 1 (Data): Under the section "Items that aren't copied," the documentation lists: "Data (for semantic models, dataflows, and datamarts)."

Ref 2 (Dataflow Gen1): Under the section "Supported items" and its subsection on limitations, Dataflow Gen1 is listed as an unsupported item.

Ref 3 (Refresh Policies): Under the section "Item properties that aren't copied," the documentation explicitly lists: "Semantic model refresh schedule."

Source: https://learn.microsoft.com/en-us/fabric/cicd/deployment-pipelines/understand-the-deployment-process

BirthDate: The default() masking function is used to apply a predefined mask based on the column's data type. For date or datetime data types, the default() function masks the value to 1900-01-01, which precisely matches the requirement.

EmailAddress: The partial(prefix, padding, suffix) function is used to mask string data, exposing a specified number of characters at the beginning (prefix) and end (suffix), while replacing the middle section with a padding string.

• The requirement is to show the "first leading character," so prefix = 1.
• The requirement is to show the "last five characters," so suffix = 5.
• The option 'partial(1, "@", 5)' correctly implements this logic, using "@" as the padding string. The email() function is incorrect as it has a fixed format (first letter and .com suffix) that does not match the "last five characters" requirement. The random() function is incorrect as it is only used for numeric data types.

Microsoft Fabric Documentation (Dynamic Data Masking):

default() function: "For date/time data types (date, datetime2, datetime, datetimeoffset, smalldatetime, time), default() masks with 1900-01-01 00:00:00.0000000 (or corresponding value for date or time)."

Source: Microsoft Learn. (2024). Dynamic data masking in Microsoft Fabric data warehousing. Section: "Dynamic data masking built-in masking functions", Subsection: "default()".

partial(prefix, padding, suffix) function: "Masking method that exposes the first and last characters according to the specified prefix and suffix values and a custom padding string... prefix [is the] Number of characters at the beginning to expose. ... suffix [is the] Number of characters at the end to expose."

Source: Microsoft Learn. (2024). Dynamic data masking in Microsoft Fabric data warehousing. Section: "Dynamic data masking built-in masking functions", Subsection: "partial(prefix, padding, suffix)".

random(start, end) function (Incorrect Option): "Use the random function to mask any numeric data type with a random value within a specified range."

Source: Microsoft Learn. (2024). Dynamic data masking in Microsoft Fabric data warehousing. Section: "Dynamic data masking built-in masking functions", Subsection: "random(start, end)".

Here is a step-by-step evaluation of the query:

1. sort by Location desc, UnitsSold desc: The table is first sorted by Location in descending alphabetical order (Washington DC, Seattle, San Francisco, New York). Within each location, it's sorted by UnitsSold in descending numerical order.
2. extend Rank=row_rank_dense(UnitsSold, prev(Location) != Location): This adds a Rank column. The row_rank_dense(Term, Restart) function assigns a rank based on the Term (UnitsSold) and restarts the rank (sets it back to 1) whenever the Restart condition (prev(Location) != Location) is true.

This query effectively partitions the data by Location and then assigns a dense rank based on UnitsSold (highest UnitsSold gets rank 1, the next highest gets 2, etc.).

Resulting Table (abbreviated):

"Washington DC", "Contoso", 400, Rank 1 "Washington DC", "Litware", 300, Rank 2 "Seattle", "Contoso", 300, Rank 1 "Seattle", "Fabrikam", 100, Rank 2 "Seattle", "Litware", 100, Rank 2 "San Francisco", "Litware", 500, Rank 1 "San Francisco", "Relecloud", 500, Rank 1 "New York", "Litware", 1000, Rank 1 "New York", "Contoso", 300, Rank 2 "New York", "Relecloud", 300, Rank 2 "New York", "Fabrikam", 200, Rank 3

Statement Analysis:

1. Litware from New York...: No. The top row is "Washington DC", "Contoso" because of the sort by Location desc.
2. Fabrikam in Seattle...: Yes. In the "Seattle" group, "Contoso" (300) gets rank 1. "Fabrikam" (100) has the next highest UnitsSold value, so it gets rank 2. "Litware" (100) shares this rank.
3. Litware in San Francisco...: Yes. "Litware in San Francisco" (500) has the highest UnitsSold for its location, getting rank 1. "Litware in New York" (1000) also has the highest UnitsSold for its location, getting rank 1.

Microsoft (Azure Data Explorer Documentation): row_rank_dense() function.

This document explains that row_rank_dense(Term, Restart) assigns a rank starting at 1. The rank increments when Term changes, and the rank restarts at 1 when the Restart expression evaluates to true. In this case, the rank restarts for each new Location.

Source: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/row-rank-dense-function

Microsoft (Azure Data Explorer Documentation): sort operator.

This document confirms that sort by Location desc will order the locations alphabetically in reverse, placing "Washington DC" before "New York".

Source: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/sort-operator

Microsoft (Azure Data Explorer Documentation): prev() function.

This document clarifies that prev(Location) returns the value of the Location column from the preceding row, making the expression prev(Location) != Location the condition that identifies the first row of each new location group.

Source: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/prev-function

The task is to apply Dynamic Data Masking (DDM) to an existing column.

1. Box 1 (ALTER): The ALTER TABLE command is required because the dbo.CreditCard table already exists, and we are modifying its properties.
2. Box 2 (ALTER): The ALTER COLUMN clause is used within ALTER TABLE to specify which column's definition is being modified—in this case, to add a mask.
3. Box 3 (PARTIAL): The PARTIAL function is the specific DDM function that masks data by showing a defined number of leading (prefix) and trailing (suffix) characters. The syntax PARTIAL(2, "XXXXXXXX", 4) directly maps to the requirement to show the first two characters (prefix=2) and the last four characters (suffix=4).

The final correct T-SQL statement would be: ALTER TABLE dbo.CreditCard ALTER COLUMN [CreditCard] ADD MASKED WITH (FUNCTION = 'PARTIAL(2,"XXXXXXXX",4)')

Microsoft Learn (Microsoft Fabric Documentation). Dynamic data masking in Microsoft Fabric data warehousing.

Section: "Implement dynamic data masking"

Explanation: This official documentation confirms that Dynamic Data Masking is the correct feature to use in a Fabric data warehouse to limit sensitive data exposure to non-privileged users (like the data analysts with the Viewer role).

Microsoft Learn (T-SQL Documentation). Dynamic Data Masking.

Sections: "Masking Functions" (sub-section "Partial") and "Examples" (sub-section "Adding a mask to an existing column").

Explanation: This document defines the T-SQL syntax. It shows that ALTER TABLE ... ALTER COLUMN ... ADD MASKED is the correct syntax for adding a mask to an existing column. It also explicitly defines the partial(prefix, [padding], suffix) function as the method for exposing a set number of first and last characters, which matches the question's requirement.

The recommendation must select a functional streaming solution.

1. Eventstream: This no-code/low-code option is disqualified. Eventstream is built on Azure Event Hubs, which has a 1 MB maximum message size limit. All three sources (10 MB, 25 MB, and 5 MB) exceed this limit, making Eventstream technically non-viable.
2. Streaming Dataflow: This option is retired and has been superseded by other Real-Time Intelligence features in Fabric.
3. A data pipeline: This is a batch orchestration tool (Data Factory) and does not meet the requirement for a "streaming solution."
4. Apache Spark Structured Streaming: This is the only remaining solution. Although it is code-intensive and violates the "minimize development effort" preference, it is the only tool in the list that can technically handle streaming data of all types (structured, semi-structured, binary) with message sizes far exceeding the 1 MB limit.

Microsoft Fabric Documentation (Event Hubs/Eventstream Limit): "Azure Event Hubs, which underlies Fabric Eventstream... The maximum size of an event [message] is 1 MB."

Source: Microsoft. (2024). Azure Event Hubs quotas and limits. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/event-hubs/event-hubs-quotas (Section: "Event Hubs quotas and limits").

Microsoft Fabric Documentation (Data Pipelines): "A data pipeline... is used to perform batch data movement and transformation tasks."

Source: Microsoft. (2024). What is Data Factory in Microsoft Fabric?. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/fabric/data-factory/data-factory-overview (Section: "Data pipelines").

Microsoft Documentation (Streaming Dataflows Retirement): "Streaming dataflows has been retired... We recommend migrating to Azure Stream Analytics on Azure or using the Spark Structured Streaming feature..."

Source: Microsoft. (2023). Streaming dataflows. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-streaming (Note block).

Apache Spark Documentation (Structured Streaming): "Structured Streaming is a scalable and fault-tolerant stream processing engine built on the Spark SQL engine... You can use the same DataFrame/Dataset API for batch and streaming." Spark is not limited by a 1 MB message size and can read binary files (for images).

Source: Apache Software Foundation. (2024). Structured Streaming Programming Guide. Apache Spark Documentation. Retrieved from https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html.

To configure the ForEach activity (named "Extraction Loop"), its Items property must be set to an array from the output of the preceding activity.

1. The preceding activity is named Lookup Schema and Table.
2. The dynamic content expression to access another activity's output is @activity('ActivityName').
3. The Lookup activity returns its results (the list of tables) as an array within the value property of its output object.

Therefore, the correct expression to pass the array of tables to the ForEach loop is @activity('Lookup Schema and Table').output.value.

Microsoft Learn (Fabric Documentation): Lookup activity

Location: learn.microsoft.com/en-us/fabric/data-factory/lookup-activity

Reference: In the "Output" section, the documentation specifies that when the firstRowOnly property is set to false (the default), the Lookup activity's output contains a value property, which is the "List of rows returned from the data source." This list is the array that the ForEach activity needs.

Microsoft Learn (Fabric Documentation): Tutorial: Copy multiple tables in bulk

Location: learn.microsoft.com/en-us/fabric/data-factory/tutorial-bulk-copy-portal

Reference: In Step 5: Configure the ForEach activity, sub-step 3 instructs the user to set the Items property by adding dynamic content. The exact expression provided is @activity('LookupTableList').output.value (using "LookupTableList" as their example activity name), which directly maps to the scenario in the question.

Microsoft Learn (Fabric Documentation): ForEach activity

Location: learn.microsoft.com/en-us/fabric/data-factory/foreach-activity

Reference: The "Syntax" and "Properties" sections confirm that the items property expects a "collection to be iterated over" and can be defined using an expression, such as the output from another activity.

The mssparkutils.notebook.runMultiple() method is used to execute multiple notebooks, which can be structured as a Directed Acyclic Graph (DAG). To establish the required execution order where Load_Orders runs only after Load_Salesperson completes, you must define this relationship within the JSON configuration.

1. dependencies: This key is used within an activity's definition (in this case, Load_Orders) to specify an array of prerequisite activities (by name) that must complete successfully before this activity can begin.
2. runMultiple: This is the specific method within the mssparkutils.notebook utility that parses the JSON DAG and orchestrates the parallel and sequential execution of the defined notebook activities.

Source: Microsoft Learn (Official Vendor Documentation)

Article: Use Microsoft Spark utilities (mssparkutils) in Fabric

Section: mssparkutils.notebook.runMultiple(DAG)

Reference: The official documentation explicitly shows the runMultiple method is used to "run multiple notebooks in parallel or with a predefined topological structure (DAG)." The accompanying example code demonstrates that the dependencies key is used within the activities JSON array to define the execution order between notebooks. For instance, an activity "NotebookSimple2" is shown with "dependencies": ["NotebookSimple"] to make it run after "NotebookSimple". This directly validates the answer.

To add a constraint to an existing table, the correct Transact-SQL statement begins with ALTER TABLE.

In a Microsoft Fabric Data Warehouse, primary key constraints have two specific requirements:

1. They must be NONCLUSTERED. This is because the table's physical data structure is typically a CLUSTERED COLUMNSTORE INDEX (the default) or a heap, not a clustered index based on the primary key.
2. They must be NOT ENFORCED. Fabric is optimized for large-scale data ingestion and analytics. Enforcing uniqueness on every load operation would be a significant performance bottleneck. The system trusts that the data is unique, but it does not check, relying on upstream data integrity processes.

Therefore, the only valid sequence is to ALTER the table, ADD a NONCLUSTERED primary key, and specify that it is NOT ENFORCED.

Microsoft Learn. (n.d.). Primary keys, foreign keys, and unique keys in Warehouse in Microsoft Fabric. Retrieved November 14, 2025, from https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints

Reference (Section: "Primary Key"): This document explicitly states, "Warehouse supports PRIMARY KEY and UNIQUE constraints, when NOT ENFORCED and NONCLUSTERED capabilities are used." This confirms the two key required attributes.

Microsoft Learn. (n.d.). Constraints in Microsoft Fabric. Retrieved November 14, 2025, from https://learn.microsoft.com/en-us/fabric/data-warehouse/constraints

Reference (Section: "Primary Key, Unique, and Foreign Key"): This documentation confirms the T-SQL syntax and limitations, noting that Fabric supports PRIMARY KEY constraints but they "must be NONCLUSTERED and NOT ENFORCED."

Microsoft Learn. (n.d.). ALTER TABLE table_constraint (Transact-SQL). Retrieved November 14, 2025, from https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-table-constraint-transact-sql?view=fabric

Reference (Section: "Syntax"): The official syntax for adding a table constraint is shown as: ALTER TABLE [ ... ] ADD CONSTRAINT [ ... ] { PRIMARY KEY | UNIQUE } [ NONCLUSTERED ] ( column [ ... ] ) [ NOT ENFORCED ]. This confirms the correct order of the provided code segments.

Statement 1: The row_cumsum() function calculates a cumulative (running) sum. The query is sorted by VendorID, and the second argument, VendorID != prev(VendorID), acts as a restart predicate. This predicate evaluates to true when the VendorID changes from the previous row, correctly restarting the cumulative sum of total_amount for each new VendorID partition.

Statement 2: The row_window_session() function calculates the starting value of a session. The query uses tpep_pickup_datetime as the time column, 1h as the maximum lookup window, and payment_type != prev(payment_type) as the restart predicate. This correctly identifies the first tpep_pickup_datetime value for each payment_type partition.

Microsoft Learn (Kusto Query Language): row_cumsum() function.

Location: Official documentation for Azure Data Explorer (KQL).

Reference: The documentation specifies the syntax row_cumsum(Column, [RestartPredicate]). It states, "The cumulative sum restarts when RestartPredicate (if provided) evaluates to true." This directly supports its use in Statement 1 for partitioning by VendorID.

Microsoft Learn (Kusto Query Language): row_window_session() function.

Location: Official documentation for Azure Data Explorer (KQL).

Reference: The documentation provides the syntax row_window_session(TimeColumn, MaxLookupWindow, [MinLookBack], [RestartPredicate]). It is used to "calculate session start values of a column." This matches the requirement in Statement 2 to find the first pickup time, using payment_type as the partition (restart predicate).

Microsoft Learn (Kusto Query Language): prev() function.

Location: Official documentation for Azure Data Explorer (KQL).

Reference: The documentation for the prev() function is essential for understanding the RestartPredicate used in both statements. It explains that prev(Column) returns the value of Column from the previous row in a serialized row set, which is fundamental to detecting a change in VendorID or payment_type.