Crucial Exams

Microsoft Fabric Data Engineer Associate DP-700 Practice Question

A stored procedure in a Microsoft Fabric warehouse runs this statement to upsert rows from StgSales into DimCustomer:

MERGE dbo.DimCustomer AS tgt
USING dbo.StgSales AS src
    ON tgt.CustomerID = src.CustomerID
WHEN MATCHED THEN
    UPDATE SET tgt.City = src.City, tgt.Region = src.Region
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, City, Region)
    VALUES (src.CustomerID, src.City, src.Region);

Execution fails with the error: "The MERGE statement attempted to UPDATE or DELETE the same row more than once. A target row matched more than one source row."
You must correct the T-SQL so the procedure succeeds while still performing the required updates and inserts.

Which change should you make to the statement?

  • Add the table hint WITH (NOLOCK) to dbo.StgSales in the USING clause.

  • Rewrite the USING clause to select DISTINCT CustomerID, City, Region from dbo.StgSales before the MERGE is executed.

  • Execute SET IDENTITY_INSERT dbo.DimCustomer ON immediately before running the MERGE.

  • Replace the MERGE with an INSERT statement that uses the ON ERROR clause to ignore conflicts.

Microsoft Fabric Data Engineer Associate DP-700
Monitor and optimize an analytics solution
Your Score:
Settings & Objectives
Random Mixed
Questions are selected randomly from all chosen topics, with a preference for those you haven’t seen before. You may see several questions from the same objective or domain in a row.
Rotate by Objective
Questions cycle through each objective or domain in turn, helping you avoid long streaks of questions from the same area. You may see some repeat questions, but the distribution will be more balanced across topics.

Check or uncheck an objective to set which questions you will receive.

Wipe Score
Bash, the Crucial Exams Chat Bot
AI Bot