Free Microsoft DP-700 Practice Test Questions MCQs

Summary:
This question tests your knowledge of transformation operators within a Fabric Eventstream. The goal is to calculate a statistical metric (standard deviation) from a stream of temperature data. Standard deviation is an aggregate function that must be computed over a set of values, typically within a defined window or group. Therefore, the operator must be capable of performing calculations like stddev() across multiple events.

Correct Option:

D. Aggregate:
The Aggregate operator is specifically designed to compute summary statistics over a set of events. It allows you to define a time window (e.g., tumbling window of 1 minute) and then apply aggregation functions like avg(), sum(), min(), max(), and crucially, stddev(). This is the direct and correct method to calculate the rolling standard deviation of the temperature readings from the thermal sensor before storing the result in the lakehouse.

Incorrect Options:

A. Expand:
The Expand operator is used to work with complex data types, such as parsing a JSON payload within an event into separate columns. It does not perform any statistical calculations across multiple events. It is used for data shaping and normalization, not for computing aggregates like standard deviation.

B. Group by:
While "Group by" is a fundamental concept in data aggregation, it is not a standalone transform operator available in the Fabric Eventstream visual interface for this purpose. The grouping logic is inherently a part of the Aggregate operator, where you define the window and the fields to group by. The "Aggregate" operator is the implementation that provides the necessary functions.

C. Union:
The Union operator is used to merge two or more separate event streams into a single output stream. It combines events from different sources, appending them together. It does not perform any calculation or transformation on the data within the events and is irrelevant for computing a statistical value from a single data source.

Reference:
Microsoft Official Documentation: Transform data by using an Eventstream in Microsoft Fabric

Summary:
This question tests your understanding of the Shortcuts Cache feature in Microsoft Fabric. When a workspace has its cache enabled, data from external sources is automatically cached in OneLake after the first query to improve performance. The cache is populated on-demand. Therefore, only the shortcuts that have been queried will have their data retrieved from the cache. The question implies we need to identify which shortcuts have already been queried and thus would be served from the cache.

Correct Option:

B. Products and Stores only
The key detail is the Size of the files. TripsFile is 2 GB, while ProductsFile and StoreFile are 50 MB and 25 MB, respectively. The cache is populated on first access. A user is most likely to have already run exploratory queries on the smaller, more manageable dimension tables (Products and Stores) to understand the data. The very large 2 GB TripsFile is less likely to have been fully queried in a development or initial phase, so its data would be retrieved directly from the source (S3) and not from the cache.

Incorrect Options:

A. Trips and Stores only
This is illogical from a caching perspective. The 2 GB TripsFile is the least likely candidate to be cached due to its size and the time/bandwidth required to load it. It's improbable that this large file was cached while the smaller ProductsFile was not.

C. Stores only
This is too restrictive. Given the small and similar sizes of ProductsFile and StoreFile, it is highly probable that both would have been queried during initial data exploration and validation, leading to both being cached.

D. Products only
Similar to option C, this is too restrictive. There is no reason provided to believe only ProductsFile was queried and not the similarly sized StoreFile. Standard practice involves profiling multiple related tables.

E. Products, Stores, and Trips
This is incorrect because it assumes the entire 2 GB TripsFile has been cached. The cache is populated on-demand. Given its large size, it is very unlikely that a user has executed a query that required pulling the entire 2 GB dataset into the Fabric cache, especially when the question hints at determining what will be retrieved from the cache based on past activity.

Reference:
Microsoft Official Documentation: OneLake shortcurts cache

Summary:
This question tests your knowledge of pipeline expression syntax in Azure Data Factory (ADF) and Azure Synapse Analytics, which is the same engine powering data pipelines in Fabric. To use a parameter's value within a dynamic expression, you must wrap the parameter reference in a special string interpolation syntax. This tells the pipeline execution engine to evaluate the expression inside the braces rather than treat it as a literal string.

Correct Option:

B. "@{pipeline().parameters.param1}"
This is the correct syntax for referencing a pipeline parameter within a dynamic expression string. The @ symbol denotes the start of an expression. The content within the curly braces {} is evaluated. pipeline() is the function to access pipeline-related properties, .parameters specifies the parameters collection, and .param1 is the name of the specific parameter. This expression will be evaluated and replaced with the integer value of param1.

Incorrect Options:

