Free Microsoft DP-700 Practice Test Questions MCQs

Explanation:

To copy data from an on-premises Microsoft SQL Server database (via gateway) into a warehouse in Microsoft Fabric, you need a tool that:
- Supports on-premises connectivity via data gateway
- Performs data movement (ETL/ELT)
- Can load into a Fabric Warehouse

✅ Why B is correct — Data pipeline
A data pipeline in Fabric:
- Uses the Copy activity to move data
- Supports on-premises sources via gateway
- Natively integrates with Fabric Warehouse destinations
- Is designed specifically for data ingestion and orchestration

👉 This is the standard and recommended approach

❌ Why the other options are incorrect

❌ A. Apache Spark job definition
Used for data processing, not ingestion.
Does not natively handle:
- On-premises gateway connections
- Simple copy operations

❌ C. Dataflow Gen1 dataflow
Legacy Power BI feature.
Not recommended in Fabric.
Limited support for:
- Warehouses
- Enterprise-scale ingestion
👉 Fabric prefers Dataflow Gen2 or pipelines

❌ D. Eventstream
Used for real-time streaming data ingestion.
Not suitable for:
- Batch copy from SQL Server
- Gateway-based ingestion

Explanation:

Group 1: An eventstream
Skillset: Azure Stream Analytics with Azure Event Hubs.
Requirement (Project 1): Move data between source and sink environments (specifically in a streaming context).
Why: In Microsoft Fabric, Eventstream is the direct equivalent to the real-time capabilities of Azure Event Hubs and Stream Analytics.
It allows users to ingest, filter, and route real-time events to various sinks (like Lakehouses or KQL databases) using a familiar interface for those with streaming backgrounds.

Group 2: A Dataflow Gen2
Skillset: Microsoft Power BI.
Requirement (Project 2): Transform data by using a low-code approach.
Why: Power BI users are experts in Power Query.
Dataflow Gen2 in Fabric is built on the Power Query engine, providing a high-scale, low-code ETL (Extract, Transform, Load) experience.
It is the natural choice for users who want to transform data visually without writing Spark code.

Correction to Image Highlight: Note that while the user-provided image highlights "A data pipeline," Dataflow Gen2 is the superior answer for a "low-code" transformation requirement.
A pipeline is for orchestration (moving data); a dataflow is for the actual low-code transformation.

Group 3: A notebook
Skillset: R language.
Requirement (Project 3): Transform data.
Why: In Microsoft Fabric, the Notebook is the primary artifact for code-based data engineering.
It natively supports multiple languages, including PySpark, Scala, SQL, and R.
For a developer specifically skilled in R, a notebook is the only environment in Fabric where they can leverage their existing code and libraries for data transformation.

Reference
Microsoft Learn: Choose a data store in Microsoft Fabric.
Microsoft Learn: Data Factory in Fabric: Dataflows Gen2 vs. Pipelines.

Explanation:

In Microsoft Fabric, when you need to run a pipeline at specific times of day, the most efficient solution is to use a data pipeline schedule.

A schedule allows you to configure recurring execution times (e.g., 2 PM and 5 PM daily).
This minimizes development effort because you don’t need to build custom triggers or job definitions — the scheduling feature is built into pipelines.
You can set multiple schedules for the same pipeline, so one pipeline can process File1.csv at 2 PM and File2.csv at 5 PM, loading both into the lakehouse (flh1).

🔄 Distractor Analysis

A. Job definition
Job definitions are used for Spark jobs, not for orchestrating pipeline runs.
They don’t provide the same scheduling flexibility for multiple files.

C. Data pipeline trigger
Triggers are event-based (e.g., file arrival, manual execution).
Since the requirement is time-based (2 PM and 5 PM daily), a schedule is more appropriate.

D. Activator
Activators are used to start pipelines based on events (like file drops).
This would add unnecessary complexity compared to simply scheduling the runs.

📚 Reference
Microsoft Learn: Scheduling pipelines in Fabric

Explanation:

In a KQL database (within a Fabric eventhouse), you create a table using the .create table command, specifying column names and their data types.

From the two images provided:

First image incorrectly uses .create function (which is for stored functions, not tables) and mismatched data types.

Second image correctly uses:
.create table – correct command for table creation.
EventStreamData – table name.
Columns: TimeStamp:datetime, DeviceId:string, StreamData:long – matches typical sample structure (timestamp, device identifier, numeric reading).

The StreamData:long indicates a 64-bit integer value (e.g., sensor reading, counter, metric).

