DP-700: Microsoft Fabric Data Engineer Associate Practice Exam

What the DP-700 exam covers

• Implement and manage an analytics solution298 questions
• Ingest and transform data299 questions
• Monitor and optimize an analytics solution263 questions

Free DP-700 practice test questions

A sample of 10 questions with answers and explanations. A free account includes a fixed set of 20 practice questions for this exam, retryable anytime. Paid access unlocks the full 860-question bank.

1. Question 1Implement and manage an analytics solution

   You need to grant a data engineer the ability to create, edit, and delete all items in a Fabric workspace, including managing other users' access, but you must NOT make them the owner of the workspace. Which workspace role should you assign?

   • AViewer
   • BContributor
   • CMemberCorrect
   • DAdmin
   • EGuest
   ✓ Correct answer: C

   Member can view, create, edit, and delete items and can add other users (as Members, Contributors, or Viewers). This meets the requirement of managing access without conferring full Admin/owner-level control such as deleting the workspace itself.

   Why the other options are wrong

   • AViewer can only read/consume content and cannot create or edit items.
   • BContributor can create and edit items but cannot add or manage other users' access.
   • DAdmin has full control including deleting the workspace and would effectively act as an owner-level role, which the requirement excludes.
   • EGuest is not a Fabric workspace role; workspace roles are Admin, Member, Contributor, and Viewer.
2. Question 2Implement and manage an analytics solution

   You want to hide an entire Warehouse table (dbo.PayrollRaw) from a role that otherwise has broad SELECT granted at the schema level. Which T-SQL enforces this with object-level security?

   • ADENY SELECT ON OBJECT::dbo.PayrollRaw TO FinanceReadersCorrect
   • BREVOKE SELECT ON SCHEMA::dbo FROM FinanceReaders
   • CADD FILTER PREDICATE dbo.fnHide(PayrollId) ON dbo.PayrollRaw
   • DALTER TABLE dbo.PayrollRaw SET (SYSTEM_VERSIONING = OFF)
   ✓ Correct answer: A

   Object-level security uses GRANT/DENY on specific objects. Because DENY takes precedence over GRANT in SQL permission evaluation, DENY SELECT ON OBJECT::dbo.PayrollRaw blocks that one table even though the role has SELECT on the whole dbo schema. This is the cleanest way to carve out an exception.

   Why the other options are wrong

   • BREVOKE only removes a previously granted permission; if access is inherited through role membership or a schema grant, REVOKE at the schema level would remove access to every table, not just PayrollRaw.
   • CA filter predicate is row-level security; it filters rows, not whether the object is accessible.
   • DSystem versioning is unrelated to permissions.
3. Question 3Implement and manage an analytics solution

   In a Fabric deployment pipeline, after deploying content from Development to Test, an item in Test shows a comparison status of 'Different'. What does this status mean?

   • AThe item exists only in Development and has not been deployed to Test yet
   • BThe item exists only in Test and does not exist in Development
   • CThe item exists in both stages but its content differs between themCorrect
   • DThe item is identical in both stages and remains fully in sync
   ✓ Correct answer: C

   Deployment pipeline comparison uses statuses to summarize each item. 'Different' (also shown as changes present) means the paired items exist in both the source and target stages but their definitions no longer match, typically because the source was edited after the last deployment. It prompts you to redeploy to bring Test back in line.

   Why the other options are wrong

   • AAn item only in the source stage shows as new or missing in the target, not 'Different'.
   • BAn item only in the target stage is flagged as not present in source, not 'Different'.
   • DItems that match are shown as 'Same' (no change), which is the opposite of 'Different'.
4. Question 4Ingest and transform data

   You want a parent notebook to run a child notebook and capture a value the child produces, then branch on that value. The child ends with mssparkutils.notebook.exit(str(row_count)). In the parent, which call runs the child and returns that string?

   • Aresult = mssparkutils.notebook.run('ChildNotebook', 300, {'source': 'sales'})Correct
   • Bresult = mssparkutils.notebook.runMultiple(['ChildNotebook'], {'source': 'sales'})
   • Cresult = spark.read.format('notebook').load('ChildNotebook')
   • D%run ChildNotebook then read the exit value from mssparkutils.notebook.exit
   ✓ Correct answer: A

   mssparkutils.notebook.run (also exposed via notebookutils) runs the referenced notebook in a new session with a timeout and a parameter dictionary, and returns the string passed to notebook.exit. Assigning it to result lets the parent branch on the returned value.

   Why the other options are wrong

   • BrunMultiple orchestrates a set of notebooks and does not return a single child's exit string this way.
   • CThere is no notebook data source for spark.read; notebooks are invoked with mssparkutils.notebook.run.
   • D%run inlines the child's cells and does not capture the value returned by notebook.exit.