A. "@pipeline().parameters.param1"
This syntax is missing the crucial curly braces {}. The @ symbol alone is often used for parameters in certain SQL-based activities where the value is passed directly without dynamic expression evaluation. In a general pipeline expression context, this would be interpreted as a literal string, not the integer value of the parameter

C. "@{pipeline().parameters.[param1]}"
This syntax is incorrect because it uses square brackets [] around the parameter name. Square brackets are used in ADF expressions for array indexing or for escaping column names with special characters (e.g., ['parameter-name']). They are not necessary for a simple parameter name like param1 and will cause an evaluation error.

D. "@{pipeline().parameters.param1}-"
This is incorrect because it appends a hyphen - to the end of the expression. This would convert the entire expression result into a string (e.g., if param1 is 5, the result would be the string "5-"). The requirement is to return the parameter as an int value, and this extra character makes the result a string, which would be invalid for any activity expecting an integer.

Reference:
Microsoft Official Documentation: How to use parameters in a Data Factory pipeline

Summary:
This question tests performance optimization in Kusto Query Language (KQL), specifically the impact of predicate placement on join operations. The goal is to reduce query execution time on large tables. A fundamental rule for optimizing joins is to reduce the number of rows in the larger table before the join is performed. Applying filters early in the query pipeline minimizes the data volume that subsequent, more expensive operations (like a join) must process.

Correct Option:

B. No
Moving the filter to line 02 does not solve the performance problem. While it is an improvement to filter early, the most significant performance issue in the original query is the placement of the join operation before the filter on Temperature. The query still joins millions of rows from the Stream table with millions of rows from the Reference table first, creating a massive intermediate result set. Only after this expensive join is the temperature filter applied. The optimal solution would be to filter the Stream table for Temperature >= 10 before the join on line 03.

Explanation of the Solution's Ineffectiveness:
The proposed solution of moving the filter to line 02 is logically correct in principle (filter early) but is presented incorrectly in the context of the given code lines. Line 02 is an extend operation. Simply moving the filter there would create a syntax error, as you cannot have a filter clause in the middle of an extend statement.

More importantly, even if we interpret "move to line 02" as "place immediately after the extend", the core performance bottleneck remains. The most impactful optimization for this query is to ensure the Temperature filter is applied to the Stream table before the join operation. The current solution, regardless of its exact line placement, does not achieve this if the join still precedes the filter. The correct sequence should be: filter the large Stream table, then perform the extend, and finally execute the join on the now-smaller result set.

Reference:
Microsoft Official Documentation: Join operator performance tips

This documentation emphasizes that to improve query performance, "if possible, reduce the left side ... of the join." The most effective way to reduce the left side (Stream) in this scenario is to apply the Temperature filter before the join.

Summary
This query ranks companies within each city based on their UnitsSold in descending order. The row_rank_dense() function assigns a rank, and the prev(Location) != Location argument resets the ranking counter whenever the Location changes. The entire dataset is first sorted by Location desc and UnitsSold desc, which groups all rows for a city together and sorts the companies within that city from highest to lowest sales.

Correct Option Explanations

1. Litware from New York will be displayed at the top of the result set.

Answer: No
The result set is sorted first by Location desc. "Seattle" and "San Francisco" come before "New York" alphabetically when sorted in descending order. Therefore, all rows for Seattle and San Francisco will appear before any rows from New York. Litware in New York will not be at the top.

2. Fabrikan in Seattle will have value = 2 in the Rank column.

Answer: Yes

Let's analyze the Seattle group after sorting:

Contoso: 300 UnitsSold -> Rank 1

Fabrikan: 100 UnitsSold -> Rank 2

Litware: 100 UnitsSold -> Rank 2 (This is a dense rank, so ties get the same rank, and the next rank is not skipped).

Since both Fabrikan and Litware in Seattle sold 100 units, they are tied and both receive a dense rank of 2. The statement is true.

3. Litware in San Francisco will have the same value in the Rank column as Litware in New York.

Answer: Yes
San Francisco Group: Relecloud (500) gets Rank 1, and Litware (500) is tied, so it also gets Rank 1.

New York Group: Litware (1000) is the highest, so it gets Rank 1.

Therefore, Litware in both San Francisco and New York has a Rank value of 1. The statement is true.

Reference:
Microsoft Official Documentation: row_rank_dense()

