mediumMultiple ChoiceObjective-mapped
DP-203 Practice Question: Your company runs a critical data pipeline using…
Your company runs a critical data pipeline using Azure Data Factory (ADF) that ingests data from multiple sources into an Azure Synapse Analytics dedicated SQL pool. Recently, you have observed that the pipeline frequently fails with the error: 'Operation for target table failed: 'Cannot insert duplicate key row in object 'dbo.FactSales' with unique index 'PK_FactSales'. The duplicate key value is (20241001, 12345).'' The pipeline uses a Copy activity with a stored procedure sink that merges data into the fact table. The fact table has a clustered columnstore index and a unique constraint on (DateKey, ProductKey). You need to modify the pipeline to handle duplicates without losing data and without impacting performance significantly. What should you do?
⚠ Common exam trap
The trap here is that candidates often overcomplicate the solution by choosing a manual staging table approach (Option C) or a destructive pre-copy script (Option D), not realizing that ADF's native upsert feature is designed specifically to handle duplicate key violations in a performant and atomic manner.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Configure the Copy activity sink to use 'upsert' behavior with the unique key columns.
Azure Data Factory's Copy activity supports native upsert behavior when using a stored procedure sink, allowing it to handle duplicate key violations by updating existing rows instead of failing. By specifying the unique key columns (DateKey, ProductKey) in the upsert configuration, the pipeline can merge incoming data into the fact table without requiring manual staging or pre-cleanup, minimizing performance impact by leveraging the existing clustered columnstore index and unique constraint.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Configure the Copy activity sink to use 'upsert' behavior with the unique key columns.
Why this is correct
ADF's upsert uses the source to update matching rows and insert new ones, avoiding duplicate key violations.
- ✗
Change the distribution of the fact table to round-robin and remove the unique constraint.
Why it's wrong here
Removing the unique constraint allows duplicates, which is not acceptable for data integrity, and round-robin distribution may degrade performance.
- ✗
Use a staging table and then execute a T-SQL MERGE statement to update or insert.
Why it's wrong here
This approach works but is less efficient than using ADF's built-in upsert, and adds complexity.
- ✗
Add a pre-copy script to delete existing rows that match the incoming data before the copy.
Why it's wrong here
Deleting rows before insert may remove data that is not identical to incoming duplicates, causing data loss.
Go deeper
Related to this question
Learn chapter
Introduction to Azure Data Engineering
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
Key term
Azure Data Factory
Azure Data Factory is a cloud-based data integration service that lets you create, schedule, and orchestrate data pipelines to move and transform data from various sources to destinations.
About these practice questions
One of 760 original DP-203 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.