Secure, monitor, and optimize data storage and data processing →hardMultiple ChoiceObjective-mapped

DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing

Exhibit

Refer to the exhibit.

```json
{
  "name": "CopyData",
  "properties": {
    "activities": [
      {
        "name": "Copy from Blob to SQL",
        "type": "Copy",
        "inputs": [{"referenceName": "BlobDataset", "type": "DatasetReference"}],
        "outputs": [{"referenceName": "SQLDataset", "type": "DatasetReference"}],
        "typeProperties": {
          "source": {
            "type": "BlobSource",
            "recursive": true
          },
          "sink": {
            "type": "SqlSink",
            "writeBatchSize": 10000,
            "preCopyScript": "TRUNCATE TABLE dbo.Staging"
          }
        }
      }
    ]
  }
}
```

You have an Azure Data Factory pipeline defined as shown. The pipeline is failing because the preCopyScript truncates the staging table before each run, but the table is empty on the first run. What change would you make to ensure the pipeline works correctly?

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

✓

Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.

The preCopyScript runs on every pipeline execution. On the first run, the staging table does not exist, so TRUNCATE TABLE dbo.Staging would fail. By adding a check with IF OBJECT_ID, the script only truncates if the table exists. This handles the first run gracefully. Option A is wrong because removing the preCopyScript would not truncate the table on subsequent runs, potentially causing duplicate data issues. Option B is wrong because increasing writeBatchSize does not solve the truncation error. Option D is wrong because setting recursive to false affects how the source dataset handles recursive file systems, not the truncation problem.

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 entirely.
Why it's wrong here
Without truncation, data may be appended, which might not be desired.
✗
Increase the writeBatchSize to 50000 to speed up the copy.
Why it's wrong here
Batch size does not fix the truncate error.
✓
Change the preCopyScript to: IF OBJECT_ID('dbo.Staging') IS NOT NULL TRUNCATE TABLE dbo.Staging.
Why this is correct
This conditional truncation prevents error when table is empty.
✗
Set recursive to false in the source.
Why it's wrong here
Recursive setting does not affect the truncate script.

Go deeper

DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question