DP-700: Microsoft Fabric Data Engineer Associate Practice Exam

What the DP-700 exam covers

• Implement and manage an analytics solution299 questions
• Ingest and transform data299 questions
• Monitor and optimize an analytics solution264 questions

Free DP-700 sample questions

A sample of 10 questions with answers and explanations. Sign up free to practice all 862.

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

   A team has a deployment pipeline with Development, Test, and Production stages. A semantic model in Development connects to a dev SQL endpoint. When they deploy to Test, they want the deployed model to point at the Test SQL endpoint instead of the dev one, without editing the model manually. Which pipeline feature accomplishes this?

   • AA deployment rule of type data-source rule on the Test stageCorrect
   • BA Git commit that promotes the change to the Test branch
   • CManually re-authoring the entire semantic model in the Test workspace
   • DEnabling the large semantic model storage format setting
   ✓ Correct answer: A

   Deployment rules are configured on the target stage of a pipeline. A data-source rule maps the source connection to a different target connection, so when the semantic model is deployed to Test it automatically binds to the Test SQL endpoint. This keeps a single item definition across stages while pointing each stage at the correct source.

   Why the other options are wrong

   • BGit integration and deployment pipelines are separate; committing to a branch does not rebind a model's data source in a pipeline.
   • CManual re-authoring defeats the automation the pipeline provides and risks drift between stages.
   • DLarge semantic model storage format is a performance/size setting and has nothing to do with connection rebinding.
4. Question 4Ingest and transform data

   An analyst accidentally ran an overwrite that replaced the contents of a Delta table two hours ago. You need to restore the table to how it looked before the overwrite, and you know the prior version number from DESCRIBE HISTORY was 14. Which command restores it in place?

   • ARESTORE TABLE sales TO VERSION AS OF 14Correct
   • BSELECT * FROM sales VERSION AS OF 14 ORDER BY id
   • CVACUUM sales RETAIN 0 HOURS DRY RUN
   • DOPTIMIZE sales ZORDER BY (order_date, region)
   ✓ Correct answer: A

   Delta time travel with RESTORE writes a new commit that returns the table state to version 14, effectively undoing the overwrite while preserving full history. It is an in-place operation that later queries see immediately.

   Why the other options are wrong

   • BVERSION AS OF in a SELECT only reads the old snapshot; it does not change the current table state.
   • CVACUUM deletes unreferenced files and with RETAIN 0 HOURS would destroy the very history needed to time travel.
   • DOPTIMIZE with ZORDER compacts and clusters files for read performance; it does not roll back data.
5. Question 5Monitor and optimize an analytics solution

   A data engineer opens the Monitoring hub to investigate a Data pipeline that failed overnight. The pipeline contains a Copy activity, a Notebook activity, and a Dataflow Gen2 refresh. The pipeline run shows Failed, but the engineer needs to know exactly which activity failed and see the underlying error message. What is the most direct way to obtain the per-activity error output from the Monitoring hub?

   • ASelect the failed run in the detail view, then drill into the specific activity's output/error to read its returned error messageCorrect
   • BRe-run the entire pipeline with verbose logging enabled, because the Monitoring hub only stores pass/fail status, not activity errors
   • COpen the Capacity Metrics app and filter by the pipeline name to view the activity-level error stack for that specific run
   • DExport the workspace diagnostic logs to a Lakehouse and query a custom ActivityErrors table with a hand-written SQL query
   ✓ Correct answer: A

   The Monitoring hub lets you select a pipeline run to open its detail view, where each activity is listed with its status. Selecting the failed activity exposes its input, output, and error details, including the returned error message, so you can pinpoint which activity failed and why without re-running anything.

   Why the other options are wrong

   • BThe Monitoring hub retains activity-level details, not just pass/fail, so a re-run with verbose logging is unnecessary to see the error.
   • CThe Capacity Metrics app reports capacity/CU consumption, not per-activity error messages, even when filtered by pipeline name.
   • DExporting to a Lakehouse and querying custom tables is not required; the Monitoring hub drill-in surfaces the activity errors directly.
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, no data skew can ever occur anywhere in the entire job again, so no manual salting or redesign is needed
   • CAQE skew join handling only works when DataFrame caching is explicitly enabled for the affected shuffle stages, so you must persist first
   • DAQE handles all skew by broadcasting the entire dataset to every executor so that no single shuffle partition can ever end up oversized
   ✓ 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; it does not broadcast the entire dataset to handle skew at all.
