Develop data processing →hardMultiple ChoiceObjective-mapped

DP-203 Develop data processing Practice Question

Exhibit

Refer to the exhibit.

{
  "name": "IncrementalLoad",
  "properties": {
    "activities": [
      {
        "name": "WatermarkQuery",
        "type": "Lookup",
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT MAX(LastModified) AS NewWatermark FROM source_table"
          },
          "dataset": {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        }
      },
      {
        "name": "CopyData",
        "type": "Copy",
        "dependsOn": [
          {
            "activity": "WatermarkQuery",
            "dependencyConditions": ["Succeeded"]
          }
        ],
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT * FROM source_table WHERE LastModified > '@{activity('WatermarkQuery').output.firstRow.NewWatermark}'"
          },
          "sink": {
            "type": "AzureSqlSink",
            "preCopyScript": "TRUNCATE TABLE target_table"
          }
        },
        "inputs": [
          {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        ],
        "outputs": [
          {
            "referenceName": "AzureSqlTable",
            "type": "DatasetReference"
          }
        ]
      }
    ]
  }
}

Refer to the exhibit. You have an Azure Data Factory pipeline that performs an incremental load from an Azure SQL Database source to a target Azure SQL Database. The pipeline uses a watermark column approach. After running the pipeline, you notice that the target table is empty. What is the most likely cause of this issue?

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

✓

The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.

The WatermarkQuery activity retrieves the maximum LastModified value from the source. If the CopyData activity's source query filters for rows where LastModified equals that value (e.g., using a parameter reference), it will only copy rows with that exact timestamp. In an incremental load scenario, the correct filter should be LastModified greater than the previous watermark value, not equal to the current maximum. Since the current maximum is the highest timestamp, no rows will have a timestamp greater than it, resulting in zero rows copied. Option A is wrong because the dependency condition 'Succeeded' is appropriate and the pipeline succeeded. Option B is wrong because the WatermarkQuery activity succeeded (no failure indicated). Option D is wrong because the WatermarkQuery activity runs before the CopyData activity due to the dependency, so no ordering issue exists.

Answer analysis

Option-by-option breakdown

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

✗
The dependency condition should be 'Completed' instead of 'Succeeded'.
Why it's wrong here
'Succeeded' is the correct condition for a successful lookup.
✗
The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
Why it's wrong here
The dependency condition is 'Succeeded', so if WatermarkQuery succeeded, CopyData runs.
✓
The watermark query returns the maximum LastModified value, but the copy query uses the same value to filter, resulting in zero rows.
Why this is correct
The copy query filters for rows where LastModified > NewWatermark, but NewWatermark is the maximum, so no rows satisfy the condition. The previous watermark should be stored and used.
✗
The CopyData activity runs before the WatermarkQuery activity completes.
Why it's wrong here
The dependsOn ensures CopyData runs after WatermarkQuery succeeds.

Go deeper

DP-203 Develop data processing Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question