mediumMultiple Choice

DP-203 Practice Question: Refer to the exhibit

Exhibit

{
  "name": "CopyDataPipeline",
  "properties": {
    "activities": [
      {
        "name": "CopyData",
        "type": "Copy",
        "inputs": [{"name": "InputDataset"}],
        "outputs": [{"name": "OutputDataset"}],
        "typeProperties": {
          "source": {
            "type": "AzureSqlSource",
            "sqlReaderQuery": "SELECT * FROM Orders WHERE OrderDate > '2024-01-01'"
          },
          "sink": {
            "type": "DelimitedTextSink",
            "storeSettings": {
              "type": "AzureBlobFSWriteSettings",
              "copyBehavior": "PreserveHierarchy"
            }
          },
          "enableStaging": false,
          "translator": {
            "type": "TabularTranslator",
            "columnMappings": {
              "OrderID": "order_id",
              "CustomerID": "customer_id",
              "OrderDate": "order_date"
            }
          }
        }
      }
    ]
  }
}

Refer to the exhibit. A data engineer wants to copy only new orders from an Azure SQL database to Azure Data Lake Storage Gen2. The pipeline runs daily at midnight. What should be added to the pipeline to ensure incremental loads?

⚠ Common exam trap

The trap is confusing post-copy deduplication with true incremental extraction; candidates might think filtering after copy is sufficient, but it still transfers all data and does not scale.

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 Lookup activity to get the last run timestamp and modify the query to use it.

To perform incremental loads from Azure SQL Database to ADLS Gen2, the pipeline must know the last successful run timestamp to fetch only new records. A Lookup activity can retrieve the last run timestamp from a control table or a file, and then the copy activity's source query can be parameterized to filter records based on that timestamp. This ensures only new orders are copied daily.

Answer analysis

Option-by-option breakdown

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

✗
Add a filter activity after the copy to remove duplicates.
Why it's wrong here
A filter activity after the copy removes rows already written, so every run still reads the full source table and duplicates are only discarded post-transfer; a watermark column compared against the last load is what restricts extraction. It is tempting because filtering is a genuine data-quality step, and would suit deduplicating a static dataset.
✓
Use a Lookup activity to get the last run timestamp and modify the query to use it.
Why this is correct
A Lookup activity retrieves the stored last-run timestamp from a control table, and the source query filters orders newer than that value, so each daily run copies only new rows. This watermark pattern delivers the incremental load the stem requires.
✗
Enable staging with a staging table.
Why it's wrong here
Staging tables buffer data during a copy but track no high-water mark, so every run re-reads the full orders table rather than only new rows. They suit bulk loading into a warehouse before transformation, not identifying deltas; a watermark column compared against the last load is what enables incremental extraction.
✗
Change the copy behavior to 'MergeFiles'.
Why it's wrong here
MergeFiles only controls how the copy activity writes multiple output files into one destination file; it does not track which rows were previously loaded, so the full source is copied each run. It is tempting because it sounds like merging data, and would be correct when consolidating many small files into a single target.

Go deeper

DP-203 Practice Question: Refer to the exhibit

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question