DEA-C02 Data Movement 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.

• ✓

  Run VALIDATION_MODE = RETURN_ERRORS in a COPY INTO statement

  Why this is correct

  VALIDATION_MODE = RETURN_ERRORS parses the staged files and returns the rejected rows with their error messages without loading anything into the target table. It is exactly the dry-run mechanism for inspecting bad rows and confirming data quality before a real load. Because nothing is committed, it also avoids polluting load metadata or creating duplicates, which fits the pre-production validation goal.

• ✗

  Set ON_ERROR = CONTINUE and inspect the target table row counts

  Why it's wrong here

  ON_ERROR = CONTINUE skips bad rows and loads the rest, which introduces exactly the partial data the team wants to avoid before promotion. Row counts alone would not reveal which rows were rejected or why, and the load would already have modified the target. This option conflates error tolerance with validation and would leave the table in an unclean state during a pre-production check.

• ✗

  Use FORCE = TRUE to reload all files and compare checksums

  Why it's wrong here

  FORCE = TRUE bypasses load metadata and reloads files that may already be loaded, creating duplicates in the target table. Comparing checksums is a manual and indirect way to detect problems, and it does not report row-level rejection reasons. This option increases risk and effort while failing to provide the file-level and row-level diagnostics the team actually needs.

• ✓

  Query the COPY_HISTORY table function for the load's file-level results

  Why this is correct

  The COPY_HISTORY table function returns per-file load results, including row counts, status, and error details for recent COPY INTO operations. It lets the engineer confirm which files loaded and how many rows each contributed, satisfying the audit portion of the requirement. It complements validation by showing actual outcomes after a load attempt rather than predicting them beforehand.

• ✗

  Create a stream on the target table and read the change records

  Why it's wrong here

  A stream captures change data after rows are inserted, so it only helps once data has already landed in the table. It cannot show which staged files were parsed or why rows were rejected, and it does not provide pre-load validation. Using a stream here is a post-hoc observation technique that does not satisfy the requirement to validate before promoting the load.