Explore / Microsoft Fabric Data Engineer (DP-700) Exam Prep / Practice questions
Microsoft Fabric Data Engineer (DP-700) practice questions
10 questions from the bank behind this course's timed mock. Free to read and to answer, no account needed, and each has its answer and an explanation. They belong to Microsoft Fabric Data Engineer (DP-700) Exam Prep.
Answer them one at a time →Read lesson 1 free
Question 1
You are 3 minutes from the end of the DP-700 exam. Five questions are still unanswered, and one is a multi-part question that you only partly understand. You want the highest possible score. What should you do?
- Leave the five questions blank, because a wrong answer lowers your scaled score more than a blank one
- Answer only the multi-part question, because it is the only one where you might earn credit
- Skip the five questions, because some unanswered items may be unscored trial items
- Fill in an answer for all five, including every part of the multi-part question, because wrong answers cost nothing and each correct part can earn credit
Show the answer
Answer: D. Fill in an answer for all five, including every part of the multi-part question, because wrong answers cost nothing and each correct part can earn credit
Wrong answers carry no penalty, and on multi-part questions you usually earn a point for each part you get right, so a guess can only help. The idea that unscored trial items make skipping safe is wrong: you can't tell which items are unscored, so treat every question as if it counts.
Question 2
You are a Fabric admin at Contoso. Last month you used Assign workspaces > By capacity to put every workspace on the Sales capacity into the Sales domain. Sales engineers have created several new workspaces since then, and none of them shows a domain. You want workspaces that sales staff create from now on to go into the Sales domain automatically, without anyone doing it by hand. What should you configure?
- The tenant setting Allow tenant and domain admins to override workspace assignments
- Add the sales users' security group as domain admins of the Sales domain
- Set the Sales domain's Contributors option to everyone in the organisation
- A Default domain for the Sales domain that specifies the sales users' security group
Show the answer
Answer: D. A Default domain for the Sales domain that specifies the sales users' security group
Bulk assignment by capacity only affects workspaces that already exist. A Default domain sends the specified users' unassigned workspaces, and any new ones they create, to the domain automatically. Opening Contributors to everyone only lets workspace admins assign their workspaces themselves, so someone still has to do it by hand.
Question 3
You are a data engineer bringing an existing Fabric Warehouse named SalesDW under source control. In VS Code you create a connection profile for the SQL Server (mssql) extension and then plan to run Create Project From Database. Which value should you use as the server name?
- The SQL connection string copied from the warehouse's Settings in the Fabric portal
- The workspace URL shown in the browser's address bar in the Fabric portal
- The URL of the Git repository connected to the workspace
- The SQL analytics endpoint address of the lakehouse that feeds the warehouse
Show the answer
Answer: A. The SQL connection string copied from the warehouse's Settings in the Fabric portal
Create Project From Database connects to the warehouse itself, so the server name comes from the SQL connection string in the warehouse's Settings (<id>.datawarehouse.fabric.microsoft.com). The SQL analytics endpoint is a different address, and confusing the two is a common setup mistake.
Question 4
You are a data engineer at Contoso. The dbo.Payments table in a Fabric Warehouse stores full card numbers in a varchar column called CardNumber. Support agents are workspace Viewers. They must see only the last four digits, in the form XXXX-XXXX-XXXX-1234, and the existing reports must not be rewritten. Which masking rule should you apply to CardNumber?
- MASKED WITH (FUNCTION = 'partial(4,"-XXXX-XXXX-",0)')
- MASKED WITH (FUNCTION = 'default()')
- MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)')
- MASKED WITH (FUNCTION = 'random(1000, 9999)')
Show the answer
Answer: C. MASKED WITH (FUNCTION = 'partial(0,"XXXX-XXXX-XXXX-",4)')
partial(prefix,"padding",suffix) shows the first prefix and last suffix characters, so prefix 0 and suffix 4 with your own padding gives XXXX-XXXX-XXXX-1234. The near miss partial(4,...,0) shows the first four digits instead of the last four, default() masks a string column completely as XXXX, and random() only works on numeric columns.
Question 5
You are a data engineer reloading the past 18 months of FactSales into a Fabric Warehouse after fixing a pricing bug. DimCustomer uses SCD Type 2 on City, with ValidFrom, ValidTo and IsCurrent columns. Each reloaded sale must link to the customer version that was in effect on its order date. How should the customer lookup join DimCustomer?
- Join on CustomerID only and keep the row with the highest CustomerKey
- Join on CustomerID where the order date falls between the version's ValidFrom and ValidTo
- Join on CustomerKey from the staging table, because surrogate keys are stable in the source
- Join on CustomerID and IsCurrent = 1, using a LEFT JOIN with COALESCE to -1
Show the answer
Answer: B. Join on CustomerID where the order date falls between the version's ValidFrom and ValidTo
Historical or late-arriving facts must be matched to the version whose effective-date range contains the fact date, so past sales keep the attribute values that were true at the time. IsCurrent = 1 is right only for new facts. On a reload it would link old sales to today's city and rewrite history.
Question 6
You are a data engineer. A nightly pipeline uses a Copy data activity to load an on-premises SQL Server table into a Fabric Warehouse table named stg.Orders, using Write behavior = Insert. Each run must leave only that night's rows in stg.Orders, and you want to do this inside the same Copy activity. What should you configure?
- Write behavior = Upsert with OrderID as the key column
- A Pre-copy script on the Warehouse destination, such as DELETE FROM stg.Orders;
- Enable staging with Workspace storage on the Settings tab
- Table option = Auto create table on the Warehouse destination
Show the answer
Answer: B. A Pre-copy script on the Warehouse destination, such as DELETE FROM stg.Orders;
A Pre-copy script runs before each load, so a DELETE clears the staging table and Insert then writes only tonight's rows. Upsert inserts new rows and updates matching ones, but it never removes the rows left over from earlier runs.
Question 7
You manage an Eventhouse in which an operations dashboard queries an accelerated OneLake shortcut with the policy '{"IsEnabled": true, "Hot": "7.00:00:00", "MaxAge": "00:05:00"}'. During a capacity spike, the acceleration index has not been refreshed for 12 minutes, and a user runs a dashboard query. What happens?
- The query returns only the rows already in the hot cache and skips anything newer.
- The query fails with an error until the index catches up to within the MaxAge window.
- The query runs in non-accelerated mode, so it reads the current Delta data more slowly instead of returning stale cached results.
- The query returns the cached accelerated data from 12 minutes ago, along with a staleness warning.
Show the answer
Answer: C. The query runs in non-accelerated mode, so it reads the current Delta data more slowly instead of returning stale cached results.
MaxAge sets how old the index can get. Once it's older than that, the query falls back to non-accelerated mode rather than serve stale accelerated data. That's why returning the 12-minute-old cache is wrong: MaxAge exists to prevent exactly that.
Question 8
You are a data engineer. Last night's Dataflow Gen2 refresh shows Failed in its refresh history. The dataflow loads eight tables into a lakehouse, and you want to know which table caused the failure before you open the Power Query editor. What should you do?
- Open the Spark application detail page for the refresh and check the Item snapshots tab
- Open the Monitor hub, select Gantt view, and compare the refresh bar with earlier runs
- In the Capacity Metrics app, drill through to the timepoint of the refresh and look at the background operations
- In the dataflow's refresh history, select the failed refresh's Start time and check the status of each entry in the tables and activities list
Show the answer
Answer: D. In the dataflow's refresh history, select the failed refresh's Start time and check the status of each entry in the tables and activities list
The refresh detail view opened from the Start time lists every table and activity with its own status, so you can see which one failed. Item snapshots belong to notebook and Spark job definition runs and show code and parameter values, not the status of each dataflow table.
Question 9
You are a Fabric data engineer with the Viewer role in a workspace. A Dataflow Gen2 that reads from an on-premises SQL Server through an on-premises data gateway failed two days ago. The error text in the run details isn't enough, so you select Download detailed logs, but there are no mashup engine logs for that refresh. You need logs for future failures of this dataflow. What should you do?
- Ask the gateway admin to turn on Admin consent for gateway diagnostics on the gateway.
- Ask a workspace admin to give you the Contributor role, because Viewers can't download detailed logs.
- Copy the Request ID and Session ID into the dataflow editor to rebuild the mashup logs.
- Run the refresh again within 28 days, because detailed logs are deleted after 24 hours.
Show the answer
Answer: A. Ask the gateway admin to turn on Admin consent for gateway diagnostics on the gateway.
For refreshes that run on a gateway, detailed logs exist only if Admin consent for gateway diagnostics is turned on. The Viewer role is already enough to download them, and they stay available for 28 days, so neither your role nor the two-day age of the run is the problem.
Question 10
A data engineer uses Spark Structured Streaming in a Fabric notebook to load IoT events into a lakehouse Delta table in micro-batches every 30 seconds. Each micro-batch must commit within a few seconds to meet a strict latency target. After two weeks, queries on the table are slow because it holds thousands of small files. What should the engineer do to fix the small-file problem without breaking the write latency target?
- Partition the table by device_id so that each micro-batch writes to its own folder
- Keep auto compaction off and schedule a separate OPTIMIZE job on the table during a quiet window
- Run VACUUM on the table after every micro-batch to remove the small files
- Set 'delta.autoOptimize.autoCompact' = 'true' on the table so compaction runs after each write that leaves too many small files
Show the answer
Answer: B. Keep auto compaction off and schedule a separate OPTIMIZE job on the table during a quiet window
Auto compaction runs a synchronous OPTIMIZE as part of the write, so it adds time to the micro-batches and can break a strict latency target. For tight write targets, Microsoft's guidance is to schedule OPTIMIZE separately. VACUUM only deletes unreferenced old files, so it doesn't compact anything, and partitioning on a high-cardinality column like device_id makes the small-file problem worse.
The full timed mock
50 questions in 100 minutes, with a pass mark of 70%. Each paper is drawn from a bank of questions written for the mock alone, and these 10 are left out of it. Full access opens it, with every level of the course.
LevelUpWith is independent of the organisation that sets this exam. These questions were written for this course; they are not taken from the exam.