Summary:
This question focuses on optimizing load performance for temporary, write-intensive staging tables in a Fabric Warehouse. The key characteristic is that these tables are used once and then dropped. Traditional optimization techniques like creating statistics are designed for long-lived tables where the cost of creating stats is amortized over many queries. For short-lived staging tables, this overhead can actually increase total load time. The solution is to disable features that add unnecessary processing overhead during the write operation itself.

Correct Option:

A. Disable V-Order.
V-Order is an automatic write-time optimization that compresses and organizes data for faster read performance. However, applying V-Order adds computational overhead during the write process. For staging tables that are written once, read once, and then dropped, this write-time overhead is a net loss. The minor read performance gain does not justify the increased write time. Disabling V-Order minimizes the initial load (write) time, which is the explicit goal.

Incorrect Options:

B. Drop statistics.
While dropping statistics might save a negligible amount of space, it does not directly minimize load time. More importantly, for a table that is about to be read (to load the dimensional model), the absence of statistics would likely harm the performance of that subsequent read query, as the query optimizer would have no data distribution information to create an efficient execution plan.

C. Enable V-Order.
This is the opposite of what is required. Enabling V-Order would increase the write-time overhead during the initial load of the staging tables, thereby increasing the load time instead of minimizing it. This is beneficial for fact and dimension tables in the main model that are queried frequently, but not for transient staging tables.

D. Create statistics.
Creating statistics adds a significant step after the data load. For tables that are read only once, the time spent generating detailed statistics is often greater than the query performance benefit gained from having them. This would increase the total process time from load to completion of the read, thus failing to meet the goal of minimizing load time.

Reference:
Microsoft Official Documentation: Optimize performance with V-ORDER

Summary:
This question involves connecting a Fabric Spark job to an Azure SQL Database that has its public endpoint disabled. This means the database is only accessible through a private network connection (e.g., a VNet). To reach this private resource from the managed Fabric environment, you must establish a secure, private network path. The correct solution is a feature designed specifically for this purpose within the Microsoft Fabric and Azure networking ecosystem.

Correct Option:

B. a managed private endpoint
A managed private endpoint (MPE) is a feature within the Fabric admin settings that creates a private, outbound connection from the Fabric platform to a specific Azure PaaS service (like Azure SQL Database). It does not use the public internet. Since Source1 has public access disabled, an MPE is the required component to establish the necessary private network connectivity, ensuring Job1 can access the data securely.

Incorrect Options:

A. an on-premises data gateway
An on-premises data gateway is used to connect cloud services (like Power BI or Fabric) to data sources located within a private on-premises network, not to other Azure PaaS services. It is not the correct tool for connecting to an Azure SQL Database within Azure's own cloud.

C. an integration runtime
While an integration runtime (IR) is the core compute infrastructure for data movement and activity execution in Azure Data Factory and Synapse Pipelines, it is not the primary mechanism for enabling private connectivity from a Fabric Spark job definition. The Azure Integration Runtime uses the public internet, and the Self-hosted Integration Runtime suffers from the same on-premises limitation as Option A. The native, Fabric-specific solution is the managed private endpoint.

D. a data management gateway
This is an outdated term that is essentially synonymous with the "on-premises data gateway" mentioned in option A. It serves the same purpose and is therefore incorrect for the same reasons.

Reference:
Microsoft Official Documentation: Managed private endpoints in Microsoft Fabric

Summary:
This question focuses on the native deployment tooling within Microsoft Fabric. You already have a Fabric deployment pipeline configured with your development, testing, and production workspaces. The goal is to add a new Fabric item (an eventhouse) to this existing deployment process. The solution involves using the built-in, low-code deployment management system that is integrated directly into the Fabric service, rather than an external CI/CD tool.

Correct Option:

B. a deployment pipeline
A Fabric deployment pipeline is the dedicated, platform-native tool for managing the lifecycle of Fabric items across different environments (like Dev, Test, Prod). To add an eventhouse to the process, you would navigate to your existing deployment pipeline in the Fabric portal, assign the eventhouse from your Dev workspace to the pipeline, and then use the pipeline's interface to deploy it through the stages. This is the direct and intended method for this task within Fabric.

Incorrect Options:

A. GitHub Actions
GitHub Actions is an external, code-based CI/CD service. While it is possible to deploy Fabric items using the Fabric APIs and PowerShell modules within a GitHub Action workflow, this is a more complex, custom-code approach. The question implies you are already using the native Fabric deployment pipeline, so the simplest and most direct method is to use that existing tool. Using GitHub Actions would be an unnecessary and more complicated alternative.

C. an Azure DevOps pipeline
Similar to GitHub Actions, an Azure DevOps pipeline is an external, code-based CI/CD service. You could use Azure Pipelines with the Fabric API or PowerShell cmdlets to deploy the eventhouse. However, this is not the primary or simplest method when you already have a Fabric deployment pipeline set up. The question is asking for the tool to use within the context of Fabric's own deployment features.

Reference:
Microsoft Official Documentation: Deployment pipelines in Microsoft Fabric

Summary:
This question focuses on file management for Delta tables in a Fabric Lakehouse. Over time, as data is written, updated, and deleted (via operations like MERGE), the underlying Parquet files can become numerous and small, leading to inefficient query performance. The goal is to consolidate these small files into larger, more efficient ones. This is a maintenance operation performed on the table itself after the data manipulation is complete.

Correct Option:

C. OPTIMIZE
The OPTIMIZE command is the specific tool in Delta Lake (and by extension, Fabric Lakehouses) for consolidating many small Parquet files into a smaller number of larger files. This process is known as "compaction." Running OPTIMIZE on a table rewrites the data into larger, more performant file sizes, which significantly improves the read efficiency for subsequent queries. This is the direct command for solving the file consolidation problem.

Incorrect Options:

A. VACUUM
The VACUUM command is used for file cleanup, not consolidation. It permanently deletes data files that are no longer part of the current table state and are older than a retention threshold. While it helps manage storage, it does not merge or reorganize the remaining small files into larger ones. Its purpose is different from file compaction.

B. BROADCAST
BROADCAST is a hint used in Spark join operations, not a table maintenance command. It suggests that a small table should be sent to all worker nodes to speed up a join. It has no functionality related to file management or consolidation in a lakehouse.

D. CACHE
The CACHE command (or spark.catalog.cacheTable) is used to persist a DataFrame or table in the memory of the Spark cluster. This improves query performance by reading from memory instead of disk, but it is a temporary, in-memory operation. It does not alter or consolidate the underlying physical Parquet files on disk.

Reference:
Microsoft Official Documentation: Optimize performance with OPTIMIZE and VACUUM

Summary
This question tests your ability to read and debug PySpark DataFrame transformation code. The code performs data cleaning and shaping, but contains a critical error in one line. The analysis involves checking each line's logic against the stated goal, paying close attention to function arguments and the overall data flow.

Correct Option Explanations

1. Line 01 will replace all the null and empty values in the CustomerName column with the Unknown value.

Answer: Yes
This line uses the when().otherwise() function correctly. The condition (col("CustomerName").isNull() | (col("CustomerName")=="") checks for both NULL values and empty strings. If this condition is True, the lit("Unknown") value is assigned. Otherwise, the original CustomerName value is retained via otherwise(col("CustomerName")). The logic is sound and will achieve the stated goal.

2. Line 02 will extract the value before the @ character and generate a new column named Username.

Answer: No
This line is incorrect and will fail. The split(col("Email"), "@") function correctly splits the email string into an array. However, to get the part before the "@" symbol (the username), you need to get the first element of the array, which is at index 0. The code uses .getItem(1), which gets the second element (index 1), i.e., the domain name after the "@". Furthermore, the line is missing an equal sign and the col function for the new column name. The correct syntax would be roughly df = df.withColumn("Username", split(col("Email"), "@").getItem(0)).

3. Line 03 will extract the year value from the OrderDate column and keep only the first occurrence for each year.

Answer: No
This statement is false for two reasons. First, the dropDuplicates(["OrderDate"]) operation removes duplicate rows based on the exact OrderDate value (e.g., '2021-01-01'), not the year extracted from it. If all orders are on the same date, only one row will remain. Second, and more critically, the logic is flawed. It removes duplicates based on OrderDate before selecting and aliasing the year. The goal of keeping the "first occurrence for each year" is not achieved by this code, as it operates on the full date, not the year.

Reference:
Microsoft Official Documentation: pyspark.sql.functions.split

Microsoft Official Documentation: pyspark.sql.DataFrame.dropDuplicates