7. Question 7Monitor and optimize an analytics solution

   Building on the alerting scenario, you want the pipeline to send the Teams alert AND then unambiguously report Failed. Which activity should you place at the end of the failure branch, after the alert?

   • AFail activityCorrect
   • BSet variable activity that writes 'error'
   • CWait activity with maximum duration
   • DInvoke pipeline activity calling the same pipeline
   ✓ Correct answer: A

   The Fail activity lets you raise a custom error message and error code and forces the pipeline run to end in a Failed state. Placing it after the alert ensures the notification is sent and then the run is marked Failed, so monitoring, alerts, and retry policies react correctly.

   Why the other options are wrong

   • BSetting a variable records a value but leaves the pipeline reporting Succeeded.
   • CA Wait activity just delays; it does not change the pipeline outcome.
   • DRe-invoking the same pipeline creates a loop and still does not force the current run to Failed.
8. Question 8Ingest and transform data

   In a Fabric Lakehouse notebook you run the following PySpark code: df = spark.read.format("csv").option("header", True).load("Files/raw/sales.csv") df.write.mode("overwrite").saveAsTable("sales_bronze") Where will the sales_bronze table be created and in what format?

   • AAs a managed Delta table in the Lakehouse Tables sectionCorrect
   • BAs a raw CSV file kept in the Lakehouse Files section
   • CAs an external table pointing to the Files/raw/sales.csv path
   • DIn the Fabric Warehouse as a managed T-SQL table
   ✓ Correct answer: A

   Using df.write.saveAsTable("sales_bronze") in a Lakehouse notebook writes a managed table in the default Lakehouse; Fabric persists Lakehouse tables as Delta by default, so it appears under Tables as a managed Delta table.

   Why the other options are wrong

   • BsaveAsTable creates a table, not a raw CSV file; the source was CSV but the output is Delta.
   • CNo path/location was specified, so it is a managed table, not an external table.
   • DsaveAsTable in a Spark notebook targets the Lakehouse, not the Warehouse engine.
9. Question 9Ingest and transform data

   You partitioned a Delta table by day and now find millions of tiny files degrade read performance. Which corrective actions best address the small-file problem in a Fabric lakehouse Delta table?

   • ARepartition to a coarser grain and run OPTIMIZE to compact filesCorrect
   • BAdd more fine-grained partition columns to spread the files further
   • CConvert the Delta lakehouse table to a row-based CSV format
   • DDisable automatic statistics collection on the whole table
   ✓ Correct answer: A

   The small-file problem is solved by reducing partition granularity (for example, month instead of day) and by running OPTIMIZE (bin-compaction, optionally with V-Order/Z-Order) to merge many small Parquet files into larger, more efficiently readable files.

   Why the other options are wrong

   • BAdding partition columns increases fragmentation, worsening the small-file problem.
   • CCSV is not columnar, lacks compression/statistics, and would hurt analytical read performance.
   • DDisabling statistics removes optimizer skipping information and does not reduce file count.
10. Question 10Monitor and optimize an analytics solution

    You are optimizing a large Delta table in a Fabric lakehouse for Power BI Direct Lake and warehouse read performance. Which write-time optimization applies a special sorting and encoding to Parquet files so that scans read fewer rows and columns?

    • ADeletion vectors
    • BV-OrderCorrect
    • CLiquid clustering
    • DColumn mapping
    ✓ Correct answer: B

    V-Order applies sorting, row group distribution, dictionary encoding, and compression to Parquet files so Fabric engines (Direct Lake, warehouse, Spark) read them with fewer I/O operations, dramatically speeding scans.

    Why the other options are wrong

    • ADeletion vectors mark rows as deleted without rewriting files, improving DML performance but not read scan efficiency of the base data.
    • CLiquid clustering is a Databricks data-layout technique and is not the Fabric write-time read optimization referenced here.
    • DColumn mapping enables renaming/dropping columns without rewriting data; it is a schema-evolution feature, not a read optimization.

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 decide when 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
• SC-200 practice examRelated
• AZ-140 practice examRelated
• AZ-900 practice examRelated

DP-700 practice exam FAQ