Develop data processing →hardMultiple Choice

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
A Derived Column transformation evaluates an expression per row, replacing NULL 'Amount' values with 0 before the sink write. This satisfies the sink's NOT NULL constraint while preserving every source row, avoiding data loss that filtering or rejecting rows would cause.
✗
Add a filter in the copy activity to exclude rows with NULL Amount
Why it's wrong here
Filtering out NULL rows silently drops source records, which the requirement forbids. It is tempting because it removes the failing rows quickly, and would be correct if the business rule genuinely excluded those records from the sink rather than requiring them to be preserved.
✗
Modify the sink table to have a default value for the Amount column
Why it's wrong here
A default value only applies when a column is omitted from the INSERT, not when an explicit NULL is supplied, so the copy still fails. It tempts because defaults are the standard fix for missing values, and would be correct if the source rows lacked the Amount field entirely rather than containing NULL.
✗
Change the sink table column to allow NULLs
Why it's wrong here
Making the column nullable accepts the NULLs but stores them as NULL, so the 'Amount' values are not preserved as data — the stem demands no data loss. It tempts because relaxing a NOT NULL constraint is the quickest way to stop the copy failing, and would be correct if NULLs were genuinely valid business values.

Visual reference

Go deeper

DP-203 Develop data processing Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question