Why other code segments are incorrect
.create function EventStreamData (... – This creates a function, not a table.
Functions are reusable queries, not storage structures.

StreamData:dynamic – While possible, the sample data shown in typical DP-700 questions for this scenario uses a numeric (long or int) value, not a complex JSON/dynamic type.

StreamData:long() or StreamData(long) – These are invalid KQL syntax for column definitions.
Data types do not use parentheses.

Reference
Microsoft Learn: .create table command in Kusto
Microsoft Learn: KQL data types
Fabric eventhouse documentation: Create a table in KQL database

Explanation:

Correct KQL Query Structure
The complete correct KQL query (based on the highlighted boxes in the Answer Area) is:
kqlReadings
| filter City == "Kansas"
| sort by Timestamp
| extend
    PrevMeterReading = prev(MeterReading),
    PrevTimestamp = prev(Timestamp)
| project
    City,
    Area,
    MeterReading,
    Timestamp,
    PrevMeterReading,
    PrevTimestamp

Step-by-Step Explanation
To compare each row’s MeterReading (and Timestamp) with the previous row based on time order, you must follow this exact sequence in KQL:

Start with the table
Readings

Filter the data (first highlighted box)
| filter City == "Kansas"
This restricts results to only Kansas rows, as shown in the sample output.

Sort the rows (second highlighted box – sort)
| sort by Timestamp (or sort by Timestamp asc)
This is critical.
The prev() function works on a serialized row set.
Without proper sorting by the time column (Timestamp), prev() would return unpredictable or incorrect previous values.

Extend with previous values (third highlighted box – extend)
| extend
    PrevMeterReading = prev(MeterReading),
    PrevTimestamp = prev(Timestamp)
The prev(column) function returns the value of the specified column from the immediately previous row in the current sorted result set.
Default offset is 1 (previous row).
For the very first row, prev() returns null.

Project the final columns (fourth highlighted box – project)
| project City, Area, MeterReading, Timestamp, PrevMeterReading, PrevTimestamp
This selects and orders the output columns exactly as shown in the expected sample table.

Why This Order Is Mandatory
sort must come before prev() — otherwise the previous row is not based on Timestamp order.
extend is used to create the new calculated columns (PrevMeterReading and PrevTimestamp).
project (or project-away) is used at the end to shape the final output.

Why the Other Highlighted Options Are Wrong (in the images)
The evaluate, lookup, and project in the left Values pane are just available operators and not necessarily the correct ones to use.
In the first image's Answer Area, the boxes for sort, extend, and project are shown in the correct sequence.
In the second image, the highlights indicate the correct operators to use: sort → extend → project.

Reference
prev() function - Kusto Query Language (KQL) | Microsoft Learn
This is the key documentation for the prev() window function used in Microsoft Fabric KQL databases (Real-Time Intelligence).

Explanation:

1. Create new records: incremental
The scenario explicitly states that the source file contains only changes that occurred since the last file was generated.

Logic: This is the definition of an incremental load.
Since you are receiving only the delta (new or modified records), you process just those records and append or merge them into Table1.

Efficiency: With 2 billion rows already in the table, performing a full load every 30 minutes would be inefficient and cost-prohibitive.

2. Delete existing records: Snapshot
This selection addresses the versioning requirement within the framework of how Delta Lake and Slowly Changing Dimensions (SCD) work in Fabric.

Logic: To support versioning (keeping the old record and the new record), you treat the current state of a record as a snapshot in time.
When a delete or change occurs, you capture a snapshot of the record’s final state before it was marked as inactive.

Implementation: In a versioning scenario, you are not simply performing an incremental delete (which would remove the data).
Instead, you use the incoming data to update the metadata of the existing snapshot in your table to preserve history.

Reference
Microsoft Learn: Design a data ingestion strategy for Microsoft Fabric.
Concept: Delta Lake Time Travel and Versioning.

Explanation:

To ensure that Spark processes for different architectural layers (like bronze and silver) run in isolation, you must provide them with dedicated compute resources.
In Microsoft Fabric, custom Spark pools allow you to define specific node sizes, scaling limits, and resource allocations that are independent of other workloads.

Why a Custom Pool?
By default, Fabric uses a shared "Starter Pool".
Creating a custom pool allows you to isolate the compute environment for specific jobs, ensuring that a heavy transformation in the silver layer does not contend for the same CPU or memory resources as a raw ingestion process in the bronze layer.

Why not High Concurrency (B)?
High Concurrency is the opposite of isolation.
It is a feature designed to allow multiple notebooks to share a single Spark session to reduce startup times and costs.
Disabling it might prevent sharing, but it does not provide dedicated resource guarantees like a custom pool.

Why not Executors (A) or Default Environment (D)?
Modifying executors controls parallelization within a single job but does not isolate one job’s resources from another.
Default environments manage software libraries and runtimes but do not control the underlying physical compute isolation required for this scenario.

Reference
Microsoft Learn: Create custom Spark pools in Microsoft Fabric documentation

Explanation:

When creating a workflow for new book cover images in Microsoft Fabric, the requirements point toward handling continuous ingestion and storage of image files.

Streaming dataflow (E): This component allows you to process data in real time as new files arrive.
It is ideal for scenarios where book cover images are continuously uploaded and need to be ingested into the system without manual triggers.

Blob storage action (F): Since images are binary files, they are best stored in blob storage.
A blob storage action ensures that the ingested images are properly saved and accessible for downstream processes (e.g., cataloging, metadata enrichment, or analytics).

Together, these two components form the backbone of a workflow that can automatically handle new image arrivals with minimal manual intervention.

🔄 Distractor Analysis

A. Notebook with Apache Spark Structured Streaming
While Spark Structured Streaming can handle real-time ingestion, it requires more development effort compared to using Fabric’s built-in streaming dataflows.
The question emphasizes minimizing effort.

B. Time-based schedule
A schedule would process data at fixed intervals, not continuously.
This does not meet the requirement for handling new images as they arrive.

C. Activator item
Activators are used to trigger pipelines based on events, but they are not sufficient alone to handle ingestion and storage of images.

D. Data pipeline
Pipelines are better suited for batch processing and orchestration.
For real-time ingestion of new files, streaming dataflows are more appropriate.

📚 Reference
Microsoft Learn: Streaming dataflows in Fabric

Explanation:

1. To identify the failure: An On failure dependency condition
In Microsoft Fabric pipelines, dependencies (the colored arrows connecting activities) are the most efficient way to control flow based on the outcome of a previous step.

Why this choice? An "On failure" dependency (red arrow) triggers the next activity only if the preceding activity fails.
It requires zero coding or complex logic.

Why not the others?
Fail activity: This is used to force a pipeline to fail, not to identify that a previous step has already failed.
If condition: While powerful, this requires writing an expression to check the output of the previous activity, which increases development effort.
On completion: This would trigger the notification whether the step succeeded or failed, which is not the requirement.

2. To send the notification: An Office365Outlook activity
To meet the requirement of notifying data engineers, you need a communication output.

Why this choice? The Office365Outlook activity is a built-in, low-configuration tool in Fabric.
It allows you to send an email to a distribution list or specific engineers with details about the error (like the Pipeline Name or Error Message) by simply filling out the "To," "Subject," and "Body" fields.

Why not the others?
Teams activity: While useful, it often requires more setup (Webhooks or Power Automate flows) compared to the direct Outlook activity in many tenant configurations.
Invoke pipeline: This would just start another pipeline; it does not send a notification itself.

Technical Summary
The most efficient pattern in Fabric for this requirement is to connect your main processing activity (e.g., a Notebook or Copy Data task) to an Office365Outlook activity using a red "On failure" connector.

Reference
Microsoft Learn: How to use Pipeline dependencies in Microsoft Fabric.
Microsoft Learn: Office 365 Outlook activity in Microsoft Fabric.

Explanation:

In Microsoft Fabric data pipelines, the Copy data activity includes a built-in retry setting.
If a connectivity issue occurs (e.g., network timeout, source/destination temporarily unavailable, throttling), enabling retries will automatically re-attempt the failed activity without any custom coding or complex orchestration.

Minimal development effort – You simply configure the retry count and interval in the activity settings.
No additional pipeline activities, loops, or notebooks are required.

Resolves transient connectivity issues – Most connectivity issues are likely intermittent (e.g., source system briefly offline).
Retries handle this pattern effectively.

Why the other options are incorrect

A. Add a ForEach activity to the data pipeline
ForEach is used to iterate over a collection (e.g., multiple files or tables).
It does not inherently resolve connectivity errors.
You could build a manual retry loop, but that requires more development effort and is less efficient than built-in retries.

C. Configure Fault tolerance for the Copy data activity
Fault tolerance in Copy activity is designed to skip incompatible or corrupt rows (e.g., type mismatches, missing columns) during data transfer, not to handle connectivity failures.
It does not retry failed connections.

D. Call a notebook from the data pipeline
A notebook could implement custom retry logic, but this requires writing and maintaining code (PySpark/Scala/SQL).
This increases development effort compared to simply setting a retry property in the Copy activity.
It also does not handle connectivity issues more effectively than built-in retries.

Reference
Microsoft Learn: Copy activity retry policy in Fabric
Microsoft Learn: Connectivity and retry logic