A reporting team frequently joins a large Orders table with a small Customers table in T-SQL to generate daily reports. The join is slow and consumes significant resources. You need to improve query performance for the read-heavy reporting workload. What should you do?

Create a denormalized table that includes customer attributes in the Orders table.

Use a Dataflow Gen2 to pre-join the tables and load the result into a new table.

Create an index on the Customers table's CustomerID column.

Use a KQL query to join the tables.

A Fabric warehouse has a table 'Sales' with columns: Region, Product, UnitsSold. The sales team wants a report that shows total units sold per region and per product, with subtotals for each region. The report should be generated using T-SQL. What should you do?

Use GROUP BY CUBE(Region, Product)

Use GROUP BY ROLLUP(Region, Product)

Use GROUP BY GROUPING SETS((Region, Product), (Region))

Which of the following is a common technique used to denormalize data?

Pre-joining tables and storing the result

Splitting a table into multiple tables

A data engineer is processing a batch of customer records in a PySpark notebook. The data contains duplicate customer IDs, and the engineer must keep only the most recent record for each customer based on the LastUpdated timestamp. Which PySpark operation should be used?

Use groupBy('CustomerID').max('LastUpdated').

Use dropDuplicates(['CustomerID']) on the DataFrame.

Use window function with row_number() partitioned by CustomerID ordered by LastUpdated desc, then filter row_number == 1.

Use distinct() on the DataFrame.

A daily batch pipeline aggregates orders by day. Due to network issues, some orders for day D arrive on day D+1. The pipeline currently only processes the current day's data, so these late orders are missed. You need to ensure the aggregation for day D includes late orders without reprocessing the entire history. What should you do?

Add late orders to the next day's aggregation.

Ignore late orders and only process the current day's data.

Modify the pipeline to reprocess the previous day's aggregation whenever new data arrives for that day.

Change the pipeline to process a rolling 7-day window every day.

Microsoft Certified:Fabric Data Engineer Associate

Domain 2Objective 2

Ingest and Transform Batch Data DP-700 Practice Questions (Page 8)

Part of the Ingest and transform data domain, which accounts for 30–35% of the DP-700 exam. Microsoft does not publish an official question count, but from its 100-minute exam (~40–65 total, ~12–23 in this domain), expect 4–8 from this objective — we provide 48 practice questions to prepare you well beyond it. (estimate)

48questions here

10free pages

13concepts

30–35%of the exam

Questions 36–40

36application · medium
A reporting team frequently joins a large Orders table with a small Customers table in T-SQL to generate daily reports. The join is slow and consumes significant resources. You need to improve query performance for the read-heavy reporting workload. What should you do?
Select an answer first
37application · medium
A Fabric warehouse has a table 'Sales' with columns: Region, Product, UnitsSold. The sales team wants a report that shows total units sold per region and per product, with subtotals for each region. The report should be generated using T-SQL. What should you do?
Select an answer first
38foundation · easy
Which of the following is a common technique used to denormalize data?
Select an answer first
39application · medium
A data engineer is processing a batch of customer records in a PySpark notebook. The data contains duplicate customer IDs, and the engineer must keep only the most recent record for each customer based on the LastUpdated timestamp. Which PySpark operation should be used?
Select an answer first
40expert · hard
A daily batch pipeline aggregates orders by day. Due to network issues, some orders for day D arrive on day D+1. The pipeline currently only processes the current day's data, so these late orders are missed. You need to ensure the aggregation for day D includes late orders without reprocessing the entire history. What should you do?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-700

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-700” is a trademark of its owner, used for identification only.