Microsoft DP-700 Implementing Data Engineering Solutions Using Microsoft Fabric Exam Practice Test

A screenshot of a computer Description automatically generated

SELECT last_run_start_time, last_run_command: These fields will help identify the execution details of the long-running queries.

FROM queryinsights.long_running_queries: The correct solution is to check the long-running queries using the queryinsights.long_running_queries view, which provides insights into queries that take longer than expected to execute.

WHERE last_run_total_elapsed_time_ms > 7200000: This condition filters queries that took more than 2 hours to complete (7200000 milliseconds), which is relevant to the issue described.

AND number_of_failed_runs > 1: This condition is key for identifying queries that have failed more than once, helping to isolate the problematic queries that cause failures and need attention.

The sales data issue can be resolved by configuring incremental refresh for the dataflow. Incremental refresh allows for only the new or changed data to be processed, minimizing the amount of data transferred and improving performance.

The solution specifies that data older than one month never changes, so setting the refresh period to 1 Month is appropriate. This ensures that only the most recent month of data will be refreshed, reducing unnecessary data transfers.

The requirement specifies that Litware plans to make the book reviews available in the lakehouse without making a copy of the data. In this case, creating a shortcut in Fabric is the most appropriate solution. A shortcut is a reference to the external data, and it allows Litware to access the book reviews stored in Amazon S3 without duplicating the data into the lakehouse.

sys.dm_exec_sessions provides real-time information about all active sessions, including the user, session ID, and status of the session. You can filter on session status to see users actively running queries.

Problem Overview:

Table1 has 2,000 small Parquet files (1 MB each).

Query performance suffers when the table contains numerous small files because the query engine must process each file individually, leading to significant overhead.

Solution:

To improve performance, file compaction is necessary to reduce the number of small files and create larger, optimized files.

Commands and Their Roles:

OPTIMIZE Command:

- Compacts small Parquet files into larger files to improve query performance.

- It supports optional features like V-Order, which organizes data for efficient scanning.

VACUUM Command:

- Removes old, unreferenced data files and metadata from the Delta table.

- Running VACUUM after OPTIMIZE ensures unnecessary files are cleaned up, reducing storage overhead and improving performance.

The correct sequence is Create a notebook → Attach the notebook to Lakehouse1 → Create a data pipeline .

First, create a Fabric notebook because notebooks provide the interactive development environment required for authoring, executing, debugging, and testing PySpark code. Microsoft describes Fabric notebooks as a primary code item for developing Apache Spark workloads and specifically supports their use for data ingestion, preparation, and transformation.

Second, attach the notebook to Lakehouse1 . Attaching a lakehouse establishes the notebook ' s lakehouse context, allowing PySpark code to access the lakehouse ' s Files and Tables through Spark and OneLake. This enables developers to interactively validate the transformation against the required Fabric data.

Third, create a data pipeline and use a Notebook activity to operationalize the notebook. Fabric Data Factory explicitly supports running Fabric notebooks as pipeline activities for automated data transformation workflows. Pipelines can subsequently be run manually, scheduled, or incorporated into larger orchestration processes.

Converting the notebook to an Apache Spark job definition is unnecessary; Spark job definitions are separate executable Fabric items. Publishing the notebook is also not required for pipeline execution.

Study Guide reference: Ingest and transform data → Transform data with Apache Spark → Fabric notebooks → Orchestrate processes and schedules with Data Factory pipelines.

Litware from New York will be displayed at the top of the result set – Yes

The data is sorted first by Location in descending order and then by UnitsSold in descending order. Since " New York " is alphabetically the last Location, it will appear first in the result set. Within " New York " , Litware has the highest UnitsSold (1000), so it will be displayed at the top.

Fabrikam in Seattle will have value = 2 in the Rank column – No

The row_rank_dense function assigns dense ranks based on UnitsSold within each location. In " Seattle " :

Contoso has UnitsSold = 300 → Rank 1

Litware has UnitsSold = 100 → Rank 2

Fabrikam also has UnitsSold = 100, so it shares the same rank (2) as Litware.

Litware in San Francisco will have the same value in the Rank column as Litware in New York – No

