Microsoft Azure & FabricLive · 108 questions · 10 notes

DP-700: Implementing Data Engineering Solutions Using Microsoft Fabric

Free practice exam, study notes with architecture diagrams and the official blueprint for Implementing Data Engineering Solutions Using Microsoft Fabric.

Passing score
700 / 1000
Domains
3
Exam code
DP-700

Official DP-700 exam blueprint

Domains and weights from Microsoft Azure & Fabric's exam guide.

DomainWeight
Implement and manage an analytics solution30–35%
Ingest and transform data30–35%
Monitor and optimize an analytics solution30–35%

Free DP-700 sample questions

The first questions of the free diagnostic. Pick your answer, then check it. Why every option is right or wrong, and the What-If twist, open in the workspace.

  1. Question 1 · Transform data by using Apache Spark

    A Fabric notebook rebuilds silver_orders from bronze_orders, where the source replays an order every time it changes, so order_id repeats with different updated_at values. Silver must hold only orders whose current version is billable, with amount above 0, and the job must be safe to rerun. Its code:

    Python
    from pyspark.sql import Window, functions as F
    
    w = Window.partitionBy("order_id").orderBy(F.col("updated_at").desc())
    latest = (spark.read.table("bronze_orders")
              .withColumn("rn", F.row_number().over(w))
              .filter("rn = 1 AND amount > 0")
              .drop("rn"))
    latest.write.mode("overwrite").saveAsTable("silver_orders")

    What does the job leave in silver_orders after a run?

    1. AEvery version of each order with amount above 0, added after the rows silver_orders already held
    2. BThe earliest version of each order, because row_number starts counting at the first row read
    3. COne row per order_id from its most recent version with amount above 0, even if a later version has amount 0
    4. DExactly one row per order_id, its most recent version, only for orders whose most recent version has amount above 0, replacing the previous contents
    Check the answer

    Answer: D. row_number() over a window ordered newest first keeps one version per key; where the filter sits decides which version that is.

    Official docs: Microsoft Learn: row_number ranking window function
    Unlock Full 4-Option Autopsy & What-If Twist in Workspace
  2. Question 2 · Implement and manage real-time analytics

    An Eventhouse KQL database stores device telemetry for a factory dashboard. Operators want a tile showing, for the last hour only, each device's average temperature in 5-minute windows, and windows with fewer than three readings must be hidden because they are too thin to trust. A developer wrote:

    KQL
    DeviceTelemetry
    | where Timestamp > ago(1h)
    | summarize AvgTemp = avg(Temperature), Readings = count()
        by DeviceId, Window = bin(Timestamp, 5m)
    | where Readings >= 3

    What does the query return?

    1. AOne row per device per 5-minute window of the last hour that has at least three readings, with that window's average temperature
    2. BOne row per DeviceId with its average temperature over the whole hour, for devices with at least three readings
    3. CThe latest temperature reading for each device, provided it reported three times in the last hour
    4. DEvery raw reading from the last 5 minutes, for devices that sent at least three of them
    Check the answer

    Answer: A. In KQL, summarize ... by bin(Timestamp, 5m) aggregates rows into fixed time windows per group.

    Official docs: Microsoft Learn: bin() function (KQL)
    Unlock Full 4-Option Autopsy & What-If Twist in Workspace
  3. Question 3 · Monitor a Fabric analytics solution

    Report queries fail with capacity errors every morning from 08:00 on an F64 capacity, and the Capacity Metrics app shows interactive rejection until mid-morning. Heavy Spark notebooks run between 00:00 and 04:00 at about 300% utilization, and the Monitoring hub shows every job completed by 04:10. The overnight jobs cannot be shortened this month and the SKU cannot change. What explains the morning failures?
    1. ABackground work is smoothed over 24 hours, so the night's overage carries into the morning, and past 60 minutes of borrowed capacity interactive requests are rejected
    2. BThe capacity pauses automatically after the overnight jobs and reports fail until it resumes
    3. CInteractive operations are smoothed over 24 hours, so this morning's reports are paying for yesterday's reports
    4. DA Spark session left running is still holding executors and consuming capacity units
    Check the answer

    Answer: A. Smoothing spreads background usage over 24 hours; overage beyond 60 minutes of future capacity triggers interactive rejection.

    Official docs: Microsoft Learn: Understand your Fabric capacity throttling
    Unlock Full 4-Option Autopsy & What-If Twist in Workspace
Answer all 10 free DP-700 questions

DP-700 study notes: key concepts

What each note covers. The full notes, with summaries, examples and memory hooks, open with a free account.

Architecture flow · Design and implement a lakehouse: Tables, Files, the SQL analytics endpoint and choosing a store

Choosing where new data should live
  • New dataset arrives for analytics in Fabric

      • May the data be copied into OneLake?

        • no
          • Shortcut from a lakehouse to the source, no copy

        • yes
          • Does the team need T-SQL DML with multi-table transactions?

            • yes
              • Warehouse written and queried with T-SQL

            • no
              • Is it high-rate time-series data explored with KQL?

                • yes
                  • Eventhouse KQL database with caching and retention policies

                • no
                  • Lakehouse Delta tables written by Spark, read by the SQL analytics endpoint

Drawn fromMicrosoft Learn: What is a lakehouse in Microsoft Fabric?

