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.

• ✗

  Set the pipe to use a file format that includes a unique key column and rely on Snowflake's automatic deduplication.

  Why it's wrong here

  Snowflake does not automatically deduplicate data during ingestion based on a unique key column. The file format only defines how data is parsed, not how duplicates are handled. To prevent duplicates, the engineer must ensure files are not loaded multiple times, either by managing load history or by using a deduplication mechanism in the target table (e.g., MERGE). Relying on the file format is incorrect.

• ✗

  Recreate the pipe with a new name to reset the load history and start fresh.

  Why it's wrong here

  Recreating the pipe with a new name would reset the load history, but it would not prevent future duplicates; in fact, it could cause all existing files to be reloaded, worsening the problem. The load history is tied to the pipe name, so a new pipe would have no history and would attempt to load all files in the stage, leading to massive duplication. This is not a solution.

• ✗

  Enable the PURGE = TRUE option in the pipe definition.

  Why it's wrong here

  PURGE = TRUE deletes the source files from the stage after they are successfully loaded. While this prevents re-loading the same files, it does not address the root cause of duplicates if files are being loaded multiple times due to overlapping events or multiple pipes. Moreover, purging files may not be desirable if they need to be retained. The duplicate issue is more likely due to load history or event duplication, not the presence of files.

• ✓

  Ensure that the pipe's load history is properly maintained and that the cloud notification integration is configured to avoid duplicate event messages.

  Why this is correct

  Duplicate loads in Snowpipe often occur when the same file triggers multiple events or when the load history is not correctly tracked. Snowflake maintains a load history for each pipe for 14 days; if a file is loaded within that period, it is skipped. However, if events are duplicated (e.g., due to at-least-once delivery from Pub/Sub or S3 event notifications), the pipe may attempt to load the same file again. Ensuring the notification integration is configured to minimize duplicates and that load history is not cleared can prevent this. Additionally, using the COPY_HISTORY function to monitor can help identify issues.