The rank is calculated separately for each location. In " San Francisco " :

Both Relecloud and Litware have UnitsSold = 500, so they share the same rank (1).

In " New York " , Litware has the highest UnitsSold = 1000 → Rank 1.

Since ranks are calculated independently for each location, Litware in San Francisco does not share the same rank as Litware in New York.

Changes to Table1 Structure:

StreamData column: An additional temperature element was added.

Timestamp column: Data type changed from datetime to date.

DeviceId column: Data type changed from guid to string.

Impact of Changes:

Only records that comply with Table2’s structure will load.

Records that deviate from Table2’s column data types or structure will be rejected.

Record B:

Timestamp: Matches Table2 (datetime format).

DeviceId: Matches Table2 (guid format).

StreamData: Contains only the index and eventid, which matches Table2.

Accepted because it fully matches Table2’s structure and data types.

Record D:

Timestamp: Matches Table2 (datetime format).

DeviceId: Matches Table2 (guid format).

StreamData: Matches Table2’s structure.

Accepted because it fully matches Table2’s structure and data types.

A screenshot of a computer Description automatically generated

Join between Products and ProductSubCategories:

Use an INNER JOIN.

The goal is to include only products that are assigned to a subcategory. An INNER JOIN ensures that only matching records (i.e., products with a valid subcategory) are included.

Join between ProductSubCategories and ProductCategories:

Use an INNER JOIN.

Similar to the above logic, we want to include only subcategories assigned to a valid product category. An INNER JOIN ensures this condition is met.

WHERE Clause

Condition: IsActive = 1

Only active products (where IsActive equals 1) should be included in the gold layer. This filters out inactive products.

To ensure that the usage of the data in the Amazon S3 bucket meets the technical requirements, we must address two key points:

Minimize egress costs associated with cross-cloud data access: Using a shortcut ensures that Fabric does not replicate the data from the S3 bucket into the lakehouse but rather provides direct access to the data in its original location. This minimizes cross-cloud data transfer and avoids additional egress costs.

Prevent saving a copy of the raw data in the lakehouses: Disabling caching ensures that the raw data is not copied or persisted in the Fabric workspace. The data is accessed on-demand directly from the Amazon S3 bucket.

MAR1 has seven entities, each accessible via a different API endpoint. A ForEach activity is required to iterate over these endpoints to fetch data from each one. It enables dynamic execution of API calls for each entity.

The Copy data activity is the primary mechanism to extract data from REST APIs and load it into the bronze layer in Delta format. It supports native connectors for REST APIs and Delta, minimizing development effort.

A screenshot of a computer Description automatically generated

Bronze Layer: A pipeline Copy activity

The bronze layer is used to store raw, unprocessed data. The requirements specify that no transformations should be applied before landing the data in this layer. Using a pipeline Copy activity ensures minimal development effort, built-in connectors, and the ability to ingest the data directly into the Delta format in the bronze layer.

Silver Layer: A notebook

The silver layer involves extensive data cleansing (deduplication, handling missing values, and standardizing capitalization). A notebook provides the flexibility to implement complex transformations and is well-suited for this task.

Data Analysts ' Access Requirements must only have read access to the Delta tables in the gold layer and not have access to the bronze and silver layers.

The gold layer data is typically queried via SQL Endpoints. Granting the Read all SQL Endpoint data permission allows data analysts to query the data using familiar SQL-based tools while restricting access to the underlying files.

The technical requirements specify that:

Medallion layers must be fully populated sequentially (bronze → silver → gold). Each layer must be populated before the next.

If any step fails, the process must notify the data engineers.

Data imports should run simultaneously when possible.

Why Use a Data Pipeline That Calls Other Data Pipelines?

A data pipeline provides a modular and reusable approach to orchestrating the sequential population of medallion layers.

By calling other pipelines, each pipeline can focus on populating a specific layer (bronze, silver, or gold), simplifying development and maintenance.

A parent pipeline can handle:

- Sequential execution of child pipelines.

- Error handling to send email notifications upon failures.

- Parallel execution of tasks where possible (e.g., simultaneous imports into the bronze layer).