Design and implement a lakehouse: Tables, Files, the SQL analytics endpoint and choosing a store

  • The Tables area holds managed Delta tables that every Fabric engine discovers automatically, while the Files area holds raw or unstructured data with no schema and no table registration.
  • The SQL analytics endpoint gives each lakehouse a read-only T-SQL surface, so views, functions and GRANT statements work there but INSERT, UPDATE and CREATE TABLE on its tables fail.
  • Choose a warehouse when the team writes T-SQL with multi-table transactions, an Eventhouse for high-rate time-series data queried with KQL, and a lakehouse when Spark engineers own the transforms.
  • Internal OneLake shortcuts pass the caller's identity through to the target by default, so the item that owns the data keeps deciding who can read it without any copy.
  • External shortcuts to ADLS Gen2, Amazon S3 or Google Cloud Storage use the connection's delegated credentials, which is why OneLake security on the shortcut controls what users may read.

Design and implement a data warehouse: T-SQL engine, transactions and tuning

  • A Fabric warehouse is a full T-SQL engine with DDL, DML and multi-table transactions, and it stores every table as Delta Parquet in OneLake for other engines to read.
  • Snapshot isolation applies to every transaction, so readers never block writers, but two concurrent updates of the same table can conflict and the later commit fails.
  • Statistics are created and maintained automatically at query time, and you can still update them after a large load so the optimizer's estimates match the new data.
  • Data clustering with CLUSTER BY stores rows with similar values together, letting selective filters skip files without the partitions or indexes of older warehouse platforms.
  • Query insights keeps query history and aggregated views for 30 days for users with Contributor or higher, while live DMVs show only a user's own requests unless they are Admin.

Real-time processing: Spark structured streaming state, watermarks and late data

  • The watermark is the maximum event time seen so far minus the declared delay, recomputed every micro-batch, and windows ending before it are final, so their state can be evicted from the checkpoint.
  • Append mode with a watermark emits each window exactly once, after the watermark passes the window's end, which delays results by the watermark threshold but never rewrites rows already written.
  • Complete mode rewrites the entire result table on every trigger, so Spark has to keep every window it has ever produced and the watermark cannot evict any state at all.
  • The number of state store partitions is fixed by spark.sql.shuffle.partitions when the checkpoint is first created, so changing that setting later has no effect on a running stateful query.
  • Rows that arrive later than the watermark threshold are not guaranteed to be aggregated; pick the delay from how late real events arrive, trading result latency against completeness.

Design and implement security for analytics solutions: roles, OneLake security, SQL permissions and private access

  • Item permissions split access: Read lets a user connect, ReadData opens tables to T-SQL on the SQL analytics endpoint, and ReadAll opens OneLake files to Spark through DefaultReader.
  • OneLake security roles grant read on chosen tables and folders across Spark and the SQL analytics endpoint, but they never restrict workspace Admins, Members or Contributors.
  • Dynamic data masking hides values from users without CONTROL, but anyone allowed to filter on a masked column can still infer its values through repeated WHERE clauses.
  • Warehouse row-level security policies apply to every user, dbo and workspace editors included, unlike semantic model roles that editors bypass.
  • Managed private endpoints give workspace Spark a private path to Azure resources with public access disabled, so no firewall exception or public traffic is needed.

Ingest data: Copy jobs, pipelines, Dataflow Gen2, mirroring, shortcuts and COPY INTO

  • Shortcuts expose ADLS Gen2, Amazon S3, Google Cloud Storage or other OneLake data in place, so nothing is copied and the source stays the only system of record.
  • Mirroring replicates databases such as Azure SQL Database, Snowflake and Azure Cosmos DB into OneLake Delta tables in near real time, with no ETL pipeline to build or schedule.
  • A Copy job copies everything on its first run and then only new or changed rows, tracking the watermark or change data itself instead of relying on hand-built control tables.
  • Pipelines orchestrate Copy activities, Lookups, ForEach loops and notebooks with retries and run history, and a Lookup returns at most 5,000 rows, so long lists need paging.
  • COPY INTO loads files from Azure storage into warehouse tables in parallel, and MAXERRORS with ERRORFILE lets a load tolerate a few bad rows while keeping them for review.

Transform data with Apache Spark: pools, environments, sessions and fast execution

  • Starter pools keep pre-warmed medium nodes so sessions start in seconds, while custom pools provision nodes on demand in exchange for chosen node sizes and autoscaling.
  • Dynamic allocation in the environment sets how many executors a job may use, so a pool that autoscales to many nodes stays idle if the executor maximum is low.
  • A published environment set as the workspace default pins runtime, libraries and Spark properties for every notebook and job definition, including those created later.
  • The native execution engine runs built-in Spark operators vectorized and falls back to JVM Spark for unsupported ones, so Python UDFs give up its speed entirely.
  • Adaptive query execution splits skewed join partitions at run time, and a broadcast hint sends a small table to every executor so a large table is not shuffled.
Read the full DP-700 study notes

DP-700 FAQ

What is the passing score for DP-700?

The passing score for Implementing Data Engineering Solutions Using Microsoft Fabric is 700 / 1000.

Which domains does DP-700 cover?

The official exam guide lists 3 domains: Implement and manage an analytics solution (30–35%); Ingest and transform data (30–35%); Monitor and optimize an analytics solution (30–35%).

Is there a free DP-700 practice test?

Yes. Gyan AI's free diagnostic is 10 DP-700 questions with no sign-up, each with an explanation for every option and a link to the official documentation. A free account unlocks the full question set and study notes.

Are these the real DP-700 exam questions?

No. Real exam content is confidential under vendor exam agreements, and using leaked "dumps" can get a certification revoked. Gyan AI questions are original and written to the published skills outline.