5. Question 5Monitor and optimize an analytics solution

   In the Monitoring hub, a data engineer wants to see only failed Spark notebook runs from the last 24 hours across a single workspace, while hiding pipelines, dataflows, and semantic model refreshes. Which combination of Monitoring hub capabilities accomplishes this most efficiently?

   • ASet the item-type filter to Notebook, the status filter to Failed, and the time-range filter to the last 24 hoursCorrect
   • BSort the run list by duration descending and then manually scan for the notebook entries that show a red failed icon
   • CSet the status filter to Failed, then type each notebook's name into the Monitoring hub search box one at a time
   • DUse the Capacity Metrics app's timepoint tab and filter the whole view down to background operations only
   ✓ Correct answer: A

   The Monitoring hub provides filters for item type (for example Notebook), status (for example Failed), and time range. Setting item type to Notebook, status to Failed, and the time range to the last 24 hours returns exactly the runs the engineer wants without manual scanning.

   Why the other options are wrong

   • BSorting by duration and scanning manually is error-prone and does not restrict to failed notebook runs.
   • CSearching name by name depends on knowing every notebook in the workspace and repeats the query for each one; the item-type filter narrows the list to notebooks in a single pass.
   • DThe Capacity Metrics app tracks CU consumption, not filtered run history by status and item type.
6. Question 6Monitor and optimize an analytics solution

   After enabling AQE, a job that previously suffered severe skew now completes much faster, and the Spark UI shows one large shuffle partition was split into several smaller sub-partitions processed in parallel. However skew persists on a different operation not covered by AQE. What is the correct understanding of AQE's skew handling limits?

   • AAQE skew join handling splits oversized partitions in supported shuffle joins but not every operation, so residual skew may need saltingCorrect
   • BOnce AQE is enabled for the session, its skew join handling covers every shuffle operator in the job, including non-join shuffles
   • CAQE skew join handling requires the affected DataFrames to be persisted with cache() or persist() first
   • DAQE handles skew by broadcasting the larger side of the join to every executor instead of shuffling it
   ✓ Correct answer: A

   AQE detects partitions far larger than the median in supported shuffle join scenarios and splits them into sub-partitions handled in parallel, which resolved the first skew. But AQE's skew handling has scope limits and does not cover every operation or pattern, so remaining skew elsewhere may still require manual techniques such as salting or restructuring the query.

   Why the other options are wrong

   • BAQE mitigates skew only in supported cases; skew can persist elsewhere, so this overstates its coverage.
   • CAQE skew handling does not depend on DataFrame caching being enabled for the affected stages first.
   • DAQE splits oversized shuffle partitions; a broadcast join is a strategy for a small side, not its skew remedy.
7. Question 7Monitor and optimize an analytics solution

   A nightly pipeline truncates a warehouse table and then loads it with a Copy activity. Occasionally the Copy fails midway, leaving the table partially loaded. You need the reload to be idempotent so a retry always yields the correct complete result without duplicates. Which design best guarantees idempotency?

   • ALoad into a staging table, then atomically swap or MERGE into the target in one transaction after the load succeedsCorrect
   • BIncrease the Copy activity Retry count to 10 so that a partially loaded run eventually completes on a later attempt
   • CAppend rows to the target on every run and rely on a separate nightly deduplication job to clean up duplicates afterward
   • DDisable the truncate step and enable Copy fault tolerance so incompatible rows are skipped during the load
   ✓ Correct answer: A

   By loading into an isolated staging table and only promoting the data to the target via an atomic swap or transactional MERGE after the load fully succeeds, a failed and retried run never leaves the target partially populated or duplicated. The target only ever reflects a complete, consistent load, which is the definition of an idempotent, restartable reload.

   Why the other options are wrong

   • BRetries alone do not make a partial load idempotent; a re-run can still leave duplicates or partial state.
   • CAppend-then-dedup leaves the target in an incorrect state between runs and is not truly idempotent.
   • DSkipping bad rows with fault tolerance does not guarantee a complete, duplicate-free reload on retry.
