DEA-C02 Data Transformation Practice Question

Answer analysis

Option-by-option breakdown

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

• ✗

  INSERT INTO ... SELECT DISTINCT

  Why it's wrong here

  The INSERT statement cannot handle updates to existing records. It would only result in appending new rows, leading to duplicate entries if the key already exists in the target. This approach is inefficient for synchronization scenarios requiring state reconciliation between source and target datasets over time.

• ✓

  MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE...

  Why this is correct

  The MERGE command allows for conditional logic based on match status, which is ideal for deduplication and incremental updates. By defining specific matching criteria, it ensures that target table data remains accurate without creating duplicate rows, which is a common requirement in ETL/ELT pipelines.

• ✗

  UPDATE target SET ... FROM source

  Why it's wrong here

  While an UPDATE statement can modify records, it fails to handle new records that do not yet exist in the target table. An UPDATE-only strategy requires a separate INSERT statement to handle new data, which is less efficient than using a single atomic MERGE operation.

• ✗

  CREATE OR REPLACE TABLE target AS SELECT ...

  Why it's wrong here

  Replacing a table is destructive and ignores the existing data. It is not an incremental transformation strategy, as it requires reloading the entire dataset every time. This approach consumes excessive compute resources and causes downtime, making it unsuitable for production environments where data availability is critical.