Develop data processing →hardMultiple Choice

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?

⚠ Common exam trap

DP-203 often tests the watermark pattern; candidates may overlook the comparison operator in the filter query, assuming that using the watermark value directly is correct, but it must be a greater-than comparison.

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 most likely cause is that the watermark query returns the maximum LastModified value, and the copy query uses that same value to filter, resulting in zero rows. In a watermark-based incremental load, the copy query should filter for rows where the watermark column is greater than the last watermark value, not equal to it. Using the same value would only capture rows with that exact timestamp, which may be none if no new rows have that exact value.

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
Changing the dependency condition to 'Completed' would let CopyData proceed after a failed WatermarkQuery, but the exhibit shows both activities succeeding, so no dependency change alters the outcome; the empty target comes from the watermark value equalling the source maximum. 'Completed' matters only when a prior activity fails but downstream work must still run.
✗
The WatermarkQuery activity failed, causing the CopyData activity to be skipped.
Why it's wrong here
A failed WatermarkQuery activity would surface as a pipeline error, and the exhibit shows the pipeline completing; the empty target stems from the watermark value being read as the current maximum, producing a zero-row slice. Watermark queries are the correct mechanism when the stored watermark genuinely lags the source data.
✓
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
Using the same watermark value in both the retrieval and the filter predicate excludes every row, because the copy query selects records strictly greater than that maximum. The watermark must be stored before the run and compared against the new maximum.
✗
The CopyData activity runs before the WatermarkQuery activity completes.
Why it's wrong here
Azure Data Factory activities joined by a success dependency cannot run concurrently, so the CopyData activity cannot start before WatermarkQuery completes; the empty target arises from the watermark variable resolving to the current maximum, yielding no rows. Sequencing concerns apply only to pipelines lacking dependency conditions.

Go deeper

DP-203 Develop data processing Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question