Develop data processing →hardMultiple ChoiceObjective-mapped

DP-203 Develop data processing Practice Question

Exhibit

{
  "name": "CopyDataFromBlobToSynapse",
  "properties": {
    "activities": [
      {
        "name": "CopyData",
        "type": "Copy",
        "inputs": [{"referenceName": "BlobSource", "type": "DatasetReference"}],
        "outputs": [{"referenceName": "SynapseSink", "type": "DatasetReference"}],
        "typeProperties": {
          "source": {
            "type": "DelimitedTextSource",
            "storeSettings": {
              "type": "AzureBlobStorageReadSettings",
              "recursive": true
            }
          },
          "sink": {
            "type": "SqlPoolSink",
            "writeBatchSize": 10000,
            "preCopyScript": "TRUNCATE TABLE dbo.Sales"
          },
          "translator": {
            "type": "TabularTranslator",
            "mappings": [
              {"source": "ProductID", "sink": "ProductID"},
              {"source": "SaleDate", "sink": "SaleDate"},
              {"source": "Quantity", "sink": "Quantity"},
              {"source": "Amount", "sink": "Amount"}
            ]
          }
        }
      }
    ]
  }
}

Refer to the exhibit. You have an Azure Data Factory pipeline that copies data from a CSV file in Blob Storage to a Synapse dedicated SQL pool table named dbo.Sales. The pipeline fails. The error message indicates that the 'Amount' column in the sink table does not allow NULLs but the source contains NULL values. What is the best way to resolve this issue without losing data?

⚠ Common exam trap

Test-takers frequently assume a default value on the column will automatically replace NULLs during a bulk insert, but in Azure Synapse and most SQL databases, a default only applies when the column is not referenced in the INSERT statement, not when NULL is explicitly provided.

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

✓

Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0

A Mapping Data Flow with a Derived Column transformation allows you to replace NULL values in the 'Amount' column with a default value (e.g., 0) before writing to the Synapse dedicated SQL pool. This resolves the NULL constraint violation without losing any rows, as the data is transformed inline within the pipeline. The copy activity alone cannot perform such transformations, making the Mapping Data Flow the appropriate choice for this ETL scenario.

Answer analysis

Option-by-option breakdown

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

✓
Use a Mapping Data Flow with a Derived Column transformation to replace NULLs with 0
Why this is correct
Mapping Data Flow allows you to handle NULLs by providing a default value, ensuring data integrity.
✗
Add a filter in the copy activity to exclude rows with NULL Amount
Why it's wrong here
Filtering would lose data, which is not ideal.
✗
Modify the sink table to have a default value for the Amount column
Why it's wrong here
Setting a default value does not help if the source provides NULLs; the copy activity would still fail.
✗
Change the sink table column to allow NULLs
Why it's wrong here
This may not be permitted due to business requirements and could cause issues later.

Visual reference

Go deeper

DP-203 Develop data processing Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question