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.

• ✗

  Increasing the size of the virtual warehouse from Medium to 2X-Large.

  Why it's wrong here

  While a larger warehouse provides more threads, it cannot overcome the overhead of processing thousands of tiny files. The bottleneck is likely at the metadata and network request layer. Simply adding more compute will not efficiently solve the problem and will lead to significantly higher costs without a proportional performance gain.

• ✓

  Aggregating the small JSON files into fewer, larger files before initiating the COPY command.

  Why this is correct

  This is the most effective way to optimize ingestion. By reducing the total number of files, you decrease the overhead associated with listing and opening files in cloud storage. Larger files allow Snowflake to utilize its parallel processing capabilities more effectively, leading to much faster and more cost-efficient data movement.

• ✗

  Changing the target table's data retention period to 0 days during the load.

  Why it's wrong here

  Data retention (Time Travel) affects storage and the ability to recover data, but it has no impact on the performance of the COPY command itself. Disabling it will not reduce the time it takes to parse JSON files or manage the metadata overhead of a large number of small files.

• ✗

  Using the 'STRIP_OUTER_ARRAY = TRUE' file format option in the COPY command.

  Why it's wrong here

  This option is used for parsing JSON files that are wrapped in a top-level array. While necessary for correct parsing of certain JSON structures, it does not improve performance for a large number of small files. It is a functional setting rather than a performance optimization for bulk data movement.