mediumMultiple ChoiceObjective-mapped

DP-203 Practice Question: Refer to the exhibit

Exhibit

Azure Data Factory pipeline JSON snippet:

{
    "name": "CopyDataPipeline",
    "activities": [
        {
            "name": "CopyFromBlobToSQL",
            "type": "Copy",
            "inputs": [{"referenceName": "BlobDS", "type": "DatasetReference"}],
            "outputs": [{"referenceName": "SQLDS", "type": "DatasetReference"}],
            "typeProperties": {
                "source": {
                    "type": "BlobSource",
                    "recursive": true
                },
                "sink": {
                    "type": "SqlSink",
                    "writeBatchSize": 10000,
                    "preCopyScript": "TRUNCATE TABLE dbo.target"
                },
                "enableStaging": false,
                "translator": {
                    "type": "TabularTranslator",
                    "columnMappings": {
                        "Id": "Id",
                        "Name": "FullName"
                    }
                }
            }
        }
    ]
}

Refer to the exhibit. A data engineer notices that the target SQL table contains duplicate rows after a pipeline run. Which change to the pipeline configuration would prevent duplicates?

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

✓

Add a 'Upsert' setting in SqlSink with a key column

The preCopyScript truncates the table before copy, but if the pipeline is run multiple times and truncation fails or is skipped, duplicates can occur. Using upsert semantics or adding a watermark could help, but the simplest fix is to ensure truncation is reliable. However, the best practice is to use a merge/upsert pattern. Among the options, adding a surrogate key and using upsert is most effective.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Remove the 'preCopyScript'
Why it's wrong here
Removing truncation would actually increase the chance of duplicates on subsequent runs.
✗
Change 'writeBatchSize' to 5000
Why it's wrong here
Batch size affects performance but not duplicate prevention.
✓
Add a 'Upsert' setting in SqlSink with a key column
Why this is correct
Upsert ensures that rows are updated or inserted based on a key, preventing duplicates.
✗
Set 'recursive' to false
Why it's wrong here
Recursive setting controls whether to read files from subfolders; it does not affect duplicates.

Go deeper

DP-203 Practice Question: Refer to the exhibit

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question