8. Question 8Ingest and transform data

   What is the key difference between a managed table and an external table in a Fabric Lakehouse?

   • BFor a managed table Fabric manages the data files' lifecycle, so dropping it deletes the data; an external table has only metadata managed and files persistCorrect
   • AA managed table stores its data files outside OneLake while an external table stores its files inside OneLake, and dropping either removes only the metadata entry
   • CA managed table stores plain Parquet files while an external table stores Delta files with a transaction log, and dropping either leaves those files in place
   • DThe difference is only in how each table is listed in the lakehouse explorer, because dropping either type removes its data files
   ✓ Correct answer: B

   A managed table has its data files' lifecycle controlled by Fabric within the Lakehouse-managed location, so dropping it removes the data. An external table stores data at a specified location (for example a path or shortcut); dropping it removes only the metadata and leaves the files intact.

   Why the other options are wrong

   • AManaged and external tables can both reside in OneLake; the distinction is data-file lifecycle, not location.
   • CBoth table types use Delta in a Fabric lakehouse, and dropping a managed table deletes its files rather than leaving them in place.
   • DDropping an external table removes only its metadata and leaves the files in place, so the two types do not behave the same on drop.
9. Question 9Ingest and transform data

   In a star-schema lakehouse, late-arriving fact records reference a dimension member that has not yet been loaded. Which technique lets the fact load proceed without violating referential integrity?

   • BInsert a placeholder inferred dimension member, link the fact to it, then update attributes when the real row arrivesCorrect
   • ADrop the late-arriving fact rows during load and reprocess them later only once the referenced dimension member has been loaded
   • CDisable the foreign key constraint on the fact table during the load and re-enable it after the dimension load completes
   • DReorder the source system extraction so that all dimensions are always fully loaded before any related fact rows are processed
   ✓ Correct answer: B

   The inferred-member (early-arriving fact / late-arriving dimension) pattern inserts a placeholder dimension row with the natural key and default attributes, assigns its surrogate key to the fact, and later updates the placeholder with real attributes when the dimension data arrives, preserving integrity and avoiding data loss.

   Why the other options are wrong

   • ADropping fact rows loses data and does not preserve referential integrity for the facts that occurred.
   • CDisabling and re-enabling the constraint does not create the missing dimension member, so the fact rows still have no valid parent and integrity is violated during the load window.
   • DYou typically cannot control source system extraction order, and it does not solve genuinely late-arriving members.
10. Question 10Monitor and optimize an analytics solution

    A data engineer runs VACUUM on a Delta table with the default retention. Users then complain that time travel queries older than a few days now fail. What is the default retention threshold that VACUUM enforces, below which files are eligible for permanent deletion?

    • A24 hours
    • B72 hours (three days)
    • C7 days (168 hours)Correct
    • D30 days
    ✓ Correct answer: C

    By default VACUUM retains data files for 7 days (168 hours). Files no longer referenced by the current table version and older than this threshold are permanently removed, which breaks time travel to versions relying on those files.

    Why the other options are wrong

    • A24 hours is below the default; VACUUM warns/errors if you attempt retention this low without disabling the safety check.
    • B72 hours is not the Delta default retention period.
    • D30 days is not the default; the default is 7 days, though you can configure longer retention explicitly.

Who this DP-700 practice exam is for

This practice set is for anyone preparing for the DP-700: Microsoft Fabric Data Engineer Associate exam at the intermediate level - from first-time candidates building a foundation to experienced Microsoft practitioners doing a final review before test day. If you learn best by working through realistic questions and reading why each answer is right or wrong, it is built for you.

How to use this DP-700 practice exam

1. Start with the free sample questions above to gauge your current baseline.
2. Read the full explanation on every question, including why each wrong option is wrong.
3. Track your weak domains and focus your study where you are losing the most marks.
4. Once you are scoring consistently well, take a timed, full-length mock exam.
5. Use your readiness score to find weak objectives and focus your study. It shows where you stand on this bank's questions, not whether you are ready to book the real DP-700 exam.

Related Microsoft resources

• DP-700 study guideKey concepts
• Microsoft practice examsAll Microsoft
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• How these questions are written and reviewedMethodology
• Report a problem with a questionCorrections
• DP-750 practice examRelated
• DP-800 practice examRelated
• DP-900 practice examRelated

DP-700 practice exam FAQ